SpringBoot集成easyexcel实现动态模板导出

添加依赖

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>3.3.2version>
        dependency>
            
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>4.1.2version>
        dependency>
1
2
3
4
5
6
7
8
9
10
11

查看easyexcel 开发文档，定义excel动态模板

根据自己需求：

单属性用{} + 属性名填充，列表属性用{.} + 属性名填充

代码实战

导入请求，Controller层：

    @GetMapping("/export")
    @ApiOperation("导出盘库范围")
    public void export(@ApiParam("盘库任务id") String id, HttpServletResponse response) {
        storeInventoryTaskService.export(id, response);
    }
1
2
3
4
5

业务实现，主要是查询需导出的数据比较简单：主要，使用map设置属性，map键值与excel中属性名对应上

    public void export(String id, HttpServletResponse response) {
        StoreInventoryTaskDetailVo taskInfo = taskInfo(id);
        List<InventoryListVo> vos = rangeInfo(id);
        Map<String, Object> map = new HashMap<>();
        map.put("inventoryNo", taskInfo.getInventoryNo());
        map.put("storeName", taskInfo.getStoreName());
        map.put("startTime", DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, taskInfo.getStartTime()));
        map.put("endTime", DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, taskInfo.getEndTime()));
        // 自增序号
        AtomicInteger idx = new AtomicInteger();
        vos.forEach(el -> el.setIdx(idx.incrementAndGet()));
        map.put("list", vos);
        try {
            TemplateExcelUtil.downLoadExcel("盘库工单" + taskInfo.getInventoryNo(), "盘库工单模板.xlsx", map, response);
        } catch (Exception e) {
            throw new ServiceException("导出盘库工单报错");
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

核心类

TemplateExcelUtil.downLoadExcel

根据模板名称获取输入流，输出流也为固定套路，同时设置导出excel文件名，其余代码基本和官方文档保持一致即可

@Slf4j
public class TemplateExcelUtil {

    /**
     * 根据模板导出数据
     *
     * @param fileName   导出文件名
     * @param sourcePath resource/template文件夹下路径
     */
    public static void downLoadExcel(String fileName, String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)
            throws Exception {
        try (OutputStream os = getOutputStream(fileName, response);
             InputStream is = TemplateExcelUtil.class.getClassLoader().getResourceAsStream("template/" + sourcePath);
             ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(is).build()) {

            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(beanParams.get("list"), fillConfig, writeSheet);
            excelWriter.fill(beanParams, writeSheet);

            // 关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     */
    private static OutputStream getOutputStream(String fileName,
                                                HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

经测试，导出效果如下：