原文地址：/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: /9/26 9:48</li>* <li>Version: 1.0</li>* <li>Content: create</li>*/@Slf4jpublic 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: /9/26 9:48</li>* <li>Version: 1.0</li>* <li>Content: create</li>*/@Slf4jpublic 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;}/*** 有多少条数据执行多少次*/@Overridepublic void invoke(T data, AnalysisContext context) {list.add(data);}/*** 等invoke把所有数据解析完成了，最后执行一次* @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("");}@Overridepublic 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);
