EasyExcel动态导出多个sheet的自定义excel模板

一.导出Excel ①header头部数据格式：

//key为excel表的sheet名称，value为对应sheet的头部数据
Map<String, List<?>> map = new HashMap<>();

②data表格数据：

//填充进excel的数据
List<?> dataList；

③下载表格：

/**
     * 多个sheet模板下载
     *
     * @param response
     * @throws IOException
     */
public void downloadMultiSheet(HttpServletResponse response) throws IOException {
          
   
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(downloadName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = null;
        excelWriter = genTemplateMultiSheet(response);
        //切记一定要关闭才能完成下载
        excelWriter.finish();
    }

/**
     * 填充多个sheet模板数据
     *
     * @param response
     */
    private ExcelWriter genTemplateMultiSheet(HttpServletResponse response) throws IOException {
          
   
        XSSFWorkbook workbook = new XSSFWorkbook(FormTemplateGenExcel.class.getResourceAsStream(templateName));
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        //根据传入的map新建多个sheet并设置name
        int j = 0;
        for (Map.Entry<String, List<Json>> entry : multiSheetHeaderMap.entrySet()) {
          
   
            if (j == 0) {
          
   
            	//首先设置第一个sheet的名称
                workbook.setSheetName(j, entry.getKey());
                j++;
                continue;
            }
            //循环克隆模板第一个sheet，修改为其他名称
            workbook.cloneSheet(0, entry.getKey());
        }
        //重新构建新的输入流
        workbook.write(bos);
        byte[] bArray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bArray);
        //创建新的excelWriter
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is).build();

        //将map数据分页填充头部和监测值
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();

        for (Map.Entry<String, List<Json>> entry : multiSheetHeaderMap.entrySet()) {
          
   
            WriteSheet writeSheet = EasyExcel.writerSheet(entry.getKey()).registerWriteHandler(new ExcelWidthStyleStrategy()).build();
            //excel模板中自定义设置的头部字段
            excelWriter.fill(new FillWrapper("item", entry.getValue()), fillConfig, writeSheet);
            //excel模板中自定义设置的数据字段
            excelWriter.fill(new FillWrapper("data", dataList), writeSheet);
        }

        return excelWriter;
    }

自定义的excel模板：