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 }
