chenjiahe
2022-12-16 5bfcd30741d716cb46bb4540051c3bed65737189
提交 | 用户 | age
5c5945 1 package com.hx.util;
E 2
3 import com.hx.exception.TipsException;
cac339 4 import org.apache.poi.POIXMLDocument;
5c5945 5 import org.apache.poi.hssf.usermodel.*;
cac339 6 import org.apache.poi.openxml4j.opc.OPCPackage;
C 7 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
5c5945 8 import org.apache.poi.ss.usermodel.Cell;
cac339 9 import org.apache.poi.ss.usermodel.Row;
C 10 import org.apache.poi.ss.usermodel.Sheet;
11 import org.apache.poi.ss.usermodel.Workbook;
c6eec8 12 import org.apache.poi.xssf.usermodel.XSSFCell;
F 13 import org.apache.poi.xssf.usermodel.XSSFRow;
14 import org.apache.poi.xssf.usermodel.XSSFSheet;
cac339 15 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
5c5945 16 import org.springframework.web.multipart.MultipartFile;
c6eec8 17
6b6bf5 18 import javax.imageio.ImageIO;
W 19 import java.awt.image.BufferedImage;
c6eec8 20 import java.io.*;
F 21 import java.text.DateFormat;
22 import java.text.SimpleDateFormat;
23 import java.util.ArrayList;
24 import java.util.Date;
25 import java.util.List;
26 import java.util.Map;
5c5945 27
E 28
29 /**
cac339 30  *
5c5945 31  * @author hjr
E 32  */
33 public final class ExcelUtil {
34
35     /**
36      * @param excelName
37      *         文件名称
38      * @param outPath
39      *             保存路径
40      * @param headList
41      *        Excel文件Head标题集合
42      * @param fieldList
43      *        Excel文件Field标题集合 根据field来寻找位置填充表格
44      * @param dataList
45      *        Excel文件数据内容部分
46      * @throws Exception
47      */
48     public static String createExcel(String outPath, String excelName,
cac339 49                                      String[] headList, String[] fieldList,
C 50                                      List<Map<String, Object>> dataList) throws Exception {
5c5945 51
E 52         String filePath = null;
53         // 创建新的Excel 工作簿
54         HSSFWorkbook workbook = new HSSFWorkbook();
55
56         // 在Excel工作簿中建一工作表,其名为缺省值
57         // 如要新建一名为"效益指标"的工作表,其语句为:
58         // HSSFSheet sheet = workbook.createSheet("效益指标");
59         HSSFSheet sheet = workbook.createSheet();
60         // 在索引0的位置创建行(最顶端的行)
61         HSSFRow row = sheet.createRow(0);
62         // ===============================================================
63         for (int i = 0; i < headList.length; i++) {
64
65             // 在索引0的位置创建单元格(左上端)
66             HSSFCell cell = row.createCell(i);
67             // 定义单元格为字符串类型
68             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
69             // 在单元格中输入一些内容
70             cell.setCellValue(headList[i]);
71         }
72         // ===============================================================
73         if (dataList != null) {
74             for (int n = 0; n < dataList.size(); n++) {
75                 // 在索引1的位置创建行
76                 HSSFRow row_value = sheet.createRow(n + 1);
77                 Map<String, Object> dataMap = dataList.get(n);
78                 // ===============================================================
79                 for (int i = 0; i < fieldList.length; i++) {
80                     // 在索引0的位置创建单元格(左上端)
81                     HSSFCell cell = row_value.createCell(i);
82                     // 定义单元格为字符串类型
83                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
84                     // 在单元格中输入一些内容
85                     cell.setCellValue(objToString(dataMap.get(fieldList[i])));
86                 }
87                 // ===============================================================
88             }
89         }
90
91         // 新建一输出文件流
c6eec8 92         File file = SimpleTool.createFile(outPath, excelName);
5c5945 93         FileOutputStream fOut = new FileOutputStream(file);
E 94         // 把相应的Excel 工作簿存盘
95         workbook.write(fOut);
96         fOut.flush();
97         // 操作结束,关闭文件
98         fOut.close();
99
100         if(outPath.endsWith("/")){
101             filePath = outPath + excelName;
102         }else{
103             filePath = outPath +"/"+ excelName;
104         }
105         return filePath;
106     }
107
db7fc9 108     /**生成临时文件
C 109      * @param headList
110      *        Excel文件Head标题集合
111      * @param fieldList
112      *        Excel文件Field标题集合 根据field来寻找位置填充表格
113      * @param dataList
114      *        Excel文件数据内容部分
115      * @throws Exception
116      */
117     public static File createExcel(String[] headList, String[] fieldList,
cac339 118                                    List<Map<String, Object>> dataList) throws Exception {
db7fc9 119         File file = File.createTempFile("temp", ".xls");
C 120         try{
121             // 创建新的Excel 工作簿
122             HSSFWorkbook workbook = new HSSFWorkbook();
123
124             // 在Excel工作簿中建一工作表,其名为缺省值
125             // 如要新建一名为"效益指标"的工作表,其语句为:
126             // HSSFSheet sheet = workbook.createSheet("效益指标");
127             HSSFSheet sheet = workbook.createSheet();
128             // 在索引0的位置创建行(最顶端的行)
129             HSSFRow row = sheet.createRow(0);
130             // ===============================================================
131             for (int i = 0; i < headList.length; i++) {
132
133                 // 在索引0的位置创建单元格(左上端)
134                 HSSFCell cell = row.createCell(i);
135                 // 定义单元格为字符串类型
136                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
137                 // 在单元格中输入一些内容
138                 cell.setCellValue(headList[i]);
139             }
140             // ===============================================================
141             if (dataList != null) {
142                 for (int n = 0; n < dataList.size(); n++) {
143                     // 在索引1的位置创建行
144                     HSSFRow row_value = sheet.createRow(n + 1);
145                     Map<String, Object> dataMap = dataList.get(n);
146                     // ===============================================================
147                     for (int i = 0; i < fieldList.length; i++) {
148                         // 在索引0的位置创建单元格(左上端)
149                         HSSFCell cell = row_value.createCell(i);
150                         // 定义单元格为字符串类型
151                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
152                         // 在单元格中输入一些内容
153                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
154                     }
155                     // ===============================================================
156                 }
157             }
158
159             // 新建一输出文件流
160             FileOutputStream fOut = new FileOutputStream(file);
161             // 把相应的Excel 工作簿存盘
162             workbook.write(fOut);
163             fOut.flush();
164             // 操作结束,关闭文件
165             fOut.close();
166         }catch (Exception e){
167
168         }finally {
169             file.deleteOnExit();
170         }
171         return file;
172     }
173
49f28b 174     /**生成临时文件
C 175      * @param headList
176      *        Excel文件Head标题集合
177      * @param fieldList
178      *        Excel文件Field标题集合 根据field来寻找位置填充表格
179      * @param dataList
180      *        Excel文件数据内容部分
181      * @throws Exception
182      */
183     public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
184             ,Integer height,Integer width) throws Exception {
185         File file = File.createTempFile("temp", ".xls");
186         try{
187
188             if(height == null){
189                 height = 450;
190             }
191             if(width == null){
192                 width = 5000;
193             }
194             // 创建新的Excel 工作簿
195             HSSFWorkbook workbook = new HSSFWorkbook();
196
197             //合并的单元格样式
198             HSSFCellStyle boderStyle = workbook.createCellStyle();
199             //垂直居中
200             boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
201             boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
202
203             // 在Excel工作簿中建一工作表,其名为缺省值
204             // 如要新建一名为"效益指标"的工作表,其语句为:
205             // HSSFSheet sheet = workbook.createSheet("效益指标");
206             HSSFSheet sheet = workbook.createSheet();
207             // 在索引0的位置创建行(最顶端的行)
208             HSSFRow row = sheet.createRow(0);
209             // ===============================================================
210             for (int i = 0; i < headList.length; i++) {
211                 //高度
212                 row.setHeight(height.shortValue());
213                 sheet.setColumnWidth(i,width);
214                 // 在索引0的位置创建单元格(左上端)
215                 HSSFCell cell = row.createCell(i);
216                 // 定义单元格为字符串类型
217                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
218                 // 在单元格中输入一些内容
219                 cell.setCellValue(headList[i]);
220                 cell.setCellStyle(boderStyle);
221             }
222             // ===============================================================
223             if (dataList != null) {
224                 for (int n = 0; n < dataList.size(); n++) {
225                     // 在索引1的位置创建行
226                     HSSFRow row_value = sheet.createRow(n + 1);
227                     row_value.setHeight(height.shortValue());
228                     Map<String, Object> dataMap = dataList.get(n);
229                     // ===============================================================
230                     for (int i = 0; i < fieldList.length; i++) {
231                         // 在索引0的位置创建单元格(左上端)
232                         sheet.setColumnWidth(i,width);
233                         HSSFCell cell = row_value.createCell(i);
234                         // 定义单元格为字符串类型
235                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
236                         // 在单元格中输入一些内容
237                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
238                         cell.setCellStyle(boderStyle);
239                     }
240                     // ===============================================================
241                 }
242             }
243
244             // 新建一输出文件流
245             FileOutputStream fOut = new FileOutputStream(file);
246             // 把相应的Excel 工作簿存盘
247             workbook.write(fOut);
248             fOut.flush();
249             // 操作结束,关闭文件
250             fOut.close();
251         }catch (Exception e){
252
253         }finally {
254             file.deleteOnExit();
255         }
256         return file;
257     }
258
db7fc9 259
5c5945 260     private static String objToString(Object obj) {
E 261         if (obj == null) {
262             return "";
263         } else {
264             if (obj instanceof String) {
265                 return (String) obj;
266             } else if (obj instanceof Date) {
267                 return null;// DateUtil.dateToString((Date)
cac339 268                 // obj,DateUtil.DATESTYLE_SHORT_EX);
5c5945 269             } else {
E 270                 return obj.toString();
271             }
272         }
273     }
274
275     /**
c6eec8 276      * 读取Excel数据
F 277      * @param file
278      * @param header
279      * @return
280      * @throws Exception
281      */
282     public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {
283
284         String fileName = file.getOriginalFilename();
285         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
286             throw new TipsException("上传文件格式不正确");
287         }
288
289         //判断不同格式处理方法不同
290         if(fileName.matches("^.+\\.(?i)(xls)$")){
291             //xls格式使用HSSF
292             return readExcelByeFileData(file, header);
293         }else{
294             //xlsx格式使用XSSF
295             return readExcelByeFileDataToXSSF(file, header);
296         }
297     }
298
299     /**
5c5945 300      * 读取 Excel文件内容
E 301      *
302      * @param file
303      * @param header 是否包括表头
304      * @return
305      * @throws Exception
306      */
307     public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
308
309         String fileName = file.getOriginalFilename();
310         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
311             throw new TipsException("上传文件格式不正确");
312         }
313
314         // 结果集
315         List<List<String>> list = new ArrayList<>();
316
317         HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
318
319         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
320         for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
321             HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
322             int col = 0;
323             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
324             for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
325                 HSSFRow hssfrow = hssfsheet.getRow(j);
326                 if(hssfrow!=null){
327                     if(j == 0) {
328                         col = hssfrow.getPhysicalNumberOfCells();
329                         if(!header) {
330                             //不包括表头
331                             continue;
332                         }
333                     }
334                     // 单行数据
335                     List<String> arrayString = new ArrayList<>();
336                     for (int i = 0; i < col; i++) {
337                         HSSFCell cell = hssfrow.getCell(i);
338                         if (cell == null) {
339                             arrayString.add("");
340                         } else if (cell.getCellType() == 0) {
341                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
342                             if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
343                                 short format = cell.getCellStyle().getDataFormat();
344                                 if(format == 14 || format == 31 || format == 57 || format == 58){
345                                     //日期(中文时间格式的)
346                                     Date d = cell.getDateCellValue();
347                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
348                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
349                                     arrayString.add(formater.format(d));
350                                     //arrayString[i] = formater.format(d);
351                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
352                                     Date d = cell.getDateCellValue();
353                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
354                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
355                                     arrayString.add(formater.format(d));
356                                     //arrayString[i] = formater.format(d);
357                                 } else {
358                                     if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
359                                         arrayString.add(cell.getStringCellValue());
360                                         //arrayString[i] =cell.getStringCellValue();
361                                     }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
362                                         arrayString.add(cell.getCellFormula());
363                                         //arrayString[i] =cell.getCellFormula();
364                                     }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
365                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
366                                         arrayString.add(dataFormatter.formatCellValue(cell));
367                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
368                                     }
369                                 }
370                             }
371                         } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
372                             arrayString.add("");
373                             //arrayString[i] = "";
374                         } else { // 如果EXCEL表格中的数据类型为字符串型
b77bfb 375                             if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
F 376                                 arrayString.add(cell.getStringCellValue().trim());
377                             }else{
378                                 arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
379                             }
5c5945 380                             //arrayString[i] = cell.getStringCellValue().trim();
E 381                         }
382                     }
383                     list.add(arrayString);
384                 }
385             }
386         }
387         return list;
388     }
389
c6eec8 390     /**
F 391      * 读取 Excel文件内容
392      *
393      * @param file
394      * @param header 是否包括表头
395      * @return
396      * @throws Exception
397      */
398     public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {
399
400         String fileName = file.getOriginalFilename();
401         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
402             throw new TipsException("上传文件格式不正确");
403         }
404
405         // 结果集
406         List<List<String>> list = new ArrayList<>();
407
408         XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
409
410         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
411         for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
412             XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
413             int col = 0;
414             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
415             for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
416                 XSSFRow xssfrow = xssfSheet.getRow(j);
417                 if(xssfrow!=null){
418                     if(j == 0) {
419                         col = xssfrow.getPhysicalNumberOfCells();
420                         if(!header) {
421                             //不包括表头
422                             continue;
423                         }
424                     }
425                     // 单行数据
426                     List<String> arrayString = new ArrayList<>();
427                     for (int i = 0; i < col; i++) {
428                         XSSFCell cell = xssfrow.getCell(i);
429                         if (cell == null) {
430                             arrayString.add("");
431                         } else if (cell.getCellType() == 0) {
432                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
b77bfb 433                             if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
c6eec8 434                                 short format = cell.getCellStyle().getDataFormat();
F 435                                 if(format == 14 || format == 31 || format == 57 || format == 58){
436                                     //日期(中文时间格式的)
437                                     Date d = cell.getDateCellValue();
438                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
439                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
440                                     arrayString.add(formater.format(d));
441                                     //arrayString[i] = formater.format(d);
442                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
443                                     Date d = cell.getDateCellValue();
444                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
445                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
446                                     arrayString.add(formater.format(d));
447                                     //arrayString[i] = formater.format(d);
448                                 } else {
b77bfb 449                                     if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
c6eec8 450                                         arrayString.add(cell.getStringCellValue());
F 451                                         //arrayString[i] =cell.getStringCellValue();
b77bfb 452                                     }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
c6eec8 453                                         arrayString.add(cell.getCellFormula());
F 454                                         //arrayString[i] =cell.getCellFormula();
b77bfb 455                                     }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
c6eec8 456                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
F 457                                         arrayString.add(dataFormatter.formatCellValue(cell));
458                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
459                                     }
460                                 }
461                             }
462                         } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
463                             arrayString.add("");
464                             //arrayString[i] = "";
465                         } else { // 如果EXCEL表格中的数据类型为字符串型
b77bfb 466                             if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
F 467                                 arrayString.add(cell.getStringCellValue().trim());
468                             }else{
469                                 arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
470                             }
471
472
c6eec8 473                             //arrayString[i] = cell.getStringCellValue().trim();
F 474                         }
475                     }
476                     list.add(arrayString);
477                 }
478             }
479         }
480         return list;
481     }
482
cac339 483
C 484     /**判断excel的版本*/
485     public static Workbook create(InputStream inp) throws Exception {
486         if (!inp.markSupported()) {
487             inp = new PushbackInputStream(inp, 8);
488         }
489         if (POIFSFileSystem.hasPOIFSHeader(inp)) {
490             return new HSSFWorkbook(inp);
491         }
492         if (POIXMLDocument.hasOOXMLHeader(inp)) {
493             return new XSSFWorkbook(OPCPackage.open(inp));
494         }
495         throw new IllegalArgumentException("你的excel版本目前poi解析不了");
496     }
497
498     /**读取excel文件,兼容2003和2007
499      * 通过流读取Excel文件
500      * @return
501      * @throws Exception
502      */
3f4072 503     public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception {
C 504         try {
505
506             String fileName = file.getName();
507             if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
508                 throw new TipsException("上传文件格式不正确");
509             }
510
511             // 结果集
512             List<List<String>> list = new ArrayList<>();
513             Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
514
515             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
516             for(int s=0;s<book.getNumberOfSheets();s++) {
517                 Sheet hssfsheet = book.getSheetAt(s);
518                 int col = 0;
519                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
520                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
521                     Row hssfrow = hssfsheet.getRow(j);
522                     if(hssfrow!=null){
523                         if(j == 0) {
524                             col = hssfrow.getPhysicalNumberOfCells();
525                             if(!header) {
526                                 //不包括表头
527                                 continue;
528                             }
529                         }
530                         // 单行数据
531                         List<String> arrayString = new ArrayList<>();
532                         for (int i = 0; i < col; i++) {
533                             Cell cell = hssfrow.getCell(i);
534                             if (cell == null) {
535                                 arrayString.add("");
536                             } else if (cell.getCellType() == 0) {
537                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
538                                 if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
539                                     short format = cell.getCellStyle().getDataFormat();
540                                     if(format == 14 || format == 31 || format == 57 || format == 58){
541                                         //日期(中文时间格式的)
542                                         Date d = cell.getDateCellValue();
543                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
544                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
545                                         arrayString.add(formater.format(d));
546                                         //arrayString[i] = formater.format(d);
547                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
548                                         Date d = cell.getDateCellValue();
549                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
550                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
551                                         arrayString.add(formater.format(d));
552                                         //arrayString[i] = formater.format(d);
553                                     } else {
554                                         if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
555                                             arrayString.add(cell.getStringCellValue());
556                                             //arrayString[i] =cell.getStringCellValue();
557                                         }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
558                                             arrayString.add(cell.getCellFormula());
559                                             //arrayString[i] =cell.getCellFormula();
560                                         }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
561                                             HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
562                                             arrayString.add(dataFormatter.formatCellValue(cell));
563                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
564                                         }
565                                     }
566                                 }
567                             } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
568                                 arrayString.add("");
569                                 //arrayString[i] = "";
570                             } else { // 如果EXCEL表格中的数据类型为字符串型
571                                 arrayString.add(cell.getStringCellValue().trim());
572                                 //arrayString[i] = cell.getStringCellValue().trim();
573                             }
574                         }
575                         list.add(arrayString);
576                     }
577                 }
578             }
579             return list;
580         } catch (Exception e) {
581             e.printStackTrace();
582         }
583         return null;
584     }
585
586
587
588
589     /**读取excel文件,兼容2003和2007
590      * 通过流读取Excel文件
591      * @return
592      * @throws Exception
593      */
cac339 594     public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
C 595         try {
596
597             String fileName = file.getOriginalFilename();
598             if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
599                 throw new TipsException("上传文件格式不正确");
600             }
601
602             // 结果集
603             List<List<String>> list = new ArrayList<>();
604             Workbook book = create(new BufferedInputStream(file.getInputStream()));
605
606             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
607             for(int s=0;s<book.getNumberOfSheets();s++) {
608                 Sheet hssfsheet = book.getSheetAt(s);
609                 int col = 0;
610                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
611                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
612                     Row hssfrow = hssfsheet.getRow(j);
613                     if(hssfrow!=null){
614                         if(j == 0) {
615                             col = hssfrow.getPhysicalNumberOfCells();
616                             if(!header) {
617                                 //不包括表头
618                                 continue;
619                             }
620                         }
621                         // 单行数据
622                         List<String> arrayString = new ArrayList<>();
623                         for (int i = 0; i < col; i++) {
624                             Cell cell = hssfrow.getCell(i);
625                             if (cell == null) {
626                                 arrayString.add("");
627                             } else if (cell.getCellType() == 0) {
628                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
629                                 if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
630                                     short format = cell.getCellStyle().getDataFormat();
631                                     if(format == 14 || format == 31 || format == 57 || format == 58){
632                                         //日期(中文时间格式的)
633                                         Date d = cell.getDateCellValue();
634                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
635                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
636                                         arrayString.add(formater.format(d));
637                                         //arrayString[i] = formater.format(d);
638                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
639                                         Date d = cell.getDateCellValue();
640                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
641                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
642                                         arrayString.add(formater.format(d));
643                                         //arrayString[i] = formater.format(d);
644                                     } else {
645                                         if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
646                                             arrayString.add(cell.getStringCellValue());
647                                             //arrayString[i] =cell.getStringCellValue();
648                                         }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
649                                             arrayString.add(cell.getCellFormula());
650                                             //arrayString[i] =cell.getCellFormula();
651                                         }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
652                                             HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
653                                             arrayString.add(dataFormatter.formatCellValue(cell));
654                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
655                                         }
656                                     }
657                                 }
658                             } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
659                                 arrayString.add("");
660                                 //arrayString[i] = "";
661                             } else { // 如果EXCEL表格中的数据类型为字符串型
662                                 arrayString.add(cell.getStringCellValue().trim());
663                                 //arrayString[i] = cell.getStringCellValue().trim();
664                             }
665                         }
666                         list.add(arrayString);
667                     }
668                 }
669             }
670             return list;
671         } catch (Exception e) {
672             e.printStackTrace();
673         }
674         return null;
675     }
676
677     private static String getCellVal(Cell cell) {
678         if (null == cell) {
679             return "";
680         }
681         switch (cell.getCellType()) {
682             // 数字
683             case HSSFCell.CELL_TYPE_NUMERIC:
684                 // 日期格式的处理
685                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
686                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
687                     return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
688                 }
689                 return String.valueOf(cell.getNumericCellValue());
690             // 字符串
691             case HSSFCell.CELL_TYPE_STRING:
692                 return cell.getStringCellValue();
693             // 公式
694             case HSSFCell.CELL_TYPE_FORMULA:
695                 return cell.getCellFormula();
696             // 空白
697             case HSSFCell.CELL_TYPE_BLANK:
698                 return "";
699             case HSSFCell.CELL_TYPE_BOOLEAN:
700                 return cell.getBooleanCellValue() + "";
701             // 错误类型
702             case HSSFCell.CELL_TYPE_ERROR:
703                 return cell.getErrorCellValue() + "";
704             default:
705                 break;
706         }
707         return "";
708     }
709
e7c3ff 710     /**
C 711      * p判断是否excel文件
712      * @param file
713      * @return
714      */
715     public static boolean isExcel(MultipartFile file){
716         String fileName = file.getOriginalFilename();
717         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
718             return false;
719         }
720         return true;
721     }
722
723
6b6bf5 724
W 725
726
727     public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
728         File file = File.createTempFile("temp", ".xls");
729         FileOutputStream fileOut = null;
730         BufferedImage bufferImg = null;
731
732         try {
733             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
734             if (height == null) {
735                 height = 450;
736             }
737
738             if (width == null) {
739                 width = 1000;
740             }
741
742             HSSFWorkbook workbook = new HSSFWorkbook();
743             HSSFCellStyle boderStyle = workbook.createCellStyle();
744             boderStyle.setVerticalAlignment((short)1);
745             boderStyle.setAlignment((short)2);
746             HSSFSheet sheet = workbook.createSheet();
747             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
748             HSSFRow row = sheet.createRow(0);
749
750             HSSFCell anchor;
751             for(int i = 0; i < headList.length; ++i) {
752                 row.setHeight(height.shortValue());
753                 sheet.setColumnWidth(i, width);
754                 anchor = row.createCell(i);
755                 anchor.setCellType(1);
756                 anchor.setCellValue(headList[i]);
757                 anchor.setCellStyle(boderStyle);
758             }
759
760             HSSFRow row_value = null;
761             anchor = null;
762             HSSFCell cell = null;
763             if (dataList != null) {
764                 for(int n = 0; n < dataList.size(); ++n) {
765                     row_value = sheet.createRow(n + 1);
766                     row_value.setHeight(height.shortValue());
767                     Map<String, Object> dataMap = (Map)dataList.get(n);
768
769                     for(int i = 0; i < fieldList.length; ++i) {
770                         sheet.setColumnWidth(i, width);
771                         cell = row_value.createCell(i);
772                         cell.setCellType(1);
773                         Object value = dataMap.get(fieldList[i]);
774                         if (value != null && "class java.io.File".equals(value.getClass().toString())) {
775                             File file2 = (File)value;
776                             if (file2 == null) {
777                                 cell.setCellValue("");
778                             } else {
779                                 bufferImg = ImageIO.read(file2);
780                                 ImageIO.write(bufferImg, "jpg", byteArrayOut);
781                                 HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1);
782                                 anchor1.setAnchorType(0);
783                                 patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
784                             }
785                         } else {
786                             cell.setCellValue(objToString(dataMap.get(fieldList[i])));
787                             cell.setCellStyle(boderStyle);
788                         }
789                     }
790                 }
791             }
792
793             FileOutputStream fOut = new FileOutputStream(file);
794             workbook.write(fOut);
795             fOut.flush();
796             fOut.close();
797         } catch (Exception var25) {
798             var25.printStackTrace();
799         } finally {
800             file.deleteOnExit();
801         }
802
803         return file;
804     }
cac339 805 }