EasyExcel导出自定义合并单元格的策略

发表于 分类于 ， 阅读次数： 本文字数： 7k 阅读时长 ≈ 6 分钟

需求概述

项目中需要导出的Excel如下所示：
在这里插入图片描述
目前的列表是查询出所有行的数据，前端再去按主键进行合并的。所以，导出时只要Excel下一行的主键相同，就合并上一行框红的列。

目前网上找到的EasyExcel自定义合并单元格都是ExcelFillCellMergeStrategy，这个工具类只要下一行的cell和上一行的cell内容相同就会合并，不符合目前的需求。本例也是在此基础上进行逻辑修改。 参考链接。

测试代码

引入相关依赖：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>
    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

ExcelModel类：

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
@Getter
@Setter
@ContentRowHeight(15) //内容行高
@HeadRowHeight(20)//表头行高
public class ExcelModel {

    public static final String RESEXCELNAME = "document.xlsx";
    public static final String TEMPLATEEXCELNAME = "文章管理";
    public static final String SUFFIX = ".xlsx";

    /**
     * notice
     * 当采用模板上传Excel且.needHead(false)设置了不生成标题头  @ColumnWidth(10)标签将无效，根据模板头的长度来走
     */
    @ColumnWidth(10)//单元格长度
    @ExcelProperty(value = "序号", index = 0)
    private String order;

    @ColumnWidth(20)//单元格长度
    @ExcelProperty(value = "文章标题", index = 1)
    private String title;

    @ColumnWidth(15)//单元格长度
    @ExcelProperty(value = "单位", index = 2)
    private String company;

    @ColumnWidth(15)//单元格长度
    @ExcelProperty(value = "编号", index = 3)
    private String documentCode;

    @ColumnWidth(12)//单元格长度
    @ExcelProperty(value = "发文日期", index = 4)
    private String publishDate;

    @ColumnWidth(25)//单元格长度
    @ExcelProperty(value = "意见", index = 5)
    private String idea;
}

ExcelFillCellMergeStrategy类(核心)：

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
/**
 * EasyExcel导出自定义合并单元格策略
 *
 * @author liquanhong
 * @createTime 2022/01/21
 */
public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    // 需要从第几行开始合并，0表示第1行
    private int mergeRowIndex = 1;
    // 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列
    private int mergeColumnRegion = 4;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnRegion = mergeColumnRegion;
    }
    @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) {
        // 隐藏id列
        writeSheetHolder.getSheet().setColumnHidden(0, true);
    }

    @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 < mergeColumnRegion; i++) {
                if (curColIndex <= mergeColumnRegion) {
                    mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并：当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 当前行的第一个Cell
        Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
        Object curFirstData = curFirstCell.getCellTypeEnum() == CellType.STRING ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
        // 上一行的第一个Cell
        Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
        Object preFirstData = preFirstCell.getCellTypeEnum() == CellType.STRING ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();

        // 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
        if (curFirstData.equals(preFirstData)) {
            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);
            }
        }
    }
}

ExportTest测试类：

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
public class ExportTest {
    public static void main(String[] args) throws IOException {
        List<ExcelModel> datas = getData();
        String filename="E:\\Downloads\\测试导出合并单元格Excel.xlsx";
        File file1 = new File(filename);
        if(!file1.exists()){
            file1.createNewFile();
        }
        //打印单个sheel页
        EasyExcel.write(filename, ExcelModel.class )
                .autoCloseStream(Boolean.TRUE)
                .registerWriteHandler(new ExcelFillCellMergeStrategy())
                .sheet("测试导出合并单元格Excel").doWrite(datas);
    }

    private static List<ExcelModel> getData() {
        List<ExcelModel> list = new ArrayList<>();
        ExcelModel model1 = new ExcelModel();
        model1.setOrder("1");
        model1.setTitle("标题111");
        model1.setCompany("单位111");
        model1.setDocumentCode("编号111");
        model1.setIdea("意见111");
        model1.setPublishDate("2022-01-21");

        ExcelModel model2 = new ExcelModel();
        model2.setOrder("1");
        model2.setTitle("标题111");
        model2.setCompany("单位222");
        model2.setDocumentCode("编号222");
        model2.setIdea("意见111");
        model2.setPublishDate("2022-01-21");

        ExcelModel model3 = new ExcelModel();
        model3.setOrder("1");
        model3.setTitle("标题333");
        model3.setCompany("单位222");
        model3.setDocumentCode("编号222");
        model3.setIdea("意见333");
        model3.setPublishDate("2022-01-21");

        ExcelModel model4 = new ExcelModel();
        model4.setOrder("4");
        model4.setTitle("标题444");
        model4.setCompany("单位444");
        model4.setDocumentCode("编号444");
        model4.setIdea("意见444");
        model4.setPublishDate("2022-01-21");

        ExcelModel model5 = new ExcelModel();
        model5.setOrder("5");
        model5.setTitle("标题555");
        model5.setCompany("单位555");
        model5.setDocumentCode("编号555");
        model5.setIdea("意见555");
        model5.setPublishDate("2022-01-21");

        list.add(model1);
        list.add(model2);
        list.add(model3);
        list.add(model4);
        list.add(model5);
        return list;
    }
}

测试代码运行结果

在这里插入图片描述
其中序号列是隐藏的。另外，CellWriteHandler接口方法的执行顺序为beforeCellCreate()->afterCellCreate()->afterCellDataConverted()->afterCellDispose()。