【学习总结】EasyExcel合并同列不同行，表格数据相同的行

实体类

@Data
@HeadRowHeight(50)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
public class CriterionDataExportDTO {


    @ColumnWidth(15)
    @ExcelProperty(value = "所属街道")
    private String streetName;

    @ColumnWidth(25)
    @ExcelProperty(value = "点位类型")
    private String pointType;

    @ColumnWidth(40)
    @ExcelProperty(value = "测评点位")
    private String pointName;

    @ColumnWidth(50)

    @ExcelProperty(value = "问题明细")
    private String issueDetails;

    @ColumnWidth(15)
    @ExcelProperty(value = "问题笔数")
    private Integer issueCount;

    @ColumnWidth(25)
    @ExcelProperty(value = "二级负责单位")
    private String responsibleUnit2;

    @ColumnWidth(25)
    @ExcelProperty(value = "二级单位接件时间")
    private String assignTime2;

    @ColumnWidth(25)
    @ExcelProperty(value = "三级负责单位")
    private String responsibleUnit3;

    @ColumnWidth(25)
    @ExcelProperty(value = "三级单位接件时间")
    private String assignTime3;

    @ExcelIgnore
    private Integer pushStatus;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

工具类

继承合并单元格，重写合并方法

package com.jeesite.modules.utils.easyExcel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;

public class EasyExcelUtils extends AbstractMergeStrategy {
    private Map<String, List<Integer>> nameRowMap = new HashMap<>();

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int columnIndex = cell.getColumnIndex();

        if (columnIndex == 0) {
            String currentValue = cell.getStringCellValue();
            if (currentValue == null || currentValue.isEmpty()) {
                return;
            }

            int currentRowIndex = cell.getRowIndex();
            List<Integer> rowList = nameRowMap.getOrDefault(currentValue, new ArrayList<>());
            rowList.add(currentRowIndex);
            nameRowMap.put(currentValue, rowList);

            mergeRows(sheet, currentValue, rowList, columnIndex);
        }

        if (columnIndex == 2) {
            String currentValue = cell.getStringCellValue();
            if (currentValue == null || currentValue.isEmpty()) {
                return;
            }

            int currentRowIndex = cell.getRowIndex();
            List<Integer> rowList = nameRowMap.getOrDefault(currentValue, new ArrayList<>());
            rowList.add(currentRowIndex);
            nameRowMap.put(currentValue, rowList);

            mergeRows(sheet, currentValue, rowList, columnIndex);
        }
    }

    private void mergeRows(Sheet sheet, String value, List<Integer> rowList, int columnIndex) {
        if (rowList.size() <= 1) {
            return;
        }

        int startRow = rowList.get(0);
        int endRow = rowList.get(rowList.size() - 1);

        // 检查是否存在重叠合并区域
        CellRangeAddress existingRegion = findOverlappingRegion(sheet, startRow, endRow, columnIndex);
        if (existingRegion != null) {
            // 扩展现有合并区域以适应新的合并行
            startRow = Math.min(existingRegion.getFirstRow(), startRow);
            endRow = Math.max(existingRegion.getLastRow(), endRow);

            // 移除现有合并区域
            removeMergedRegion(sheet, existingRegion);
        }

        CellRangeAddress range = new CellRangeAddress(startRow, endRow, columnIndex, columnIndex);
        sheet.addMergedRegionUnsafe(range);
    }

    private CellRangeAddress findOverlappingRegion(Sheet sheet, int startRow, int endRow, int columnIndex) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.getFirstColumn() == columnIndex && region.getLastColumn() == columnIndex) {
                // 只考虑指定列的合并区域
                if (startRow <= region.getLastRow() && endRow >= region.getFirstRow()) {
                    return region;
                }
            }
        }
        return null;
    }

    private void removeMergedRegion(Sheet sheet, CellRangeAddress region) {
        int index = -1;
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.equals(region)) {
                index = i;
                break;
            }
        }
        if (index >= 0) {
            sheet.removeMergedRegion(index);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

调用

public void exportTaskDetails() {
        List<CriterionDataExportDTO> dataDetails = dataDao.findTaskDataDetails();
        for (CriterionDataExportDTO item : dataDetails) {
            //数据处理
            }
        }
		
		//写入路径
        String fileName =  "D:\\数据测试_" + System.currentTimeMillis() + ".xlsx";
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)15);
        headWriteCellStyle.setWriteFont(headWriteFont);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(fileName, CriterionDataExportDTO.class)
                .registerWriteHandler(new EasyExcelUtils())
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet("模板")
                .doWrite(dataDetails);

    }
    
    
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31