复杂表头excel导出（一对多）

项目场景：复杂表头excel导出

项目场景：业务需求，列表套列表数据，在最外层导出两个列表中的数据（一对多）

---

使用easyExcel导出复杂表头信息（效果图如下）

代码如下：
放在Util包下即可

package com.risen.app.util;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * @description: 多表头excel导出合并单元格策略
 * @author:YJ
 **/
@Data
public class MergeUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public MergeUtil() {
    }

    public MergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 当前行
        int curRowIndex = cell.getRowIndex();
        // 当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

   
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 获取当前行的当前列和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergedRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

---

在接口中使用Util：

                //内容策略
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();

                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                //设置 垂直居中
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
                
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                EasyExcel.write(response.getOutputStream())
                        .head(header())
                        // 合并策略：合并相同数据的行。第一个参数表示从哪一行开始进行合并，由于表头占了两行，因此从第2行开始（索引从0开始）
                        // 第二个参数是指定哪些列要进行合并
                        .registerWriteHandler(new MergeUtil(2, new int[]{0, 1, 2, 3}))
                        //设置列宽
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(22))
                        //设置内容样式（垂直居中）
                        .registerWriteHandler(horizontalCellStyleStrategy)
                        // 注意：需要先调用registerWriteHandler()再调用sheet()方法才能使合并策略生效！！！
                        .sheet("试运行记录")
                        //数据源 dataList(List<List<Object>>)双层list集合
                        .doWrite(dataList);

下面这个是表头，在自己的接口中引用的内部方法，相同的表头需要合并就设置一样的名字即可，表数据合并策略已经在Util中做过处理

    //导出表头
    private List<List<String>> header() {
        List<List<String>> headers = new ArrayList<>();
        headers.add(Arrays.asList("试运行日期", "试运行日期"));
        headers.add(Arrays.asList("试运行基本情况", "试运行基本情况"));
        headers.add(Arrays.asList("操作人", "操作人"));
        headers.add(Arrays.asList("更新时间", "更新时间"));
        headers.add(Arrays.asList("问题记录", "问题描述"));
        headers.add(Arrays.asList("问题记录", "提出人 "));
        headers.add(Arrays.asList("问题记录", "问题状态"));
        headers.add(Arrays.asList("问题记录", "解决时间"));
        headers.add(Arrays.asList("问题记录", "解决人"));
        headers.add(Arrays.asList("问题记录", "解决方法"));
        return headers;
    }