使用easyexcel,很多时候客户上传的Excel并没有按照你要求的模板进行上传,所以我们需要去限制上传的Excel模板是否与我们要求的模板一致,验证表头是否一致。
@Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { log.info("解析到一条头数据:{}", JsonUtils.obj2json(headMap)); if (context.readRowHolder().getRowIndex() == 0) { String[] headList = {"序号", "姓名", "性别", "年龄", "文化程度", "民族", "身份证号", "工作单位/学校", "手机/电话", "备注"}; for (int i = 0; i < headList.length; i++) { try { if (!headMap.get(i).equals(headList[i])) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } catch (Exception e) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } } }
这时候其实模板已经不一样了,也没有必要向下继续校验数据的正确性了,所以需要停止解析了,而尝试了return之类的方法,并没有什么效果,网上搜索了半天,基本都是throw new ExcelAnalysisStopException()做异常
@Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { log.info("解析到一条头数据:{}", JsonUtils.obj2json(headMap)); if (context.readRowHolder().getRowIndex() == 0) { String[] headList = {"序号", "姓名", "性别", "年龄", "文化程度", "民族", "身份证号", "工作单位/学校", "手机/电话", "备注"}; for (int i = 0; i < headList.length; i++) { try { if (!headMap.get(i).equals(headList[i])) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } catch (Exception e) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } } throw new ExcelAnalysisException(isNull); } /** * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 */ @Override public void onException(Exception exception, AnalysisContext context) { log.error("解析失败,但是继续解析下一行:{}", exception.getMessage()); // 如果要获取头的信息 配合invokeHeadMap使用 ExcelDataConvertException excelDataConvertException = null; if (exception instanceof ExcelDataConvertException) { excelDataConvertException = (ExcelDataConvertException) exception; log.error("第{}行,第{}列,{}解析异常", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()); } if (null != excelDataConvertException) { int row = excelDataConvertException.getRowIndex() + 1; int col = excelDataConvertException.getColumnIndex() + 1; isNull = "第" + row + "行,第" + col + "列," + excelDataConvertException.getCellData() + "解析异常"; } throw new ExcelAnalysisStopException(isNull); }
使用这样的异常处理,解析虽然是暂停了,但抛出了异常信息并不是我想要的
09:46:49.188 [main] DEBUG com.alibaba.excel.context.AnalysisContextImpl - Began to read:ReadSheetHolder{sheetNo=0, sheetName='在职员工'} com.alibaba.excel.read.metadata.holder.ReadSheetHolder@7486b455 09:46:50.119 [main] INFO cn.oick.api.ExcelReadIsNullListener - 解析到一条头数据:{"0":"序号","1":"姓名","2":"性别","3":"年龄","4":"工作单位/学校/社区","5":"联系电话","6":"备注"} 09:46:50.120 [main] ERROR cn.oick.api.ExcelReadIsNullListener - 解析失败,但是继续解析下一行:上传模板与系统模板不匹配,请使用平台模板上传数据 Exception in thread "main" com.alibaba.excel.exception.ExcelAnalysisException: 上传模板与系统模板不匹配,请使用平台模板上传数据 at com.alibaba.excel.read.metadata.holder.AbstractReadHolder.notifyEndOneRow(AbstractReadHolder.java:170) at com.alibaba.excel.analysis.v07.handlers.ProcessResultCellHandler.endHandle(ProcessResultCellHandler.java:44) at com.alibaba.excel.analysis.v07.XlsxRowHandler.endElement(XlsxRowHandler.java:44) at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(AbstractSAXParser.java:609) at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(XMLDocumentFragmentScannerImpl.java:1782) at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2967) at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602) at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:505) at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:842) at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:771)
然后看到这篇文章《easyexcel 异常处理》同样使用throw new ExcelAnalysisStopException(“解析出错:”+row+”行 “+column+”列,停止运行”);但最后的异常信息是这样的
10:35:50.577 [main] INFO com.example.demo.listener.CustomListener3 - 解析出错:Converter not found, convert STRING to java.time.LocalDateTime 10:35:50.577 [main] ERROR com.example.demo.listener.CustomListener3 - 解析出错:1行 1列 10:35:50.577 [main] DEBUG com.alibaba.excel.analysis.ExcelAnalyserImpl - Custom stop!
这样才是想要的信息,结果看了半天这文章,和之前的并没有什么差别,然后就看到这个类com.alibaba.excel.analysis.ExcelAnalyserImpl
@Override public void analysis(ListreadSheetList, Boolean readAll) { try { if (!readAll && CollectionUtils.isEmpty(readSheetList)) { throw new IllegalArgumentException("Specify at least one read sheet."); } try { excelReadExecutor.execute(readSheetList, readAll); } catch (ExcelAnalysisStopException e) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Custom stop!"); } } // The last sheet is read if (excelReadExecutor instanceof XlsSaxAnalyser) { if (analysisContext.readSheetHolder() != null) { analysisContext.readSheetHolder().notifyAfterAllAnalysed(analysisContext); } } } catch (RuntimeException e) { finish(); throw e; } catch (Throwable e) { finish(); throw new ExcelAnalysisException(e); } }
通过debug发现,之前抛出的异常并没有走LOGGER.debug(“Custom stop!”);中,而是走了RuntimeException e,通过另一个类AbstractReadHolder的notifyEndOneRow中发现,我们之前在onException抛出的异常再解析过程中变成了throw new ExcelAnalysisException(exception.getMessage(), exception);
for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) { try { readListener.invoke(readRowHolder.getCurrentRowAnalysisResult(), analysisContext); } catch (Exception e) { for (ReadListener readListenerException : analysisContext.currentReadHolder().readListenerList()) { try { readListenerException.onException(e, analysisContext); } catch (Exception exception) { throw new ExcelAnalysisException(exception.getMessage(), exception); } } break; } if (!readListener.hasNext(analysisContext)) { throw new ExcelAnalysisStopException(); } }
异常抛出不行,我们可以使用readListener.hasNext()方法让它抛出throw new ExcelAnalysisStopException();所以我们只需要在之前的监听类中添加这个方法就行了,完整代码如下:
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.exception.ExcelDataConvertException; import cn.oick.api.base.util.JsonUtils; import cn.oick.api.excel.dto.train.ExcelTrainPopularAmbulanceData; import lombok.extern.slf4j.Slf4j; import org.springframework.util.StringUtils; import java.util.Map; @Slf4j public class ExcelReadIsNullListener extends AnalysisEventListener{ // Excel行数 private int num; // 校验规则信息 private String isNull; // 返回的校验规则信息 public String getIsNull() { return isNull; } /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 */ public ExcelReadIsNullListener() { // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 num = 0; isNull = "true"; } @Override public void invokeHeadMap(Map headMap, AnalysisContext context) { log.info("解析到一条头数据:{}", JsonUtils.obj2json(headMap)); if (context.readRowHolder().getRowIndex() == 0) { String[] headList = {"序号", "姓名", "性别", "年龄", "文化程度", "民族", "身份证号", "工作单位/学校", "手机/电话", "备注"}; for (int i = 0; i < headList.length; i++) { try { if (!headMap.get(i).equals(headList[i])) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } catch (Exception e) { isNull = "上传模板与系统模板不匹配,请使用平台模板上传数据"; break; } } } } /** * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 */ @Override public void onException(Exception exception, AnalysisContext context) { log.error("解析失败,但是继续解析下一行:{}", exception.getMessage()); // 如果要获取头的信息 配合invokeHeadMap使用 ExcelDataConvertException excelDataConvertException = null; if (exception instanceof ExcelDataConvertException) { excelDataConvertException = (ExcelDataConvertException) exception; log.error("第{}行,第{}列,{}解析异常", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()); } if (null != excelDataConvertException) { int row = excelDataConvertException.getRowIndex() + 1; int col = excelDataConvertException.getColumnIndex() + 1; isNull = "第" + row + "行,第" + col + "列," + excelDataConvertException.getCellData() + "解析异常"; } } /** * 这个每一条数据解析都会来调用 */ @Override public void invoke(ExcelTrainPopularAmbulanceData data, AnalysisContext context) { log.info("解析到一条数据:{}", JsonUtils.obj2json(data)); int row = context.readRowHolder().getRowIndex() + 1; if (StringUtils.isEmpty(data.getName())) { isNull = "第" + row + "行,第" + 2 + "列," + "姓名不能为空"; } num++; } /** * 当出现模板数据异常时,结束往下解析,抛出异常 */ @Override public boolean hasNext(AnalysisContext context) { return "true".equals(isNull); } /** * 所有数据解析完成了 都会来调用 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("所有数据校验完成!"); if (num == 0 && "true".equals(isNull)) { isNull = "请勿上传空数据文件"; } } }
输出了我想要的校验信息
10:27:47.821 [main] DEBUG com.alibaba.excel.context.AnalysisContextImpl - Began to read:ReadSheetHolder{sheetNo=0, sheetName='在职员工'} com.alibaba.excel.read.metadata.holder.ReadSheetHolder@7486b455 10:27:48.417 [main] INFO cn.oick.api.ExcelReadIsNullListener - 解析到一条头数据:{"0":"序号","1":"姓名","2":"性别","3":"年龄","4":"工作单位/学校/社区","5":"联系电话","6":"备注"} 10:27:48.418 [main] DEBUG com.alibaba.excel.analysis.ExcelAnalyserImpl - Custom stop! 10:27:48.420 [main] INFO cn.oick.api.WxhhApplicationTests - 规则校验:上传模板与系统模板不匹配,请使用平台模板上传数据
未经允许不得转载:作者:名晨,
转载或复制请以 超链接形式 并注明出处 零七生活网 - 名晨'blog - 网络资源教程的分享平台。
原文地址:《easyexcel验证表头是否一致,并做异常处理停止向下继续解析》 发布于2020-12-25
评论 抢沙发