ChenJiaHe
2021-04-20 38fc97caaa5097b5a260a57d4d1156df260cb04c
提交 | 用户 | age
5c5945 1 package com.hx.util;
E 2
cac339 3 import java.io.*;
5c5945 4 import java.text.DateFormat;
E 5 import java.text.SimpleDateFormat;
cac339 6 import java.util.*;
5c5945 7
E 8 import com.hx.exception.TipsException;
cac339 9 import org.apache.poi.POIXMLDocument;
5c5945 10 import org.apache.poi.hssf.usermodel.*;
cac339 11 import org.apache.poi.openxml4j.opc.OPCPackage;
C 12 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
5c5945 13 import org.apache.poi.ss.usermodel.Cell;
cac339 14 import org.apache.poi.ss.usermodel.Row;
C 15 import org.apache.poi.ss.usermodel.Sheet;
16 import org.apache.poi.ss.usermodel.Workbook;
17 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
5c5945 18 import org.springframework.web.multipart.MultipartFile;
E 19
20
21 /**
cac339 22  *
5c5945 23  * @author hjr
E 24  */
25 public final class ExcelUtil {
26
27     /**
28      * @param excelName
29      *         文件名称
30      * @param outPath
31      *             保存路径
32      * @param headList
33      *        Excel文件Head标题集合
34      * @param fieldList
35      *        Excel文件Field标题集合 根据field来寻找位置填充表格
36      * @param dataList
37      *        Excel文件数据内容部分
38      * @throws Exception
39      */
40     public static String createExcel(String outPath, String excelName,
cac339 41                                      String[] headList, String[] fieldList,
C 42                                      List<Map<String, Object>> dataList) throws Exception {
5c5945 43
E 44         String filePath = null;
45         // 创建新的Excel 工作簿
46         HSSFWorkbook workbook = new HSSFWorkbook();
47
48         // 在Excel工作簿中建一工作表,其名为缺省值
49         // 如要新建一名为"效益指标"的工作表,其语句为:
50         // HSSFSheet sheet = workbook.createSheet("效益指标");
51         HSSFSheet sheet = workbook.createSheet();
52         // 在索引0的位置创建行(最顶端的行)
53         HSSFRow row = sheet.createRow(0);
54         // ===============================================================
55         for (int i = 0; i < headList.length; i++) {
56
57             // 在索引0的位置创建单元格(左上端)
58             HSSFCell cell = row.createCell(i);
59             // 定义单元格为字符串类型
60             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
61             // 在单元格中输入一些内容
62             cell.setCellValue(headList[i]);
63         }
64         // ===============================================================
65         if (dataList != null) {
66             for (int n = 0; n < dataList.size(); n++) {
67                 // 在索引1的位置创建行
68                 HSSFRow row_value = sheet.createRow(n + 1);
69                 Map<String, Object> dataMap = dataList.get(n);
70                 // ===============================================================
71                 for (int i = 0; i < fieldList.length; i++) {
72                     // 在索引0的位置创建单元格(左上端)
73                     HSSFCell cell = row_value.createCell(i);
74                     // 定义单元格为字符串类型
75                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
76                     // 在单元格中输入一些内容
77                     cell.setCellValue(objToString(dataMap.get(fieldList[i])));
78                 }
79                 // ===============================================================
80             }
81         }
82
83         // 新建一输出文件流
84         File file = SimpleTool.createFile(outPath,excelName);
85         FileOutputStream fOut = new FileOutputStream(file);
86         // 把相应的Excel 工作簿存盘
87         workbook.write(fOut);
88         fOut.flush();
89         // 操作结束,关闭文件
90         fOut.close();
91
92         if(outPath.endsWith("/")){
93             filePath = outPath + excelName;
94         }else{
95             filePath = outPath +"/"+ excelName;
96         }
97         return filePath;
98     }
99
db7fc9 100     /**生成临时文件
C 101      * @param headList
102      *        Excel文件Head标题集合
103      * @param fieldList
104      *        Excel文件Field标题集合 根据field来寻找位置填充表格
105      * @param dataList
106      *        Excel文件数据内容部分
107      * @throws Exception
108      */
109     public static File createExcel(String[] headList, String[] fieldList,
cac339 110                                    List<Map<String, Object>> dataList) throws Exception {
db7fc9 111         File file = File.createTempFile("temp", ".xls");
C 112         try{
113             // 创建新的Excel 工作簿
114             HSSFWorkbook workbook = new HSSFWorkbook();
115
116             // 在Excel工作簿中建一工作表,其名为缺省值
117             // 如要新建一名为"效益指标"的工作表,其语句为:
118             // HSSFSheet sheet = workbook.createSheet("效益指标");
119             HSSFSheet sheet = workbook.createSheet();
120             // 在索引0的位置创建行(最顶端的行)
121             HSSFRow row = sheet.createRow(0);
122             // ===============================================================
123             for (int i = 0; i < headList.length; i++) {
124
125                 // 在索引0的位置创建单元格(左上端)
126                 HSSFCell cell = row.createCell(i);
127                 // 定义单元格为字符串类型
128                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
129                 // 在单元格中输入一些内容
130                 cell.setCellValue(headList[i]);
131             }
132             // ===============================================================
133             if (dataList != null) {
134                 for (int n = 0; n < dataList.size(); n++) {
135                     // 在索引1的位置创建行
136                     HSSFRow row_value = sheet.createRow(n + 1);
137                     Map<String, Object> dataMap = dataList.get(n);
138                     // ===============================================================
139                     for (int i = 0; i < fieldList.length; i++) {
140                         // 在索引0的位置创建单元格(左上端)
141                         HSSFCell cell = row_value.createCell(i);
142                         // 定义单元格为字符串类型
143                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
144                         // 在单元格中输入一些内容
145                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
146                     }
147                     // ===============================================================
148                 }
149             }
150
151             // 新建一输出文件流
152             FileOutputStream fOut = new FileOutputStream(file);
153             // 把相应的Excel 工作簿存盘
154             workbook.write(fOut);
155             fOut.flush();
156             // 操作结束,关闭文件
157             fOut.close();
158         }catch (Exception e){
159
160         }finally {
161             file.deleteOnExit();
162         }
163         return file;
164     }
165
49f28b 166     /**生成临时文件
C 167      * @param headList
168      *        Excel文件Head标题集合
169      * @param fieldList
170      *        Excel文件Field标题集合 根据field来寻找位置填充表格
171      * @param dataList
172      *        Excel文件数据内容部分
173      * @throws Exception
174      */
175     public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
176             ,Integer height,Integer width) throws Exception {
177         File file = File.createTempFile("temp", ".xls");
178         try{
179
180             if(height == null){
181                 height = 450;
182             }
183             if(width == null){
184                 width = 5000;
185             }
186             // 创建新的Excel 工作簿
187             HSSFWorkbook workbook = new HSSFWorkbook();
188
189             //合并的单元格样式
190             HSSFCellStyle boderStyle = workbook.createCellStyle();
191             //垂直居中
192             boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
193             boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
194
195             // 在Excel工作簿中建一工作表,其名为缺省值
196             // 如要新建一名为"效益指标"的工作表,其语句为:
197             // HSSFSheet sheet = workbook.createSheet("效益指标");
198             HSSFSheet sheet = workbook.createSheet();
199             // 在索引0的位置创建行(最顶端的行)
200             HSSFRow row = sheet.createRow(0);
201             // ===============================================================
202             for (int i = 0; i < headList.length; i++) {
203                 //高度
204                 row.setHeight(height.shortValue());
205                 sheet.setColumnWidth(i,width);
206                 // 在索引0的位置创建单元格(左上端)
207                 HSSFCell cell = row.createCell(i);
208                 // 定义单元格为字符串类型
209                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
210                 // 在单元格中输入一些内容
211                 cell.setCellValue(headList[i]);
212                 cell.setCellStyle(boderStyle);
213             }
214             // ===============================================================
215             if (dataList != null) {
216                 for (int n = 0; n < dataList.size(); n++) {
217                     // 在索引1的位置创建行
218                     HSSFRow row_value = sheet.createRow(n + 1);
219                     row_value.setHeight(height.shortValue());
220                     Map<String, Object> dataMap = dataList.get(n);
221                     // ===============================================================
222                     for (int i = 0; i < fieldList.length; i++) {
223                         // 在索引0的位置创建单元格(左上端)
224                         sheet.setColumnWidth(i,width);
225                         HSSFCell cell = row_value.createCell(i);
226                         // 定义单元格为字符串类型
227                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
228                         // 在单元格中输入一些内容
229                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
230                         cell.setCellStyle(boderStyle);
231                     }
232                     // ===============================================================
233                 }
234             }
235
236             // 新建一输出文件流
237             FileOutputStream fOut = new FileOutputStream(file);
238             // 把相应的Excel 工作簿存盘
239             workbook.write(fOut);
240             fOut.flush();
241             // 操作结束,关闭文件
242             fOut.close();
243         }catch (Exception e){
244
245         }finally {
246             file.deleteOnExit();
247         }
248         return file;
249     }
250
db7fc9 251
5c5945 252     private static String objToString(Object obj) {
E 253         if (obj == null) {
254             return "";
255         } else {
256             if (obj instanceof String) {
257                 return (String) obj;
258             } else if (obj instanceof Date) {
259                 return null;// DateUtil.dateToString((Date)
cac339 260                 // obj,DateUtil.DATESTYLE_SHORT_EX);
5c5945 261             } else {
E 262                 return obj.toString();
263             }
264         }
265     }
266
267     /**
268      * 读取 Excel文件内容
269      *
270      * @param file
271      * @param header 是否包括表头
272      * @return
273      * @throws Exception
274      */
275     public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
276
277         String fileName = file.getOriginalFilename();
278         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
279             throw new TipsException("上传文件格式不正确");
280         }
281
282         // 结果集
283         List<List<String>> list = new ArrayList<>();
284
285         HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
286
287         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
288         for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
289             HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
290             int col = 0;
291             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
292             for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
293                 HSSFRow hssfrow = hssfsheet.getRow(j);
294                 if(hssfrow!=null){
295                     if(j == 0) {
296                         col = hssfrow.getPhysicalNumberOfCells();
297                         if(!header) {
298                             //不包括表头
299                             continue;
300                         }
301                     }
302                     // 单行数据
303                     List<String> arrayString = new ArrayList<>();
304                     for (int i = 0; i < col; i++) {
305                         HSSFCell cell = hssfrow.getCell(i);
306                         if (cell == null) {
307                             arrayString.add("");
308                         } else if (cell.getCellType() == 0) {
309                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
310                             if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
311                                 short format = cell.getCellStyle().getDataFormat();
312                                 if(format == 14 || format == 31 || format == 57 || format == 58){
313                                     //日期(中文时间格式的)
314                                     Date d = cell.getDateCellValue();
315                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
316                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
317                                     arrayString.add(formater.format(d));
318                                     //arrayString[i] = formater.format(d);
319                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
320                                     Date d = cell.getDateCellValue();
321                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
322                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
323                                     arrayString.add(formater.format(d));
324                                     //arrayString[i] = formater.format(d);
325                                 } else {
326                                     if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
327                                         arrayString.add(cell.getStringCellValue());
328                                         //arrayString[i] =cell.getStringCellValue();
329                                     }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
330                                         arrayString.add(cell.getCellFormula());
331                                         //arrayString[i] =cell.getCellFormula();
332                                     }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
333                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
334                                         arrayString.add(dataFormatter.formatCellValue(cell));
335                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
336                                     }
337                                 }
338                             }
339                         } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
340                             arrayString.add("");
341                             //arrayString[i] = "";
342                         } else { // 如果EXCEL表格中的数据类型为字符串型
343                             arrayString.add(cell.getStringCellValue().trim());
344                             //arrayString[i] = cell.getStringCellValue().trim();
345                         }
346                     }
347                     list.add(arrayString);
348                 }
349             }
350         }
351         return list;
352     }
353
cac339 354
C 355     /**判断excel的版本*/
356     public static Workbook create(InputStream inp) throws Exception {
357         if (!inp.markSupported()) {
358             inp = new PushbackInputStream(inp, 8);
359         }
360         if (POIFSFileSystem.hasPOIFSHeader(inp)) {
361             return new HSSFWorkbook(inp);
362         }
363         if (POIXMLDocument.hasOOXMLHeader(inp)) {
364             return new XSSFWorkbook(OPCPackage.open(inp));
365         }
366         throw new IllegalArgumentException("你的excel版本目前poi解析不了");
367     }
368
369     /**读取excel文件,兼容2003和2007
370      * 通过流读取Excel文件
371      * @return
372      * @throws Exception
373      */
374     public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
375         try {
376
377             String fileName = file.getOriginalFilename();
378             if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
379                 throw new TipsException("上传文件格式不正确");
380             }
381
382             // 结果集
383             List<List<String>> list = new ArrayList<>();
384             Workbook book = create(new BufferedInputStream(file.getInputStream()));
385
386             // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
387             for(int s=0;s<book.getNumberOfSheets();s++) {
388                 Sheet hssfsheet = book.getSheetAt(s);
389                 int col = 0;
390                 // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
391                 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
392                     Row hssfrow = hssfsheet.getRow(j);
393                     if(hssfrow!=null){
394                         if(j == 0) {
395                             col = hssfrow.getPhysicalNumberOfCells();
396                             if(!header) {
397                                 //不包括表头
398                                 continue;
399                             }
400                         }
401                         // 单行数据
402                         List<String> arrayString = new ArrayList<>();
403                         for (int i = 0; i < col; i++) {
404                             Cell cell = hssfrow.getCell(i);
405                             if (cell == null) {
406                                 arrayString.add("");
407                             } else if (cell.getCellType() == 0) {
408                                 // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
409                                 if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
410                                     short format = cell.getCellStyle().getDataFormat();
411                                     if(format == 14 || format == 31 || format == 57 || format == 58){
412                                         //日期(中文时间格式的)
413                                         Date d = cell.getDateCellValue();
414                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
415                                         // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
416                                         arrayString.add(formater.format(d));
417                                         //arrayString[i] = formater.format(d);
418                                     }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
419                                         Date d = cell.getDateCellValue();
420                                         //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
421                                         DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
422                                         arrayString.add(formater.format(d));
423                                         //arrayString[i] = formater.format(d);
424                                     } else {
425                                         if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
426                                             arrayString.add(cell.getStringCellValue());
427                                             //arrayString[i] =cell.getStringCellValue();
428                                         }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
429                                             arrayString.add(cell.getCellFormula());
430                                             //arrayString[i] =cell.getCellFormula();
431                                         }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
432                                             HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
433                                             arrayString.add(dataFormatter.formatCellValue(cell));
434                                             //arrayString[i] =dataFormatter.formatCellValue(cell);
435                                         }
436                                     }
437                                 }
438                             } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
439                                 arrayString.add("");
440                                 //arrayString[i] = "";
441                             } else { // 如果EXCEL表格中的数据类型为字符串型
442                                 arrayString.add(cell.getStringCellValue().trim());
443                                 //arrayString[i] = cell.getStringCellValue().trim();
444                             }
445                         }
446                         list.add(arrayString);
447                     }
448                 }
449             }
450             return list;
451         } catch (Exception e) {
452             e.printStackTrace();
453         }
454         return null;
455     }
456
457     private static String getCellVal(Cell cell) {
458         if (null == cell) {
459             return "";
460         }
461         switch (cell.getCellType()) {
462             // 数字
463             case HSSFCell.CELL_TYPE_NUMERIC:
464                 // 日期格式的处理
465                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
466                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
467                     return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
468                 }
469                 return String.valueOf(cell.getNumericCellValue());
470             // 字符串
471             case HSSFCell.CELL_TYPE_STRING:
472                 return cell.getStringCellValue();
473             // 公式
474             case HSSFCell.CELL_TYPE_FORMULA:
475                 return cell.getCellFormula();
476             // 空白
477             case HSSFCell.CELL_TYPE_BLANK:
478                 return "";
479             case HSSFCell.CELL_TYPE_BOOLEAN:
480                 return cell.getBooleanCellValue() + "";
481             // 错误类型
482             case HSSFCell.CELL_TYPE_ERROR:
483                 return cell.getErrorCellValue() + "";
484             default:
485                 break;
486         }
487         return "";
488     }
489
490 }