基于EasyExcel实现导入数据校验

Excel导入工具类

/**
 * Excel导入工具类，支持校验数据非空、重复，及自定义前置、后置校验
 *
 * @author wangmeng
 * @version 1.0.0
 * @Date 2021/10/10
 **/
@Slf4j
public class ExcelImportUtils {
    /**
     * 下载错误信息的url
     */
    public final static ThreadLocal<String> downloadUrl = new ThreadLocal<>();

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        return filePath != null && (isExcel2003(filePath) || isExcel2007(filePath));
    }


    public static <T> List<T> importDataAndCheck(MultipartFile file, Class<T> clazz) {
        return importDataAndCheck(file, clazz, null, null);
    }

    /**
     * 解析excel返回list集合，并校验数据
     *
     * @param file
     * @param clazz
     * @param preHandlerList
     * @param afterHandlerList
     * @param <T>
     * @return
     */
    public static <T> List<T> importDataAndCheck(MultipartFile file, Class<T> clazz, List<ExcelDataListener.PreHandler<T>> preHandlerList, List<ExcelDataListener.AfterHandler<T>> afterHandlerList) {
        if (file == null) {
            throw new ExcelCheckException(ApiResponseCodeEnum.DATA_IS_NULL.getMsg());
        }
        String fileName = file.getOriginalFilename();
        if (!ExcelImportUtils.validateExcel(fileName)) {
            throw new ExcelCheckException("文件必须是excel格式！");
        }
        if (StringUtils.isBlank(fileName) || file.getSize() == 0) {
            throw new ExcelCheckException("文件内容不能为空");
        }
        ExcelDataListener<T> listener = new ExcelDataListener<>(preHandlerList, afterHandlerList);
        List<T> dataList = null;
        try (InputStream inputStream = file.getInputStream()) {
            dataList = EasyExcel.read(inputStream, clazz, listener).sheet().doReadSync();
        } catch (ExcelAnalysisException e) {
            ExcelDataConvertException exception = (ExcelDataConvertException) e.getCause();
            List<ExcelErrorMessage> errorList = listener.getErrorList();
            String headerName = exception.getExcelContentProperty().getField().getAnnotation(ExcelProperty.class).value()[0];
            errorList.add(new ExcelErrorMessage().setRowNum(exception.getRowIndex() + 1)
                    .setColHeaderName(headerName)
                    .setMessage("'" + headerName + "'类型转换失败,请输入正确格式"));
        } catch (IOException ioe) {
            log.info("导入失败,异常,", ioe);
            throw new ExcelCheckException("导入失败!");
        }

        //失败抛异常，返回下载链接
        if (CollectionUtils.isNotEmpty(listener.getErrorList())) {
            log.info("errorlist 错误集合:{}", JSON.toJSONString(listener.getErrorList()));
            OssUploadSignService ossService = SpringUtils.getBean(OssUploadSignService.class);
            //生成excel

            String filePath = FileUtil.getPath() + DateUtils.formatToString(new Date(), "yyyyMMddHHmmss");
            File dir = new File(filePath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            File file1 = new File(filePath + File.separator + "错误导出信息.xlsx");
            try {
                EasyExcel.write(new FileOutputStream(file1), ExcelErrorMessage.class)
                        .sheet("错误内容").doWrite(listener.getErrorList());
            } catch (FileNotFoundException e) {
                log.info("写入execl异常");
                throw new ExcelCheckException("写入execl异常");
            }
            //上传oss
            log.info("本地暂存excel文件路径:{}", filePath + File.separator + "错误导出信息.xlsx");
            String url = ossService.uploadFile(new File(filePath + File.separator + "错误导出信息.xlsx"));
            log.info("error Excel url:{}", url);
            downloadUrl.set(url);
            throw new ExcelCheckException("导入校验失败");
        }
        if (CollectionUtils.isEmpty(dataList)) {
            throw new ExcelCheckException("解析数据为空!");
        }
        return dataList;
    }


    /**
     * 导出错误信息
     *
     * @param errorList 错误集合
     */
    public static void exportError(List<ExcelErrorMessage> errorList) {
        ServletRequestAttributes servletRequestAttributes =
                (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("错误信息导出", "UTF-8")
                    .replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExcelErrorMessage.class)
                    .sheet("错误内容").doWrite(errorList);
        } catch (IOException e) {
            log.info("下载excel错误信息异常:{}", e.getMessage());
        }
    }

}

/**
 * excel导入共通监听类
 *
 * @author wangmeng
 * @version 1.0.0
 * @Date 2021/9/30
 **/
@EqualsAndHashCode(callSuper = true)
@Slf4j
@Data
public class ExcelDataListener<T> extends AnalysisEventListener<T> {


    @ApiModelProperty(value = "check注解对象")
    private List<Object[]> filedList;
    @ApiModelProperty(value = "excel数据")
    private List<T> list = new ArrayList<>();
    @ApiModelProperty(value = "错误信息集合")
    private List<ExcelErrorMessage> errorList = new ArrayList<>();
    //前置回调方法
    private List<PreHandler<T>> preList;
    //后置回调方法
    private List<AfterHandler<T>> afterList;

    private Boolean isEmpty = false;


    public ExcelDataListener(List<PreHandler<T>> preList, List<AfterHandler<T>> afterList) {
        super();
        this.preList = preList;
        this.afterList = afterList;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        if (CollectionUtils.isEmpty(list)) {
            Class<?> clazz = data.getClass();
            //含check注解的字段
            filedList = Arrays.stream(clazz.getDeclaredFields())
                    .filter(o -> null != o.getAnnotation(ExcelCheck.class))
                    .map(o -> new Object[]{o, o.getAnnotation(ExcelCheck.class), o.getAnnotation(ExcelProperty.class)}).collect(Collectors.toList());
        }
        log.info("data:{}", JSON.toJSONString(data));
        list.add(data);
        if (CollectionUtils.isNotEmpty(filedList)) {
            checkEmpty(data);
            //存在空值则不进行其他校验
            if (isEmpty) {
                return;
            }
        }
        //前置回调
        if (CollectionUtils.isNotEmpty(preList)) {
            preList.forEach(check -> {
                CheckResult result = check.invoke(data);
                if (!result.getCheckOk()) {
                    errorList.add(new ExcelErrorMessage()
                            .setRowNum(list.size() + 1)
                            .setMessage(result.getMessage()));
                }
            });
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (isEmpty) {
            return;
        }
        //后置回调
        if (CollectionUtils.isNotEmpty(afterList)) {
            afterList.forEach(handler -> handler.invoke(list, errorList));
            if (CollectionUtils.isNotEmpty(filedList)) {
                checkRepeat();
            }
        }
        errorList.sort(Comparator.comparing(ExcelErrorMessage::getRowNum));
    }

    /**
     * 检验非空
     *
     * @param data
     */
    public void checkEmpty(T data) {
        for (Object[] os : filedList) {
            Field filed = (Field) os[0];
            filed.setAccessible(true);
            ExcelCheck excelCheck = (ExcelCheck) os[1];
            ExcelProperty excelProperty = (ExcelProperty) os[2];
            try {
                //校验非空
                if (!excelCheck.canEmpty()) {
                    if (filed.get(data) == null ||
                            (filed.getType() == String.class && StringUtils.isBlank((String) filed.get(data)))) {
                        errorList.add(new ExcelErrorMessage()
                                .setRowNum(list.size() + 1)
                                .setColHeaderName(excelProperty.value()[0])
                                .setMessage(excelProperty.value()[0] + "字段不能为空!"));
                        isEmpty = true;
                    }
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 检验重复
     */
    public void checkRepeat() {
        List<Object[]> repeatAnnotation = filedList.stream().filter(o -> {
            ExcelCheck excelCheck = (ExcelCheck) o[1];
            return !excelCheck.canRepeat();
        }).collect(Collectors.toList());
        for (Object[] objects : repeatAnnotation) {
            ExcelProperty property = (ExcelProperty) objects[2];
            //使用iterate方式构建流以获取行号
            Stream.iterate(0, i -> i + 1).limit(list.size()).collect(Collectors.groupingBy(i -> {
                Field field = (Field) objects[0];
                String result = "";
                try {
                    field.setAccessible(true);
                    result = JSON.toJSONString(field.get(list.get(i)));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                return result;
            }, LinkedHashMap::new, Collectors.mapping(i -> i + 2, Collectors.toList())))
                    .forEach((k, v) -> {
                        if (v.size() > 1) {
                            for (int i = 0; i < v.size(); i++) {
                                if (i == 0) {
                                    continue;
                                }
                                errorList.add(new ExcelErrorMessage()
                                        .setRowNum(v.get(i))
                                        .setColHeaderName(property.value()[0])
                                        .setMessage(property.value()[0] + "字段重复!"));
                            }
                        }
                    });
        }
    }


    /**
     * check结果
     */
    @Setter
    @Getter
    public static class CheckResult {
        private String message;
        private Boolean checkOk = true;
    }

    /**
     * 前置处理
     *
     * @param <T>
     */
    @FunctionalInterface
    public interface PreHandler<T> {
        CheckResult invoke(T t);
    }


    /**
     * 后置处理
     *
     * @param <T>
     */
    @FunctionalInterface
    public interface AfterHandler<T> {
        void invoke(List<T> t, List<ExcelErrorMessage> errorList);
    }


}