使用 EasyExcel 相邻数据相同时行和列的合并,包括动态表头、数据

前言

在处理 Excel 文件时，经常会遇到需要对表格中的某些单元格进行合并的情况，例如合并相同的行或列。Apache POI 是一个强大的工具，但它使用起来相对复杂。相比之下，EasyExcel 是一个基于 Apache POI 的轻量级 Excel 处理库，它提供了更简单易用的 API，使得处理 Excel 文件变得更加方便。

本文将介绍如何使用 EasyExcel 进行列和列的合并，并提供一个完整的示例代码。

准备工作

首先，确保你的项目中已经引入了 EasyExcel 的依赖。如果你使用的是 Maven，可以在 pom.xml 中添加以下依赖：

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version>
</dependency>

创建合并策略

EasyExcel 提供了一个 AbstractMergeStrategy 抽象类，我们可以继承它来实现自定义的合并策略。下面是一个示例，展示了如何创建一个可以同时进行行和列合并的策略：

package org.songtang.exceldemo.test;import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.*;public class OptimizedMergeCellStrategyHandler extends AbstractMergeStrategy {private final boolean alikeColumn;private final boolean alikeRow;private final int rowIndex;private final int rowIndexStart;private final Set<Integer> columns;private int currentRowIndex = 0;public OptimizedMergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns) {this(alikeColumn, alikeRow, rowIndex, columns, 0);}public OptimizedMergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns, int rowIndexStart) {this.alikeColumn = alikeColumn;this.alikeRow = alikeRow;this.rowIndex = rowIndex;this.columns = columns;this.rowIndexStart = rowIndexStart;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {int rowId = cell.getRowIndex();currentRowIndex = rowId;if (rowIndex > rowId) {return;}int columnId = cell.getColumnIndex();if (alikeColumn && columnId > 0) {mergeCells(sheet, cell, columnId - 1, columnId, 0);}if (alikeRow && rowId > rowIndexStart && columns.contains(columnId)) {mergeCells(sheet, cell, rowId - 1, rowId, 1);}}private void mergeCells(Sheet sheet, Cell cell, int start, int end, int direction) {String cellValue = getCellVal(cell);Cell referenceCell = direction == 0 ? cell.getRow().getCell(start) : sheet.getRow(start).getCell(cell.getColumnIndex());String refCellValue = getCellVal(referenceCell);if (Objects.equals(cellValue, refCellValue)) {CellRangeAddress rangeAddress = createRangeAddress(sheet, cell, start, end, direction);if (rangeAddress != null) {sheet.addMergedRegion(rangeAddress);}}}private CellRangeAddress createRangeAddress(Sheet sheet, Cell cell, int start, int end, int direction) {CellRangeAddress rangeAddress = direction == 0 ?new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), start, end) :new CellRangeAddress(start, end, cell.getColumnIndex(), cell.getColumnIndex());return findExistAddress(sheet, rangeAddress, getCellVal(cell));}private CellRangeAddress findExistAddress(Sheet sheet, CellRangeAddress rangeAddress, String currentVal) {List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();for (int i = mergedRegions.size() - 1; i >= 0; i--) {CellRangeAddress exist = mergedRegions.get(i);if (exist.intersects(rangeAddress)) {if (exist.getLastRow() < rangeAddress.getLastRow()) {exist.setLastRow(rangeAddress.getLastRow());}if (exist.getLastColumn() < rangeAddress.getLastColumn()) {exist.setLastColumn(rangeAddress.getLastColumn());}sheet.removeMergedRegion(i);return exist;}}return rangeAddress;}private String getCellVal(Cell cell) {try {return cell.getStringCellValue();} catch (Exception e) {// 使用日志框架代替 System.out.printf// Logger logger = LoggerFactory.getLogger(OptimizedMergeCellStrategyHandler.class);// logger.error("读取单元格内容失败:行{} 列{}", cell.getRowIndex() + 1, cell.getColumnIndex() + 1, e);System.out.printf("读取单元格内容失败:行%d 列%d %n", (cell.getRowIndex() + 1), (cell.getColumnIndex() + 1));return null;}}
}

编写测试代码

接下来，我们编写一个测试类来生成一个包含合并行和列的 Excel 文件：

package org.songtang.exceldemo;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import org.junit.jupiter.api.Test;
import org.songtang.exceldemo.test.OptimizedMergeCellStrategyHandler;import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;//@SpringBootTest
public class ExcelTest {@Testpublic void generateRowMergedFile() {String fileName = "/Users/test/Downloads/" + System.currentTimeMillis() + ".xlsx";ExcelWriterBuilder write = EasyExcel.write(fileName);Set<Integer> set = new HashSet<>();set.add(0); // 合并第0列set.add(1); // 合并第1列set.add(2); // 合并第2列write.registerWriteHandler(new OptimizedMergeCellStrategyHandler(true, true, 2, set)); // 启用列和行合并write.head(head()).automaticMergeHead(true).sheet("模板").doWrite(data1());}private List<List<String>> data1() {List<List<String>> data = new ArrayList<>();List<String> data1 = new ArrayList<>();data1.add("人员");data1.add("人员");data1.add("语文");data1.add("数值一");data1.add("数值二");List<String> data2 = new ArrayList<>();data2.add("人员");data2.add("人员");data2.add("语文");data2.add("数值三");data2.add("数值四");data.add(data1);data.add(data2);return data;}private List<List<String>> head() {List<List<String>> list = new ArrayList<>();List<String> head0 = new ArrayList<>();head0.add("模块");head0.add("模块");List<String> head00 = new ArrayList<>();head00.add("模块");head00.add("模块");List<String> head1 = new ArrayList<>();head1.add("课程");head1.add("课程");List<String> head2 = new ArrayList<>();head2.add("完美世界");head2.add("石昊");List<String> head3 = new ArrayList<>();head3.add("完美世界");head3.add("火灵儿");list.add(head0);list.add(head00);list.add(head1);list.add(head2);list.add(head3);return list;}
}