fwq
2023-10-23 29c36380f636c0ae22d668c856c03a543bcca1c8
提交 | 用户 | age
5c5945 1 package com.hx.util;
E 2
3 import com.hx.exception.TipsException;
2e06d1 4 import com.monitorjbl.xlsx.StreamingReader;
5c5945 5 import org.apache.poi.hssf.usermodel.*;
29c363 6 import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
d9e60e 7 import org.apache.poi.ss.usermodel.*;
C 8 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
5b623b 9 import org.apache.poi.xssf.usermodel.XSSFCell;
C 10 import org.apache.poi.xssf.usermodel.XSSFRow;
11 import org.apache.poi.xssf.usermodel.XSSFSheet;
12 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
5c5945 13 import org.springframework.web.multipart.MultipartFile;
c6eec8 14
6b6bf5 15 import javax.imageio.ImageIO;
W 16 import java.awt.image.BufferedImage;
c6eec8 17 import java.io.*;
F 18 import java.text.DateFormat;
19 import java.text.SimpleDateFormat;
20 import java.util.ArrayList;
21 import java.util.Date;
22 import java.util.List;
23 import java.util.Map;
5c5945 24
E 25
26 /**
cac339 27  *
5c5945 28  * @author hjr
E 29  */
30 public final class ExcelUtil {
31
32     /**
33      * @param excelName
34      *         文件名称
35      * @param outPath
36      *             保存路径
37      * @param headList
38      *        Excel文件Head标题集合
39      * @param fieldList
40      *        Excel文件Field标题集合 根据field来寻找位置填充表格
41      * @param dataList
42      *        Excel文件数据内容部分
43      * @throws Exception
44      */
45     public static String createExcel(String outPath, String excelName,
cac339 46                                      String[] headList, String[] fieldList,
C 47                                      List<Map<String, Object>> dataList) throws Exception {
5c5945 48
E 49         String filePath = null;
50         // 创建新的Excel 工作簿
51         HSSFWorkbook workbook = new HSSFWorkbook();
52
53         // 在Excel工作簿中建一工作表,其名为缺省值
54         // 如要新建一名为"效益指标"的工作表,其语句为:
55         // HSSFSheet sheet = workbook.createSheet("效益指标");
56         HSSFSheet sheet = workbook.createSheet();
57         // 在索引0的位置创建行(最顶端的行)
58         HSSFRow row = sheet.createRow(0);
59         // ===============================================================
60
7f9b53 61         HSSFCell cell;
C 62         for (int i = 0; i < headList.length; i++) {
5c5945 63             // 在索引0的位置创建单元格(左上端)
7f9b53 64             cell = row.createCell(i);
5c5945 65             // 定义单元格为字符串类型
2e06d1 66             //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
5c5945 67             // 在单元格中输入一些内容
E 68             cell.setCellValue(headList[i]);
69         }
70         // ===============================================================
71         if (dataList != null) {
7f9b53 72             HSSFRow row_value;
C 73             Map<String, Object> dataMap;
5c5945 74             for (int n = 0; n < dataList.size(); n++) {
E 75                 // 在索引1的位置创建行
7f9b53 76                 row_value = sheet.createRow(n + 1);
C 77                 dataMap = dataList.get(n);
5c5945 78                 // ===============================================================
E 79                 for (int i = 0; i < fieldList.length; i++) {
80                     // 在索引0的位置创建单元格(左上端)
7f9b53 81                     cell = row_value.createCell(i);
5c5945 82                     // 定义单元格为字符串类型
2e06d1 83                     //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
5c5945 84                     // 在单元格中输入一些内容
E 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 {
b4fb02 119         File file = File.createTempFile("temp", ".xlsx");
db7fc9 120         try{
C 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             // ===============================================================
7f9b53 131             HSSFCell cell;
db7fc9 132             for (int i = 0; i < headList.length; i++) {
C 133                 // 在索引0的位置创建单元格(左上端)
7f9b53 134                 cell = row.createCell(i);
db7fc9 135                 // 定义单元格为字符串类型
2e06d1 136                 //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
db7fc9 137                 // 在单元格中输入一些内容
C 138                 cell.setCellValue(headList[i]);
139             }
140             // ===============================================================
141             if (dataList != null) {
7f9b53 142                 HSSFRow row_value;
C 143                 Map<String, Object> dataMap;
db7fc9 144                 for (int n = 0; n < dataList.size(); n++) {
C 145                     // 在索引1的位置创建行
7f9b53 146                     row_value = sheet.createRow(n + 1);
C 147                     dataMap = dataList.get(n);
db7fc9 148                     // ===============================================================
C 149                     for (int i = 0; i < fieldList.length; i++) {
150                         // 在索引0的位置创建单元格(左上端)
7f9b53 151                         cell = row_value.createCell(i);
db7fc9 152                         // 定义单元格为字符串类型
2e06d1 153                         //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
db7fc9 154                         // 在单元格中输入一些内容
C 155                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
156                     }
157                     // ===============================================================
158                 }
159             }
160
161             // 新建一输出文件流
162             FileOutputStream fOut = new FileOutputStream(file);
163             // 把相应的Excel 工作簿存盘
164             workbook.write(fOut);
165             fOut.flush();
166             // 操作结束,关闭文件
167             fOut.close();
168         }catch (Exception e){
169
170         }finally {
171             file.deleteOnExit();
172         }
173         return file;
174     }
175
49f28b 176     /**生成临时文件
C 177      * @param headList
178      *        Excel文件Head标题集合
179      * @param fieldList
180      *        Excel文件Field标题集合 根据field来寻找位置填充表格
181      * @param dataList
182      *        Excel文件数据内容部分
183      * @throws Exception
184      */
185     public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
186             ,Integer height,Integer width) throws Exception {
187         File file = File.createTempFile("temp", ".xls");
188         try{
189
190             if(height == null){
191                 height = 450;
192             }
193             if(width == null){
194                 width = 5000;
195             }
196             // 创建新的Excel 工作簿
197             HSSFWorkbook workbook = new HSSFWorkbook();
198
199             //合并的单元格样式
200             HSSFCellStyle boderStyle = workbook.createCellStyle();
201             //垂直居中
2e06d1 202             boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
C 203             boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
49f28b 204
C 205             // 在Excel工作簿中建一工作表,其名为缺省值
206             // 如要新建一名为"效益指标"的工作表,其语句为:
207             // HSSFSheet sheet = workbook.createSheet("效益指标");
208             HSSFSheet sheet = workbook.createSheet();
209             // 在索引0的位置创建行(最顶端的行)
210             HSSFRow row = sheet.createRow(0);
211             // ===============================================================
7f9b53 212             HSSFCell cell;
49f28b 213             for (int i = 0; i < headList.length; i++) {
C 214                 //高度
215                 row.setHeight(height.shortValue());
216                 sheet.setColumnWidth(i,width);
217                 // 在索引0的位置创建单元格(左上端)
7f9b53 218                 cell = row.createCell(i);
49f28b 219                 // 定义单元格为字符串类型
2e06d1 220                 //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
49f28b 221                 // 在单元格中输入一些内容
C 222                 cell.setCellValue(headList[i]);
223                 cell.setCellStyle(boderStyle);
224             }
225             // ===============================================================
226             if (dataList != null) {
7f9b53 227                 HSSFRow row_value;
C 228                 Map<String, Object> dataMap;
49f28b 229                 for (int n = 0; n < dataList.size(); n++) {
C 230                     // 在索引1的位置创建行
7f9b53 231                     row_value = sheet.createRow(n + 1);
49f28b 232                     row_value.setHeight(height.shortValue());
7f9b53 233                     dataMap = dataList.get(n);
49f28b 234                     // ===============================================================
C 235                     for (int i = 0; i < fieldList.length; i++) {
236                         // 在索引0的位置创建单元格(左上端)
237                         sheet.setColumnWidth(i,width);
7f9b53 238                         cell = row_value.createCell(i);
49f28b 239                         // 定义单元格为字符串类型
2e06d1 240                         //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
49f28b 241                         // 在单元格中输入一些内容
C 242                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
243                         cell.setCellStyle(boderStyle);
244                     }
245                     // ===============================================================
246                 }
247             }
248
249             // 新建一输出文件流
250             FileOutputStream fOut = new FileOutputStream(file);
251             // 把相应的Excel 工作簿存盘
252             workbook.write(fOut);
253             fOut.flush();
254             // 操作结束,关闭文件
255             fOut.close();
256         }catch (Exception e){
257
258         }finally {
259             file.deleteOnExit();
260         }
261         return file;
262     }
263
1b1815 264     /**无限制行数生成ecxel,生成临时文件
C 265      * @param headList
266      *        Excel文件Head标题集合
267      * @param fieldList
268      *        Excel文件Field标题集合 根据field来寻找位置填充表格
269      * @param dataList
270      *        Excel文件数据内容部分
271      * @param height  单元格高度,默认450
272      * @param width  单元格宽度,默认5000
273      * @throws Exception
274      */
275     public static File createXSSExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
276             ,Integer height,Integer width) throws Exception {
277         File file = File.createTempFile("temp", ".xls");
278         try{
279
280             if(height == null){
281                 height = 450;
282             }
283             if(width == null){
284                 width = 5000;
285             }
286             // 创建新的Excel 工作簿
d9e60e 287             //XSSFWorkbook workbook = new XSSFWorkbook();
C 288             SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
1b1815 289
C 290             //合并的单元格样式
d9e60e 291             CellStyle boderStyle = workbook.createCellStyle();
1b1815 292             //垂直居中
2e06d1 293             boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
C 294             boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
1b1815 295
C 296             // 在Excel工作簿中建一工作表,其名为缺省值
297             // 如要新建一名为"效益指标"的工作表,其语句为:
298             // HSSFSheet sheet = workbook.createSheet("效益指标");
d9e60e 299             Sheet sheet = workbook.createSheet();
1b1815 300             // 在索引0的位置创建行(最顶端的行)
d9e60e 301             Row row = sheet.createRow(0);
1b1815 302             // ===============================================================
d9e60e 303             Cell cell;
1b1815 304             for (int i = 0; i < headList.length; i++) {
C 305                 //高度
306                 row.setHeight(height.shortValue());
307                 sheet.setColumnWidth(i,width);
308                 // 在索引0的位置创建单元格(左上端)
7f9b53 309                 cell = row.createCell(i);
1b1815 310                 // 定义单元格为字符串类型
2e06d1 311                 //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
1b1815 312                 // 在单元格中输入一些内容
C 313                 cell.setCellValue(headList[i]);
314                 cell.setCellStyle(boderStyle);
315             }
316             // ===============================================================
317             if (dataList != null) {
d9e60e 318                 Row row_value;
7f9b53 319                 Map<String, Object> dataMap;
1b1815 320                 for (int n = 0; n < dataList.size(); n++) {
C 321                     // 在索引1的位置创建行
7f9b53 322                     row_value = sheet.createRow(n + 1);
1b1815 323                     row_value.setHeight(height.shortValue());
7f9b53 324                     dataMap = dataList.get(n);
1b1815 325                     // ===============================================================
C 326                     for (int i = 0; i < fieldList.length; i++) {
327                         // 在索引0的位置创建单元格(左上端)
328                         sheet.setColumnWidth(i,width);
7f9b53 329                         cell = row_value.createCell(i);
1b1815 330                         // 定义单元格为字符串类型
2e06d1 331                         //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
1b1815 332                         // 在单元格中输入一些内容
C 333                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
334                         cell.setCellStyle(boderStyle);
335                     }
336                     // ===============================================================
337                 }
338             }
339
340             // 新建一输出文件流
341             FileOutputStream fOut = new FileOutputStream(file);
342             // 把相应的Excel 工作簿存盘
343             workbook.write(fOut);
344             fOut.flush();
345             // 操作结束,关闭文件
346             fOut.close();
347         }catch (Exception e){
348
349         }finally {
350             file.deleteOnExit();
351         }
352         return file;
353     }
354
db7fc9 355
5c5945 356     private static String objToString(Object obj) {
E 357         if (obj == null) {
358             return "";
359         } else {
360             if (obj instanceof String) {
361                 return (String) obj;
362             } else if (obj instanceof Date) {
363                 return null;// DateUtil.dateToString((Date)
cac339 364                 // obj,DateUtil.DATESTYLE_SHORT_EX);
5c5945 365             } else {
E 366                 return obj.toString();
367             }
368         }
369     }
370
371     /**
c6eec8 372      * 读取Excel数据
F 373      * @param file
374      * @param header
375      * @return
376      * @throws Exception
377      */
378     public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {
379
380         String fileName = file.getOriginalFilename();
381         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
382             throw new TipsException("上传文件格式不正确");
383         }
384
385         //判断不同格式处理方法不同
386         if(fileName.matches("^.+\\.(?i)(xls)$")){
387             //xls格式使用HSSF
388             return readExcelByeFileData(file, header);
389         }else{
390             //xlsx格式使用XSSF
391             return readExcelByeFileDataToXSSF(file, header);
392         }
393     }
394
395     /**
5c5945 396      * 读取 Excel文件内容
E 397      *
398      * @param file
399      * @param header 是否包括表头
400      * @return
401      * @throws Exception
402      */
403     public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
404
405         String fileName = file.getOriginalFilename();
406         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
407             throw new TipsException("上传文件格式不正确");
408         }
409
410         // 结果集
411         List<List<String>> list = new ArrayList<>();
412
413         HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
414
415         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
416         for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
417             HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
418             int col = 0;
419             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
420             for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
421                 HSSFRow hssfrow = hssfsheet.getRow(j);
422                 if(hssfrow!=null){
423                     if(j == 0) {
424                         col = hssfrow.getPhysicalNumberOfCells();
425                         if(!header) {
426                             //不包括表头
427                             continue;
428                         }
429                     }
430                     // 单行数据
431                     List<String> arrayString = new ArrayList<>();
432                     for (int i = 0; i < col; i++) {
433                         HSSFCell cell = hssfrow.getCell(i);
434                         if (cell == null) {
435                             arrayString.add("");
2e06d1 436                         } else if (cell.getCellType() == CellType.NUMERIC) {
5c5945 437                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
2e06d1 438                             if (CellType.NUMERIC == cell.getCellType()) {
5c5945 439                                 short format = cell.getCellStyle().getDataFormat();
E 440                                 if(format == 14 || format == 31 || format == 57 || format == 58){
441                                     //日期(中文时间格式的)
442                                     Date d = cell.getDateCellValue();
443                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
444                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
445                                     arrayString.add(formater.format(d));
446                                     //arrayString[i] = formater.format(d);
447                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
448                                     Date d = cell.getDateCellValue();
449                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
450                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
451                                     arrayString.add(formater.format(d));
452                                     //arrayString[i] = formater.format(d);
453                                 } else {
2e06d1 454                                     if(CellType.STRING == cell.getCellType()){
5c5945 455                                         arrayString.add(cell.getStringCellValue());
E 456                                         //arrayString[i] =cell.getStringCellValue();
2e06d1 457                                     }else if(CellType.FORMULA==cell.getCellType()){
5c5945 458                                         arrayString.add(cell.getCellFormula());
E 459                                         //arrayString[i] =cell.getCellFormula();
2e06d1 460                                     }else if(CellType.NUMERIC== cell.getCellType()){
5c5945 461                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
E 462                                         arrayString.add(dataFormatter.formatCellValue(cell));
463                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
464                                     }
465                                 }
466                             }
2e06d1 467                         } else if(cell.getCellType() == CellType.BLANK){
5c5945 468                             arrayString.add("");
E 469                             //arrayString[i] = "";
470                         } else { // 如果EXCEL表格中的数据类型为字符串型
2e06d1 471                             if(cell.getCellType() != CellType.BOOLEAN){
b77bfb 472                                 arrayString.add(cell.getStringCellValue().trim());
F 473                             }else{
474                                 arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
475                             }
5c5945 476                             //arrayString[i] = cell.getStringCellValue().trim();
E 477                         }
478                     }
479                     list.add(arrayString);
480                 }
481             }
482         }
483         return list;
484     }
485
c6eec8 486     /**
F 487      * 读取 Excel文件内容
488      *
489      * @param file
490      * @param header 是否包括表头
491      * @return
492      * @throws Exception
493      */
494     public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {
495
496         String fileName = file.getOriginalFilename();
497         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
498             throw new TipsException("上传文件格式不正确");
499         }
500
501         // 结果集
502         List<List<String>> list = new ArrayList<>();
503
504         XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
505
506         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
507         for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
508             XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
509             int col = 0;
510             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
511             for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
512                 XSSFRow xssfrow = xssfSheet.getRow(j);
513                 if(xssfrow!=null){
514                     if(j == 0) {
515                         col = xssfrow.getPhysicalNumberOfCells();
516                         if(!header) {
517                             //不包括表头
518                             continue;
519                         }
520                     }
521                     // 单行数据
522                     List<String> arrayString = new ArrayList<>();
523                     for (int i = 0; i < col; i++) {
524                         XSSFCell cell = xssfrow.getCell(i);
525                         if (cell == null) {
526                             arrayString.add("");
2e06d1 527                         } else if (cell.getCellType() == CellType.NUMERIC) {
c6eec8 528                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
2e06d1 529                             if (CellType.NUMERIC == cell.getCellType()) {
c6eec8 530                                 short format = cell.getCellStyle().getDataFormat();
F 531                                 if(format == 14 || format == 31 || format == 57 || format == 58){
532                                     //日期(中文时间格式的)
533                                     Date d = cell.getDateCellValue();
534                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
535                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
536                                     arrayString.add(formater.format(d));
537                                     //arrayString[i] = formater.format(d);
538                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
539                                     Date d = cell.getDateCellValue();
540                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
541                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
542                                     arrayString.add(formater.format(d));
543                                     //arrayString[i] = formater.format(d);
544                                 } else {
2e06d1 545                                     if(CellType.STRING == cell.getCellType()){
c6eec8 546                                         arrayString.add(cell.getStringCellValue());
F 547                                         //arrayString[i] =cell.getStringCellValue();
2e06d1 548                                     }else if(CellType.FORMULA==cell.getCellType()){
c6eec8 549                                         arrayString.add(cell.getCellFormula());
F 550                                         //arrayString[i] =cell.getCellFormula();
2e06d1 551                                     }else if(CellType.NUMERIC== cell.getCellType()){
c6eec8 552                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
F 553                                         arrayString.add(dataFormatter.formatCellValue(cell));
554                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
555                                     }
556                                 }
557                             }
2e06d1 558                         } else if(cell.getCellType() == CellType.BLANK){
c6eec8 559                             arrayString.add("");
F 560                             //arrayString[i] = "";
561                         } else { // 如果EXCEL表格中的数据类型为字符串型
2e06d1 562                             if(cell.getCellType() != CellType.BOOLEAN){
b77bfb 563                                 arrayString.add(cell.getStringCellValue().trim());
F 564                             }else{
565                                 arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
566                             }
567
568
c6eec8 569                             //arrayString[i] = cell.getStringCellValue().trim();
F 570                         }
571                     }
572                     list.add(arrayString);
573                 }
574             }
575         }
576         return list;
577     }
578
cac339 579
C 580     /**判断excel的版本*/
2e06d1 581     public static Workbook create(InputStream inp) throws IOException {
C 582         //这样写  excel 能兼容03和07
583         return WorkbookFactory.create(inp);
cac339 584     }
C 585
586     /**读取excel文件,兼容2003和2007
587      * 通过流读取Excel文件
588      * @return
589      * @throws Exception
590      */
3f4072 591     public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception {
C 592         try {
593
594             String fileName = file.getName();
595             if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
596                 throw new TipsException("上传文件格式不正确");
597             }
598
599             // 结果集
600             List<List<String>> list = new ArrayList<>();
601             Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
602
603             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
2e06d1 604             Sheet hssfsheet;
C 605             Row hssfrow;
606             List<String> arrayString;
607             Cell cell;
608             short format;
609             Date d;
610             DateFormat formater;
611             HSSFDataFormatter dataFormatter;
3f4072 612             for(int s=0;s<book.getNumberOfSheets();s++) {
2e06d1 613                 hssfsheet = book.getSheetAt(s);
3f4072 614                 int col = 0;
C 615                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
616                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
2e06d1 617                     hssfrow = hssfsheet.getRow(j);
3f4072 618                     if(hssfrow!=null){
C 619                         if(j == 0) {
620                             col = hssfrow.getPhysicalNumberOfCells();
621                             if(!header) {
622                                 //不包括表头
623                                 continue;
624                             }
625                         }
626                         // 单行数据
2e06d1 627                         arrayString = new ArrayList<>();
3f4072 628                         for (int i = 0; i < col; i++) {
2e06d1 629                             cell = hssfrow.getCell(i);
3f4072 630                             if (cell == null) {
C 631                                 arrayString.add("");
2e06d1 632                             } else if (cell.getCellType() == CellType.NUMERIC) {
3f4072 633                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
2e06d1 634                                 if (CellType.NUMERIC == cell.getCellType()) {
C 635                                     format = cell.getCellStyle().getDataFormat();
3f4072 636                                     if(format == 14 || format == 31 || format == 57 || format == 58){
C 637                                         //日期(中文时间格式的)
2e06d1 638                                          d = cell.getDateCellValue();
C 639                                          formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
3f4072 640                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
C 641                                         arrayString.add(formater.format(d));
642                                         //arrayString[i] = formater.format(d);
643                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
2e06d1 644                                         d = cell.getDateCellValue();
3f4072 645                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
2e06d1 646                                         formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
3f4072 647                                         arrayString.add(formater.format(d));
C 648                                         //arrayString[i] = formater.format(d);
649                                     } else {
2e06d1 650                                         if(CellType.STRING == cell.getCellType()){
3f4072 651                                             arrayString.add(cell.getStringCellValue());
C 652                                             //arrayString[i] =cell.getStringCellValue();
2e06d1 653                                         }else if(CellType.FORMULA==cell.getCellType()){
3f4072 654                                             arrayString.add(cell.getCellFormula());
C 655                                             //arrayString[i] =cell.getCellFormula();
2e06d1 656                                         }else if(CellType.NUMERIC== cell.getCellType()){
C 657                                             dataFormatter = new HSSFDataFormatter();
3f4072 658                                             arrayString.add(dataFormatter.formatCellValue(cell));
C 659                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
660                                         }
661                                     }
662                                 }
2e06d1 663                             } else if(cell.getCellType() == CellType.BLANK){
3f4072 664                                 arrayString.add("");
C 665                                 //arrayString[i] = "";
666                             } else { // 如果EXCEL表格中的数据类型为字符串型
667                                 arrayString.add(cell.getStringCellValue().trim());
668                                 //arrayString[i] = cell.getStringCellValue().trim();
669                             }
670                         }
671                         list.add(arrayString);
672                     }
673                 }
674             }
675             return list;
676         } catch (Exception e) {
677             e.printStackTrace();
678         }
679         return null;
680     }
2e06d1 681
3f4072 682     /**读取excel文件,兼容2003和2007
C 683      * 通过流读取Excel文件
684      * @return
685      * @throws Exception
686      */
cac339 687     public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
C 688         try {
689
690             String fileName = file.getOriginalFilename();
691             if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
692                 throw new TipsException("上传文件格式不正确");
693             }
694
695             // 结果集
696             List<List<String>> list = new ArrayList<>();
697             Workbook book = create(new BufferedInputStream(file.getInputStream()));
698
2e06d1 699             Sheet hssfsheet;
C 700             Row hssfrow;
701             List<String> arrayString;
702             Cell cell;
703             short format;
704             Date d;
705             DateFormat formater;
706             HSSFDataFormatter dataFormatter;
cac339 707             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
2e06d1 708             for(int s=0;s<book.getNumberOfSheets();s++) {
C 709                 hssfsheet = book.getSheetAt(s);
710                 int col = 0;
711                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
712                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
713                     hssfrow = hssfsheet.getRow(j);
714                     if(hssfrow!=null){
715                         if(j == 0) {
716                             col = hssfrow.getPhysicalNumberOfCells();
717                             if(!header) {
718                                 //不包括表头
719                                 continue;
720                             }
721                         }
722                         // 单行数据
723                         arrayString = new ArrayList<>();
724                         for (int i = 0; i < col; i++) {
725                             cell = hssfrow.getCell(i);
726                             if (cell == null) {
727                                 arrayString.add("");
728                             } else if (cell.getCellType() == CellType.NUMERIC) {
729                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
730                                 if (CellType.NUMERIC == cell.getCellType()) {
731                                     format = cell.getCellStyle().getDataFormat();
732                                     if(format == 14 || format == 31 || format == 57 || format == 58){
733                                         //日期(中文时间格式的)
734                                         d = cell.getDateCellValue();
735                                         formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
736                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
737                                         arrayString.add(formater.format(d));
738                                         //arrayString[i] = formater.format(d);
739                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
740                                         d = cell.getDateCellValue();
741                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
742                                         formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
743                                         arrayString.add(formater.format(d));
744                                         //arrayString[i] = formater.format(d);
745                                     } else {
746                                         if(CellType.STRING == cell.getCellType()){
747                                             arrayString.add(cell.getStringCellValue());
748                                             //arrayString[i] =cell.getStringCellValue();
749                                         }else if(CellType.FORMULA==cell.getCellType()){
750                                             arrayString.add(cell.getCellFormula());
751                                             //arrayString[i] =cell.getCellFormula();
752                                         }else if(CellType.NUMERIC== cell.getCellType()){
753                                             dataFormatter = new HSSFDataFormatter();
754                                             arrayString.add(dataFormatter.formatCellValue(cell));
755                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
756                                         }
757                                     }
758                                 }
759                             } else if(cell.getCellType() == CellType.BLANK){
760                                 arrayString.add("");
761                                 //arrayString[i] = "";
762                             } else { // 如果EXCEL表格中的数据类型为字符串型
763                                 arrayString.add(cell.getStringCellValue().trim());
764                                 //arrayString[i] = cell.getStringCellValue().trim();
765                             }
766                         }
767                         list.add(arrayString);
768                     }
769                 }
770             }
771             return list;
772         } catch (Exception e) {
773             e.printStackTrace();
774         }
775         return null;
776     }
777
399ee0 778     /**读取excel文件,兼容2003和2007
C 779      * 通过流读取Excel文件
780      * @return
781      * @throws Exception
782      */
783     public static List<List<String>> getExcelDataCompatible(InputStream inputStream,boolean header) throws Exception {
784         try {
785             // 结果集
786             List<List<String>> list = new ArrayList<>();
787             Workbook book = create(new BufferedInputStream(inputStream));
788
789             Sheet hssfsheet;
790             Row hssfrow;
791             List<String> arrayString;
792             Cell cell;
793             short format;
794             Date d;
795             DateFormat formater;
796             HSSFDataFormatter dataFormatter;
797             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
798             for(int s=0;s<book.getNumberOfSheets();s++) {
799                 hssfsheet = book.getSheetAt(s);
800                 int col = 0;
801                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
802                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
803                     hssfrow = hssfsheet.getRow(j);
804                     if(hssfrow!=null){
805                         if(j == 0) {
806                             col = hssfrow.getPhysicalNumberOfCells();
807                             if(!header) {
808                                 //不包括表头
809                                 continue;
810                             }
811                         }
812                         // 单行数据
813                         arrayString = new ArrayList<>();
814                         for (int i = 0; i < col; i++) {
815                             cell = hssfrow.getCell(i);
816                             if (cell == null) {
817                                 arrayString.add("");
818                             } else if (cell.getCellType() == CellType.NUMERIC) {
819                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
820                                 if (CellType.NUMERIC == cell.getCellType()) {
821                                     format = cell.getCellStyle().getDataFormat();
822                                     if(format == 14 || format == 31 || format == 57 || format == 58){
823                                         //日期(中文时间格式的)
824                                         d = cell.getDateCellValue();
825                                         formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
826                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
827                                         arrayString.add(formater.format(d));
828                                         //arrayString[i] = formater.format(d);
829                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
830                                         d = cell.getDateCellValue();
831                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
832                                         formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
833                                         arrayString.add(formater.format(d));
834                                         //arrayString[i] = formater.format(d);
835                                     } else {
836                                         if(CellType.STRING == cell.getCellType()){
837                                             arrayString.add(cell.getStringCellValue());
838                                             //arrayString[i] =cell.getStringCellValue();
839                                         }else if(CellType.FORMULA==cell.getCellType()){
840                                             arrayString.add(cell.getCellFormula());
841                                             //arrayString[i] =cell.getCellFormula();
842                                         }else if(CellType.NUMERIC== cell.getCellType()){
843                                             dataFormatter = new HSSFDataFormatter();
844                                             arrayString.add(dataFormatter.formatCellValue(cell));
845                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
846                                         }
847                                     }
848                                 }
849                             } else if(cell.getCellType() == CellType.BLANK){
850                                 arrayString.add("");
851                                 //arrayString[i] = "";
852                             } else { // 如果EXCEL表格中的数据类型为字符串型
853                                 arrayString.add(cell.getStringCellValue().trim());
854                                 //arrayString[i] = cell.getStringCellValue().trim();
855                             }
856                         }
857                         list.add(arrayString);
858                     }
859                 }
860             }
861             return list;
862         } catch (Exception e) {
863             e.printStackTrace();
864         }
865         return null;
866     }
5b623b 867
C 868     /**
869      * 新版读取Excel,只支持2007以上版本,也就是xslx格式
cd78e3 870      * 支持大数据量
5b623b 871      * @param file 文件
C 872      * @return 数据
2e06d1 873      */
5b623b 874     public static List<List<String>> readExcelData(File file){
C 875         if(!isExcel(file)){
876             throw new TipsException("请上传excel的文件格式!");
877         }
878         List<List<String>> listData = new ArrayList<>();
879         try{
880             //rowCacheSize 缓存到内存中的行数(默认是10)
881             //bufferSize 读取资源时,缓存到内存的字节大小(默认是1024)
882             //open InputStream或者XLSX格式的File(必须)
883             Workbook book = StreamingReader.builder()
884                     .rowCacheSize(100)
399ee0 885                     .bufferSize(10240)
5b623b 886                     .open(new FileInputStream(file));
399ee0 887             listData =readhandle(book);
29c363 888         }catch (OLE2NotOfficeXmlFileException ex){
F 889             ex.printStackTrace();
890             throw new RuntimeException("excel版本不为2007及以上");
891         } catch (Exception e){
d57fc1 892             e.printStackTrace();
5b623b 893             throw new RuntimeException(e.getMessage());
d57fc1 894         }
5b623b 895         return listData;
d57fc1 896     }
F 897
5b623b 898     /**
C 899      * 新版读取Excel,只支持2007以上版本,也就是xslx格式
cd78e3 900      * 支持大数据量
5b623b 901      * @param file 文件
C 902      * @return 数据
2e06d1 903      */
5b623b 904     public static List<List<String>> readExcelData(MultipartFile file){
C 905         if(!isExcel(file)){
906             throw new TipsException("请上传excel的文件格式!");
907         }
908         List<List<String>> listData = new ArrayList<>();
909         try{
910             //rowCacheSize 缓存到内存中的行数(默认是10)
911             //bufferSize 读取资源时,缓存到内存的字节大小(默认是1024)
912             //open InputStream或者XLSX格式的File(必须)
913             Workbook book = StreamingReader.builder()
914                     .rowCacheSize(100)
399ee0 915                     .bufferSize(10240)
5b623b 916                     .open(new BufferedInputStream(file.getInputStream()));
2e06d1 917
399ee0 918             listData =readhandle(book);
5b623b 919         }catch (Exception e){
2e06d1 920             e.printStackTrace();
5b623b 921             throw new RuntimeException(e.getMessage());
2e06d1 922         }
5b623b 923         return listData;
2e06d1 924     }
C 925
399ee0 926     /**
C 927      * 新版读取Excel,只支持2007以上版本,也就是xslx格式
cd78e3 928      * 支持大数据量
399ee0 929      * @param file 文件
C 930      * @return 数据
931      */
932     public static List<List<String>> readExcelData(InputStream file){
933         List<List<String>> listData = new ArrayList<>();
934         try{
935             //rowCacheSize 缓存到内存中的行数(默认是10)
936             //bufferSize 读取资源时,缓存到内存的字节大小(默认是1024)
937             //open InputStream或者XLSX格式的File(必须)
938             Workbook book = StreamingReader.builder()
939                     .rowCacheSize(100)
940                     .bufferSize(10240)
941                     .open(file);
942
943             listData =readhandle(book);
944         }catch (Exception e){
945             e.printStackTrace();
946             throw new RuntimeException(e.getMessage());
947         }
948         return listData;
949     }
950
951     /**处理数据*/
952     public static List<List<String>> readhandle(Workbook book){
953         List<List<String>> listData = new ArrayList<>();
954
955         //是否存在数据
956         boolean isData;
957         List<String> arrayString;
958         short format;
959         Date d;
960         DateFormat formater;
961         Sheet sheet;
756acb 962         Cell cell;
399ee0 963         //遍历所有的sheet
C 964         for(int i=0;i<book.getNumberOfSheets();i++) {
965             sheet = book.getSheetAt(i);
756acb 966             //列数
C 967             Integer arrange = null;
399ee0 968             //遍历所有的行
C 969             for (Row row : sheet) {
756acb 970                 if(row == null){
C 971                     continue;
972                 }
399ee0 973                 isData = false;
C 974                 arrayString = new ArrayList<>();
756acb 975                 if(arrange == null){
C 976                     arrange = row.getPhysicalNumberOfCells();
977                 }
399ee0 978                 //遍历所有的列
756acb 979                 for (int j = 0;j<arrange;j++) {
C 980                     cell = row.getCell(j);
399ee0 981                     if (cell == null) {
a1bdc4 982                         arrayString.add("");
8b3e27 983                     }else{
C 984                         if (cell.getCellType() == CellType.NUMERIC) {
985                             isData = true;
986                             format = cell.getCellStyle().getDataFormat();
987                             if (format == 14 || format == 31 || format == 57 || format == 58) {
988                                 //日期(中文时间格式的)
989                                 d = cell.getDateCellValue();
990                                 formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
991                                 arrayString.add(formater.format(d));
992                             } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
993                                 d = cell.getDateCellValue();
994                                 formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
995                                 arrayString.add(formater.format(d));
399ee0 996                             } else {
8b3e27 997                                 if (CellType.STRING == cell.getCellType()) {
C 998                                     arrayString.add(cell.getStringCellValue());
999                                 } else if (CellType.FORMULA == cell.getCellType()) {
1000                                     arrayString.add(cell.getCellFormula());
1001                                 } else {
1002                                     arrayString.add(cell.getStringCellValue().trim());
1003                                 }
399ee0 1004                             }
8b3e27 1005                         } else if (cell.getCellType() == CellType.BLANK) {
C 1006                             arrayString.add("");
1007                         } else { // 如果EXCEL表格中的数据类型为字符串型
1008                             isData = true;
1009                             arrayString.add(cell.getStringCellValue().trim());
399ee0 1010                         }
C 1011                     }
1012                 }
1013                 if (isData) {
1014                     listData.add(arrayString);
1015                 }
1016             }
1017         }
1018         return listData;
1019     }
1020
cac339 1021
e7c3ff 1022     /**
C 1023      * p判断是否excel文件
1024      * @param file
1025      * @return
1026      */
1027     public static boolean isExcel(MultipartFile file){
5b623b 1028         return isExcel(file.getOriginalFilename());
C 1029     }
1030
1031
1032     /**
1033      * p判断是否excel文件
1034      * @param file
1035      * @return
1036      */
1037     public static boolean isExcel(File file){
1038         return isExcel(file.getName());
1039     }
1040
1041
1042     /**判断文件格式是不是excel*/
1043     public static boolean isExcel(String fileName){
e7c3ff 1044         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
C 1045             return false;
1046         }
1047         return true;
1048     }
6b6bf5 1049
W 1050     public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
1051         File file = File.createTempFile("temp", ".xls");
1052         FileOutputStream fileOut = null;
1053         BufferedImage bufferImg = null;
1054
1055         try {
1056             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
1057             if (height == null) {
1058                 height = 450;
1059             }
1060
1061             if (width == null) {
1062                 width = 1000;
1063             }
1064
1065             HSSFWorkbook workbook = new HSSFWorkbook();
1066             HSSFCellStyle boderStyle = workbook.createCellStyle();
2e06d1 1067             boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
C 1068             boderStyle.setAlignment(HorizontalAlignment.CENTER);
6b6bf5 1069             HSSFSheet sheet = workbook.createSheet();
W 1070             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
1071             HSSFRow row = sheet.createRow(0);
1072
1073             HSSFCell anchor;
1074             for(int i = 0; i < headList.length; ++i) {
1075                 row.setHeight(height.shortValue());
1076                 sheet.setColumnWidth(i, width);
1077                 anchor = row.createCell(i);
1078                 anchor.setCellValue(headList[i]);
1079                 anchor.setCellStyle(boderStyle);
1080             }
1081
1082             HSSFRow row_value = null;
1083             anchor = null;
1084             HSSFCell cell = null;
1085             if (dataList != null) {
1086                 for(int n = 0; n < dataList.size(); ++n) {
1087                     row_value = sheet.createRow(n + 1);
1088                     row_value.setHeight(height.shortValue());
1089                     Map<String, Object> dataMap = (Map)dataList.get(n);
1090
1091                     for(int i = 0; i < fieldList.length; ++i) {
1092                         sheet.setColumnWidth(i, width);
1093                         cell = row_value.createCell(i);
1094                         Object value = dataMap.get(fieldList[i]);
1095                         if (value != null && "class java.io.File".equals(value.getClass().toString())) {
1096                             File file2 = (File)value;
1097                             if (file2 == null) {
1098                                 cell.setCellValue("");
1099                             } else {
1100                                 bufferImg = ImageIO.read(file2);
1101                                 ImageIO.write(bufferImg, "jpg", byteArrayOut);
1102                                 HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1);
2e06d1 1103                                 anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
6b6bf5 1104                                 patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
W 1105                             }
1106                         } else {
1107                             cell.setCellValue(objToString(dataMap.get(fieldList[i])));
1108                             cell.setCellStyle(boderStyle);
1109                         }
1110                     }
1111                 }
1112             }
1113
1114             FileOutputStream fOut = new FileOutputStream(file);
1115             workbook.write(fOut);
1116             fOut.flush();
1117             fOut.close();
1118         } catch (Exception var25) {
1119             var25.printStackTrace();
1120         } finally {
1121             file.deleteOnExit();
1122         }
1123
1124         return file;
2e06d1 1125     }
C 1126
cac339 1127 }