安徽专业网站制作公司,wordpress管理密码修改,做网站 空间,电话销售做网站推销EasyExcel中非常重要的AnalysisEventListener类使用#xff0c;继承该类并重写invoke、doAfterAllAnalysed#xff0c;必要时重写onException方法。
Listener 中方法的执行顺序 首先先执行 invokeHeadMap() 读取表头#xff0c;每一行都读完后#xff0c;执行 invoke()方法…EasyExcel中非常重要的AnalysisEventListener类使用继承该类并重写invoke、doAfterAllAnalysed必要时重写onException方法。
Listener 中方法的执行顺序 首先先执行 invokeHeadMap() 读取表头每一行都读完后执行 invoke()方法 读取数据,最后是doAfterAllAnalysed() 方法。 invoke通过 AnalysisContext 对象还可以获取当前 sheet当前行等数据。对当前获取的行的数据配合实体类字段的校验注解来实现数据校验并返回错误提示也可校验表头相应index下表对应excel中的中文名称是否正确。 doAfterAllAnalysed方法可定义在资源解析完成之后的操作 onException方法定义在数据解析出错失败时的操作一般为当前数据行的数据无法与ExcelProperty所注解的字段类型想符合从而导致无法继续解析流沉的错误。 一、ExcelListener监听器
public class ExcelListener extends AnalysisEventListener {/*** 自定义用于暂时存储data。* 可以通过实例获取该值*/private ListObject datas new ArrayList();private ListImportErrVo errDatas new ArrayList();private MapInteger, String headMap new HashMap();private Boolean head false;/*** 通过 AnalysisContext 对象还可以获取当前 sheet当前行等数据*/Overridepublic void invoke(Object object, AnalysisContext context) {checkHead(object);//属性验证int rowNum context.readRowHolder().getRowIndex();String errMsg;try {errMsg EasyExcelValiHelper.validateEntity(object);} catch (Exception e) {errMsg 解析数据出错;e.printStackTrace();}if (!StringUtils.isEmpty(errMsg)) {ImportErrVo errVo new ImportErrVo();errVo.setLine(rowNum 1 );errVo.setErrMsg(errMsg);errDatas.add(errVo);} else {//数据存储到list供批量处理或后续自己业务逻辑处理。datas.add(object);}//根据业务自行 dosomethingdoSomething();}/*** 根据业务自行实现该方法*/private void doSomething() {}/*** 校验表头判断是否传错** param object*/public void checkHead(Object object) {Field[] fields object.getClass().getDeclaredFields();if (head) {return;}for (Field f : fields) {if (f.getName().equals(serialVersionUID)) {continue;}ExcelProperty excelProperty f.getAnnotation(ExcelProperty.class);int index 0;if (excelProperty!null){index excelProperty.index();}if ((excelProperty ! null headMap.get(index)null) || (excelProperty ! null !headMap.get(index).equals(excelProperty.value()[excelProperty.value().length-1]))) {ImportErrVo errVo new ImportErrVo();errVo.setLine(1);errVo.setErrMsg(表头数据不对应导入数据失败);errDatas.add(errVo);this.head true;break;}}}/*** 解析结束的操作** param object*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {if (head) {errDatas.removeIf(a - !a.getLine().equals(1));datas.clear();}/*datas.clear();解析结束销毁不用的资源*/}/*** 解析出错时的操作** param object*/Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {int rowIndex context.readRowHolder().getRowIndex();int headLineNumber context.readSheetHolder().getHeadRowNumber();if (rowIndex headLineNumber) {Object object null;try {object context.currentReadHolder().excelReadHeadProperty().getHeadClazz().newInstance();} catch (Exception e) {ImportErrVo errVo new ImportErrVo();errVo.setLine(1);errVo.setErrMsg(表头数据不对应导入数据失败);errDatas.add(errVo);this.head true;}if (object ! null) {checkHead(object);}}if (head) {return;}String error;if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException (ExcelDataConvertException) exception;CellData cellData excelDataConvertException.getCellData();error cellData.toString();} else {String eMassage exception.getCause().toString();if (eMassage.contains(\)) {error eMassage.substring(eMassage.lastIndexOf(:) 2);} else {error eMassage.substring(eMassage.lastIndexOf() 1);}}LinkedHashMapInteger, CellData content (LinkedHashMapInteger, CellData) context.readRowHolder().getCurrentRowAnalysisResult();error error.replaceAll(\, );ImportErrVo errVo new ImportErrVo();for (Map.EntryInteger, CellData map : content.entrySet()) {CellDataTypeEnum type map.getValue().getType();String c null;if (type.equals(CellDataTypeEnum.NUMBER)) {c map.getValue().getNumberValue().toString();} else if (type.equals(CellDataTypeEnum.STRING)) {c map.getValue().getStringValue();} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {c map.getValue().getBooleanValue().toString();}if (error.equals(c)) {errVo.setLine(rowIndex 1 );errVo.setErrMsg(headMap.get(map.getKey()) 数据格式错误);break;}}boolean flag true;for (ImportErrVo importErrVo : errDatas) {if (importErrVo.getLine().equals(errVo.getLine())) {importErrVo.setErrMsg(importErrVo.getErrMsg() errVo.getErrMsg() ;);flag false;break;}}if (flag) {errDatas.add(errVo);}}Overridepublic void invokeHeadMap(Map headMap, AnalysisContext context) {this.headMap headMap;}public ListObject getDatas() {return datas;}public void setDatas(ListObject datas) {this.datas datas;}public ListImportErrVo getErrDatas() {return errDatas;}public void setErrDatas(ListImportErrVo errDatas) {this.errDatas errDatas;}}
二、初步检验导入参数
在实体类中可用 javax.validation.constraints下的注解对字段进行校验 EasyExcelValiHelper类初步校验字段
public class EasyExcelValiHelper {private static Validator validator Validation.buildDefaultValidatorFactory().getValidator();public static T String validateEntity(T obj) throws NoSuchFieldException, SecurityException {StringBuilder result new StringBuilder();SetConstraintViolationT set validator.validate(obj, Default.class);if (set ! null set.size() ! 0) {for (ConstraintViolationT cv : set) {Field declaredField obj.getClass().getDeclaredField(cv.getPropertyPath().toString());ExcelProperty annotation declaredField.getAnnotation(ExcelProperty.class);result.append(annotation.value()[0]cv.getMessage()).append(;);}}return result.toString();}
}
三、ExcelUti类
public class ExcelUtil {/*** 读取某个 sheet 的 Excel** param excel 文件* param head* param sheetNo sheet 的序号 从1开始* return Excel 数据 list*/public static ListObject readExcel(MultipartFile excel, Class head, int sheetNo) {return readExcel(excel, head, sheetNo, 1);}/*** 读取某个 sheet 的 Excel** param excel 文件* param head* param sheetNo sheet 的序号 从1开始* return Excel 数据 list*/public static ListObject readExcel(MultipartFile excel, Class head, int sheetNo, ExcelListener excelListener) {ExcelReader reader getReader(excel, head,excelListener);if (reader null) {return null;}ReadSheet readSheet EasyExcel.readSheet(sheetNo).build();reader.read(readSheet);// 这里千万别忘记关闭读的时候会创建临时文件到时磁盘会崩的reader.finish();return excelListener.getDatas();}/*** 读取某个 sheet 的 Excel** param excel 文件* param head* param sheetNo sheet 的序号 从1开始* param headLineNum 表头行数默认为1* return Excel 数据 list*/public static ListObject readExcel(MultipartFile excel,Class head, int sheetNo, int headLineNum) {ExcelListener excelListener new ExcelListener();ExcelReader reader getReader(excel, head,excelListener);if (reader null) {return null;}ReadSheet readSheet EasyExcel.readSheet(sheetNo).headRowNumber(headLineNum).build();reader.read(readSheet);// 这里千万别忘记关闭读的时候会创建临时文件到时磁盘会崩的reader.finish();return excelListener.getDatas();}/*** 读取某个 sheet 的 Excel** param excel 文件* param head* param sheetNo sheet 的序号 从1开始* param headLineNum 表头行数默认为1* return Excel 数据 list*/public static ListObject readExcel(MultipartFile excel,Class head, int sheetNo, int headLineNum,ExcelListener excelListener) {ExcelReader reader getReader(excel, head,excelListener);if (reader null) {return null;}ReadSheet readSheet EasyExcel.readSheet(sheetNo).headRowNumber(headLineNum).build();reader.read(readSheet);// 这里千万别忘记关闭读的时候会创建临时文件到时磁盘会崩的reader.finish();return excelListener.getDatas();}/*** 返回 ExcelReader** param excel 需要解析的 Excel 文件* param excelListener new ExcelListener()*/private static ExcelReader getReader(MultipartFile excel, Class head, AnalysisEventListener excelListener){String filename excel.getOriginalFilename();if (filename null || (!filename.toLowerCase().endsWith(.xls) !filename.toLowerCase().endsWith(.xlsx))) {return null;}InputStream inputStream;try {inputStream new BufferedInputStream(excel.getInputStream());ExcelReader excelReader EasyExcel.read(inputStream,head,excelListener).build();return excelReader;} catch (IOException e) {e.printStackTrace();}return null;}/*** 判断你一个类是否存在某个属性字段** param fieldName 字段* param obj 类对象* return true:存在false:不存在, null:参数不合法*/public static Boolean isExistField(String fieldName, Object obj) {if (obj null || StringUtils.isEmpty(fieldName)) {return null;}//获取这个类的所有属性Field[] fields obj.getClass().getDeclaredFields();boolean flag false;//循环遍历所有的fieldsfor (int i 0; i fields.length; i) {if (fields[i].getName().equals(fieldName)) {flag true;break;}}return flag;}private static ExcelReader getReaderNoHead(MultipartFile excel, AnalysisEventListener excelListener){String filename excel.getOriginalFilename();if (filename null || (!filename.toLowerCase().endsWith(.xls) !filename.toLowerCase().endsWith(.xlsx))) {return null;}InputStream inputStream;try {inputStream new BufferedInputStream(excel.getInputStream());ExcelReader excelReader EasyExcel.read(inputStream,excelListener).build();return excelReader;} catch (IOException e) {e.printStackTrace();}return null;}/*** 新增或修改合并策略map* param strategyMap* param key* param index*/private static void fillStrategyMap(MapString, ListRowRangeVo strategyMap, String key, int index) {ListRowRangeVo rowRangeDtoList strategyMap.get(key) null ? new ArrayList() : strategyMap.get(key);boolean flag false;for (RowRangeVo dto : rowRangeDtoList) {//分段list中是否有end索引是上一行索引的如果有则索引1if (dto.getEnd() index) {dto.setEnd(index 1);flag true;}}//如果没有则新增分段if (!flag) {rowRangeDtoList.add(new RowRangeVo(index, index 1));}strategyMap.put(key, rowRangeDtoList);}
}
五、controller中的使用导入时解析成列表数据并获得错误提示 PostMapping(/import)ApiOperation(导入XX信息)public ResponseResult? importProject(RequestParam(file) MultipartFile file) throws Exception{ListProjectExpView list new ArrayList(1);ListImportErrVo errMsgList new ArrayList(1);ExcelListener excelListener new ExcelListener();Object Object1 ExcelUtil.readExcel(file,ProjectExpView.class,0,excelListener);errMsgList excelListener.getErrDatas();list (ListProjectExpView) Object1;projectService.importProject(list);if(null!errMsgListerrMsgList.size()0){return ResponseResult.error(HttpStatus.NOT_IMPLEMENTED,部分导入成功:成功(nulllist?0:list.size())条,失败errMsgList.size()条,errMsgList);}return ResponseResult.importSuccess();}
参考https://blog.csdn.net/her_he/article/details/125297884?spm1001.2014.3001.5502 该作者主页还有其他easyExcel相关感兴趣可以看看
