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

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

article 2024/10/24 3:48:09

前言

在处理 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;
    }

    @Override
    protected 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 {

    @Test
    public 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;
    }
}