1、自定义校验注解
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDecimalValid {
String min();
String max();
String message() default "小数类型数字填写超出范围";
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelPatternValid {
String regexp() default "";
String name() default "";
String message() default "不符合规则";
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIntValid {
int min();
int max();
String message() default "整数数字填写超出范围";
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
int length() default 0;
String message() default "文字填写超出长度要求";
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
String message() default "导入有未填入的字段";
String name() default "";
}
2、校验类
public class ExcelImportValid {
@SneakyThrows
public static void valid(Object object) throws ExcelValidException {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
//设置可访问
field.setAccessible(true);
//属性的值
Object fieldValue = null;
try {
fieldValue = field.get(object);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
//是否包含必填校验注解
boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
if (isExcelValid && Objects.isNull(fieldValue)) {
String name = field.getAnnotation(ExcelValid.class).name();
throw new ExcelValidException("【" + name + "】列有未填值项");
}
//是否包含字符串长度校验注解
boolean isExcelStrValid = field.isAnnotationPresent(ExcelStrValid.class);
if (isExcelStrValid) {
String cellStr = fieldValue.toString();
int length = field.getAnnotation(ExcelStrValid.class).length();
if (StringUtils.isNotBlank(cellStr) && cellStr.length() > length) {
throw new ExcelValidException(field.getAnnotation(ExcelStrValid.class).message());
}
}
//是否包含int类型校验注解
boolean isExcelIntValid = field.isAnnotationPresent(ExcelIntValid.class);
if (isExcelIntValid) {
if (fieldValue instanceof Integer) {
int cellInt = Integer.parseInt(fieldValue.toString());
int min = field.getAnnotation(ExcelIntValid.class).min();
int max = field.getAnnotation(ExcelIntValid.class).max();
if (cellInt < min || cellInt > max) {
throw new ExcelValidException(field.getAnnotation(ExcelIntValid.class).message());
}
}
}
//是否包含decimal类型注解
boolean isExcelDecimalValid = field.isAnnotationPresent(ExcelDecimalValid.class);
if (isExcelDecimalValid) {
if (isBigDecimal(fieldValue.toString())) {
BigDecimal cellDecimal = new BigDecimal(fieldValue.toString());
BigDecimal min = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).min());
BigDecimal max = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).max());
if (cellDecimal.compareTo(min) < 0 || cellDecimal.compareTo(max) > 0) {
throw new ExcelValidException(field.getAnnotation(ExcelDecimalValid.class).message());
}
} else {
throw new ExcelValidException("不是小数数字类型");
}
}
// 是否包含正则校验
boolean isExcelPatternValid = field.isAnnotationPresent(ExcelPatternValid.class);
if (isExcelPatternValid) {
String cellStr = fieldValue.toString();
String pattern = field.getAnnotation(ExcelPatternValid.class).regexp();
String name = field.getAnnotation(ExcelPatternValid.class).name();
if (StringUtils.isBlank(name)) {
throw new ExcelValidException("【" + name + "】列有未填值项");
}
if (StringUtils.isNotBlank(cellStr) && !Pattern.matches(pattern, cellStr)) {
throw new ExcelValidException("【" + name + "】不符合规则");
}
}
}
}
private static boolean isBigDecimal(String decimal) {
try {
BigDecimal bd = new BigDecimal(decimal);
return true;
} catch (NumberFormatException e) {
return false;
}
}
}
3、自定义异常处理
public class ExcelValidException extends RuntimeException {
private static final long serialVersionUID = 1L;
private int code;
public ExcelValidException(String msg) {
this(100, msg, null);
}
public ExcelValidException(int code, String msg) {
this(code, msg, null);
}
public ExcelValidException(int code, String msg, Exception nestedEx) {
super(msg, nestedEx);
this.code = code;
}
}
4、监听器
@Slf4j public class ImportExcelDataListenerextends AnalysisEventListener { //service的名称 private final String serviceName; // 校验通过数据处理方法 private final String accessMethodName; // 异常数据处理 private final String errorMethodName; private final String version; private Map paramMap = new HashMap<>(); public ImportExcelDataListener(String serviceName, Map param) { this.serviceName = serviceName; this.accessMethodName = "savaSuccessForExcel"; this.errorMethodName = "saveErrorForExcel"; version = UUID.randomUUID().toString(); paramMap = param; } public ImportExcelDataListener(String serviceName) { this.serviceName = serviceName; this.accessMethodName = "savaSuccessForExcel"; this.errorMethodName = "saveErrorForExcel"; version = UUID.randomUUID().toString(); paramMap = new HashMap<>(); } // 校验通过数据 private final List successList = new LinkedList<>(); //异常数据 (key:原因,value:data项) private final Map > errorMap = new HashMap<>(); @SneakyThrows @Override public void doAfterAllAnalysed(AnalysisContext context) throws ExcelValidException { //保存数据,确保最后的遗留数据也能保存到数据库或者进行其他操作 this.execute(); if (errorMap.size() > 0) { this.executeError(); } } @Override public void onException(Exception exception, AnalysisContext context) throws Exception { exception.printStackTrace(); if (exception instanceof ExcelDataConvertException) { Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1; Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1; String message = String.format("第%s行,第%s列数据格式有误,请核实", rowIndex, columnIndex); throw new RuntimeException(message); } else { super.onException(exception, context); } } @Override public void invoke(T data, AnalysisContext analysisContext) { //通用方法数据校验 try { ExcelImportValid.valid(data); } catch (ExcelValidException e) { List ts = errorMap.get(e.getMessage()); if (CollectionUtils.isEmpty(ts)) { ts = new LinkedList<>(); } ts.add(data); errorMap.put(e.getMessage(), ts); return; } //将解析完的数据加入到list中 successList.add(data); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (successList.size() >= 600) { try { this.execute(); } catch (Exception e) { e.printStackTrace(); } // 存储完成清理 list successList.clear(); } } //执行数据保存的操作 public void execute() throws Exception { try { Object beanService = SpringUtil.getBean(serviceName + "Impl"); Class> beanClass = beanService.getClass(); Method method = beanClass.getMethod(accessMethodName, List.class, String.class, Map.class); method.invoke(beanService, successList, version, paramMap); } catch (NoSuchMethodException | IllegalAccessException e) { log.warn("import error:", e); throw new RuntimeException("导入执行保存失败"); } catch (InvocationTargetException e) { e.printStackTrace(); throw new ExcelValidException("有异常数据"); } } //执行错误数据处理的操作 public void executeError() { try { Object beanService = SpringUtil.getBean(serviceName + "Impl"); Class> beanClass = beanService.getClass(); Method method = beanClass.getMethod(errorMethodName, Map.class, String.class); method.invoke(beanService, errorMap, version); } catch (NoSuchMethodException | IllegalAccessException e) { log.warn("execute error:", e); throw new RuntimeException("处理错误数据失败"); } catch (InvocationTargetException e) { throw new ExcelValidException("有异常数据"); } } }
5、数据处理接口,使用范型
public interface EasyExcelImportService{ void saveErrorForExcel(Map > errorMap,String version) throws ExcelValidException; void savaSuccessForExcel(List entityList,String version, Map param); }
6、异常数据实体类(把校验失败的数据存入数据库,方便做导出,根据自己的具体业务自定义字段)
public class ImportErrorLog {
@TableId(type = IdType.ASSIGN_UUID)
private String id;
private String service;
private String data;
private String version;
@TableField(fill = FieldFill.INSERT)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
}
7、日期类型需要自定义转换器
public class LocalDateConverter implements Converter{ @Override public Class supportJavaTypeKey() { return LocalDate.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public LocalDate convertToJavaData(ReadCellData> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { if (cellData.getType().equals(CellDataTypeEnum.NUMBER)) { LocalDate localDate = LocalDate.of(1900, 1, 1); //excel 有些奇怪的bug, 导致日期数差2 localDate = localDate.plusDays(cellData.getNumberValue().longValue() - 2); return localDate; }else { return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd")); } } @Override public WriteCellData> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))); } }
8、业务处理类实现EasyExcelImportService接口即可
public class DemoServiceImpl implements EasyExcelImportService{ @Override public void saveErrorForExcel(Map > errorMap, String version) throws ExcelValidException { LinkedList errorList = new LinkedList<>(); ImportErrorLog errorLog = importErrorLogMapper.selectOne(new QueryWrapper () .eq("version", version) .last("limit 1") ); // 因为注解只能校验数据的合法性,并不能校验是否符合业务逻辑,所以这里加了版本号,同一批次的错误信息保存一条记录 if (errorLog == null){ errorLog = ImportErrorLog.builder() .service("service") .version(version) .data(JSON.toJSONString(errorList)) .build(); // 入库 importErrorLogMapper.insert(errorLog); }else { String data = errorLog.getData(); List importDemoList = JSONObject.parseArray(data, Demo.class); errorList.addAll(importDemoList); errorLog.setData(JSON.toJSONString(errorList)); // 入库 importErrorLogMapper.updateById(errorLog); } // 为了提醒前端有异常数据 throw new ExcelValidException("have error data"); } // 正常信息处理 @Override public void savaSuccessForExcel(List entityList, String version, Map param) { if (CollectionUtils.isEmpty(entityList)) { return; } // 业务逻辑校验 Map > errorMap = new HashMap<>(); Iterator iterator = entityList.iterator(); while (iterator.hasNext()){ Demo next = iterator.next(); if (projectTempByCodeMap.get(next.getProjectCode()) == null){ iterator.remove(); List importDemoList = errorMap.get("项目编码错误"); if (CollectionUtils.isEmpty(importDemoList)){ importDemoList = new LinkedList<>(); } importPmDailyList.add(next); errorMap.put("项目编码错误",importPmDailyList); } } if (CollectionUtils.isEmpty(entityList)){ // 存储错误信息 if (errorMap.size()>0){ saveErrorForExcel(errorMap, version); } return; } // 业务逻辑处理。。。。。。 }
9、如果想导出某一批次的异常数据,直接查询数据库,做导出即可



