首先对easyexcel的监听器要有一定了解
这是我的监听器
package com.pcb.eoms.module.common.easyexcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.metadata.Cell;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.util.StringUtils;
import com.google.protobuf.ServiceException;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.lang.reflect.Field;
import java.util.*;
/**
* easyExcel监听器
*/
@Data
@EqualsAndHashCode(callSuper=false)
@SuppressWarnings("unchecked")
public class EasyExcelListener <T> extends AnalysisEventListener<T> {
//成功结果集
private List<T> successList = new ArrayList<>();
//失败结果集
private List<ExcelCheckErrDto<T>> errList = new ArrayList<>();
//成功和失败所有结果集
private List<T> successAndErrList = new ArrayList<>();
//处理逻辑service
private ExcelCheckManager<T> excelCheckManager;
private List<T> list = new ArrayList<>();
//excel对象的反射类
private Class<T> clazz;
public EasyExcelListener(ExcelCheckManager<T> excelCheckManager){
this.excelCheckManager = excelCheckManager;
}
public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){
this.excelCheckManager = excelCheckManager;
this.clazz = clazz;
}
@Override
public void invoke(T t, AnalysisContext analysisContext) {
String errMsg;
try {
//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
errMsg = EasyExcelValiHelper.validateEntity(t);
} catch (NoSuchFieldException e) {
errMsg = "解析数据出错";
e.printStackTrace();
}
if (!StringUtils.isEmpty(errMsg)){
ExcelCheckErrDto excelCheckErrDto = new ExcelCheckErrDto(t, errMsg);
errList.add(excelCheckErrDto);
}else{
list.add(t);
}
//每100000条处理一次
if (list.size() > 100000){
//校验
ExcelCheckResult result = excelCheckManager.checkImportExcel(list);
successList.addAll(result.getSuccessDtos());
successAndErrList.addAll(result.getSuccessAndErrs());
errList.addAll(result.getErrDtos());
list.clear();
}
}
//所有数据解析完成了 都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
ExcelCheckResult result = excelCheckManager.checkImportExcel(list);
successList.addAll(result.getSuccessDtos());
successAndErrList.addAll(result.getSuccessAndErrs());
errList.addAll(result.getErrDtos());
list.clear();
}
/**
* 校验excel头部格式,必须完全匹配
* @param headMap 传入excel的头部(第一行数据)数据的index,name
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
super.invokeHeadMap(headMap, context);
//对设定的rowNumber进行处理
ReadRowHolder readRowHolder = context.readRowHolder();
Map<Integer, CellData> cellDataMap = (Map)readRowHolder.getCellMap();
readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
int rowIndex = readRowHolder.getRowIndex();
int currentHeadRowNumber = context.readSheetHolder().getHeadRowNumber();
boolean isData = rowIndex >= currentHeadRowNumber;
if (!isData && currentHeadRowNumber != rowIndex + 1) {
return;
}
if (clazz != null){
try {
Map<Integer, String> indexNameMap = getIndexNameMap(clazz);
Set<Integer> keySet = indexNameMap.keySet();
for (Integer key : keySet) {
if (StringUtils.isEmpty(headMap.get(key))){
throw new ExcelAnalysisException("解析excel出错,头部格式有误!");
//throw new ExcelAnalysisException("解析excel出错,请传入正确格式的excel");
}
if (!headMap.get(key).equals(indexNameMap.get(key))){
throw new ExcelAnalysisException("解析excel出错,头部格式有误!");
//throw new ExcelAnalysisException("解析excel出错,请传入正确格式的excel");
}
}
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
}
/**
* 获取注解里ExcelProperty的value,用作校验excel
*/
@SuppressWarnings("rawtypes")
public Map<Integer,String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
Map<Integer,String> result = new HashMap<>();
Field field;
Field[] fields=clazz.getDeclaredFields();
for (int i = 0; i <fields.length ; i++) {
field=clazz.getDeclaredField(fields[i].getName());
field.setAccessible(true);
ExcelProperty excelProperty=field.getAnnotation(ExcelProperty.class);
if(excelProperty!=null){
int index = excelProperty.index();
String[] values = excelProperty.value();
StringBuilder value = new StringBuilder();
for (String v : values) {
value.append(v);
}
result.put(index,value.toString());
}
}
return result;
}
}
如上面代码所示,主要原因在于重写 invokeHeadMap 方法之后对首行做了校验,恰好我第一行不是head信息,而是注释信息,不满足和实体类不一样,故报错。
加了如下代码之后,对当前页和设定的headRowNumber 进行比较,只有相同才进行校验。
//对设定的rowNumber进行处理
ReadRowHolder readRowHolder = context.readRowHolder();
Map<Integer, CellData> cellDataMap = (Map)readRowHolder.getCellMap();
readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
int rowIndex = readRowHolder.getRowIndex();
int currentHeadRowNumber = context.readSheetHolder().getHeadRowNumber();
boolean isData = rowIndex >= currentHeadRowNumber;
if (!isData && currentHeadRowNumber != rowIndex + 1) {
return;
}
此代码来源于easyexcel源码 DefaultAnalysisEventProcessor 这个类中
其中的 dealData 方法(每个版本大同小异,区别不大),根据源码调试了解到:每读取excel一行之后都会调用如下方法,判断是数据还是头,然后进行对应的处理,如果不是数据的话,就会调用 readListener.invokeHead(cellDataMap, analysisContext),如果你重写了 invokeHeadMap 方法,就会调用到我们自己的监听器重写的这个方法。就可能出现问题,所以需要你在重写的方法中判断当前行和设定的headRowNumber的关系。
private void dealData(AnalysisContext analysisContext) {
ReadRowHolder readRowHolder = analysisContext.readRowHolder();
Map<Integer, CellData> cellDataMap = (Map)readRowHolder.getCellMap();
readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
int rowIndex = readRowHolder.getRowIndex();
int currentHeadRowNumber = analysisContext.readSheetHolder().getHeadRowNumber();
boolean isData = rowIndex >= currentHeadRowNumber;
// Last head column
if (!isData && currentHeadRowNumber == rowIndex + 1) {
buildHead(analysisContext, cellDataMap);
}
// Now is data
for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
try {
if (isData) {
readListener.invoke(readRowHolder.getCurrentRowAnalysisResult(), analysisContext);
} else {
readListener.invokeHead(cellDataMap, analysisContext);
}
} catch (Exception e) {
onException(analysisContext, e);
break;
}
if (!readListener.hasNext(analysisContext)) {
throw new ExcelAnalysisStopException();
}
}
}