一、需求背景

业务想要根据column1和column3这两列的值进行行合并，如果这两列的值相等，则需要合并column1、column2、column3、column4这四列的值 想要如下的Excel表格：

难点如下

1. 多级表头
2. 根据特定列合并行
3. 第一列No计数器如何合并递增

二、代码实现

2.1 核心类ExcelFillRowMergeStrategy是为了完成行合并

@Data
public class ExcelFillRowMergeStrategy implements RowWriteHandler {


    //这个是为了在第一列增加一个No.计数器
    private int excelNo = 1;

    /**
     * 合并字段的下标，如第一到四列new int[]{0,1,2,3}
     */
    private int[] mergeColumnIndex;

    /**
     * 从第几行开始合并，如果表头占两行，这个数字就是2
     */
    private int mergeRowIndex;

    /**
     * 需要比较的列下标,如需要比较第一列和第三列new int[]{0,2}
     */
    private int[] compareColumnIndex;

    public ExcelFillRowMergeStrategy() {
    }

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


    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = row.getRowNum();
        Cell cell = row.getCell(0);

        if (curRowIndex == 2) {
            cell.setCellValue(String.valueOf(excelNo++));
        }
        //要从真实数据的第二行开始比较合并，所以此处是 大于号，而不是等于号码
        if (curRowIndex > mergeRowIndex) {
            cell.setCellValue(String.valueOf(excelNo));
            mergeWithPrevRow(writeSheetHolder, row, curRowIndex);
        }

    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Row row, int curRowIndex) {

        for (int index : compareColumnIndex) {

            Cell cell = row.getCell(index);
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(index);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
            // 比较当前行的第一列的单元格与上一行是否相同，不相同则跳过
            if (!curData.equals(preData)) {
                excelNo++;
                return;
            }

        }

        //需要比较的列数据相同，则和上一行数据进行合并,逐列进行合并
        for (int i = 0; i < mergeColumnIndex.length; i++) {
            mergeColumn(writeSheetHolder, curRowIndex, i);
        }

    }

    private void mergeColumn(WriteSheetHolder writeSheetHolder, int curRowIndex, int curColIndex) {
        {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.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);
            }
        }
    }


}

2.2 测试方法主入口

@Autowired
private HttpServletResponse response;

public void excelTest(ExcelRequestDTO excelRequestDTO) {
    try {
        //根据请求参数excelRequestDTO调用getExcelData（）方法获取文件中的数据源
        List<DataDO> dataDoList = this.getExcelData(excelRequestDTO);
        
        //转化为要easyexcel需要的表头和属性映射
        List<ExcelDTO> excelDTOS = BeanUtil.copyToList(dataDoList, ExcelDTO.class);
        int[] mergeColumnIndex = new int[]{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
        int[] compareColumnIndex = new int[]{1,2,5,11};
        doWriteExcel(response, "RA Deduction report", "sheet1", AfsDealerDeductionExcelDTO.class, excelDTOS, mergeColumnIndex, compareColumnIndex);
    } catch (Exception e) {
        log.error("fromDealerDownloadTest error, req: {}", JSONObject.toJSONString(downloadDTO), e);
    }
}

2.3 表头和属性映射，注意二级表头的注解

@Data
public class ExcelDTO {

    @ExcelProperty("No.")
    private String excelNo;   

    @ExcelProperty("column1")
    private String column1;

    @ExcelProperty("column2")
    private String column2;

    @ExcelProperty("column3")
    private String column3;

    @ExcelProperty("column4")
    private String column4;

    //此处是实现二级表头的关键
    @ExcelProperty(value = {"first column", "second column1"})
    private String secondColumn1;
    //此处是实现二级表头的关键
    @ExcelProperty(value = {"first column", "second column2"})
    private String secondColumn2;
    //此处是实现二级表头的关键
    @ExcelProperty(value = {"first column", "second column3"})
    private String secondColumn3;

2.4 写入Excel

public static <T> void doWriteExcel(HttpServletResponse response, String filename, String sheetName,Class<T> head, List<T> data, int[] mergeColumnIndex, int[] compareColumnIndex) throws IOException {
    // 输出 Excel
    ExcelWriterSheetBuilder sheet = EasyExcel.write(response.getOutputStream(), head)
            .autoCloseStream(false)
            .registerWriteHandler(new ExcelFillRowMergeStrategy(2,
                    mergeColumnIndex,
                    compareColumnIndex)) 
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .sheet(sheetName);
    sheet.doWrite(data);
    // 设置 header 和 contentType
    response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}