基于EasyExcel模板填充方式进行二次导出（动态表头、合并单元格问题处理）

🎈 1 参考文档

填充Excel | Easy Excel官方文档

EasyExcel-合并单元格 | 博客园-默念x

---

🔍2 个人需求

2.1 数据需求

• 第一部分
  • 粉色部分：表头固定，直接使用模板；
  • 红色部分：正常数据内容，和一般的Excel导出大同小异；
• 第二部分
  • 绿色部分：上面的表头是动态的，并且这些字段没有落表；
  • 橙色部分：左边的实测值、总权重得分、总得分相当于固定的表头；
  • 黄色部分：这部分是正常数据，但是橙色部分也需要被当作成数据。

2.2 单元格合并需求

• 紫色部分：表头部分，类别相同的进行横向和纵向合并；
• 绿色部分：根据相同类别，将表格数据进行横向合并；
• 橙色部分：相同两行实测值包括实测值所属的数据进行纵向合并；
• 红色部分：因为橙色部分加上绿色部分对应“实测值、实测值、总权重得分、总得分”一共四行，所以红色部分是相同的四行产品数据，需要进行纵向合并。

合并后的样子：

💡 3 解决方案

3.1 数据处理

使用EasyExcel利用模板填充的方式，以一个单元格为最小单位，把数据全部查出来，然后将数据处理成一行一行的形式进行填充，碰到相同的数据，就进行合并单元格。

3.2 数据字段没落表

有一部分表头数据的字段没有落表，在实际数据库中都属于一个字段，例如下图：光学、电学、声学实际上都属于category，而不是optics、electricity、acoustics。

可以使用map的进行对数据进行处理和存储，处理后的样子：

3.3 动态表头

一般都是固定表头，然后填充数据，相当于一维的。因为表头是动态的，所以第二部分数据相当于二维的，需要将表头和表格数据分别进行填充。

EasyExcel的填充方式是通过模板进行填充导出的，那我们可以导出两次，第一次用/resources/template下的模板文件将Excel导出成流，接着以第一次导出的Excel流，作为第二次导出的模板，最后再导出需要的Excel表格。

模板：

第一次导出：

第二次导出：

以这种方法，不仅仅是导出两次，还可以导出多次，以此处理更加复杂的表格。

3.4 合并单元格

参考官方的文章合并单元格和文章EasyExcel-合并单元格-默念x，然后根据需要自定义合并策略Strategy并且继承于AbstractMergeStrategy，计算出需要合并单元格数量的列表，然后利用CellRangeAddress进行单元格合并。

CellRangeAddress cellRangeAddress = new CellRangeAddress(起始行，结尾行，起始列，结尾列)；
sheet.addMergedRegionUnsafe(cellRangeAddress);

使用合并策略的方式：

ExcelWriter screenTemplateExcelWriter = EasyExcel
        .write(templateOut)   								// 导出最终临时文件
        .withTemplate(templateFileName)						// 使用的模板
        .registerWriteHandler(new XXXStrategy(需要的参数))	// 自定义单元格合并策略
        .build();

---

🚀4 第一次导出（部分代码）

4.1 Excel 填充模板

1. 总体样貌，模板名称为screenTemplate.xlsx，工作表名称为sheet0。

   

2. 拉长单元格，查看具体变量。

   

4.2 ScreenServiceImpl 业务实现层

@Service
public class ScreenServiceImpl implements ScreenService {
    @Override
    public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
        // HttpServletResponse消息头参数设置
        String filename = "exportFile.xlsx";
        httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);
        httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");
        httpServletResponse.addHeader("Pragma", "no-cache");
        httpServletResponse.addHeader("Cache-Control", "no-cache");
   
        // 通过ClassPathResource获取/resources/template下的模板文件
        ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");
        
        // 这里用try-with-resource
        try (
                // 获取模板文件
                InputStream screenParamTemplateFileName = classPathResource.getInputStream();

                OutputStream screenOut = httpServletResponse.getOutputStream();
                BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);
        ) {
            // --------------------------------基本配置--------------------------------
            // 设置内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置内容水平居中对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 设置内容垂直居中对齐
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 自动换行
            contentWriteCellStyle.setWrapped(true);
            // 设置字体样式和大小
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteFont.setFontName("微软雅黑");
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 配置横向填充
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            // sheet名称
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();
            // --------------------------------基本配置--------------------------------


            // ---------------------模拟获取第一部分的表格数据、表头参数---------------------
            List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();
            // 构造5个产品数据
            for (int i = 1; i <= 5; i++) {
                // 每份数据乘以4，为了合并单元格做准备
                for (int j = 0; j < 4; j++) {
                    ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();
                    screenGatherDTO.setScreenSize(String.valueOf(i * 10));
                    screenGatherDTO.setSupplier("厂商" + i);
                    screenGatherDTO.setPartMode("型号" + i);
                    screenGatherDTO.setResolution("1080P");
                    screenGatherDTO.setRefreshRate("60Hz");
                    screenGatherDTO.setPanel("IPS");
                    screenGatherDTOList.add(screenGatherDTO);
                }
            }
            if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
                for (int i = 0; i < screenGatherDTOList.size(); i++) {
                    // 在屏规格末尾加上表格模板参数
                    screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");
                }
            }

            // 填充第一个表头的单元格
            ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();
            List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();
            screenValueExcelDTO.setValue("产品测试");
            screenValueExcelDTOList.add(screenValueExcelDTO);

            // 在屏规格末尾加上表头模板参数
            List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();
            for (int i = 0; i < 4; i++) {
                ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();
                switch (i) {
                    case 0:
                        screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");
                        break;
                    case 1:
                        screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");
                        break;
                    case 2:
                        screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");
                        break;
                    case 3:
                        screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");
                        break;
                    default:
                        break;
                }
                screenTableExcelDTOList.add(screenTableExcelDTO);
            }
            // ---------------------模拟获取第一部分的表格数据、表头参数---------------------


            // --------------------------------第一次导出--------------------------------
            ExcelWriter screenTemplateExcelWriter = EasyExcel
                    .write(screenBos)   // 导出临时文件，使用的是BufferedOutputStream
                    .withTemplate(screenParamTemplateFileName)  // 使用的模板
                    .registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5))   // 自定义单元格合并策略
                    .registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略，头部为null
                    .build();
            // 填充屏规格表格数据
            screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);
            // 填充第一个表头的单元格
            screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);
            // 填充表头模板参数
            screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);
            screenTemplateExcelWriter.finish();
            // --------------------------------第一次导出--------------------------------
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

4.3 ScreenValueMergeStrategy 自定义合并单元格策略

public class ScreenValueMergeStrategy extends AbstractMergeStrategy {
    /**
     * 分组，每几行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 合并的目标开始列索引
     */
    private Integer targetBeginColIndex;
    /**
     * 合并的目标结束列索引
     */
    private Integer targetEndColIndex;
    /**
     * 需要开始合并单元格的首行索引
     */
    private Integer firstRowIndex;

    public ScreenValueMergeStrategy() {
    }

    /**
     * @param exportDataList      待合并目标行的值
     * @param targetBeginColIndex 合并的目标开始列索引
     * @param targetEndColIndex   合并的目标结束列索引
     * @param firstRowIndex       需要开始合并单元格的首行索引
     */
    public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetBeginColIndex = targetBeginColIndex;
        this.targetEndColIndex = targetEndColIndex;
        this.firstRowIndex = firstRowIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
            int rowCount = this.firstRowIndex;
            for (Integer count : exportFieldGroupCountList) {
                if (count == 1) {
                    rowCount += count;
                    continue;
                }
                // 合并单元格
                CellRangeAddress cellRangeAddress;
                for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {
                    cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
                rowCount += count;
            }
        }
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;
        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());

            if (equals) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
}

4.4 拉长单元格并查看导出效果

1. 未合并的效果。

   

2. 合并后的效果。

   

---

🚀5 第二次导出(完整代码)：以第一次导出的excel流，作为第二次导出的模板

5.1 配置文件

5.1.1 pom.xml 依赖

主要用到EasyExcel、Hutool、Lombok。

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.0.5</version>
    </dependency>
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.8.8</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

5.1.2 Excel 模板文件

Excel模板文件路径在：/resources/template/screenTemplate.xlsx，文件内容同4.1。

5.2 controller

5.2.1 ScreenController

import com.example.demo.service.ScreenService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author Cauli
 * @date 2022/12/1 9:40
 * @description 控制层
 */
@RestController
@RequestMapping("/screen")
public class ScreenController {
    @Autowired
    private ScreenService screenService;

    @GetMapping(value = "/export")
    public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
        screenService.export(httpServletRequest, httpServletResponse);
    }
}

⭐5.3 service

5.3.1 ScreenServiceImpl

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
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.example.demo.dto.ScreenExcelDTO;
import com.example.demo.dto.ScreenGatherDTO;
import com.example.demo.dto.ScreenValueExcelDTO;
import com.example.demo.service.ScreenService;
import com.example.demo.strategy.ScreenScoreHeaderMergeStrategy;
import com.example.demo.strategy.ScreenScoreValueHorizontalMergeStrategy;
import com.example.demo.strategy.ScreenScoreValueMergeStrategy;
import com.example.demo.strategy.ScreenValueMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Cauli|
 * @date: 2022/12/1 9:47
 * @description: 业务实现层
 */
@Service
public class ScreenServiceImpl implements ScreenService {
    @Override
    public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
        // HttpServletResponse消息头参数设置
        this.setHttpServletResponse(httpServletResponse);
        // 通过ClassPathResource获取/resources/template下的模板文件
        ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");
        // 需要导出的临时模板文件
        InputStream screenTemporaryTemplate = null;

        // 这里用try-with-resource
        try (
                // 获取模板文件
                InputStream screenParamTemplateFileName = classPathResource.getInputStream();

                OutputStream screenOut = httpServletResponse.getOutputStream();
                BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);

                ByteArrayOutputStream screenTemplateOut = new ByteArrayOutputStream();
        ) {
            // --------------------------------基本配置--------------------------------
            // 设置内容的策略
            WriteCellStyle contentWriteCellStyle = this.getWriteCellStyle();
            // 配置横向填充
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            // sheet名称，注意要和Excel模板中的工作表名称相同，不然无法导出数据
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();
            // --------------------------------基本配置--------------------------------


            // ---------------------模拟获取第一部分的表格数据、表头参数---------------------
            // 构造第一部分产品数据
            List<ScreenGatherDTO> screenGatherDTOList = this.getScreenGatherDTOList();
            // 填充第一个表头的单元格
            List<ScreenValueExcelDTO> screenValueExcelDTOList = this.getScreenValueExcelDTOList();
            // 在屏规格末尾加上表头模板参数
            List<ScreenValueExcelDTO> screenTableExcelDTOList = this.getScreenTableExcelDTOList();
            // ---------------------模拟获取第一部分的表格数据、表头参数---------------------


            // --------------------------------第一次导出--------------------------------
            ExcelWriter screenTemplateExcelWriter = EasyExcel
                    .write(screenBos)   // 导出临时文件，使用的是BufferedOutputStream
//                    .write(screenTemplateOut)   // 导出最终临时文件，使用的是ByteArrayOutputStream
                    .withTemplate(screenParamTemplateFileName)  // 使用的模板
                    .registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5))   // 自定义单元格合并策略
                    .registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略，头部为null
                    .build();
            // 填充屏规格表格数据
            screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);
            // 填充第一个表头的单元格
            screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);
            // 填充表头模板参数
            screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);
            screenTemplateExcelWriter.finish();

            // excel导出成流
            byte[] bytes = screenTemplateOut.toByteArray();
            screenTemporaryTemplate = new ByteArrayInputStream(bytes);
            // --------------------------------第一次导出--------------------------------


            // --------------------------模拟获取第二部分数据--------------------------
            // 模拟获取第二部分的表头数据
            List<ScreenExcelDTO> screenScoreExcelDTOList = this.getScoreExcelDTOList();
            // 模拟获取第二部分的表格数据
            Map<String, List<ScreenValueExcelDTO>> screenScoreMap = this.getScreenScoreMap(screenGatherDTOList);
            // --------------------------模拟获取第二部分的数据--------------------------


            // --------------------------------第二次导出--------------------------------
            // 测评模型表格数据开始索引
            int screenScoreCostFirstColumnIndex = 7;
            // 测评模型表格数据结束索引
            int screenScoreTargetEndColIndex = screenScoreCostFirstColumnIndex + screenScoreMap.get("screenValueTemplateParam0").size() - 1;
            // 导出最终文件
            ExcelWriter screenScoreExcelWriter = EasyExcel
                    .write(screenBos)   // 导出最终文件
                    .withTemplate(screenTemporaryTemplate)  // 以第一次导出的excel流，作为第二次导出的模板
                    .registerWriteHandler(new ScreenScoreHeaderMergeStrategy(0, screenScoreExcelDTOList, 1, screenScoreCostFirstColumnIndex))   // 表头（模块）合并策略
                    .registerWriteHandler(new ScreenScoreHeaderMergeStrategy(1, screenScoreExcelDTOList, 2, screenScoreCostFirstColumnIndex))   // 表头合并策略
                    .registerWriteHandler(new ScreenScoreValueMergeStrategy(screenGatherDTOList, screenScoreCostFirstColumnIndex, screenScoreTargetEndColIndex, 5)) // 表格数据合并策略
                    .registerWriteHandler(new ScreenScoreValueHorizontalMergeStrategy(screenGatherDTOList, screenScoreExcelDTOList, screenScoreCostFirstColumnIndex))   // 表格数据横向合并策略
                    .registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略，头部为null
                    .build();
            // 填充测评模型表头数据
            screenScoreExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenScoreExcelDTOList), fillConfig, writeSheet);
            // 填充测评模型表格数据
            screenScoreMap.forEach((k, v) -> screenScoreExcelWriter.fill(new FillWrapper(k, v), fillConfig, writeSheet));
            screenScoreExcelWriter.finish();
            // --------------------------------第二次导出--------------------------------
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            // 防止出现异常，导致流关闭失败
            if (screenTemporaryTemplate != null) {
                try {
                    screenTemporaryTemplate.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 模拟获取第二部分的表格数据
     *
     * @param screenGatherDTOList
     * @return
     */
    private Map<String, List<ScreenValueExcelDTO>> getScreenScoreMap(List<ScreenGatherDTO> screenGatherDTOList) {
        // 模拟这部分数据的字段没有落表，采用Map存储和处理数据
        Map<String, List<ScreenValueExcelDTO>> screenScoreMap = new LinkedHashMap<>();
        if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
            for (int j = 0; j < screenGatherDTOList.size(); j++) {
                Map<String, String> map = new LinkedHashMap<>();
                if ((j + 1) % 4 == 0) {
                    map.put("项目", "总得分");
                } else if ((j + 1) % 3 == 0) {
                    map.put("项目", "总权重得分");
                } else {
                    map.put("项目", "实测值");
                }
                for (int i = 1; i <= 1; i++) {
                    map.put("光学测试子项" + i, "1");
                }
                for (int i = 1; i <= 2; i++) {
                    map.put("电学测试子项" + i, "2");
                }
                for (int i = 1; i <= 3; i++) {
                    map.put("声学测试子项" + i, "3");
                }
                screenGatherDTOList.get(j).setScoreMap(map);
            }

            for (int i = 0; i < screenGatherDTOList.size(); i++) {
                List<ScreenValueExcelDTO> valueExcelDTOList = new ArrayList<>();
                Map<String, String> scoreMap = screenGatherDTOList.get(i).getScoreMap();
                if (MapUtils.isNotEmpty(scoreMap)) {
                    scoreMap.forEach((k, v) -> {
                        ScreenValueExcelDTO valueExcelDTO = new ScreenValueExcelDTO();
                        valueExcelDTO.setValue(v);
                        valueExcelDTOList.add(valueExcelDTO);
                    });
                }
                // 填充表格数据
                screenScoreMap.put("screenValueTemplateParam" + i, valueExcelDTOList);
            }
        }
        return screenScoreMap;
    }

    /**
     * 模拟获取第二部分的表头数据
     *
     * @return
     */
    private List<ScreenExcelDTO> getScoreExcelDTOList() {
        List<ScreenExcelDTO> screenScoreExcelDTOList = new ArrayList<>();
        for (int i = 1; i <= 1; i++) {
            ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
            screenExcelDTO.setModelName("产品测试");
            screenExcelDTO.setTestItemCategory("光学");
            screenExcelDTO.setTestItemName("光学");
            screenExcelDTO.setSubTestItemName("光学测试子项" + i);
            screenScoreExcelDTOList.add(screenExcelDTO);
        }
        for (int i = 1; i <= 2; i++) {
            ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
            screenExcelDTO.setModelName("产品测试");
            screenExcelDTO.setTestItemCategory("电学");
            screenExcelDTO.setTestItemName("电学");
            screenExcelDTO.setSubTestItemName("电学测试子项" + i);
            screenScoreExcelDTOList.add(screenExcelDTO);
        }
        for (int i = 1; i <= 3; i++) {
            ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
            screenExcelDTO.setModelName("产品测试");
            screenExcelDTO.setTestItemCategory("声学");
            screenExcelDTO.setTestItemName("声学");
            screenExcelDTO.setSubTestItemName("声学测试子项" + i);
            screenScoreExcelDTOList.add(screenExcelDTO);
        }
        return screenScoreExcelDTOList;
    }

    /**
     * 在屏规格末尾加上表头模板参数
     *
     * @return
     */
    private List<ScreenValueExcelDTO> getScreenTableExcelDTOList() {
        List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();
            switch (i) {
                case 0:
                    screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");
                    break;
                case 1:
                    screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");
                    break;
                case 2:
                    screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");
                    break;
                case 3:
                    screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");
                    break;
                default:
                    break;
            }
            screenTableExcelDTOList.add(screenTableExcelDTO);
        }
        return screenTableExcelDTOList;
    }

    /**
     * 填充第一个表头的单元格
     *
     * @return
     */
    private List<ScreenValueExcelDTO> getScreenValueExcelDTOList() {
        ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();
        List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();
        screenValueExcelDTO.setValue("产品测试");
        screenValueExcelDTOList.add(screenValueExcelDTO);
        return screenValueExcelDTOList;
    }

    /**
     * 构造第一部分产品数据
     *
     * @return
     */
    private List<ScreenGatherDTO> getScreenGatherDTOList() {
        List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();
        // 构造5个产品数据
        for (int i = 1; i <= 5; i++) {
            // 每份数据乘以4，为了合并单元格做准备
            for (int j = 0; j < 4; j++) {
                ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();
                screenGatherDTO.setScreenSize(String.valueOf(i * 10));
                screenGatherDTO.setSupplier("厂商" + i);
                screenGatherDTO.setPartMode("型号" + i);
                screenGatherDTO.setResolution("1080P");
                screenGatherDTO.setRefreshRate("60Hz");
                screenGatherDTO.setPanel("IPS");
                screenGatherDTOList.add(screenGatherDTO);
            }
        }
        if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
            for (int i = 0; i < screenGatherDTOList.size(); i++) {
                // 在屏规格末尾加上表格模板参数
                screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");
            }
        }
        return screenGatherDTOList;
    }

    /**
     * 设置内容的策略
     *
     * @return
     */
    private WriteCellStyle getWriteCellStyle() {
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 设置字体样式和大小
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setFontName("微软雅黑");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return contentWriteCellStyle;
    }

    /**
     * HttpServletResponse消息头参数设置
     *
     * @param httpServletResponse
     */
    private void setHttpServletResponse(HttpServletResponse httpServletResponse) {
        String filename = "exportFile.xlsx";
        httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);
        httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");
        httpServletResponse.addHeader("Pragma", "no-cache");
        httpServletResponse.addHeader("Cache-Control", "no-cache");
    }
}

5.3.2 ScreenService

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author Cauli
 * @date 2022/12/1 9:47
 * @description 业务层
 */
public interface ScreenService {
    /**
     * 导出
     *
     * @param httpServletRequest
     * @param httpServletResponse
     */
    void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse);
}

⭐5.4 strategy

5.4.1 ScreenValueMergeStrategy

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author yicheng1.he
 * @date 2022/12/1 14:06
 * @description 第一部分表格数据合并策略
 */
public class ScreenValueMergeStrategy extends AbstractMergeStrategy {
    /**
     * 分组，每几行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 合并的目标开始列索引
     */
    private Integer targetBeginColIndex;
    /**
     * 合并的目标结束列索引
     */
    private Integer targetEndColIndex;
    /**
     * 需要开始合并单元格的首行索引
     */
    private Integer firstRowIndex;

    public ScreenValueMergeStrategy() {
    }

    /**
     * @param exportDataList      待合并目标行的值
     * @param targetBeginColIndex 合并的目标开始列索引
     * @param targetEndColIndex   合并的目标结束列索引
     * @param firstRowIndex       需要开始合并单元格的首行索引
     */
    public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetBeginColIndex = targetBeginColIndex;
        this.targetEndColIndex = targetEndColIndex;
        this.firstRowIndex = firstRowIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
            int rowCount = this.firstRowIndex;
            for (Integer count : exportFieldGroupCountList) {
                if (count == 1) {
                    rowCount += count;
                    continue;
                }
                // 合并单元格
                CellRangeAddress cellRangeAddress;
                for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {
                    cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
                rowCount += count;
            }
        }
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;
        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());

            if (equals) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
}

5.4.2 ScreenScoreHeaderMergeStrategy

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenExcelDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Cauli
 * @date 2022/12/1 15:51
 * @description 第二部分表头合并策略
 */
public class ScreenScoreHeaderMergeStrategy extends AbstractMergeStrategy {
    /**
     * 分组，每几列合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 目标合并行index
     */
    private Integer targetRowIndex;

    /**
     * 需要开始合并单元格的首列index
     */
    private Integer firstColumnIndex;

    /**
     * 0:表头 1：分类 2：测试项
     */
    private Integer mergeMark;

    /**
     * 额外队尾数量
     */
    private static final Integer TAILS_LENGTH = 2;

    /**
     * 表头第零行索引
     */
    private static final Integer HEADER_ROW_ZERO = 0;

    /**
     * 表头第一行索引
     */
    private static final Integer HEADER_ROW_ONE = 1;

    /**
     * 表头第二行索引
     */
    private static final Integer HEADER_ROW_TWO = 2;

    public ScreenScoreHeaderMergeStrategy() {
    }

    /**
     * @param mergeMark        0:表头 1：分类 2：测试项
     * @param exportDataList   为待合并目标列的值
     * @param targetRowIndex   合并的目标行索引
     * @param firstColumnIndex 需要开始合并单元格的首列索引
     */
    public ScreenScoreHeaderMergeStrategy(Integer mergeMark, List<ScreenExcelDTO> exportDataList, Integer targetRowIndex, Integer firstColumnIndex) {
        this.mergeMark = mergeMark;
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetRowIndex = targetRowIndex;
        this.firstColumnIndex = firstColumnIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (cell.getColumnIndex() == this.firstColumnIndex && cell.getRowIndex() == targetRowIndex - 1) {
            int columnCount = this.firstColumnIndex;
            for (Integer count : exportFieldGroupCountList) {
                // 合并单元格
                CellRangeAddress cellRangeAddress = null;
                if (mergeMark == 0) {
                    cellRangeAddress = new CellRangeAddress(targetRowIndex - 1, targetRowIndex - 1, columnCount - 1, columnCount + count - 1);
                } else if (mergeMark == 1) {
                    if (count == 1) {
                        cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount);
                    } else {
                        cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount + count - 1);
                    }
                }

                sheet.addMergedRegionUnsafe(cellRangeAddress);
                columnCount += count;
            }
        }
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的列数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getGroupCountList(List<ScreenExcelDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;
        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = false;
            if (0 == mergeMark) {
                equals = exportDataList.get(i).getModelName().equals(exportDataList.get(i - 1).getModelName());
            } else {
                equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());
            }
            if (equals) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
}

4.5.3 ScreenScoreValueMergeStrategy

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author yicheng1.he
 * @date 2022/12/1 14:06
 * @description 第二部分表格数据合并策略
 */
public class ScreenScoreValueMergeStrategy extends AbstractMergeStrategy {
    /**
     * 分组，每几行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 合并的目标开始列索引
     */
    private Integer targetBeginColIndex;
    /**
     * 合并的目标结束列索引
     */
    private Integer targetEndColIndex;
    /**
     * 需要开始合并单元格的首行索引
     */
    private Integer firstRowIndex;

    /**
     * 额外的长度为2
     */
    private static final Integer ADDITIONAL_LENGTH = 2;

    public ScreenScoreValueMergeStrategy() {
    }

    /**
     * @param exportDataList      待合并目标行的值
     * @param targetBeginColIndex 合并的目标开始列
     * @param targetEndColIndex   合并的目标结束列索引
     * @param firstRowIndex       需要开始合并单元格的首行索引
     */
    public ScreenScoreValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetBeginColIndex = targetBeginColIndex;
        this.targetEndColIndex = targetEndColIndex;
        this.firstRowIndex = firstRowIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
            int rowCount = this.firstRowIndex;
            for (Integer count : exportFieldGroupCountList) {
                if (count == 1) {
                    rowCount += count;
                    continue;
                }
                // 合并单元格
                CellRangeAddress cellRangeAddress;
                for (int i = targetBeginColIndex - 1; i <= targetEndColIndex - 1; i++) {
                    cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount - 1 + count - 1, i, i);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
                rowCount += count;
            }
        }
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;

        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());

            if (equals) {
                count++;
            } else {
                groupCountList.add(count - ADDITIONAL_LENGTH);
                groupCountList.add(1);
                groupCountList.add(1);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count - ADDITIONAL_LENGTH);
        groupCountList.add(1);
        groupCountList.add(1);
        return groupCountList;
    }
}

5.4.3 ScreenScoreValueHorizontalMergeStrategy

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenExcelDTO;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author yicheng1.he
 * @date 2022/12/1 15:51
 * @description 第二部分表头横向合并策略
 */
public class ScreenScoreValueHorizontalMergeStrategy extends AbstractMergeStrategy {
    /**
     * 分组，每几行合并一次
     */
    private List<Integer> rowFieldGroupCountList;

    /**
     * 分组，每几列合并一次
     */
    private List<Integer> colFieldGroupCountList;

    /**
     * 目标合并列索引
     */
    private Integer targetColIndex;

    /**
     * 需要合并的行索引
     */
    private Integer targetRowIndex;

    /**
     * 需要开始合并单元格的首列索引
     */
    private Integer firstColumnIndex;

    /**
     * 表格数据第一行索引
     */
    private static final Integer FIRST_ROW_INDEX = 5;

    /**
     * 额外的长度为2
     */
    private static final Integer ADDITIONAL_LENGTH = 2;

    public ScreenScoreValueHorizontalMergeStrategy() {
    }

    /**
     * @param screenGatherDTOList     为待合并目标行的值
     * @param screenScoreExcelDTOList 为待合并目标列的值
     * @param firstColumnIndex        需要开始合并单元格的首列索引
     */
    public ScreenScoreValueHorizontalMergeStrategy(List<ScreenGatherDTO> screenGatherDTOList, List<ScreenExcelDTO> screenScoreExcelDTOList, Integer firstColumnIndex) {
        this.rowFieldGroupCountList = getRowGroupCountList(screenGatherDTOList);
        this.colFieldGroupCountList = getColGroupCountList(screenScoreExcelDTOList);
        this.firstColumnIndex = firstColumnIndex;
        this.targetRowIndex = FIRST_ROW_INDEX;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (cell.getColumnIndex() == this.firstColumnIndex) {
            int columnCount = this.firstColumnIndex;
            // 列合并
            for (int i = 0; i < colFieldGroupCountList.size(); i++) {
                Integer count = colFieldGroupCountList.get(i);
                if (count == 1) {
                    columnCount += count;
                    continue;
                }
                // 行合并
                int rowIndexCount = 0;
                for (int j = 0; j < rowFieldGroupCountList.size(); j++) {
                    if (j == 0) {
                        rowIndexCount += this.targetRowIndex + rowFieldGroupCountList.get(j);
                    } else {
                        rowIndexCount += rowFieldGroupCountList.get(j);
                    }
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH - 1, rowIndexCount - ADDITIONAL_LENGTH - 1, columnCount, columnCount + count - 1);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                    cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH, rowIndexCount - ADDITIONAL_LENGTH, columnCount, columnCount + count - 1);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);

                }
                columnCount += count;
            }
        }
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的列数
     * 防止每个产品行数不同、不一定是4行的情况，该方法计算出每个产品的行数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getRowGroupCountList(List<ScreenGatherDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;
        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());

            if (equals) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }

    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的列数
     *
     * @param exportDataList
     * @return
     */
    private List<Integer> getColGroupCountList(List<ScreenExcelDTO> exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;
        for (int i = 1; i < exportDataList.size(); i++) {
            boolean equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());

            if (equals) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
}

5.5 dto

5.5.1 ScreenExcelDTO

import lombok.Data;
import lombok.ToString;

import java.io.Serializable;

/**
 * @author Cauli
 * @date 2022/12/1 9:48
 * @description excel模板参数返回数据模型
 */
@Data
@ToString
public class ScreenExcelDTO implements Serializable {
    /**
     * 模块名称
     */
    private String modelName;

    /**
     * 测试项名称
     */
    private String testItemName;

    /**
     * 测试子项名称
     */
    private String subTestItemName;

    /**
     * 分类名称
     */
    private String testItemCategory;

    /**
     * 测试项值
     */
    private String testItemValue;
}

5.5.2 ScreenGatherDTO

import lombok.Data;
import lombok.ToString;

import java.util.Map;

/**
 * @author Cauli
 * @date 2022/12/1 15:10
 * @description 查询返回数据模型
 */
@Data
@ToString
public class ScreenGatherDTO {
    /**
     * 产品型号
     */
    private String partMode;

    /**
     * 厂商
     */
    private String supplier;

    /**
     * 屏幕尺寸
     */
    private String screenSize;

    /**
     * 屏幕分辨率
     */
    private String resolution;

    /**
     * 屏幕刷新率
     */
    private String refreshRate;

    /**
     * 面板属性
     */
    private String panel;

    /**
     * 用于存储得分模块map
     */
    private Map<String, String> scoreMap;

    /**
     * 表头数据模板参数
     */
    private String valueTemplateParam;

    /**
     * 表格数据模板参数
     */
    private String headerTemplateParam;
}

5.5.3 ScreenValueExcelDTO

import java.io.Serializable;

/**
 * @author Cauli
 * @date: 2022/12/1 20:20
 * @description: excel值返回数据模型
 */
public class ScreenValueExcelDTO implements Serializable {
    private String value;

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public String toString() {
        return "ScreenValueExcelDTO{" +
                "value='" + value + '\'' +
                '}';
    }
}

5.6 项目目录结构

---

📋6 最终导出效果

📫7 代码仓库

代码量比较多，建议下载Demo进行查看。

EasyExcel-Export-Demo | Gitee