Springboot使用EasyExcel 的填充模板导出，导出为多Sheet工作簿

概述

Springboot使用EasyExcel 的填充模板导出，导出为多Sheet工作簿

详细代码

Excel数据填充

/**
 * 使用 EasyExcel 写入Excel
 * @param excelModelFilePath	模板文件地址
 * @param sheetNameAndDataMap	Sheet名称与Sheet数据Map集合，key为Sheet名称，value为Sheet数据集合
 * @return
 */
public byte[] writeExcelData(String excelModelFilePath, Map<String, List> sheetNameAndDataMap){
        try(
                ByteArrayOutputStream returnStream = new ByteArrayOutputStream();
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                InputStream resourceAsStream = getClass().getResourceAsStream(excelModelFilePath);){
            XSSFWorkbook workbook = new XSSFWorkbook(resourceAsStream);
            int i = 0;
            for (String sheetName : sheetNameAndDataMap.keySet()) {
                //设定key为工作簿名称
                if (i == 0) {
                    workbook.setSheetName(0, StrUtil.isBlank(sheetName) ? ("Sheet" + i) : sheetName);
                } else {
                    workbook.cloneSheet(0, StrUtil.isBlank(sheetName) ? ("Sheet" + i) : sheetName);
                }
                i++;
            }
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            try (InputStream inputStream = new ByteArrayInputStream(bytes);
                 ExcelWriter excelWriter = EasyExcel.write(returnStream).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build()
            ) {
                i = 0;
                for (Map.Entry<String, List> entry : sheetNameAndDataMap.entrySet()) {
                    String sheetName = entry.getKey();
                    List sheetData = entry.getValue();
                    // 根据Sheet名称填充数据，注意：workbook填充SheetName工作簿名称时，最长31位，当超过时会自动截取，所以此处也需要手动截取，否则找不到Sheet进行填充会报错
                    WriteSheet writeSheet = EasyExcel.writerSheet(StrUtil.isBlank(sheetName) ? ("Sheet" + i) : StrUtil.sub(sheetName, 0, 31)).build();
                    excelWriter.fill(sheetData, writeSheet);
                    i++;
                }
                excelWriter.finish();
            }
            return returnStream.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Excel写入错误：" + e.getMessage());
        }
    }

注意：在填充模板中的占位符前加上【.】这样才能填充List集合，如果不添加则只能填充单一的KeyValue类型的Map或实体类

导出Excel

@PostMapping("/downloadExcel")
public void downloadExcel(HttpServletResponse response){
    
    String excelModelFilePath = "/xxx/xxx/xxx/模板.xlsx";
    Map<String, List> sheetMap = new HashMap<>();
    sheetMap.put("Sheet1", new ArrayList<>());
    sheetMap.put("Sheet2", new ArrayList<>());
    
    // 写入Excel数据并获取写入后的Excel的byte[]
    byte[] exportExcelByte = service.writeExcelData(excelModelFilePath, sheetMap);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
    String fileName = URLEncoder.encode("测试", StandardCharsets.UTF_8.toString());
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
    InputStream inputStream = new ByteArrayInputStream(exportExcelByte);
    IoUtil.copy(inputStream, response.getOutputStream());
}