EasyExcel导入、导出合并单元格excel文件

原文地址：https://blog.csdn.net/weixin_42195311/article/details/110441885

<!--版本最好大于2.2.6，版本太低会导致没有extra方法-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

一.导入

设置读取额外信息

重写Listener中的extra()方法，获取合并单元格的信息
然后通过ExcelAnalysisHelper类把数据加到每一行

主要有两个通用公共类，一个ExcelAnalysisHelper类，一个Listener类。导入即可用。

注：
合并单元格只有第一个（firstRowIndex，firstColumnIndex）有值，所以要取到这个值。
通过获取到的合并单元格信息（firstRowIndex,lastRowIndex,firstColumnIndex,lastColumnIndex），遍历此区域的每一个单元格，并给每一个单元格都赋上该值
此方法的重点在于利用反射找到实体对应的属性，对应关系是@ExcelProperty(index = 0)->columnIndex
index对应了columnIndex（也就是字段在excel所在的位置）；rowindex对应了解析出来的List data的索引值

1.ExcelAnalysisHelper

package com.crunii.micro.service.jztz.others.listener;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.List;

/**
 * @Filename: ExcelAnalysisHelper
 * @Author: sheng.wanping
 * <li>Date: 2021/9/26 9:48</li>
 * <li>Version: 1.0</li>
 * <li>Content: create</li>
 */
@Slf4j
public class ExcelAnalysisHelper<T> {

    public List<T> getList(InputStream inputStream, Class<T> clazz) {
        return getList(inputStream, clazz, 0, 2);
    }

    public List<T> getList(InputStream inputStream, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        ExcelImportListener<T> listener = new ExcelImportListener<>(headRowNumber);

        EasyExcel.read(inputStream, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();

        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
        return data;
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private 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        解析数据
     */
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        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.warn("解析数据时发生异常!");
                      //  throw new BizException(ResultCode.FAILURE, "解析数据时发生异常!");
                    }
                }
            }
        }
    }


    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private 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.warn("解析数据时发生异常!");
                      //  throw new BizException(ResultCode.FAILURE, "解析数据时发生异常!");
                    }
                }
            }
        }
        return filedValue;
    }
}

2.ExcelImportListener：

package com.crunii.micro.service.jztz.others.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;


/**
 * @Filename: ExcelImportListener
 * @Author: sheng.wanping
 * <li>Date: 2021/9/26 9:48</li>
 * <li>Version: 1.0</li>
 * <li>Content: create</li>
 */
@Slf4j
public class ExcelImportListener<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportListener.class);

    /**
     * 解析的数据
     */
    List<T> list = new ArrayList<>();
    public List<T> getData(){
        return list;
    }
    /**
     * 正文起始行
     */
    private Integer headRowNumber;
    /**
     * 合并单元格
     */
    private List<CellExtra> extraMergeInfoList = new ArrayList<>();
    public List<CellExtra> getExtraMergeInfoList(){
        return extraMergeInfoList;
    }
    public ExcelImportListener(Integer headRowNumber) {
        this.headRowNumber = headRowNumber;
    }

    /**
     * 有多少条数据执行多少次
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
    }
    /**
     * 等invoke把所有数据解析完成了，最后执行一次
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("");
    }

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

3.测试

ExcelAnalysisHelper excelAnalysisHelper = new ExcelAnalysisHelper();
List<DcEcSystemImportCondition> list = excelAnalysisHelper.getList(inputStream, DcEcSystemImportCondition.class);
list.forEach(System.out::println);