根据EXCEL模板导出多个SHEET数据

描述一下场景，我们需要根据固定模板导出一个班上N个同学的成绩，N个同学未知，需要从数据库查询，然后才能知道数量，每个同学的数据是一个列表，包含（科目、成绩）。

结果如下

我们并不知道多少 人，所以不可能按照人员个数制定模板，只能创建一个sheet页的模板，如下：

需要引入：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

思路：根据人员个数，根据只有一个sheet模板的原始模板文件，创建一个新模板，新模板存在多个sheet页，sheet页个数和学生个数一样，sheet页命名和学生名字一样、代码如下：

package com.crscic.inter.uniview.content;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/****************************************
 * @Description 测试类
 * @Date 2023/11/10 16:12
 * @Author admin
 * @Version 0.0.0.0.0
 ****************************************/
public class MainTest {


    public static void main(String[] args) {
        File file = new File("E:\\jishuchendian\\new_template.xlsx");
        String json = "{\"张三\":[{\"SUBJECT\":\"语文\",\"SCORE\":\"115\"},{\"SUBJECT\":\"数学\",\"SCORE\":\"128\"}],\"李四\":[{\"SUBJECT\":\"语文\",\"SCORE\":\"108\"},{\"SUBJECT\":\"数学\",\"SCORE\":\"142\"},{\"SUBJECT\":\"物理\",\"SCORE\":\"62\"}]}";
        Map<String, List<Map<String, String>>> dataMap = JSONObject.parseObject(json, HashMap.class);
        System.out.println(JSONObject.toJSONString(dataMap));
        crtDataFile(dataMap, file);
    }

    private static String crtSixRandomNum() {
        Random random = new Random();
        int randomNumber = random.nextInt(999999);
        String formattedNumber = String.format("%06d", randomNumber);
        return formattedNumber;
    }

    private static void crtDataFile(Map<String, List<Map<String, String>>> data, File file) {
        List<String> list = new ArrayList<>();
        Iterator<String> iterator = data.keySet().iterator();
        while (iterator.hasNext()) {
            list.add(iterator.next());
        }
        createModel(list, file);//根据set中数据创建多个模板sheet页
        String random = crtSixRandomNum();
        String fileName = "E:\\jishuchendian\\violate_export_" + random + ".xlsx";
        File outPutFile = new File(fileName);
        ExcelWriter ew = setStyle(file, outPutFile);
        //开始写数据
        for (String key : list) {
            //工作表对象
            WriteSheet ws = EasyExcel.writerSheet(key).build();
            //组合填充换行
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            //填充数据
            ew.fill(data.get(key), fillConfig, ws);
        }
        ew.close();
    }

    private static ExcelWriter setStyle(File outModelFile, File outsFile) {
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 内容
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();        // 标题
        WriteFont contentWriteFont = new WriteFont();// 字体策略
        contentWriteFont.setFontHeightInPoints((short) 12);// 字体大小
        contentWriteFont.setFontName("等线");
        contentWriteFont.setBold(false);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);//设置 水平居中
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        ExcelWriter excelWriter = EasyExcel.write(outsFile)
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .withTemplate(outModelFile)
                .build();
        return excelWriter;
    }

    private static void createModel(List<String> list, File outModelFile) {
        try {
            String modelTemplateFile = "E:\\jishuchendian\\template_origin.xlsx";
            InputStream inputStream = new FileInputStream(modelTemplateFile);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            workbook.cloneSheet(0);
            Sheet sourceSheet = workbook.getSheetAt(0);
            Workbook targetWorkbook = new XSSFWorkbook();
            for (String key : list) {
                Sheet targetSheet = targetWorkbook.createSheet(key);
                copySheet(sourceSheet, targetSheet);
            }
            //将产生的工作薄（只是一个工作薄对象）写入文件
            FileOutputStream fileOutputStream = new FileOutputStream(outModelFile);
            targetWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取模板内容和格式
     *
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
        Workbook targetWorkbook = targetSheet.getWorkbook();
        CellStyle targetCellStyle;
        for (Row sourceRow : sourceSheet) {
            //创建行
            Row targetRow = targetSheet.createRow(sourceRow.getRowNum());
            for (Cell sourceCell : sourceRow) {
                Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                // 复制单元格的值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                }
                // 复制单元格的样式
                CellStyle sourceCellStyle = sourceCell.getCellStyle();
                targetCellStyle = targetWorkbook.createCellStyle();
                targetCellStyle.cloneStyleFrom(sourceCellStyle);
                targetCell.setCellStyle(targetCellStyle);
            }
        }
    }
}