调研记录,此记录代码使用的easyexcel版本为3.2.1
1、自定义Listener
通过自定义的listener读取所有合并单元格的信息
@Slf4j
public class EasyExcelListener<T> extends AnalysisEventListener<T> {
/**
* 数据
*/
List<T> dataList = new ArrayList<>();
/**
* 正文起始行
*/
private Integer headRowNumber;
/**
* 合并单元格
*/
private List<CellExtra> extraMergeInfoList = new ArrayList<>();
public EasyExcelListener(Integer headRowNumber) {
this.headRowNumber = headRowNumber;
}
@Override
public void invoke(T data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
dataList.add(data);
}
/**
* 读取额外信息:合并单元格
*/
@Override
public void extra(CellExtra extra, AnalysisContext context) {
log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
switch (extra.getType()) {
case MERGE: {
if (extra.getRowIndex() >= headRowNumber) {
extraMergeInfoList.add(extra);
}
break;
}
default: break;
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("所有数据解析完成!");
}
/**
* 返回解析出来的List
*/
public List<T> getData() {
return dataList;
}
/**
* 返回解析出来的合并单元格List
*/
public List<CellExtra> getExtraMergeInfoList() {
return extraMergeInfoList;
}
}
2、导入帮助类
@Slf4j
public class EasyExcelHelper<T> {
/**
* 返回解析后的List
*
* @param: fileName 文件名
* @param: clazz Excel对应属性名
* @param: sheetNo 要解析的sheet
* @param: headRowNumber 正文起始行
* @return java.util.List<T> 解析后的List
*/
public List<T> getList(InputStream inputStream, Class<T> head, Integer sheetNo, Integer headRowNumber) {
EasyExcelListener listener = new EasyExcelListener(headRowNumber);
try {
EasyExcelFactory.read(inputStream, listener)
.excelType(ExcelTypeEnum.XLSX)
.extraRead(CellExtraTypeEnum.MERGE)
.sheet(sheetNo)
.headRowNumber(headRowNumber)
.head(head)
.doRead();
} catch (Exception e) {
log.error(e.getMessage());
}
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 firstColumnIndex = cellExtra.getFirstColumnIndex();
int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
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.error("设置合并单元格值异常:{}", e.getMessage());
}
}
}
}
}
/**
* 获取合并单元格的初始值
* 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);
//获取注解index的值
if (annotation != null) {
if (annotation.index() == firstColumnIndex) {
try {
filedValue = field.get(object);
break;
} catch (IllegalAccessException e) {
log.error("获取合并单元格初始值异常:{}", e.getMessage());
}
}
}
}
return filedValue;
}
3、使用
EasyExcelHelper<Test> helper = new EasyExcelHelper<>();
List<Test> list = helper.getList(file.getInputStream(), Test.class,0, 1);