【使用EasyExcel导出】动态合并单元格

2025-05-16 15:00:57 39 阅读

1.导入EasyExcel依赖


    com.alibaba
    easyexcel
    4.0.3

2.以学生信息为例，编写一个实体类

package org.example.easyexcel;

import com.alibaba.excel.annotation.ExcelProperty;

public class Student {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private int age;

    @ExcelProperty("地址")
    private String address;

    public Student(String name, int age, String address) {
        this.name = name;
        this.age = age;
        this.address = address;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

3.方法一：提前计算合并的单元格，在sheet创建后一次性合并，如下：

package org.example.easyexcel;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.Collections;
import java.util.List;

public class MergeCellRangeWriteHandler implements SheetWriteHandler {
    private final List rangeCellList;

    public MergeCellRangeWriteHandler(List rangeCellList) {
        this.rangeCellList = (rangeCellList == null) ? Collections.emptyList() : rangeCellList;
    }

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        for (CellRangeAddress cellRangeAddress : rangeCellList) {
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }
    }
}

写之前先计算需要合并的单元格：

package org.example.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class EasyExcelTest02 {
    public static void main(String[] args) {

        String fileName = "D:	est.xls";
        List studentList = new ArrayList<>();
        studentList.add(new Student("张三", 18, "北京"));
        studentList.add(new Student("李四", 20, "北京"));
        studentList.add(new Student("李四", 20, "天津"));
        studentList.add(new Student("王六", 27, "上海"));
        studentList.add(new Student("王六", 27, "苏州"));
        studentList.add(new Student("王六", 27, "杭州"));

        try (ExcelWriter excelWriter = EasyExcel.write(fileName, Student.class).build()) {
            int[] mergeColumnIndex = {0, 1};
            List rangeCellList = new ArrayList<>();
            int lineCount = 1;

            Map groupMap = new LinkedHashMap<>();
            for (Student student : studentList) {
                groupMap.put(student.getName(), groupMap.getOrDefault(student.getName(), 0L) + 1);
            }

            for (Map.Entry entry : groupMap.entrySet()) {
                int count = entry.getValue().intValue();
                if (count > 1) {
                    int endRowIndex = lineCount + count - 1;
                    for (int columnIndex : mergeColumnIndex) {
                        rangeCellList.add(new CellRangeAddress(lineCount, endRowIndex, columnIndex, columnIndex));
                    }
                }
                lineCount += count;
            }

            WriteSheet writeSheet =
                    EasyExcel.writerSheet("学生").registerWriteHandler(new MergeCellRangeWriteHandler(rangeCellList)).build();
            excelWriter.write(studentList, writeSheet);
            excelWriter.finish();
        }
    }
}

4、方法二：在每写完一行后，跟上一行对比，需要合并的进行合并

package org.example.easyexcel;

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

public class DynamicMergeStrategy implements RowWriteHandler {
    private static final int[] MERGE_COLUMN_INDEX = {0, 1};

    private String lastValue = "";
    private int startRow = 0;
    private int endRow = 0;
    private int totalRows = 0;

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {
        if (isHead || row.getRowNum() <= 0) {
            // 跳过表头和第一行
            return;
        }

        Sheet sheet = writeSheetHolder.getSheet();
        // 取第一列比较
        String currentValue = row.getCell(0).getStringCellValue();
        if (currentValue.equals(lastValue)) {
            endRow++;
            // 防止最后一组相同的没有合并
            if (row.getRowNum() == totalRows) {
                needToMerge(sheet);
            }
        } else {
            needToMerge(sheet);
            startRow = row.getRowNum();
            endRow = row.getRowNum();
        }
        lastValue = currentValue;
    }

    private void needToMerge(Sheet sheet) {
        if (endRow > startRow) {
            for (int columnIndex : MERGE_COLUMN_INDEX) {
                CellRangeAddress rangeAddress = new CellRangeAddress(startRow, endRow, columnIndex, columnIndex);
                sheet.addMergedRegionUnsafe(rangeAddress);
            }
        }
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }
}

package org.example.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

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

public class EasyExcelTest03 {
    public static void main(String[] args) {
        String fileName = "D:	est.xls";
        List studentList = new ArrayList<>();
        studentList.add(new Student("张三", 18, "北京"));
        studentList.add(new Student("李四", 20, "北京"));
        studentList.add(new Student("李四", 20, "天津"));
        studentList.add(new Student("王六", 27, "上海"));
        studentList.add(new Student("王六", 27, "苏州"));
        studentList.add(new Student("王六", 27, "杭州"));

        try (ExcelWriter excelWriter = EasyExcel.write(fileName, Student.class).build()) {
            DynamicMergeStrategy strategy = new DynamicMergeStrategy();
            strategy.setTotalRows(studentList.size());
            WriteSheet writeSheet = EasyExcel.writerSheet("学生").registerWriteHandler(strategy).build();
            excelWriter.write(studentList, writeSheet);
            excelWriter.finish();
        }
    }
}