EasyExcel 根据模板复制Sheet并将数据分页填充

需求

• 指定 Excel 模板文件，只给一个 Sheet
• 每个 Sheet 填充指定数量的数据，超过指定条数，根据模板 Sheet 复制并且追加数据

准备工作

1. 引入easyExcel pom依赖

   <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> 
   <dependency> 
       <groupId>com.alibaba</groupId> 
       <artifactId>easyexcel</artifactId> 
       <version>3.2.1</version> 
   </dependency>
   

2. 模板填充

   

• 表头数据，不需要根据list填充的使用{}作为占位符，如
• 列表数据，需要根据数据列表填充，使用{.}作为占位符，如
• 占位符里填充的值即后端的实体字段名称

开发

1. 获取数据并计算分页，例如我这个demo假定的是每个Sheet存放十条数据

   List<ExcelEthicRecordData> records = this.getExcelEthicRecord(ethIds); // 获取要导出的项目数据列表
           // 每个sheet最多十条数据
           int batchSize = 10;
           int totalRecords = records.size();
           int sheetCount = (int) Math.ceil((double) totalRecords / batchSize);
   

2. 根据模板文件生成文件流，根据模板文件创建文件流并通过Apache Poi的API 根据sheetCount复制出多个Sheet出来,并且生成字节输出流返回

   /**
        * 根据原有的文件流以及sheet0的模板追加sheet并且输出流
        *
        * @param sheetCount
        * @return byte[] 生成字节流
        * @throws IOException
        */
       private static byte[] getNewExcelStream(int sheetCount) throws IOException {
           // 加载模板文件
           ClassPathResource templateResource = new ClassPathResource("excel-template/ethic_vote_template.xlsx");
           InputStream templateStream = templateResource.getInputStream();
           Workbook workbook = WorkbookFactory.create(templateStream);
           for (int i = 0; i < sheetCount; i++) {
               String sheetName = "Sheet" + i;
               // 复制sheet
               if (i != 0) {
                   Sheet templateSheet = workbook.getSheet("Sheet0");
                   Sheet newSheet = workbook.cloneSheet(workbook.getSheetIndex(templateSheet));
                   workbook.setSheetName(workbook.getSheetIndex(newSheet), sheetName);
               }
           }
   
           ByteArrayOutputStream ops = new ByteArrayOutputStream();
           workbook.write(ops);
           byte[] byteArray = ops.toByteArray();
           // 原文件流后续已不使用，此处关闭
           templateStream.close();
           ops.close();
           return byteArray;
       }
   

3. 此时输出字节流中的Excel其实就包含了多个Sheet了，拿到该字节流使用EasyExcel 创建excelWriter

       // 创建临时导出文件
       File temporaryFile = Files.createTempFile("ethic_vote_data_", ".xlsx").toFile();
       // easyExcel API 根据is作为模板填充数据，并写入临时文件temporaryFile
       ExcelWriter excelWriter = EasyExcel.write(temporaryFile, ExcelEthicRecordData.class).withTemplate(asInputStream)
               .build();
   

4. 接下来就是填充数据的逻辑，calculateType是我业务上的方法，标题和数据分别填充，标题可以不需要map封装，数据即根据前面计算的分页参数获取分页数据

   String type = calculateType(records);
               for (int i = 0; i < sheetCount; i++) {
                   String sheetName = "Sheet" + i;
                   WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                   // 写入数据到当前sheet
                   int fromIndex = i * batchSize;
                   int toIndex = Math.min(fromIndex + batchSize, totalRecords);
                   List<ExcelEthicRecordData> sheetRecords = records.subList(fromIndex, toIndex);
                   excelWriter.fill(sheetRecords, writeSheet);
                   Map<String, Object> map = new HashMap<>();
                   map.put("type", type);
                   excelWriter.fill(map, writeSheet);
               }
   

完整主方法（忽略获取数据和业务方法）

public ResponseEntity<byte[]> exportVote(List<String> ethIds) {
        List<ExcelEthicRecordData> records = this.getExcelEthicRecord(ethIds); // 获取要导出的项目数据列表
        // 每个sheet最多十条数据
        int batchSize = 10;
        int totalRecords = records.size();
        int sheetCount = (int) Math.ceil((double) totalRecords / batchSize);
        try {
            // 创建新的Excel工作簿 POI 的API 用于复制sheet
            byte[] newExcelStream = getNewExcelStream(sheetCount);
            InputStream asInputStream = new ByteArrayInputStream(newExcelStream);

            // 创建临时导出文件
            File temporaryFile = Files.createTempFile("ethic_vote_data_", ".xlsx").toFile();
            // easyExcel API 根据is作为模板填充数据，并写入临时文件temporaryFile
            ExcelWriter excelWriter = EasyExcel.write(temporaryFile, ExcelEthicRecordData.class).withTemplate(asInputStream)
                    .build();
            String type = calculateType(records);
            for (int i = 0; i < sheetCount; i++) {
                String sheetName = "Sheet" + i;
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                // 写入数据到当前sheet
                int fromIndex = i * batchSize;
                int toIndex = Math.min(fromIndex + batchSize, totalRecords);
                List<ExcelEthicRecordData> sheetRecords = records.subList(fromIndex, toIndex);
                excelWriter.fill(sheetRecords, writeSheet);
                Map<String, Object> map = new HashMap<>();
                map.put("type", type);
                excelWriter.fill(map, writeSheet);
            }
            excelWriter.finish();
            // 构建下载响应
            return response(temporaryFile);
        } catch (Exception e) {
            log.error("表决模板导出文件时出现异常：{}", e.getMessage(), e);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(null);
        }
    }

    private static ResponseEntity<byte[]> response(File temporaryFile) throws IOException {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "伦理表决票模版.xlsx");
        byte[] fileBytes = Files.readAllBytes(temporaryFile.toPath());
        Files.delete(temporaryFile.toPath());

        return ResponseEntity.ok()
                .headers(headers)
                .contentLength(fileBytes.length)
                .body(fileBytes);
    }