POI使用

最新推荐文章于 2024-01-23 19:51:51 发布

原创最新推荐文章于 2024-01-23 19:51:51 发布 · 332 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#apache #poi #excel

Apache POI 使用小记

最近通过项目学习了Apache POI操作Excel的方法，记录一下方便日后使用。

首先导入Excel，生成WorkBook实例，之后便可以通过workbook读取Excel文档中的数据

public void importWorkBook(String filename){

      WorkBook wb =null;
      wb = getWorkBook(filename);  //检查导入的文件是哪一个版本的excel 创建对应的工作簿
      if(wb == null) 
           return;
      int numbs = wb.getNumberOfSheets();  //获取工作簿中的工作表数目
      if(numbs > 0){
            for(int i=0;i<numbs;i++){   //遍历每个工作表，进行相应的处理，比如将表中数据存入数据库
                Sheet sheet = wb.getSheetAt(i);
                if(sheet!=null &&  sheetName.equals("工程造价指标(定额)")){
                    ...
                }else if(sheet!=null && sheetName.contains("工程造价指标(清单)")){
                    saveSheetToDb(sheet);
                }else{
                    continue;
                }
            }
        }

}

生成WorkBook实例

private Workbook getWorkbook (String filename) {
    Workbook wb = null;
    if (filename == null || !filename.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
        return null;
    }
    boolean isExcel2003 = true;
    /** 对文件的合法性进行验证 */
    if (filename.matches("^.+\\.(?i)(xlsx)$")) {
        isExcel2003 = false;
    }
    /** 检查文件是否存在 */
    File file = new File(filename);
    if (file == null || !file.exists()) {
        return null;
    }
    /** 根据版本选择创建Workbook的方式 */
    try {
        wb = (Workbook) (isExcel2003 ? new HSSFWorkbook(new FileInputStream(file)) : new XSSFWorkbook(new FileInputStream(file)));
    } catch (FileNotFoundException e) {
        logger.error(e.getMessage(),e);
    } catch (IOException e) {
        logger.error(e.getMessage(),e);
    }
    return wb;
}

将每张工作表中的数据保存到数据库中

private void saveSheetToDb(Sheet sheet){
    Map<Integer, List<String>> rowMap = ExcelUtil.parseSheet(sheet); //导入的行集合
    Map<String, Object> resMap= ExcelUtil.resMap;
    Map<String, Object> modelMap = (Map<String, Object>) resMap.get("model");//行模型集合
    Map<String, Object> paramMap = (Map<String, Object>) resMap.get("param");//参数集合
    List<Entity> resList = new ArrayList<>();//将要插入数据库的实体集合

    //1 把行集合转化成一张或多张表格
    List<List<Integer>> tableList = ExcelUtil.convertToTableList(rowMap, (List<String>) modelMap.get("tableHeadModelRow"));

    for (List<Integer> table : tableList) {
           /*
                一张Sheet工作表中可能存在多张表格数据 
                根据定义的行模型集合modelMap和参数集合paramMap  
                找到需要的数据
                将其封装为实体类Entiy
           */
           Entity entity = new Entity(,,,,);
           resList.add(entity);
    }

    // 插入费用到数据库，如果实体数据条数大于200条，分批次插入
    if (!resList.isEmpty()){
        if (resList.size() > 200){
            int size = resList.size();
            int times = size % 200 == 0 ? size / 200 : size / 200 + 1;
            List<Entity> list = null;
            for (int i = 1; i <= times; i++){
                if (i == times){
                    list = resList.subList((i -1) * 200, size);
                }else {
                    list = resList.subList((i -1) * 200, i * 200);
                }
                zbFillDonorSpreadMapper.addBatchDonorSpread(list);
            }
        }else {
            zbFillDonorSpreadMapper.addBatchDonorSpread(resList);
        }
    }

}

ExcelUtil parseSheet 方法

/**
 * 把页签解析成行集合
 * @param sheet 页签
 * @return 行集合，key为行索引，value为单元格集合
 */
public static Map<Integer, List<String>> parseSheet(Sheet sheet){
   int rowNum = sheet.getLastRowNum() + 1;
   Map<Integer, List<String>> rowMap = new LinkedHashMap<>();
   for (int r = 0; r < rowNum; r++){
       Row row = sheet.getRow(r);
       List<String> cellList = new ArrayList<>();
       boolean isDataList = false;
       for (int c = 0; c < row.getLastCellNum(); c++){
           Cell cell = row.getCell(c);
           cell.setCellType(Cell.CELL_TYPE_STRING);
           String value = getCellValue(row, c);
           if (value == null){
               cellList.add("?");//占位符
           }else {
               if (Pattern.matches(IS_NUM_EXP, value)){
                   isDataList = true;
               }
               cellList.add(value.replaceAll("\\s", ""));
           }
       }
       if (isDataList){
           cellList.add("dataRowFlag");//增加标识，用于识别数据行
       }
       rowMap.put(r, cellList);
   }
    return rowMap;
}

ExcelUtil convertToTableList 方法

 /**
 * 把行集合按表头规则解析成多个表格
 * @param rowMap 行集合
 * @param tableHeadModelRow 表头模板
 * @return 一个或多个表格的集合,集合保存的是行号，并不是数据
 */
public static List<List<Integer>> convertToTableList(Map<Integer, List<String>> rowMap, List<String> tableHeadModelRow){
    List<List<Integer>> tableList = new ArrayList<>();
    for (Map.Entry<Integer, List<String>> entry : rowMap.entrySet()) {//遍历所有行
        List<String> row = entry.getValue();
        int rowIndex = entry.getKey();
        if (row.containsAll(tableHeadModelRow)){//如果行是表头，创建新的表
            tableList.add(new ArrayList<Integer>());
            tableList.get(tableList.size() - 1).add(rowIndex);
        }else if (tableList.size() > 0){//如果不是表头行，并且不为页签内第一个表头行上方的行
            tableList.get(tableList.size() - 1).add(rowIndex);
        }
    }
    return tableList;
}

下面列出resMap的数据和对应的一份excel文件截图

ExcelUtil.resMap = 
{
    model={
        normTitleModelRow=[
            管理费,
            利润,
            风险费用
        ],
        tableHeadModelRow=[
            项目编码,
            项目名称,
            计量单位,
            综合单价
        ],
        totalModelRow=[
            合计,
            —,
            —
        ],
        machineHeadModelRow=[
            3.机械
        ],
        labourHeadModelRow=[
            1.人工
        ],
        materialHeadModelRow=[
            2.材料
        ],
        dataModelRow=[
            dataRowFlag
        ],
        resTableHeadModelRow=[
            人工、材料及机械名称,
            单位,
            数量,
            基价单价,
            基价合价,
            市场单价,
            市场合价,
            备注
        ]
    },
    param={
        profitFee={
            name=利润,
            cellOffset=0,
            rowOffset=-185
        },
        managementFee={
            name=管理费,
            cellOffset=0,
            rowOffset=-185
        },
        unit={
            name=单位,
            cellOffset=-1,
            rowOffset=-1
        },
        riskFee={
            name=风险费用,
            cellOffset=0,
            rowOffset=-185
        },
        quatity={
            name=数量,
            cellOffset=-1,
            rowOffset=-1
        },
        unitPrice={
            name=市场单价,
            cellOffset=-1,
            rowOffset=-1
        },
        qdCode={
            name=项目编码,
            cellOffset=1,
            rowOffset=0
        },
        costName={
            name=人工、材料及机械名称,
            cellOffset=-1,
            rowOffset=-1
        }
    }
}