需求

• 指定 Excel 模板文件，只給一個(gè) Sheet
• 每個(gè) Sheet 填充指定數(shù)量的數(shù)據(jù)，超過指定條數(shù)，根據(jù)模板 Sheet 復(fù)制并且追加數(shù)據(jù)

準(zhǔn)備工作

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. 模板填充

• 表頭數(shù)據(jù)，不需要根據(jù)list填充的使用{}作為占位符，如
• 列表數(shù)據(jù)，需要根據(jù)數(shù)據(jù)列表填充，使用{.}作為占位符，如
• 占位符里填充的值即后端的實(shí)體字段名稱

開發(fā)

1. 獲取數(shù)據(jù)并計(jì)算分頁(yè)，例如我這個(gè)demo假定的是每個(gè)Sheet存放十條數(shù)據(jù)

   List<ExcelEthicRecordData> records = this.getExcelEthicRecord(ethIds); // 獲取要導(dǎo)出的項(xiàng)目數(shù)據(jù)列表
           // 每個(gè)sheet最多十條數(shù)據(jù)
           int batchSize = 10;
           int totalRecords = records.size();
           int sheetCount = (int) Math.ceil((double) totalRecords / batchSize);
   

2. 根據(jù)模板文件生成文件流，根據(jù)模板文件創(chuàng)建文件流并通過Apache Poi的API 根據(jù)sheetCount復(fù)制出多個(gè)Sheet出來,并且生成字節(jié)輸出流返回

   /**
        * 根據(jù)原有的文件流以及sheet0的模板追加sheet并且輸出流
        *
        * @param sheetCount
        * @return byte[] 生成字節(jié)流
        * @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;
               // 復(fù)制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();
           // 原文件流后續(xù)已不使用，此處關(guān)閉
           templateStream.close();
           ops.close();
           return byteArray;
       }
   

3. 此時(shí)輸出字節(jié)流中的Excel其實(shí)就包含了多個(gè)Sheet了，拿到該字節(jié)流使用EasyExcel 創(chuàng)建excelWriter

       // 創(chuàng)建臨時(shí)導(dǎo)出文件
       File temporaryFile = Files.createTempFile("ethic_vote_data_", ".xlsx").toFile();
       // easyExcel API 根據(jù)is作為模板填充數(shù)據(jù)，并寫入臨時(shí)文件temporaryFile
       ExcelWriter excelWriter = EasyExcel.write(temporaryFile, ExcelEthicRecordData.class).withTemplate(asInputStream)
               .build();
   

4. 接下來就是填充數(shù)據(jù)的邏輯，calculateType是我業(yè)務(wù)上的方法，標(biāo)題和數(shù)據(jù)分別填充，標(biāo)題可以不需要map封裝，數(shù)據(jù)即根據(jù)前面計(jì)算的分頁(yè)參數(shù)獲取分頁(yè)數(shù)據(jù)

   String type = calculateType(records);
               for (int i = 0; i < sheetCount; i++) {
                   String sheetName = "Sheet" + i;
                   WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                   // 寫入數(shù)據(jù)到當(dāng)前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);
               }
   

完整主方法（忽略獲取數(shù)據(jù)和業(yè)務(wù)方法）

public ResponseEntity<byte[]> exportVote(List<String> ethIds) {
        List<ExcelEthicRecordData> records = this.getExcelEthicRecord(ethIds); // 獲取要導(dǎo)出的項(xiàng)目數(shù)據(jù)列表
        // 每個(gè)sheet最多十條數(shù)據(jù)
        int batchSize = 10;
        int totalRecords = records.size();
        int sheetCount = (int) Math.ceil((double) totalRecords / batchSize);
        try {
            // 創(chuàng)建新的Excel工作簿 POI 的API 用于復(fù)制sheet
            byte[] newExcelStream = getNewExcelStream(sheetCount);
            InputStream asInputStream = new ByteArrayInputStream(newExcelStream);

            // 創(chuàng)建臨時(shí)導(dǎo)出文件
            File temporaryFile = Files.createTempFile("ethic_vote_data_", ".xlsx").toFile();
            // easyExcel API 根據(jù)is作為模板填充數(shù)據(jù)，并寫入臨時(shí)文件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();
                // 寫入數(shù)據(jù)到當(dāng)前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();
            // 構(gòu)建下載響應(yīng)
            return response(temporaryFile);
        } catch (Exception e) {
            log.error("表決模板導(dǎo)出文件時(shí)出現(xiàn)異常：{}", 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);
    }