easyExcel-读取合并单元格

前言

Java-easyExcel入门教程：https://blog.csdn.net/xhmico/article/details/134714025

之前有介绍过如何使用 easyExcel，以及写了两个入门的 demo ，这两个 demo 能应付在开发中大多数的导入和导出需求，不过有时候面对一些复杂的表格，就会有点不够用，该篇就是关于我如何处理表格中的合并单元格的一个开发过程记录

以下内容是结合 Java-easyExcel入门教程中的案例代码去实现的，可能与你项目中所使用的 ex=asyExcel 会有点不同

一、情景介绍

假如说你有一个表格的数据想要导入到系统中，在通常情况下，面对标准的表格文件，比如：

表头和内容都比较工整，每个单元格对应一个数据，通过 Java-easyExcel入门教程中的内容就能轻易实现导入

但是如果导入的文件中存在一些 合并的单元格，例如：

在这里插入图片描述

还是按照之前的方式实现导入，那么读出来的数据是：

在这里插入图片描述

原本期望第一条数据 访问IP 读到的值为 unknown，第三条数据 登录名 读到的值为 mike，当时结果均为 null

二、问题分析

首先需要知道 excel 表格合并单元格的原理：在合并单元格时，仅保留左上角的值，而放弃其他值

在这里插入图片描述

也就是说合并的单元格取的值都是 左上角的值

在官方文档中对于如何读取合并单元格信息是这样的描述的：

在这里插入图片描述

首先是需要在监听器中添加一个 extra 方法

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch (extra.getType()) {
            case COMMENT:
                log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
                    extra.getText());
                break;
            case HYPERLINK:
                if ("Sheet1!A1".equals(extra.getText())) {
                    log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                        extra.getColumnIndex(), extra.getText());
                } else if ("Sheet2!A1".equals(extra.getText())) {
                    log.info(
                        "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
                            + "内容是:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex(), extra.getText());
                } else {
                    Assert.fail("Unknown hyperlink!");
                }
                break;
            case MERGE:
                log.info(
                    "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                    extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                    extra.getLastColumnIndex());
                break;
            default:
        }
    }

在读取文件的时候添加 .extraRead(CellExtraTypeEnum.MERGE) 就能获取到所有的合并单元格

在这里插入图片描述

    /**
     * 额外信息（批注、超链接、合并单元格信息读取）
     * <p>
     * 由于是流式读取，没法在读取到单元格数据的时候直接读取到额外信息，所以只能最后通知哪些单元格有哪些额外信息
     *
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoExtraData}
     * <p>
     * 2. 由于默认异步读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoExtraListener}
     * <p>
     * 3. 直接读即可
     *
     * @since 2.2.0-beat1
     */
    @Test
    public void extraRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "extra.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet
        EasyExcel.read(fileName, DemoExtraData.class, new DemoExtraListener())
            // 需要读取合并单元格信息 默认不读取
            .extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();
    }

由于是流式读取，没法在读取到单元格数据的时候直接读取到额外信息，所以只能最后通知哪些单元格有哪些额外信息

按照官方文档说的方式可以看到是有读到 合并单元格 的

在这里插入图片描述

firstRowIndex：起始行索引
lastRowIndex：结束行索引
firstColumnIndex：起始列索引
lastColumnIndex：结束列索引

在这里插入图片描述

也就是说只要获取到所有的数据 datas 和所有的合并单元格 mergeDatas，合并单元格中的值可以通过 firstRowIndex 和 firstColumnIndex 去 datas 中获取，firstRowIndex 可以锁定哪个 data ，firstColumnIndex 可以锁定是 data 中的哪个字段

在 easyExcel 中可以通过 @ExcelProperty 中的 index 属性来标明该字段的索引，例如：

    @ApiModelProperty(value = "自增主键")
    @ExcelProperty(value = "自增主键", index = 0)
    @ColumnWidth(15)
    private Integer id;

三、代码实现

关于代码的实现我是参考了：陈彦斌-easyexcel 读取合并单元格

类比 Java-easyExcel入门教程中的 导入案例 主要做了以下修改：

在监听器中添加收集 合并单元格 相关的代码

ExcelDateListener.java

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelDateListener<M> extends AnalysisEventListener<M> {

    private final ExcelReaderListenerCallback<M> callback;

    // 每隔50条存储数据库，实际使用中可以3000条，然后清理list，方便内存回收
    private static final int BATCH_COUNT = 50;
    // 表头数据
    Map<Integer,String> headMap=new HashMap<>();
    // 缓存数据
    List<M> cacheList = new ArrayList<>();
    // 合并单元格
    private final List<CellExtra> extraMergeInfoList = new ArrayList<>();

    public ExcelDateListener(ExcelReaderListenerCallback<M> callback) {
        this.callback = callback;
    }

    /**
     * 获取合并单元格
     */
    public List<CellExtra> getExtraMergeInfoList() {
        return this.extraMergeInfoList;
    }

    /**
     * 这里会一行行的返回头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap=headMap;
        log.info("解析到一条头数据:{}", JSON.toJSONString( headMap));
    }


    @Override
    public void invoke(M data, AnalysisContext analysisContext) {
        cacheList.add(data);
        // 在这里可以做一些其他的操作，就靠自己去拓展了
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cacheList.size() >= BATCH_COUNT) {
            // 这里是存数据库的操作
            callback.convertData(cacheList,headMap);
            // 存储完成清理 list
            cacheList.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        callback.convertData(cacheList,headMap);
        cacheList.clear();
    }


    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            log.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex());
        }
    }

    /**
     * 读取条额外信息：批注、超链接、合并单元格信息等
     */
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch (extra.getType()) {
            case COMMENT:
                log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
                        extra.getText());
                break;
            case HYPERLINK:
                if ("Sheet1!A1".equals(extra.getText())) {
                    log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                            extra.getColumnIndex(), extra.getText());
                } else if ("Sheet2!A1".equals(extra.getText())) {
                    log.info(
                            "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
                                    + "内容是:{}",
                            extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                            extra.getLastColumnIndex(), extra.getText());
                } else {
                    log.error("Unknown hyperlink!");
                }
                break;
            case MERGE:
                log.info(
                        "额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex());
                extraMergeInfoList.add(extra);
                break;
            default:
        }
    }
}

easyExcel 工具类中添加解析合并单元格的方法

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellExtra;
import com.mike.common.core.constant.DateFormatConstant;
import com.mike.common.core.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public class EasyExcelUtil {

    /**
     * 初始化响应体
     * @param response 请求头
     * @param fileName 导出名称
     */
    public static void initResponse(HttpServletResponse response, String fileName) {
        String finalFileName = fileName + "_(截止"+ StringUtils.getNowTimeStr(DateFormatConstant.Y0M0D)+")";
        // 设置content—type 响应类型
        // response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码
            finalFileName = URLEncoder.encode(finalFileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + finalFileName + ".xlsx");
    }

    /**
     * 处理合并单元格
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    public static <T> List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
        // 循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            // 获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
            // 设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, data);
                }
            }
        });
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    private static <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        if (rowIndex >= data.size()) return;

        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            // 提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        log.error("设置合并单元格的值异常：{}", e.getMessage());
                    }
                }
            }
        }
    }

    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            // 提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        log.error("设置合并单元格的初始值异常：{}", e.getMessage());
                    }
                }
            }
        }
        return filedValue;
    }

}

四、测试方法

首先一定不要忘记给模板对象的字段设置 index 属性，例如：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.mike.common.core.constant.DateFormatConstant;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
// 头背景设置
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
//标题高度
@HeadRowHeight(40)
//内容高度
@ContentRowHeight(30)
//内容居中,左、上、右、下的边框显示
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
public class AccessLogEasyVo {

    @ApiModelProperty(value = "自增主键")
    @ExcelProperty(value = "自增主键", index = 0)
    @ColumnWidth(15)
    private Integer id;

    @ApiModelProperty(value = "登录名")
    @ExcelProperty(value = "登录名", index = 1)
    @ColumnWidth(15)
    private String loginName;

    @ApiModelProperty(value = "访问路径")
    @ExcelProperty(value = "访问路径", index = 2)
    @ColumnWidth(15)
    private String accessPath;

    @ApiModelProperty(value = "访问IP")
    @ExcelProperty(value = "访问IP", index = 3)
    @ColumnWidth(15)
    private String accessIp;

    @ApiModelProperty(value = "创建时间")
    @ExcelProperty(value = "创建时间", index = 4)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ColumnWidth(15)
    private Date createTime;

    @ApiModelProperty(value = "访问状态：0已拦截；1已放行")
    @ExcelProperty(value = "访问状态", index = 5)
    @ColumnWidth(15)
    // @ExcelIgnore
    private Boolean state;

}

写一个简单的代码示例：

TestController.java

    @ApiOperation(value = "示例：导入-含合并单元格")
    @PostMapping("/importDate/merge-easy")
    @ApiImplicitParam(name = "file", value = "文件", dataTypeClass = MultipartFile.class, required = true)
    public ResponseBean<String> easyImportMergeDate(@RequestPart("file") MultipartFile file) {
        testService.easyImportMergeDate(file);
        return ResponseBean.success();
    }

TestService.java

    /**
     * 导入-含合并单元格
     */
    void easyImportMergeDate(MultipartFile file);

TestServiceImpl.java

    @Override
    public void easyImportMergeDate(MultipartFile file) {

        try {
            // 所有的表数据
            List<AccessLogEasyVo> voList = new ArrayList<>();

            // 从第几行还是读取
            int headRowNumber = 1;

            // 创建监听器
            ExcelDateListener<AccessLogEasyVo> listener = new ExcelDateListener<>(new ExcelReaderListenerCallback<AccessLogEasyVo>() {
                @Override
                public void convertData(List<AccessLogEasyVo> data, Map<Integer, String> headMap) {
                    // 将读到的数据放到集合中
                    voList.addAll(data);
                }
            });

            EasyExcel.read(file.getInputStream(), AccessLogEasyVo.class, listener)
                    .headRowNumber(headRowNumber) // 从第几行读起
                    .extraRead(CellExtraTypeEnum.MERGE) // 需要读取合并单元格信息
                    .sheet().doRead();

            // 获取所有的合并单元格
            List<CellExtra> mergeInfoList = listener.getExtraMergeInfoList();
            // 解析合并单元格并赋值到对于的表数据中
            List<AccessLogEasyVo> accessLogEasyVos = EasyExcelUtil.explainMergeData(voList, mergeInfoList, headRowNumber);
            // 处理数据
            System.out.println("accessLogEasyVos = " + accessLogEasyVos);

        } catch (IOException e) {
            log.error("import excel error：",e);
            throw new CommonException(ExceptionEnum.IMPORT_EXCEL_ERROR);
        }
    }