解析Excel获取单元格数据

  今天自己写了一遍解析Excel的方法，虽然也是根据网上一些例子来写的，但是一边写一边在网上查找这些类、方法的含义及左右，感觉还是有所收获的。
  记录一下以后方便使用。

	private static final String  xls = "xls";
    private static final String  xlsx = "xlsx";

    /**
     * 读取excel文件内容
     * @param file MultipartFile
     * @return List<SQLEntity>
     * @throws Exception
     */
    public static List<SQLEntity> excel(MultipartFile file) throws Exception {

        Workbook workbook = getWorkBook(file);//获取getWorkBook实例

        List<SQLEntity> list = new ArrayList<>();//返回的List

        if(workbook == null) return list;

        int sheetNum = workbook.getNumberOfSheets();//sheet的页数

        for(int num = 0; num < sheetNum; num++){
            Sheet sheet = workbook.getSheetAt(num);//获取当前下标的Sheet

            if(sheet == null) continue;

            int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的开头行
            int lastRowNum = sheet.getLastRowNum();//获得当前sheet的结束行

            for(int rowNum = firstRowNum+1; rowNum <= lastRowNum; rowNum++){//遍历除第一行之外的行
                Row row = sheet.getRow(rowNum);//获得当前行

                if(row == null) continue;

                int firstCellNum = row.getFirstCellNum();//获得当前row的开始列
                int lastCellNum = row.getPhysicalNumberOfCells();//获取有记录的列

                //创建实体类
                SQLEntity sqlEntity = new SQLEntity();
                for(int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++){
                    Cell cell = row.getCell(cellNum);
                    //String cellValue = cell.toString();
                    String cellValue = getCellValue(cell);

                    //根据不同下标将cell的value放入实体中
                    switch (cellNum){
                        case 0:
                            sqlEntity.setName(cellValue);
                            break;
                        case 1:
                            sqlEntity.setDesc(cellValue);
                            break;
                    }
                }
                list.add(sqlEntity);
            }
        }
        workbook.close();
        return list;
    }

    /**
     * 生产WorkBook对象
     * @param file MultipartFile
     * @return Workbook
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        Workbook workbook = null;

        InputStream fis = file.getInputStream();;//创建输入流
        //这里用BufferedInputStream再包装一层，可解决：mark/reset not supported问题
        BufferedInputStream bis = new BufferedInputStream(fis);
        String[] splitName = file.getOriginalFilename().split("\\.");//分割名称，获取后缀
        String suffix = splitName[splitName.length-1];

        //检查是否为excel文件
        if(splitName==null && !(xls.equals(suffix) || xlsx.equals(suffix))) return workbook;

        switch(suffix){
            case xls://2003及以下
                workbook = new HSSFWorkbook(bis);
                break;
            case xlsx://2007及以上
                workbook = new XSSFWorkbook(bis);
                break;
        }
        return workbook;
    }

    /**
     * 将不同cellType都转为String类型
     * @param cell
     * @return String
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";

        if(cell == null) return cellValue;

        //将数字转成string来读取，如果不注释会出现1.5转成1.0的现象
        //if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cell.setCellValue(Cell.CELL_TYPE_STRING);

        switch(cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC://数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING://字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN://布尔
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA://公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_ERROR://错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_BLANK://空
                break;
            default:
                cellValue = "默认";
                break;
        }
        return cellValue;
    }