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