使用 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 }
问题二：如何正确转换数据类型。
解释一下这个问题，假如我这一列的数据名称是“发票号码”，是由数字组成的字符串，那么在保存在数据库中，期望保存的是“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; // 错误类型
时间内容也属于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 }
第二节：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 }
这段代码，很清晰，在实现的过程中，要注意一点，就是模板的后缀要和下载到的模板文件后缀保持一致。否则在导入的时候会报下面这个异常信息提示：
以上内容均由本人实际工作中遇到的问题及个人总结，如有错误，欢迎大家指正！
