EasyExcel: 根据excel模板, 在多个sheet页生成数据

在一个excel表格的多个sheet页中写入数据, 并且多个sheet页的数据可以根据同一个模板进行填充, 表格比较复杂时更适合使用模板

开发思路:

1.设计好excel模板

2.读取模板后将模板进行copy至需要的数量(多个sheet页)

3.在多个sheet页中进行数据填充

模板示例:

代码实现如下:

import lombok.Data;
@Data
public class UserExcelBean {

    private String index;
    private String code;
    private String name;
    private String amount;

}

public void export3(HttpServletResponse response) {
        // 多个sheet的数据
        Map<String, List<UserExcelBean>> excelBeanMap = new HashMap<>();
        ArrayList<UserExcelBean> list1 = new ArrayList<>();
        ArrayList<UserExcelBean> list2 = new ArrayList<>();
        ArrayList<UserExcelBean> list3 = new ArrayList<>();
        UserExcelBean e1 = new UserExcelBean();
        e1.setIndex("1");
        e1.setCode("233251");
        e1.setName("张三");
        e1.setAmount("324");
        list1.add(e1);
        excelBeanMap.put("title1", list1);
        UserExcelBean e2 = new UserExcelBean();
        e2.setIndex("1");
        e2.setCode("32545");
        e2.setName("李四");
        e2.setAmount("565");
        list2.add(e2);
        excelBeanMap.put("title2", list2);
        UserExcelBean e3 = new UserExcelBean();
        e3.setIndex("1");
        e3.setCode("2112321");
        e3.setName("赵七");
        e3.setAmount("113");
        list3.add(e3);
        excelBeanMap.put("title3", list3);
        // 这里 指定文件
        InputStream templateInputStream;
        try {
            InputStream fileInputStream = new DefaultResourceLoader().getResource("excelTemplate/test001.xlsx").getInputStream();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //设置模板的第一个sheet的名称
            workbook.setSheetName(0, "Sheet1");
            for (int i = 1; i < excelBeanMap.size(); i++) {
                //复制模板，得到第i个sheet
                int num = i + 1;
                workbook.cloneSheet(0, "Sheet" + num);
            }
            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            templateInputStream = new ByteArrayInputStream(bArray);
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=test001.xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        } catch (IOException e) {
            log.error("下载文件失败 : ", e);
            throw new RuntimeException(e);
        }
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateInputStream).build()) {
            int i = 0;
            for (Map.Entry<String, List<UserExcelBean>> entry : excelBeanMap.entrySet()) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).build();
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                String title = entry.getKey();
                List<UserExcelBean> sheetDataList = entry.getValue();
                // 直接写入数据
                excelWriter.fill(sheetDataList, fillConfig, writeSheet);
                // 写入list之前的数据
                Map<String, Object> map = new HashMap<>();
                map.put("title", title);
                map.put("total", "8999");
                excelWriter.fill(map, writeSheet);
                i++;
            }
        } catch (Exception e) {
            log.error("下载文件失败 : ", e);
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("code", "1");
            map.put("message", "下载文件失败" + e.getMessage());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException ioException) {
                log.error("导出文件出现异常", ioException);
            }
        }
    }

最后生成的excel效果: