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