Excel模板数据填充导出

Excel模板数据填充导出

需求描述：

POI实现

1.添加依赖：

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.11</version>
  </dependency>
  <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.5.9</version>
  </dependency>

2.具体实现：

/**
     * 打印模板清单
     * @param httpServletResponse
     * @return
     */
    public void printList(HttpServletResponse httpServletResponse) throws Exception {
        Map<String, String> map = new HashMap<>();
        map.put("moveNo","moveNo");
        map.put("comName","comName");
        map.put("currentDate",new DateTime().toString("yyyy-MM-dd"));
        map.put("count", 1+"件");
        map.put("operator","userName");
        map.put("date",new DateTime().toString("yyyy-MM-dd"));

        List<Map<String,Object>> printVoList = new ArrayList<>();
        Map<String, Object> hashMap = new HashMap<>();
        hashMap.put("COL1","123");
        hashMap.put("COL2","234");
        hashMap.put("COL3","345");
        hashMap.put("COL4","456");
        hashMap.put("COL5","567");
        printVoList.add(hashMap);

        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/SimpleTemplates.xlsx");
        ServletOutputStream out = httpServletResponse.getOutputStream();

        httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
        httpServletResponse.setCharacterEncoding("utf-8");
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("test", "utf-8") + ".xlsx");

        if (inputStream == null){
            throw new RuntimeException("文件未找到！");
        }

        Workbook workbook = new XSSFWorkbook(inputStream);

        //获取创建工作簿的第一页
        Sheet sheet=workbook.getSheetAt(0);
        //给指定的sheet命名
        workbook.setSheetName(0,"dataSheet");
        //获取当前工作簿的行数
        int totalRows=sheet.getPhysicalNumberOfRows();
        //****遍历模板sheet，根据当中的设定进行赋值****
        for (int i=0;i<totalRows;i++) {
            Row row = sheet.getRow(i);
            int column = row.getLastCellNum();
            for (int j=0;j<column;j++){
                Cell cell=row.getCell(j)!=null?row.getCell(j):row.createCell(j);
                String cellValue=cell.getStringCellValue();

                if (cellValue.startsWith("#{") && cellValue.endsWith("}")){
                    String key = cellValue.replace("#", "").replace("{", "").replace("}", "");
                    //设置新的单元格存储的值
                    cell.setCellValue(map.get(key));
                }
            }
        }
        int newRowIndex = 5;//起始行 从第6行开始
        //获取模板对应的数据表字段的值
        Row dataRow=sheet.getRow(newRowIndex++);
        //获得该行对应的字段的数量
        int columnNum=dataRow.getPhysicalNumberOfCells();

        //将结果集渲染到当前sheet当中
        for (Map<String,Object> pageData:printVoList) {
            sheet.shiftRows(newRowIndex, totalRows + 1, 1); //在startRow和endRow之间的行移动一行
            Row newRow = sheet.createRow(newRowIndex++);
            //创建需要插入的目标行，该值需要在每次完成一行记录值插入后重新归0
            int cellIndex = 0;

            //ic此处为cells数组的结果集的字段的位置
            for (int i = 1; i <= columnNum; i++) {
                //从pageData当中取出目标单元格需要的值
                String cellContent = pageData.get("COL"+i) != null ? String.valueOf(pageData.get("COL"+i)) : "";
                //设置目标单元格的位置和类型
                Cell cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                //设置目标单元格的值
                cell.setCellValue(cellContent);
            }
        }
        workbook.write(out);
        out.flush();
        out.close();
    }

完成

EasyExcel实现

1.添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.1.6</version>
     <exclusions>
         <exclusion>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi</artifactId>
         </exclusion>
         <exclusion>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi-ooxml</artifactId>
         </exclusion>
         <exclusion>
             <groupId>org.ehcache</groupId>
             <artifactId>ehcache</artifactId>
         </exclusion>
     </exclusions>
 </dependency>

2.添加excel模板
根据自己需要设计模板
两种填充方式：

• {}里面对应的是map的键值对，{}里面与键名匹配就自动填入
• {.}对应的是List的对象属性，{.}里面要与对象的属性字段名对应

3.填充数据：
具体分三步走，
1.构建好所需要map与list
略
2.获取excel模板

InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("static/ReportTemplate.xlsx");
ServletOutputStream out = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(inputStream).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();

3.填充list与map并导出

//列表填充
excelWriter.fill(dayReportListAndAll.getList(), writeSheet);

//值填充
Map<String, Object> map = new HashMap<>();
map.put("reportDate", DateFormatConstant.yyyyMMdd.format(reportDate));
map.put("operator", "张三");
excelWriter.fill(map, writeSheet);

//开始导出
excelWriter.finish();

完成

参考

Java用POI实现根据Excel表格模板生成新的Excel并实现数据输出
2020最简单的java导出excel，使用easyexcel和准备好的excel模板进行数据填充导出