基于EasyExcel导出Excel后,通过对合并单元格的简单规则配置,实现如下图所示的单元格合并效果:
效果截图
原表格数据如下:
通过配置单元格合并规则后,生成的合并后的表格如下:
注:其中第三列,没有配置合并规则,数据保持不动
自定义合并规则类
如下代码类,是处理单元格合并的核心类,主要是按行计算相同数据进行单元格合并。
package com.shanhy.demo.project.service.impl;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.shanhy.demo.common.utils.JsonUtils;
import com.shanhy.demo.project.vo.ExcelMergeStrategyModel;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
/**
* Excel 行合并策略
*/
@Slf4j
public class CustomLoopMergeStrategy implements RowWriteHandler {
//上一行
private Row beforeRow = null;
//合并规则(多个)
private List<ExcelMergeStrategyModel> strategyList;
//总行数(不含表头)
private int dataRowTotalSize;
//当前已经处理的行数(不含表头)
private int dataRowCurrentSize = 0;
private CustomLoopMergeStrategy() {
}
/**
* 构造方法
*
* @param loopMergeStrategyJson 合并规则JSON,其中 columnName 为要被自动计算合并的列,relativeColumnNames 表示目标合并列需要参照的相关列(值全部相同则会触发合并目标目标列的单元格)
* 示例:
* String loopMergeStrategyJson =
* [
* {
* "columnName": "A",
* "relativeColumnNames": "A"
* },
* {
* "columnName": "B",
* "relativeColumnNames": "A,B"
* },
* {
* "columnName": "C",
* "relativeColumnNames": "A,B,C"
* },
* {
* "columnName": "D",
* "relativeColumnNames": "A,B,C,D"
* }
* ];
* @param dataRowTotalSize 所有数据的行数
*/
public CustomLoopMergeStrategy(String loopMergeStrategyJson, int dataRowTotalSize) {
//记录excel行数
this.dataRowTotalSize = dataRowTotalSize;
//解析json 获取合并规则
this.strategyList = JsonUtils.jsonToList(loopMergeStrategyJson, ExcelMergeStrategyModel.class);
}
@SneakyThrows
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
//表头直接跳过
if