使用 Apache poi 导入Excel

本文主要记录Excel导入及模板下载，遇到的问题及注意事项。

第一节：Excel导入

1、如何获取Excel中的最大行，也就是最后一行？

2、如何获取有效行？有效行的定义是每一行记录中每一列中值都不为空。

3、如何读取每一个cell中的值，无论是什么类型的

到导入Excel操作时，发现当导入的Excel内容有格式，或者空的行有格式，读取会和预想的不一样。使用sheet.getLastRowNum()获取最后一行是不准确的。网上查了，也没有找到有效的方法。这里不知道大家有好的方法没，或者大家在项目中怎么导入？

总结一下，共有如下两个问题：

问题一：如何获取正确的Excel的有效最大行。

首先搞明白为什么需要获取Excel的有效最大行，因为考虑到Excel中数据量太大，有可能会响应超时，所以在导入之前要判断是否满足系统要求的导入最大行数，超出最大行，抛出异常。

在实现过程中发现如果Excel中有效行只有一行，其他有200行是有样式的空内容，那么使用sheet.getLastRowNum()获取的最大行数是202行（加表头），获得202行这个数字是错误的，实际Excel中的有效最大行是1行。

我的处理方式是：

首先，定义一个List集合，用来存储有效的Row。

那么什么是有效的Row呢？这个标准是什么？假如导入的模板要求一行是七列，那么判断每一行的这个七个Cell满足不为null且不为""，，则为有效行。

循环这个Excel，将满足条件的有效行add中List集合中，获取这个List集合的size值，通过size值来校验Excel内容的最大行数，这个数字基本上可以认为是正确的。

具体实现看下面这块代码：

  1 /**
  2        * @param returnMap
  3        * @Title: dealExcelData
  4        * @Description: TODO(保存Excel中的数据，并过滤重复的记录)
  5        * @author: yanghai
  6 
  7        * @param: @param contents 存储 Excel中的内容
  8        * @param: @param item 上传的Excel元素
  9        * @param: @param request
 10        * @param: @param repeatCount
 11        * @return: void
 12        * @throws
 13        */
 14 private void dealExcelData(List<CompanyInvoiceRecord> contents, MultipartFile item, HttpServletRequest request, Integer repeatCount, Map<String, Object> returnMap) throws Exception
 15       {
 16             List<Integer> l = new ArrayList<Integer>();
 17             Integer count = 0;
 18             CompanyInvoiceRecord dto = null;
 19 
 20             //临时文件名称
 21             String tempDir = "/files-" +DateTool.formatDate(System.currentTimeMillis(), "yyyy-MM-dd-HH-mm");
 22             //临时文件全路径
 23             String tempFileDir = request.getSession().getServletContext().getRealPath(tempDir);
 24             //创建临时文件
 25             File tempFile = new File(tempFileDir);
 26             if(!tempFile.exists())
 27             {
 28                   tempFile.mkdir();
 29             }
 30 
 31             //获取原始文件全名称
 32             String originalFilename = item.getOriginalFilename();
 33             // 获取文件后缀
 34             String suffix = "";
 35             try
 36             {
 37                   suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
 38             }
 39             catch (Exception e)
 40             {
 41                   e.printStackTrace();
 42                   throw new Exception("没有文件信息！");
 43             }
 44             //完整的文件目录
 45             String fileName = tempFileDir + File.separator + originalFilename;
 46             File newFile = new File(fileName);
 47 
 48             try
 49             {
 50                   // 保存到一个目标文件中。
 51                   item.transferTo(newFile);
 52             }
 53             catch (Exception e)
 54             {
 55                   e.printStackTrace();
 56                   throw new Exception("保存上传Excel文件失败！");
 57             }
 58             Workbook wb = null;
 59 
 60             FormulaEvaluator formulaEvaluator = null;
 61 
 62             try
 63             {
 64                   FileInputStream inputStream = new FileInputStream(newFile);
 65 
 66                   if(suffix.endsWith("xls"))
 67                   {
 68                         wb = new HSSFWorkbook(inputStream);
 69 
 70                         formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
 71                   }
 72                   else
 73                   {
 74                         wb = new XSSFWorkbook(inputStream);
 75 
 76                         formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
 77                   }
 78             }
 79             catch (IOException e)
 80             {
 81                   // 删除目录
 82                   deleteDir(new File(tempFileDir));
 83                   e.printStackTrace();
 84 
 85             }
 86 
 87             // 保存有效的 Excel行
 88             List<Row> rowList = new ArrayList<Row>();
 89 
 90             Sheet sheet = wb.getSheetAt(0);
 91             if(null == sheet)
 92             {
 93                   deleteDir(new File(tempFileDir));
 94                   throw new Exception("导入失败：导入文件中不存在sheet页！");
 95             }
 96             else
 97             {
 98                   /*int lastRowNum = sheet.getLastRowNum();
 99                   System.out.println("==============="+lastRowNum);
100                   if(lastRowNum > 5001)
101                   {
102                         throw new Exception("超过导入上限。最多导入5000条！");
103                   }*/
104                   try
105                   {
106                         for(Row row : sheet)
107                         {
108                               // 校验表头
109                               if(row.getRowNum() == 0)
110                               {
111                                     if(StringUtils.isNotEmpty(row.getCell(0).toString().trim()) && "付款日期".equals(row.getCell(0).toString().trim())
112                                                 && StringUtils.isNotEmpty(row.getCell(1).toString().trim()) && "发票号".equals(row.getCell(1).toString().trim())
113                                                 && StringUtils.isNotEmpty(row.getCell(2).toString().trim()) && "金额".equals(row.getCell(2).toString().trim())
114                                                 && StringUtils.isNotEmpty(row.getCell(3).toString().trim()) && "税额".equals(row.getCell(3).toString().trim())
115                                                 && StringUtils.isNotEmpty(row.getCell(4).toString().trim()) && "合计".equals(row.getCell(4).toString().trim())
116                                                 && StringUtils.isNotEmpty(row.getCell(5).toString().trim()) && "公司名称".equals(row.getCell(5).toString().trim())
117                                                 && StringUtils.isNotEmpty(row.getCell(6).toString().trim()) && "货物名称".equals(row.getCell(6).toString().trim())
118                                                 && StringUtils.isNotEmpty(row.getCell(7).toString().trim()) && "申请人".equals(row.getCell(7).toString().trim())
119                                                 && StringUtils.isNotEmpty(row.getCell(8).toString().trim()) && "申请金额".equals(row.getCell(8).toString().trim())
120                                        )
121                                     {
122                                           continue;
123                                     }
124                                     else
125                                     {
126                                           deleteDir(new File(tempFileDir));
127                                           throw new Exception("表头信息错误！");
128                                     }
129                               }
130                               else if(row.getRowNum() >= 1)
131                               {
132 
133                                     try
134                                     {
135                                           if((null == row.getCell(0) || String.valueOf(row.getCell(0)).equals(""))
136                                                       && (null == row.getCell(1) || String.valueOf(row.getCell(1)).equals(""))
137                                                       && (null == row.getCell(2) || String.valueOf(row.getCell(2)).equals(""))
138                                                       && (null == row.getCell(3) || String.valueOf(row.getCell(3)).equals(""))
139                                                       && (null == row.getCell(4) || String.valueOf(row.getCell(4)).equals(""))
140                                                       && (null == row.getCell(5) || String.valueOf(row.getCell(5)).equals(""))
141                                                       && (null == row.getCell(6) || String.valueOf(row.getCell(6)).equals(""))
142                                                       && (null == row.getCell(7) || String.valueOf(row.getCell(7)).equals(""))
143                                                       && (null == row.getCell(8) || String.valueOf(row.getCell(8)).equals("")))
144                                           {
145                                                 System.out.println("===公司费用 发票 导入 记录 导入===此行"+row.getRowNum()+"为空");
146                                           }
147                                           else
148                                           {
149                                                 rowList.add(row);
150                                           }
151                                     } catch (Exception e1)
152                                     {
153                                           e1.printStackTrace();
154                                     }
155                               }
156                         }
157 
158                         if(null != rowList && rowList.size() > 0)
159                         {
160                               int lastRowNum = rowList.size();
161                               System.out.println("==============="+lastRowNum);
162                               if(lastRowNum > 5001)
163                               {
164                                     throw new Exception("超过导入上限。最多导入5000条！");
165                               }
166 
167                               for(Row row : rowList)
168                               {
169                                     try
170                                     {
171                                           dto = new CompanyInvoiceRecord();
172 
173                                           short lastCellNum = row.getLastCellNum();
174                                           if(lastCellNum < 1)
175                                           {
176                                                 deleteDir(new File(tempFileDir));
177                                                 throw new Exception("第" + row.getRowNum() + "行列数不足!");
178                                           }
179 
180                                           if(StringUtils.isNotEmpty(row.getCell(0).toString()))
181                                           {
182                                                 // 付款日期
183                                                 dto.setPayDate(readCellToStringToTrim(row.getCell(0)).toString().trim());
184                                           }
185 
186                                           row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
187                                           if(StringUtils.isNotEmpty(row.getCell(1).toString().trim()))
188                                           {
189                                                 // 发票号码
190                                                 dto.setInvoiceNum(readCell(row.getCell(1)).toString().trim());
191                                           }
192                                           if(StringUtils.isNotEmpty(row.getCell(2).toString()))
193                                           {
194                                                 // 金额
195                                                 dto.setAmount(readCell(row.getCell(2)).toString().trim());
196                                           }
197                                           if(StringUtils.isNotEmpty(row.getCell(3).toString()))
198                                           {
199                                                 // 税额
200                                                 dto.setTaxAmount(readCell(row.getCell(3)).toString().trim());
201                                           }
202                                           if(StringUtils.isNotEmpty(row.getCell(4).toString()))
203                                           {
204                                                 // 合计
205                                                 dto.setTotalAmount(readCell(row.getCell(4)).toString().trim());
206                                           }
207                                           if(StringUtils.isNotEmpty(row.getCell(5).toString()))
208                                           {
209                                                 // 抬头
210                                                 dto.setTitle(readCell(row.getCell(5)).toString().trim());
211                                           }
212                                           if(StringUtils.isNotEmpty(row.getCell(6).toString()))
213                                           {
214                                                 // 货物名称
215                                                 dto.setGoodsName(readCell(row.getCell(6)).toString().trim());
216                                           }
217                                           if(StringUtils.isNotEmpty(row.getCell(7).toString()))
218                                           {
219                                                 // 申请人
220                                                 dto.setApplicantName(readCell(row.getCell(7)).toString().trim());
221                                           }
222                                           if(StringUtils.isNotEmpty(row.getCell(8).toString()))
223                                           {
224                                                 // 申请金额
225                                                 dto.setApplyAmount(readCell(row.getCell(8)).toString().trim());
226                                           }
227 
228                                           if(StringUtils.isEmpty(dto.getPayDate()) && StringUtils.isEmpty(dto.getInvoiceNum())
229                                                       && StringUtils.isEmpty(dto.getAmount()) && StringUtils.isEmpty(dto.getTitle())
230                                                       && StringUtils.isEmpty(dto.getTaxAmount()) && StringUtils.isEmpty(dto.getTotalAmount())
231                                                       && StringUtils.isEmpty(dto.getGoodsName()) && StringUtils.isEmpty(dto.getApplicantName())
232                                                       && StringUtils.isEmpty(dto.getApplyAmount()))
233                                           {
234 
235                                           }
236                                           else
237                                           {
238                                                 Boolean flag = true;
239                                                 if(contents.contains(dto)) // 已包含
240                                                 {
241                                                       repeatCount++;
242                                                       flag = false;
243                                                 }
244                                                 if(flag)
245                                                 {
246                                                       contents.add(dto);
247                                                 }
248                                           }
249                                     } catch (Exception e)
250                                     {
251                                           l.add(row.getRowNum()+1);
252                                           count++;
253                                           System.out.println("==公司费用导入异常："+e);
254                                     }
255                               }
256                         }
257 
258                         returnMap.put("count", count);
259                         returnMap.put("repeatCount", repeatCount);
260 
261                         System.out.println("======异常条数："+count+",发生异常的行数分别是："+l.toString());
262                         // 删除excel
263                         deleteDir(new File(tempFileDir));
264 
265                   } catch (Exception e)
266                   {
267                         e.printStackTrace();
268                         deleteDir(new File(tempFileDir));
269                         throw new Exception("请按照要求填写Excel的内容！");
270                   }
271             }
272       }
273 
274      /**
275        * 删除文件夹及文件夹下的内容
276        * @param dir
277        * @return
278        */
279       private boolean deleteDir(File dir)
280       {
281         if (dir.isDirectory())
282         {
283             String[] children = dir.list();
284             // 递归删除目录中的子目录下
285             for (int i = 0; i < children.length; i++)
286             {
287                 boolean success = deleteDir(new File(dir, children[i]));
288                 if (!success)
289                 {
290                     return false;
291                 }
292             }
293         }
294         // 目录此时为空，可以删除
295         return dir.delete();
296       }

View Code

问题二：如何正确转换数据类型。

解释一下这个问题，假如我这一列的数据名称是“发票号码”，是由数字组成的字符串，那么在保存在数据库中，期望保存的是“123456”，而不是“123456.0”，保存的有小数点，说明在读取Excel的内容是，当成了数字类型了，这就涉及到Excel中几种数据类型的转换了。

当然上面针对这个问题有个处理技巧，当知道这一列是字符串类型，可以直接读取cell中内容前将cellType设置为CELL_TYPE_STRING。

参考Cell接口源码，粘出Excel中Cell都有下面几种cellType:

 1 /**
 2      * Numeric Cell type (0)
 3      * @see #setCellType(int)
 4      * @see #getCellType()
 5      */
 6     public final static int CELL_TYPE_NUMERIC = 0; // 数字类型
 7 
 8     /**
 9      * String Cell type (1)
10      * @see #setCellType(int)
11      * @see #getCellType()
12      */
13     public final static int CELL_TYPE_STRING = 1; // 字符串类型
14 
15     /**
16      * Formula Cell type (2)
17      * @see #setCellType(int)
18      * @see #getCellType()
19      */
20     public final static int CELL_TYPE_FORMULA = 2; // 公式类型
21 
22     /**
23      * Blank Cell type (3)
24      * @see #setCellType(int)
25      * @see #getCellType()
26      */
27     public final static int CELL_TYPE_BLANK = 3; // 空白类型
28 
29     /**
30      * Boolean Cell type (4)
31      * @see #setCellType(int)
32      * @see #getCellType()
33      */
34     public final static int CELL_TYPE_BOOLEAN = 4; // 布尔类型 
35 
36     /**
37      * Error Cell type (5)
38      * @see #setCellType(int)
39      * @see #getCellType()
40      */
41     public final static int CELL_TYPE_ERROR = 5; // 错误类型

View Code

时间内容也属于CELL_TYPE_NUMERIC类型，如果是时间类型，进行相应的时间格式转换，否则不用做处理。时间格式在本人处理起来比较麻烦，开发前一定要做好约束规范，否则，在读取Excel中内容需要考虑各种类型，代码是控制不了的。

关于处理Excel中各种数据类型，这里本人整理了一个通用方法，基本上可以满足使用。

 1   /**
 2        * @description:读取Excel单元格数据
 3        * @param cell excel单元格
 4        * @return String
 5        */
 6       private static String readCell(Cell cell)
 7       {
 8             String cell_value = "";
 9 
10             if (cell != null)
11             {
12                   switch (cell.getCellType())
13                   {
14                   case Cell.CELL_TYPE_BOOLEAN:
15                         // 得到Boolean对象的方法
16                         if (cell.getBooleanCellValue())
17                         {
18                               cell_value = "TRUE";
19                         } else
20                         {
21                               cell_value = "FALSE";
22                         }
23                         break;
24                   case Cell.CELL_TYPE_NUMERIC:
25                         // 先看是否是日期格式
26                         if (DateUtil.isCellDateFormatted(cell))
27                         {
28                               // 读取日期格式
29                               cell_value = DateUtils.formatDate(cell.getDateCellValue(), "yyyy-MM-dd");
30                         } else
31                         {
32                               // 读取数字
33                               cell_value = String.valueOf(cell.getNumericCellValue());
34                         }
35                         break;
36                   case Cell.CELL_TYPE_FORMULA:
37                         // 读取公式的值
38                         cell_value = cell.getCellFormula();
39                         break;
40                   case Cell.CELL_TYPE_STRING:
41                         // 读取String
42                         cell_value = cell.getRichStringCellValue().getString();
43                         break;
44                   case Cell.CELL_TYPE_ERROR:
45                         cell_value = cell.getErrorCellValue() + "";
46                   break;
47                   case HSSFCell.CELL_TYPE_BLANK:
48                         cell_value = "";
49                         break;
50                   default:
51                         cell_value = "";
52                   }
53             }
54             return cell_value;
55       }

View Code

第二节：Excel模板下载

关于模板下载，贴出实现代码。

 1 @RequestMapping("/downExcel")
 2       public ModelAndView downBlack(HttpServletRequest request, HttpServletResponse response) throws IOException
 3       {
 4             String realPathName = "";
 5             String tempPath = "";
 6             String fileName = "";
 7 
 8             BufferedInputStream bis = null;
 9             BufferedOutputStream bos = null;
10 
11             try {
12                   tempPath = request.getSession().getServletContext().getRealPath("/") + "/download/";
13                   fileName = "batchReceivedTicketTemplate.xlsx";
14                   realPathName = tempPath + fileName;
15 
16                   long fileLength = new File(tempPath + fileName).length();
17 
18                   // 文件下载设置response
19                   response.setContentType("text/html;charset=utf-8");
20                   request.setCharacterEncoding("UTF-8");
21                   response.setContentType("application/x-msdownload;");
22 
23                   // 火狐
24                   if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
25                   {
26                         response.setHeader("Content-disposition", "attachment; filename=" + new String("批量收票导入模板.xlsx".getBytes("utf-8"), "ISO8859-1"));
27                   }
28                   else
29                   {
30                         response.setHeader("Content-Disposition", "attachment;filename=" + new String("批量收票导入模板.xlsx".getBytes("gb2312"), "ISO8859-1"));
31                   }
32 
33                   response.setHeader("Content-Length", String.valueOf(fileLength));
34 
35                   // 从模板获取输入流
36                   bis = new BufferedInputStream(new FileInputStream(realPathName));
37 
38                   // 输出流
39                   bos = new BufferedOutputStream(response.getOutputStream());
40 
41                   // 读取文件流输出
42                   byte[] buff = new byte[2048];
43                   int bytesRead;
44                   while (-1 != (bytesRead = bis.read(buff, 0, buff.length)))
45                   {
46                         bos.write(buff, 0, bytesRead);
47                   }
48             } catch (UnsupportedEncodingException e) {
49                   e.printStackTrace();
50             } catch (FileNotFoundException e) {
51                   e.printStackTrace();
52             } catch (IOException e) {
53                   e.printStackTrace();
54             }
55             finally
56             {
57                   if (bis != null)
58                         bis.close();
59                   if (bos != null)
60                         bos.close();
61             }
62 
63             return null;
64       }

View Code

这段代码，很清晰，在实现的过程中，要注意一点，就是模板的后缀要和下载到的模板文件后缀保持一致。否则在导入的时候会报下面这个异常信息提示：

以上内容均由本人实际工作中遇到的问题及个人总结，如有错误，欢迎大家指正！

相关阅读:
Java使用Apache Commons Exec运行本地命令行命令
 Win10安装PostgreSQL9.6
软件工程学习笔记——软件工具
 软件工程学习笔记——软件过程模型
 软件工程学习笔记——软件过程
 软件工程学习笔记——软件生存周期
 软件工程学习笔记——软件工程基本原理
 MySQL学习笔记——MySQL5.7的启动过程（一）
Windows下免费的屏幕录制软件——EV录屏——推荐
 SecureCRT 8.1破解方式
原文地址：https://www.cnblogs.com/yangh965/p/6626966.html