Excel文件上传，解析，下载（二 Excel文件解析）

文件上传之后的操作是对Excel文件进行解析的操作，按照Excel文件来进行批量创建的操作，这边使用的是POI对Excel文件进行操作

通过文件的路径获得文件 HSSFWorkbook是2003版本的，XSSFWorkbook是2003以上的版本的，两个类实现了Workbook 这个接口。

 1 public Workbook getFileExcel(String fileName, String filePath)
 2     {
 3         InputStream is = null;
 4         Workbook workBook = null;
 5         try {
 6             is = new FileInputStream(filePath + File.separator + fileName);
 7             logger.debug("获得文件" + filePath + File.separator + fileName);
 8             if (fileName.contains(".xls")) {
 9                 workBook = new HSSFWorkbook(is);
10                 //return workBook;
11             }else if (fileName.contains(".xlsx")) {
12                 // 暂时先不考虑这样的问题，如果客户需要代码放开即可使用
13                 // workBook = new XSSFWorkbook(is);
14                 //return workBook;
15             }
16         } catch (FileNotFoundException e) {
17             logger.error("未发现文件" + filePath + File.separator + fileName);
18             logger.error(e.getMessage());
19         } catch (IOException e) {
20             logger.error("读取文件失败" + filePath + File.separator + fileName);
21             logger.error(e.getMessage());
22         }
23         return workBook;
24     }

将表格当中的数据转换成你所想要的对象

1.workBook.getSheetAt(numSheet) 遍历sheet页
2. Row currentRow = currSheet.getRow(rowNum) 遍历行
3.excelOfRowDigester 此方法将每一行的数据解析成一个对象
4.row.getCell(colums[i])获取每个单元格的数据

 1 private List<Spare> excelFileDigester(Workbook workBook) {
 2         List<Spare> spares = new ArrayList<Spare>();
 3         for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
 4             Sheet currSheet = workBook.getSheetAt(numSheet);
 5             if (currSheet == null) {
 6                 continue;
 7             } else {
 8                 for (int rowNum = 1; rowNum < getExcelColumAndRowNum().get(
 9                         SpareTransaction.ROW); rowNum++) {
10                     Row currentRow = currSheet.getRow(rowNum);
11                     if (currentRow == null) {
12                         continue;
13                     } else {
14                         //这一部分需要抽取出来
15                         Spare sp = this.excelOfRowDigester(currentRow);
16                         logger.debug("rowNumTotal:"+getExcelColumAndRowNum().get(SpareTransaction.ROW));
17                         logger.debug("rowNum:"+rowNum);
18                         logger.debug(sp.toString());
19                         spares.add(sp);
20                     }
21                 }
22             }
23         }
24         return spares;
25     }

 1 private Spare excelOfRowDigester(Row row) {
 2         Spare sp = new Spare();
 3         SpareTransaction.setMAPCHECKCOLUMN();
 4         Map<String, String> columnType = SpareTransaction.getMAPCHECKCOLUMN();
 5         Integer[] colums = this.getCheckColumn(); 
 6         for (int i = 0; i < colums.length; i++) {
 7             // 获取验证后的数据 如何获取 哪一些需要用来封装对象
 8             String columnName = columnType.get(String.valueOf(colums[i]));
 9             if (SpareTransaction.NAME.equals(columnName)) {
10                 sp.setSpareName(String.valueOf(row.getCell(colums[i])));
11             }
12             if (SpareTransaction.NAME.equals(columnName)) {
13                 sp.setSpareNo(String.valueOf(row.getCell(colums[i])));
14             }
15             if (SpareTransaction.RESTYPE.equals(columnName)) {
16                 sp.setSpareTypeId(this.searchValuesFromDirectory(String.valueOf(row
17                         .getCell(colums[i]))));
18             }
19             if (SpareTransaction.STOREHOUST.equals(columnName)) {
20                 // 查找所在的机房适配的ID
21                 sp.setRoomId(this.searchValuesFromDirectory(String.valueOf(row
22                         .getCell(colums[i]))));
23             }
24             if (SpareTransaction.SLOT.equals(columnName)) {
25                 //设置插槽
26                 String slotName = String.valueOf(row.getCell(Integer
27                         .valueOf(SpareTransaction.SLOTCOLUMN)));
28                 String layerName = String.valueOf(row.getCell(Integer
29                         .valueOf(SpareTransaction.LAYERCOLUMN)));
30                 String despositName = String.valueOf(row.getCell(Integer
31                         .valueOf(SpareTransaction.STORECHESTCOLUMN)));
32                 sp.setBlongSlotId(this.searchValuesFromDirectory(despositName
33                         + SpareTransaction.SPARESPLITER + layerName
34                         + SpareTransaction.SPARESPLITER + slotName));
35             }
36         }
37         //批量创建设置状态待入库状态
38         sp.setSpareStatusId(SpareTransaction.PREP_INTO_HOUSE_STATE);
39         return sp;
40     }

不同的单元格取值的方式不同数值

 1 public String getCell(Cell cell) {
 2         if (cell == null)
 3             return "";
 4         switch (cell.getCellType()) {
 5         case Cell.CELL_TYPE_NUMERIC:
 6             return cell.getNumericCellValue() + "";
 7         case Cell.CELL_TYPE_STRING:
 8             return cell.getStringCellValue();
 9         case Cell.CELL_TYPE_FORMULA:
10             return cell.getCellFormula();
11         case Cell.CELL_TYPE_BLANK:
12             return "";
13         case Cell.CELL_TYPE_BOOLEAN:
14             return cell.getBooleanCellValue() + "";
15         case Cell.CELL_TYPE_ERROR:
16             return cell.getErrorCellValue() + "";
17         }
18         return "";
19     }