EasyExcel 实现批量合并单元格（支持自定义）

目录

1 Maven配置文件

2 MergeCellModel

3 CustomMergeCellHandler

4 调试代码

5 调试结果

 注：

---

1 Maven配置文件

        <!--hutool工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>

2 MergeCellModel

 合并单元格信息。

package com.xudongbase.common.easyexcel.model;

import lombok.Getter;

/**
 * 合并单元格信息
 *
 * @author xudongmaster
 */
@Getter
public class MergeCellModel {

    /**
     * sheet名称
     */
    private String sheetName;
    /**
     * 开始行号
     */
    private int startRowIndex;
    /**
     * 开始列号
     */
    private int startColumnIndex;
    /**
     * 结束行号
     */
    private int endRowIndex;
    /**
     * 结束列号
     */
    private int endColumnIndex;

    private void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    private void setStartRowIndex(int startRowIndex) {
        this.startRowIndex = startRowIndex;
    }

    private void setStartColumnIndex(int startColumnIndex) {
        this.startColumnIndex = startColumnIndex;
    }

    private void setEndRowIndex(int endRowIndex) {
        this.endRowIndex = endRowIndex;
    }

    private void setEndColumnIndex(int endColumnIndex) {
        this.endColumnIndex = endColumnIndex;
    }

    private MergeCellModel() {

    }

    /**
     * 生成合并列单元格信息
     *
     * @param sheetName        sheet页名称
     * @param rowIndex         行号
     * @param startColumnIndex 开始列号
     * @param endColumnIndex   结束列号
     * @return
     */
    public static MergeCellModel createMergeColumnCellModel(String sheetName, int rowIndex, int startColumnIndex
            , int endColumnIndex) {
        return createMergeCellModel(sheetName, rowIndex, rowIndex, startColumnIndex, endColumnIndex);
    }

    /**
     * 生成合并单元格信息
     *
     * @param sheetName     sheet页名称
     * @param startRowIndex 开始行号
     * @param endRowIndex   结束行号
     * @param columnIndex   列号
     * @return
     */
    public static MergeCellModel createMergeRowCellModel(String sheetName, int startRowIndex, int endRowIndex, int columnIndex) {
        return createMergeCellModel(sheetName, startRowIndex, endRowIndex, columnIndex, columnIndex);
    }

    /**
     * 生成合并单元格信息
     *
     * @param sheetName        sheet页名称
     * @param startRowIndex    开始行号
     * @param endRowIndex      结束行号
     * @param startColumnIndex 开始列号
     * @param endColumnIndex   结束列号
     * @return
     */
    public static MergeCellModel createMergeCellModel(String sheetName, int startRowIndex, int endRowIndex, int startColumnIndex
            , int endColumnIndex) {
        MergeCellModel mergeCellModel = new MergeCellModel();
        //sheet页名称
        mergeCellModel.setSheetName(sheetName);
        //开始行号
        mergeCellModel.setStartRowIndex(startRowIndex);
        //结束行号
        mergeCellModel.setEndRowIndex(endRowIndex);
        //开始列号
        mergeCellModel.setStartColumnIndex(startColumnIndex);
        //结束列号
        mergeCellModel.setEndColumnIndex(endColumnIndex);
        return mergeCellModel;
    }
}

3 CustomMergeCellHandler

自定义合并单元格处理器。

package com.xudongbase.common.easyexcel.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.xudongbase.common.easyexcel.model.MergeCellModel;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.stream.Collectors;

/**
 * 自定义合并单元格处理器
 * 每次合并需要sheet页名称、指定开始行号、开始列号、结束行号、结束列号
 * 支持批量合并单元格
 *
 * @author xudongmaster
 */
public class CustomMergeCellHandler implements SheetWriteHandler {
    /**
     * 合并单元格信息
     */
    private List<MergeCellModel> mergeCellList;
    /**
     * sheet页名称列表
     */
    private List<String> sheetNameList;

    public CustomMergeCellHandler(List<MergeCellModel> mergeCellList) {
        if (CollUtil.isEmpty(mergeCellList)) {
            return;
        }
        this.mergeCellList = mergeCellList.stream().filter(x ->
                StrUtil.isNotBlank(x.getSheetName()) && x.getStartRowIndex() >= 0 && x.getEndRowIndex() >= 0
                        && x.getStartColumnIndex() >= 0 && x.getEndColumnIndex() >= 0).collect(Collectors.toList());
        List<String> sheetNameList = this.mergeCellList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
        if (CollUtil.isEmpty(sheetNameList)) {
            return;
        }
        this.sheetNameList = sheetNameList;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * sheet页创建之后调用
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //不需要合并单元格信息，或者当前sheet页不需要合并单元格信息
        if (CollUtil.isEmpty(mergeCellList) || sheetNameList.contains(sheet.getSheetName()) == false) {
            return;
        }
        List<MergeCellModel> sheetMergeCellList = mergeCellList.stream().filter(x ->
                StrUtil.equals(x.getSheetName(), sheet.getSheetName())).collect(Collectors.toList());
        for (MergeCellModel mergeCellModel : sheetMergeCellList) {
            //开始行号
            int startRowIndex = mergeCellModel.getStartRowIndex();
            //结束行号
            int endRowIndex = mergeCellModel.getEndRowIndex();
            //开始列号
            int startColumnIndex = mergeCellModel.getStartColumnIndex();
            //结束列号
            int endColumnIndex = mergeCellModel.getEndColumnIndex();
            //行号和列号非法(<0)
            if (startColumnIndex < 0 || endColumnIndex < 0 || startRowIndex < 0 || endRowIndex < 0) {
                continue;
            }
            //合并单元格区域只有一个单元格时，不合并
            if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {
                continue;
            }
            //开始行号大于结束行号，或者开始列号大于结束列号
            if (startColumnIndex > endColumnIndex || startRowIndex > endRowIndex) {
                continue;
            }
            //添加合并单元格区域
            CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }
        //删除合并单元格信息
        mergeCellList.removeAll(sheetMergeCellList);
        sheetNameList = mergeCellList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
    }
}

4 调试代码

    /**
     * 测试合并单元格
     */
    @Test
    public void testMergeCell(){
        try {
            File file = new File("D:/easyexcel/testMergeCell.xlsx");
            FileUtil.createNewFile(file);
            //生成表格数据
            List<List<Object>> dataList = new ArrayList<>();
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));

            //生成合并单元格信息
            List<MergeCellModel> mergeCellList = new ArrayList<>();
            String sheetName="模板";
            mergeCellList.add(MergeCellModel.createMergeCellModel(sheetName, 0, 1 , 0, 1));
            mergeCellList.add(MergeCellModel.createMergeColumnCellModel(sheetName,  0 , 2, 3));
            mergeCellList.add(MergeCellModel.createMergeRowCellModel(sheetName,  1 ,2, 2));

            FileOutputStream fileOutputStream = new FileOutputStream(file);
            ExcelWriter excelWriter = EasyExcel.write(fileOutputStream).registerWriteHandler(new CustomMergeCellHandler(mergeCellList)).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(dataList, writeSheet);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5 调试结果

 注：

如果需要源码请前往Gitee查看。

旭东怪/xudongbasehttps://gitee.com/xudong_master/xudongbase