easyPoi多sheet页导出、自定义动态列(ExcelExportEntity)

easyPoi4.2.0

ExcelExportUtil工具类中，提供的

①exportExcel(ExportParams entity, List entityList, Collection<?> dataSet)
可以支持ExcelExportEntity动态列，但是只能一个sheet页

②exportExcel(List<Map<String, Object>> list, ExcelType type)
可以支持多个sheet页，但是只能反射实体类取字段

为了既支持多sheet页，又可以使用ExcelExportEntity自定义导出列，自行封装方法如下：

SheetConfig ：

package com.hnevol.common.config;



import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import lombok.*;
import lombok.experimental.Accessors;

import java.util.List;
import java.util.Map;

@Data
@NoArgsConstructor
@Accessors(chain = true)
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
public class SheetConfig {
private String sheetName;
private List<Map<String, Object>> data;
private List<ExcelExportEntity> entities;
}

ExcelExportUtils：

package com.hnevol.common.utils;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.hnevol.common.config.SheetConfig;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelExportUtils {

    //表头第一行
    public  static final String HEADER_FIRST="HEADER_FIRST";
    //表头第二行
    public  static final String HEADER_SECOND="HEADER_SECOND";
    //表头第三行
    public  static final String HEADER_THIRD="HEADER_THIRD";
    //excel表头ket
    public  static final String HEADER="HEADER";
    //对应表头的内容值key
    public  static final String MAP_LIST="mapList";

  

    /**
     * 【通用方法】执行多 Sheet Excel 导出（支持每页独立列配置）
     * <p>
     * 该方法完全解耦业务，每个 Sheet 的列定义由调用方在 Config 中单独指定。
     *
     * @param fileName     文件名（不含后缀）
     * @param response     HTTP 响应对象
     * @param sheetConfigs Sheet 配置列表，每个配置包含独立的列定义和数据
     */
    public static void exportMultiSheetExcel(String fileName,
                                      HttpServletResponse response,
                                      List<SheetConfig> sheetConfigs) throws Exception {

        Workbook workbook = new XSSFWorkbook();
        ExcelExportService exportService = new ExcelExportService();

        try {
            // 遍历配置列表，为每个配置创建一个独立的 Sheet
            for (SheetConfig config : sheetConfigs) {
                // 设置 Sheet 参数
                ExportParams params = new ExportParams(config.getSheetName(), config.getSheetName(), ExcelType.XSSF);

                // 核心：使用当前配置独有的 entities 和 data 创建 Sheet
                exportService.createSheetForMap(workbook, params, config.getEntities(), config.getData());
            }

            // 设置响应头
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8") + ".xlsx";
            response.setCharacterEncoding("UTF-8");
            // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName);

            // 写入输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            }
        } finally {
            // 确保资源关闭
            workbook.close();
        }
    }
}

使用：

@ApiOperation(value = "理论考试成绩列表多sheet导出", notes = "理论考试成绩列表多sheet导出")
    @PostMapping(value ="/queryAcademicGradeResultExportSheet", produces = "application/octet-stream")
    public void queryAcademicGradeResultExportSheet(@RequestBody NurseEduAcademicGradeResultDTO data) {
        if (Objects.isNull(data.getQueryPlanId()) || data.getQueryPlanId() == 0) {
            return;
        }
        try {
            // --- 1. 数据查询与准备 ---
            MyPage<NurseEduAcademicGradeResultDTO> myPage = getMyPage(data);
            myPage.getPage().setCurrent(1);
            myPage.getPage().setSize(9999);

            R<Page<NurseEduAcademicGradeResultDTO>> pageR = examEduItemDataApi.queryAcademicGradeResultByParam(getUri(), myPage);
            if (!ApiReqResultVerifyUtil.verify(pageR)) {
                return;
            }

            NurseEduAcademicPlanService planService = examEduItemDataApi.queryEduPlansPassScore(getUri(), data.getQueryPlanId());
            Double passScore = NumberHelper.doubleValueOf0(planService.getPassingScore());
            List<NurseEduAcademicGradeResultDTO> results = pageR.getData().getRecords();

            // --- 2. 数据分组 ---
            Map<String, List<NurseEduAcademicGradeResultDTO>> rawGroupedData = new LinkedHashMap<>(); // 使用 LinkedHashMap 保持顺序
            rawGroupedData.put("已完成考试", results.stream().filter(e -> "completed".equals(e.getExamStatus())).collect(Collectors.toList()));
            rawGroupedData.put("未完成考试", results.stream().filter(e -> !"completed".equals(e.getExamStatus())).collect(Collectors.toList()));

            // 及格逻辑
            rawGroupedData.put("及格", results.stream().filter(e ->
                    "completed".equals(e.getExamStatus()) && (
                            NumberHelper.getOrDef(e.getScore(), 0D) >= passScore ||
                                    (data.getQueryIsMakeUpPlan() == 1 && NumberHelper.getOrDef(e.getMakeUpScore(), 0D) >= passScore)
                    )
            ).collect(Collectors.toList()));

            // 未及格逻辑
            rawGroupedData.put("未及格", results.stream().filter(e ->
                    "completed".equals(e.getExamStatus()) && (
                            data.getQueryIsMakeUpPlan() == 1
                                    ? (NumberHelper.getOrDef(e.getScore(), 0D) < passScore && NumberHelper.getOrDef(e.getMakeUpScore(), 0D) < passScore)
                                    : NumberHelper.getOrDef(e.getScore(), 0D) < passScore
                    )
            ).collect(Collectors.toList()));

            // --- 3. 构建 Sheet 配置列表 (核心改动) ---
            List<SheetConfig> sheetConfigs = new ArrayList<>();

            for (Map.Entry<String, List<NurseEduAcademicGradeResultDTO>> entry : rawGroupedData.entrySet()) {
                String sheetName = entry.getKey();
                List<NurseEduAcademicGradeResultDTO> sheetData = entry.getValue();

                // A. 为当前 Sheet 构建专属列定义
                // 注意：这里目前所有 Sheet 复用同一个 buildDynamicExcelEntities 逻辑。
                // 【扩展点】如果你需要不同 Sheet 有不同列（例如“未及格” sheet 多一列“原因”），
                // 可以在此处根据 sheetName 判断，调用不同的构建方法或手动添加列。
                List<ExcelExportEntity> currentSheetEntities = buildDynamicExcelEntities(planService);

               /* // 【示例扩展】如果“未及格” sheet 需要额外加一列“改进建议”
                if ("未及格".equals(sheetName)) {
                    currentSheetEntities.add(new ExcelExportEntity("改进建议", "improveSuggestion"));
                    // 注意：需要在 convertToDynamicMapList 中也对应处理这个字段，或者传一个特殊的 planService 标记
                }*/
                // B. 转换当前 Sheet 的数据
                List<Map<String, Object>> currentSheetDataMap = convertToDynamicMapList(sheetData, planService);

               /* // 【示例扩展】如果上面加了“改进建议”列，这里需要给 Map 填充默认值或实际值
                if ("未及格".equals(sheetName)) {
                    for (Map<String, Object> row : currentSheetDataMap) {
                        row.put("improveSuggestion", "请加强复习"); // 示例数据
                    }
                }*/
                SheetConfig config = new SheetConfig();
                config.setSheetName(sheetName)
                      .setData(currentSheetDataMap)
                      .setEntities(currentSheetEntities);
                // C. 添加到配置列表
                sheetConfigs.add(config);
            }

            // --- 4. 调用通用导出方法 ---
            String fileName = StrHelper.getOrDef(data.getExportTitle(), "考试成绩");
            ExcelExportUtils.exportMultiSheetExcel(fileName, response, sheetConfigs);
        } catch (Exception e) {
            log.error("导出 Excel 失败", e);
            if (!response.isCommitted()) {
                try {
                    response.reset();
                    response.setContentType("application/json");
                    response.setCharacterEncoding("UTF-8");
                    response.getWriter().write("{\"code\":500,\"msg\":\"导出失败：" + e.getMessage() + "\"}");
                } catch (Exception ex) {
                    log.error("写入错误响应失败", ex);
                }
            }
        }
    }


    /**
     * 动态构建 Excel 导出列配置
     * 根据 planService 的不同属性组合，动态添加列
     */
    private List<ExcelExportEntity> buildDynamicExcelEntities(NurseEduAcademicPlanService planService) {
        List<ExcelExportEntity> entityList = new ArrayList<>();

        // 基础列 (所有情况都有)
        entityList.add(new ExcelExportEntity("序号", "num"));
        entityList.add(new ExcelExportEntity("人员类型", "personType"));
        entityList.add(new ExcelExportEntity("姓名", "name"));
        entityList.add(new ExcelExportEntity("工号", "account"));
        entityList.add(new ExcelExportEntity("科室", "deptName"));
        entityList.add(new ExcelExportEntity("所属批次", "examBatch"));
        entityList.add(new ExcelExportEntity("成绩", "score"));
        entityList.add(new ExcelExportEntity("备注", "examDesc"));

        if (NumberHelper.getOrDef(planService.getAllowFillExamTarget(), 0) == 1) {
            entityList.add(new ExcelExportEntity("考试目的", "examTarget"));
        }

        entityList.add(new ExcelExportEntity("补考成绩", "makeUpScore"));

        // 动态列: 学分与学时
        // 逻辑：如果有学分 或者 有时学，则显示相关列 (根据您的原逻辑是两者都为 1 才显示，这里可根据需求调整)
        // 原逻辑：if (planService.getHaveCredits()==1 && planService.getHaveCreditsHour()==1)
        if (NumberHelper.getOrDef(planService.getHaveCredits(), 0) == 1 &&
                NumberHelper.getOrDef(planService.getHaveCreditsHour(), 0) == 1) {
            entityList.add(new ExcelExportEntity("学分类型", "creditType"));
            entityList.add(new ExcelExportEntity("学分", "creditNum"));
            entityList.add(new ExcelExportEntity("学时", "classHours"));
        }

        // 尾部固定列
        entityList.add(new ExcelExportEntity("作答开始时间", "examActualStartTime"));
        entityList.add(new ExcelExportEntity("作答结束时间", "examActualEndTime"));
        entityList.add(new ExcelExportEntity("答题用时", "answerTime"));
        entityList.add(new ExcelExportEntity("交卷状态", "examSubmitStatus"));

        return entityList;
    }

    private List<Map<String, Object>> convertToDynamicMapList(List<NurseEduAcademicGradeResultDTO> results, NurseEduAcademicPlanService planService) {
        List<Map<String, Object>> list = new ArrayList<>();
        int i = 0;

        for (NurseEduAcademicGradeResultDTO result : results) {
            i++;
            Map<String, Object> map = new HashMap<>();

            map.put("num", i);
            map.put("personType", getPersonType(result.getPersonType()));
            map.put("name", result.getName());
            map.put("account", result.getAccount());
            map.put("deptName", result.getDeptName());
            map.put("examBatch", result.getExamBatch());
            map.put("score", result.getScore());
            map.put("examDesc", result.getExamDesc());
            map.put("makeUpScore", result.getMakeUpScore());

            // 动态字段处理：只有当列存在时才放入 Map (虽然 EasyPoi 忽略多余 key，但为了严谨)
            if (NumberHelper.getOrDef(planService.getHaveCredits(), 0) == 1 &&
                    NumberHelper.getOrDef(planService.getHaveCreditsHour(), 0) == 1) {
                map.put("creditType", Objects.nonNull(result.getCreditType()) ? (result.getCreditType() == 1 ? "I 类" : "II 类") : "");
                map.put("creditNum", result.getCreditNum());
                map.put("classHours", result.getClassHours());
            }

            if (NumberHelper.getOrDef(planService.getAllowFillExamTarget(), 0) == 1) {
                map.put("examTarget", result.getExamTarget());
            }

            map.put("examActualStartTime", LocalDateTimeUtil.formatNormal(result.getExamActualStartTime()));
            map.put("examActualEndTime", LocalDateTimeUtil.formatNormal(result.getExamActualEndTime()));

            if (Objects.nonNull(result.getExamActualStartTime()) && Objects.nonNull(result.getExamActualEndTime())) {
                Duration duration = Duration.between(result.getExamActualStartTime(), result.getExamActualEndTime());
                long diffSeconds = duration.getSeconds();
                map.put("answerTime", conversionTime(NumberHelper.doubleValueOf0(diffSeconds)));
            }

            map.put("examSubmitStatus", conversionStatus(result.getExamSubmitStatus()));
            list.add(map);
        }
        return list;
    }

导出效果