easy-excel导入

使用easy excel导入excel数据到系统

1、自定义校验注解

/**
 * <p>Excel导入Decimal类型校验</p>
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDecimalValid {
    String min();
    String max();
    String message() default "小数类型数字填写超出范围";
}
/**
* <p>Excel导入正则表达式校验</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelPatternValid {

    String regexp() default "";

    String name() default "";

    String message() default "不符合规则";
}

/**
 * <p>Excel导入Int类型校验</p>
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIntValid {
    int min();
    int max();
    String message() default "整数数字填写超出范围";
}

/**
 * <p>Excel导入字符串长度校验</p>
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
    int length() default 0;
    String message() default "文字填写超出长度要求";
}

/**
 * <p>Excel导入必填校验注解</p>
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
    String message() default "导入有未填入的字段";

    /**
     * 表头字段
     */
    String name() default "";
}

2、校验类

/**
 * <p>Excel导入字段校验</p
 */
public class ExcelImportValid {

    /**
     * Excel导入字段校验
     *
     * @param object 校验的JavaBean 其属性须有自定义注解
     */
    @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、自定义异常处理

/**
 * 导入excel自定义异常类
 */
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 ImportExcelDataListener<T> extends AnalysisEventListener<T> {
    //service的名称
    private final String serviceName;

    // 校验通过数据处理方法
    private final String accessMethodName;

    // 异常数据处理
    private final String errorMethodName;

    private final String version;

    private Map<String, Object> paramMap = new HashMap<>();

    public ImportExcelDataListener(String serviceName, Map<String, Object> 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<T> successList = new LinkedList<>();

    //异常数据  （key：原因，value：data项）
    private final Map<String, List<T>> errorMap = new HashMap<>();

    /**
     * <p>所有数据解析完成后调用此方法</p>
     */
    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) throws ExcelValidException {
        //保存数据，确保最后的遗留数据也能保存到数据库或者进行其他操作
        this.execute();
        if (errorMap.size() > 0) {
            this.executeError();
        }
    }

    /**
     * <p>数据转换异常时的处理，比如required是Integer，provided是"sss"的字符串</p>
     */
    @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<T> 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<T> {
    /**
     * 异常数据处理方法 <errorMsg,data>
     * @param errorMap
     */
    void saveErrorForExcel(Map<String, List<T>> errorMap,String version) throws ExcelValidException;

    /**
     * 正常数据处理
     * @param entityList
     * @param param 参数
     */
    void savaSuccessForExcel(List<T> entityList,String version, Map<String,Object> param);
}

6、异常数据实体类（把校验失败的数据存入数据库，方便做导出，根据自己的具体业务自定义字段）

public class ImportErrorLog {
    @TableId(type = IdType.ASSIGN_UUID)
    private String id;

    /**
     * 服务名称
     */
    private String service;

    /**
     * json数据
     */
    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<LocalDate> {

	@Override
	public Class<LocalDate> 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<Demo> {
	/**
	* 	异常数据处理
	*/
	@Override
    public void saveErrorForExcel(Map<String, List<Demo>> errorMap, String version) throws ExcelValidException {
        LinkedList<Demo> errorList = new LinkedList<>();
		/**
		* errorMap 的key是错误信息，value是excel的一行数据
		*/
        ImportErrorLog errorLog = importErrorLogMapper.selectOne(new QueryWrapper<ImportErrorLog>()
                .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<Demo> 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<Demo> entityList, String version, Map<String,Object> param) {
        if (CollectionUtils.isEmpty(entityList)) {
            return;
        }
        // 业务逻辑校验
        Map<String,List<Demo>> errorMap = new HashMap<>();
        Iterator<Demo> iterator = entityList.iterator();
        while (iterator.hasNext()){
            Demo next = iterator.next();
            if (projectTempByCodeMap.get(next.getProjectCode()) == null){
                iterator.remove();
                List<Demo> 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、如果想导出某一批次的异常数据，直接查询数据库，做导出即可