当前位置：首页 > news >正文

SpringBoot集成阿里easyexcel（二）Excel监听以及常用工具类

news 2024/11/14 9:52:28

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 List<Object> datas = new ArrayList<>();private List<ImportErrVo> errDatas = new ArrayList<>();private Map<Integer, 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);}}LinkedHashMap<Integer, CellData> content = (LinkedHashMap<Integer, CellData>) context.readRowHolder().getCurrentRowAnalysisResult();error = error.replaceAll("\"", "");ImportErrVo errVo = new ImportErrVo();for (Map.Entry<Integer, 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 List<Object> getDatas() {return datas;}public void setDatas(List<Object> datas) {this.datas = datas;}public List<ImportErrVo> getErrDatas() {return errDatas;}public void setErrDatas(List<ImportErrVo> 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();Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);if (set != null && set.size() != 0) {for (ConstraintViolation<T> 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 List<Object> 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 List<Object> 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 List<Object> 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 List<Object> 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(Map<String, List<RowRangeVo>> strategyMap, String key, int index) {List<RowRangeVo> 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{List<ProjectExpView> list = new ArrayList<>(1);List<ImportErrVo> errMsgList = new ArrayList<>(1);ExcelListener excelListener = new ExcelListener();Object Object1 = ExcelUtil.readExcel(file,ProjectExpView.class,0,excelListener);errMsgList = excelListener.getErrDatas();list = (List<ProjectExpView>) Object1;projectService.importProject(list);if(null!=errMsgList&&errMsgList.size()>0){return ResponseResult.error(HttpStatus.NOT_IMPLEMENTED,"部分导入成功:成功"+(null==list?0:list.size())+"条,失败"+errMsgList.size()+"条",errMsgList);}return ResponseResult.importSuccess();}