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

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

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

public Workbook getFileExcel(String fileName, String filePath)
    {
        InputStream is = null;
        Workbook workBook = null;
        try {
            is = new FileInputStream(filePath + File.separator + fileName);
            logger.debug("获得文件" + filePath + File.separator + fileName);
            if (fileName.contains(".xls")) {
                workBook = new HSSFWorkbook(is);
                //return workBook;
            }else if (fileName.contains(".xlsx")) {
                // 暂时先不考虑这样的问题，如果客户需要代码放开即可使用
                // workBook = new XSSFWorkbook(is);
                //return workBook;
            }
        } catch (FileNotFoundException e) {
            logger.error("未发现文件" + filePath + File.separator + fileName);
            logger.error(e.getMessage());
        } catch (IOException e) {
            logger.error("读取文件失败" + filePath + File.separator + fileName);
            logger.error(e.getMessage());
        }
        return workBook;
    }

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

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

private List<Spare> excelFileDigester(Workbook workBook) {
        List<Spare> spares = new ArrayList<Spare>();
        for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
            Sheet currSheet = workBook.getSheetAt(numSheet);
            if (currSheet == null) {
                continue;
            } else {
                for (int rowNum = 1; rowNum < getExcelColumAndRowNum().get(
                        SpareTransaction.ROW); rowNum++) {
                    Row currentRow = currSheet.getRow(rowNum);
                    if (currentRow == null) {
                        continue;
                    } else {
                        //这一部分需要抽取出来
                        Spare sp = this.excelOfRowDigester(currentRow);
                        logger.debug("rowNumTotal:"+getExcelColumAndRowNum().get(SpareTransaction.ROW));
                        logger.debug("rowNum:"+rowNum);
                        logger.debug(sp.toString());
                        spares.add(sp);
                    }
                }
            }
        }
        return spares;
    }

private Spare excelOfRowDigester(Row row) {
        Spare sp = new Spare();
        SpareTransaction.setMAPCHECKCOLUMN();
        Map<String, String> columnType = SpareTransaction.getMAPCHECKCOLUMN();
        Integer[] colums = this.getCheckColumn(); 
        for (int i = 0; i < colums.length; i++) {
            // 获取验证后的数据 如何获取 哪一些需要用来封装对象
            String columnName = columnType.get(String.valueOf(colums[i]));
            if (SpareTransaction.NAME.equals(columnName)) {
                sp.setSpareName(String.valueOf(row.getCell(colums[i])));
            }
            if (SpareTransaction.NAME.equals(columnName)) {
                sp.setSpareNo(String.valueOf(row.getCell(colums[i])));
            }
            if (SpareTransaction.RESTYPE.equals(columnName)) {
                sp.setSpareTypeId(this.searchValuesFromDirectory(String.valueOf(row
                        .getCell(colums[i]))));
            }
            if (SpareTransaction.STOREHOUST.equals(columnName)) {
                // 查找所在的机房适配的ID
                sp.setRoomId(this.searchValuesFromDirectory(String.valueOf(row
                        .getCell(colums[i]))));
            }
            if (SpareTransaction.SLOT.equals(columnName)) {
                //设置插槽
                String slotName = String.valueOf(row.getCell(Integer
                        .valueOf(SpareTransaction.SLOTCOLUMN)));
                String layerName = String.valueOf(row.getCell(Integer
                        .valueOf(SpareTransaction.LAYERCOLUMN)));
                String despositName = String.valueOf(row.getCell(Integer
                        .valueOf(SpareTransaction.STORECHESTCOLUMN)));
                sp.setBlongSlotId(this.searchValuesFromDirectory(despositName
                        + SpareTransaction.SPARESPLITER + layerName
                        + SpareTransaction.SPARESPLITER + slotName));
            }
        }
        //批量创建设置状态待入库状态
        sp.setSpareStatusId(SpareTransaction.PREP_INTO_HOUSE_STATE);
        return sp;
    }

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

public String getCell(Cell cell) {
        if (cell == null)
            return "";
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            return cell.getNumericCellValue() + "";
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        case Cell.CELL_TYPE_BLANK:
            return "";
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue() + "";
        case Cell.CELL_TYPE_ERROR:
            return cell.getErrorCellValue() + "";
        }
        return "";
    }