当前位置：首页 > news >正文

easyExcel单元格合并

news 2025/4/26 21:19:24

环境

java 1.8
easyexcel 3.1.1

实现方式

自定义WriteHandler，实现AbstractMergeStrategy抽象类，实现merge 方法完成自定义的合并操作。
本测试代码，实现了eques方法，用于判断是否需要合并

核心代码

实现AbstractMergeStrategy类的merge方法进行单元格合并
需要注意的是，单元格需要一次合并完成，合并了以后不能再次合并。所以判断当前对象与前一个对象不相等时，才会对之前的对象进行合并操作


/*** 合并策略* @author dzh* @since 2025/4/17*/
public class MyMergeStrategy extends AbstractMergeStrategy {// 根据数据的下标判断是否需要合并行private final Function<Integer, Boolean> eqBefore;// 根据列序号判断该列列是否需要行合并，从0开始private final Function<Integer, Boolean> columnNeedMerge;public MyMergeStrategy(Function<Integer, Boolean> eqBefore, Function<Integer, Boolean> columnNeedMerge) {this.eqBefore = eqBefore;this.columnNeedMerge = columnNeedMerge;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (relativeRowIndex == null || relativeRowIndex <= 1) {return;}if (!columnNeedMerge.apply(cell.getColumnIndex())) {return;}if (!eqBefore.apply(relativeRowIndex) && eqBefore.apply(relativeRowIndex - 1)) {int endRow = cell.getRowIndex() - 1;int firstRow = getFirstRow(endRow, relativeRowIndex - 1);CellRangeAddress rangeAddress = new CellRangeAddress(firstRow, endRow, cell.getColumnIndex(), cell.getColumnIndex());sheet.addMergedRegion(rangeAddress);}}private int getFirstRow(int rowIndex, int dataIndex) {if (dataIndex == 0) {return rowIndex;}if (this.eqBefore.apply(dataIndex)) {return getFirstRow(rowIndex - 1, dataIndex - 1);}return rowIndex;}
}

测试代码

测试代码中定义的对象覆盖了eques方法，通过该方法判断多个对象如果相等则进行合并操作


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.google.common.collect.Sets;
import lombok.Data;
import org.jetbrains.annotations.NotNull;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;/*** @author dzh* @since 2023-06-28*/
public class ExportExcelTest {@Testpublic void template() throws IOException {InputStream in = new ClassPathResource("template.xlsx").getInputStream();File out = new File("c://temp/report.xlsx");List<Student> data = data();Function<Integer, Boolean> comp = (i) -> data.get(i - 1).equals(data.get(i));Function<Integer, Boolean> colMerge = (i) -> i < 2;MyMergeStrategy writeHandler = new MyMergeStrategy(comp, colMerge);ExcelWriterBuilder builder = EasyExcel.write(out).withTemplate(in).registerWriteHandler(writeHandler);try (ExcelWriter excelWriter = builder.build()) {WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(data, fillConfig, writeSheet);Map<String, Object> map = new HashMap<>();map.put("tableName", "成绩报表");map.put("total", 886655);excelWriter.fill(map, writeSheet);} finally {in.close();}}@NotNullprivate static List<Student> data() {List<Student> list = new ArrayList<Student>() {{add(new Student("三年级一班", "lucy", "语文", 99));add(new Student("三年级一班", "lucy", "数学", 98));add(new Student("三年级一班", "lucy", "英语", 100));add(new Student("三年级一班", "jack", "语文", 87));add(new Student("三年级二班", "lucy", "语文", 78));add(new Student("三年级二班", "lily", "语文", 97));add(new Student("三年级二班", "lily", "数学", 79));add(new Student("三年级三班", "andy", "语文", 99));}};for (int i = 0; i < list.size(); i++) {Student cur = list.get(i);if (i == 0) {cur.setIndex(1);} else {Student pre = list.get(i - 1);if (pre.equals(cur)) {cur.setIndex(pre.getIndex());} else {cur.setIndex(pre.getIndex() + 1);}}}return list;}@Datastatic class Student {private Integer index;private String clas;private String name;private String subject;private Integer score;public Student(String clas, String name, String subject, Integer score) {this.clas = clas;this.name = name;this.subject = subject;this.score = score;}public boolean equals(Object obj) {Function<Student, Object> con = s -> String.join("-", s.getName(), s.getClas());Object a = con.apply(this);Object b = con.apply((Student) obj);System.out.println(a + "\t" + b);return a.equals(b);}}
}