基于EasyExcel实现导入数据校验,生成错误信息Excel

PS:本文中的设计功能已向FastExcel项目的github提交了PR，欢迎大家来review，推动这项功能落地
PR：https://github.com/fast-excel/fastexcel/pull/282

功能设计

由于项目中涉及到大量的文件导入功能，故考虑设计一个excel导入的通用框架，解决以下问题

1. 导入的数据不可信任，可能出现空值校验的许多判断，如果将这些判断加入业务代码可能会造成大量代码的堆积，如下情况：

if(name==null){
	throw new RuntimeException("名称不能为空");
}
if(age==null){
	throw new RuntimeException("年龄不能为空");
}
if(sex==null){
	throw new RuntimeException("性别不能为空");
}
if(order.size()>10){
	throw new RuntimeException("订单号长度不能大于10");
}

2. EasyExcel帮我处理导入文件时，只是简单的根据列名把内容set到字段上，如果字段类型不符是会直接报错的！而我们需要将数据的错误内容提交给用户，所以如下的报错是不可取的
   

3. 针对文件中的问题，需要清晰地呈现给用户，每一行具体出现了哪种类型的错误，例如如下：
   

4. 导出错误信息Excel这个功能需要足够灵活，需要支持手动将业务校验产生的错误信息添加进去。

5. 基于EasyExcel封装，由于项目中本身使用的EasyExcel，考虑到不改动项目的技术组成，还是基于EasyExcel开发。

设计思路

EasyExcel做的工作其实很简单，就是把文件中的内容映射到我们实体类的字段上，我们要做的就是在映射前和映射后做校验

使用案例

 public File test(MultipartFile file) {
        ExcelImportProcessor importProcessor = new ExcelImportProcessor();
        String errorFilePath = "D:\\新建文件夹\\错误信息.xlsx";
        File errorFile = new File(errorFilePath);

        List<CustomsClearImportHeaderDto> list = null;
        try {
            list = importProcessor.importData(file, CustomsClearImportHeaderDto.class);
        } catch (ExcelCheckException e) {
            // 通用校验产生错误
            if (importProcessor.generateErrorSheet(errorFilePath)) {
                return errorFile;
            }
        }

        // 业务校验
        for (int index = 0; index < list.size(); index++) {
            CustomsClearImportHeaderDto dto = list.get(index);
            importProcessor.addError(index, "订单已存在");
        }
        if (importProcessor.generateErrorSheet(errorFilePath)) {
            return errorFile;
        }
        // 处理业务逻辑
        // …………………………………………
        return null;
    }

代码解析

我先把完整代码贴上，下面再详细分析

注解类

/**
 * 导入校验注解
 *
 * @author wangmeng
 * @since 2024/5/25
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelCheck {

    /**
     * 是否可以为空,默认是
     */
    boolean canEmpty() default true;

    /**
     * 是否可以重复,默认是
     */
    boolean canRepeat() default true;

    /**
     * 长度校验,只对String生效
     */
    int length() default -1;
}

错误信息实体类

/**
 * excel导入错误信息
 *
 * @author wangmeng
 * @since 2024/5/25
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class ExcelErrorMessage {
    /**
     * 行号
     */
    private Integer rowNum;


    /**
     * 错误信息
     */
    private String message;

}

导入通用的listener

/**
 * 具有校验功能的导入listener
 *
 * @author wangmeng
 * @since 2024/5/25
 */
@Slf4j
public class CheckImportListener<T> implements ReadListener<T> {


    /**
     * excel数据
     */
    protected final List<T> list = new ArrayList<>();
    /**
     * 错误信息集合
     */
    @Getter
    private final List<ExcelErrorMessage> errorList = new ArrayList<>();
    /**
     * 字段元数据
     */
    private final Map<Field, ValidateAnnotation> fieldMap = new HashMap<>();


    private boolean existValidate = true;


    public CheckImportListener() {
    }


    /**
     * 初始化信息.
     *
     * @param headMap headMap
     * @param context context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        ExcelReadHeadProperty excelReadHeadProperty = context.currentReadHolder().excelReadHeadProperty();
        for (Head head : excelReadHeadProperty.getHeadMap().values()) {
            Field field = head.getField();
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            ExcelCheck excelCheck = field.getAnnotation(ExcelCheck.class);
            if (excelProperty != null && excelCheck != null && anyCheck(excelCheck)) {
                String headName = String.join("-", head.getHeadNameList());
                field.setAccessible(true);
                fieldMap.put(field, new ValidateAnnotation(excelProperty, excelCheck, headName));
            }
        }
        if (fieldMap.isEmpty()) {
            existValidate = false;
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("解析单元格失败,", exception);
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), JSON.toJSONString(excelDataConvertException.getCellData()));
        }
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        log.debug("data:{}", JSON.toJSONString(data));
        list.add(data);
        if (existValidate) {
            Integer rowIndex = context.readRowHolder().getRowIndex();
            try {
                // 校验是否空值
                checkEmpty(data, rowIndex);
                // 校验长度
                checkLength(data, rowIndex);
            } catch (Exception e) {
                log.error("校验excel信息失败,", e);
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        try {
            checkRepeat();
        } catch (Exception e) {
            log.error("校验重复信息失败,", e);
        }
        errorList.sort(Comparator.comparing(ExcelErrorMessage::getRowNum));
    }


    /**
     * 检验非空
     *
     * @param data     数据
     * @param rowIndex 行索引
     */
    public void checkEmpty(T data, Integer rowIndex) {
        fieldMap.forEach(((field, annotation) -> {
            ExcelCheck excelCheck = annotation.getExcelCheck();
            try {
                Object value = field.get(data);
                //校验非空
                if (!excelCheck.canEmpty()) {
                    if (value == null ||
                            (field.getType() == String.class && StringUtils.isEmpty((String) value))) {
                        addError(rowIndex + 1, annotation.getHeadName() + "字段不能为空!");
                    }
                }
            } catch (IllegalAccessException e) {
                log.error("校验excel信息失败,", e);
                throw new RuntimeException(e);
            }
        }));
    }


    /**
     * 校验长度
     *
     * @param data     数据
     * @param rowIndex 行索引
     */
    public void checkLength(T data, Integer rowIndex) {
        fieldMap.forEach(((field, annotation) -> {
            ExcelCheck excelCheck = annotation.getExcelCheck();
            try {
                if (excelCheck.length() > 0 && field.getType() == String.class) {
                    String value = (String) field.get(data);
                    if (value.length() > excelCheck.length()) {
                        addError(rowIndex+1, annotation.getHeadName() + "字段长度大于" + excelCheck.length() + "!");
                    }
                }
            } catch (IllegalAccessException e) {
                log.error("校验excel信息失败,", e);
                throw new RuntimeException(e);
            }
        }));
    }


    /**
     * 检验重复
     */
    public void checkRepeat() {
        Map<Field, ValidateAnnotation> repeatFieldMap = fieldMap.entrySet().stream().filter(entry -> !entry.getValue().getExcelCheck().canRepeat())
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        repeatFieldMap.forEach((field, annotation) -> {
            //使用iterate方式构建流以获取行号
            Stream.iterate(0, i -> i + 1).limit(list.size()).collect(Collectors.groupingBy(i -> {
                        try {
                            Object value = field.get(list.get(i));
                            return JSON.toJSONString(value);
                        } catch (IllegalAccessException e) {
                            throw new RuntimeException(e);
                        }
                    }, LinkedHashMap::new, Collectors.mapping(i -> i + 2, Collectors.toList())))
                    .forEach((value, indexList) -> {
                        if (indexList.size() > 1) {
                            for (int i = 0; i < indexList.size(); i++) {
                                if (i == 0) {
                                    continue;
                                }
                                addError(indexList.get(i), annotation.getHeadName() + "字段重复!");
                            }
                        }
                    });
        });
    }


    public void addError(Integer index, String errorMessage) {
        ExcelErrorMessage excelErrorMessage = new ExcelErrorMessage().setRowNum(index).setMessage(errorMessage);
        errorList.add(excelErrorMessage);
    }


    /**
     * 存在任何校验
     *
     * @param excelCheck excelCheck
     * @return 存在任何校验
     */
    private Boolean anyCheck(ExcelCheck excelCheck) {
        if (!excelCheck.canEmpty()) {
            return true;
        }
        if (excelCheck.length() != -1) {
            return true;
        }
        if (!excelCheck.canRepeat()) {
            return true;
        }
        return false;
    }
}

导入处理器类

/**
 * excel导入处理器,在easyExcel基础封装,增加通用读取、校验功能
 *
 * @author wangmeng
 * @since 2024/6/7
 */
@Setter
@Getter
@Accessors(chain = true)
@Slf4j
public class ExcelImportProcessor {

    /**
     * 默认校验类型listener
     */
    private CheckableImportListener<?> listener = new CheckableImportListener<>();


    private Consumer<ExcelReaderBuilder> readerBuilderConsumer;

    /**
     * 默认第一个sheet
     */
    private Integer sheetNo = 0;

    /**
     * 错误列名
     */
    private final static String ERROR_COLUMN_NAME = "错误信息";


    public ExcelImportProcessor() {

    }

    public ExcelImportProcessor(CheckableImportListener<?> listener) {
        this.listener = listener;
    }


    public <R> List<R> importData(MultipartFile file, Class<R> clazz) {
        // 校验文件
        validateExcel(file);
        List<R> dataList = null;
        try (InputStream inputStream = file.getInputStream()) {
            ExcelReaderBuilder readerBuilder = EasyExcel.read(inputStream, clazz, listener);
            if (readerBuilderConsumer != null) {
                readerBuilderConsumer.accept(readerBuilder);
            }
            dataList = readerBuilder.sheet(sheetNo).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 RuntimeException("导入失败!");
        }
        if (CollectionUtils.isEmpty(dataList)) {
            throw new RuntimeException("解析数据为空!");
        }
        return dataList;
    }


    public List<ExcelErrorMessage> getErrorList() {
        return listener.getErrorList();
    }


    /**
     * 手动添加错误
     *
     * @param index        data的下标(从0开始)
     * @param errorMessage 错误信息
     */
    public void addError(Integer index, String errorMessage) {
        // 下标从0开始+1,标题占一行+1,总计+2
        Integer row = index + 2;
        listener.addError(row, errorMessage);
    }

    /**
     * 生成错误信息excel,在原excel文件追加错误列
     *
     * @param filePath 源文件路径
     */
    public Boolean generateErrorSheet(String filePath) {
        List<ExcelErrorMessage> errorList = listener.getErrorList();
        if (CollectionUtils.isEmpty(errorList)) {
            return false;
        }
        Map<Integer, String> errorMap = errorList.stream().collect(Collectors.groupingBy(ExcelErrorMessage::getRowNum,
            Collectors.mapping(ExcelErrorMessage::getMessage, Collectors.joining(";"))));

        Workbook workbook = null;
        // 打开原excel文件
        try (
            FileInputStream inputStream = new FileInputStream(filePath)) {
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(sheetNo);

            // 添加错误列
            Row headerRow = sheet.getRow(0);
            short lastCellNum = headerRow.getLastCellNum();
            // 检查是否已经存在错误列
            Cell lastValidCell = headerRow.getCell(lastCellNum - 1);
            if (lastValidCell != null) {
                if (!ERROR_COLUMN_NAME.equals(lastValidCell.getStringCellValue())) {
                    Cell errorHeaderCell = headerRow.createCell(lastCellNum);
                    errorHeaderCell.setCellValue(ERROR_COLUMN_NAME);
                    errorMap.forEach((rowNum, msg) -> {
                        Row row = sheet.getRow(rowNum - 1);
                        if (row != null) {
                            Cell errorCell = row.createCell(lastCellNum);
                            errorCell.setCellValue(msg);
                        }
                    });

                } else {
                    int lastRowNum = sheet.getLastRowNum();
                    for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
                        Row row = sheet.getRow(rowNum);
                        String setErrorMsg = errorMap.get(rowNum + 1);
                        // 如果没有需要设置的错误信息,要把旧的错误信息清除
                        Cell errorCell = row.getCell(lastCellNum - 1);
                        if (setErrorMsg == null) {
                            if (errorCell != null) {
                                errorCell.setCellValue((String) null);
                            }
                        } else {
                            if (errorCell == null) {
                                errorCell = row.createCell(lastCellNum - 1);
                            }
                            errorCell.setCellValue(setErrorMsg);
                        }
                    }
                }
            }


        } catch (IOException e) {
            log.error("生成错误信息失败,", e);
            throw new RuntimeException("生成错误信息失败");
        }

        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            // 写回去
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            log.error("生成错误信息失败,", e);
            throw new RuntimeException("生成错误信息失败");
        }
        return true;
    }


    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     *
     * @param file
     * @return
     */
    public static void validateExcel(MultipartFile file) {
        if (file == null) {
            throw new RuntimeException("文件为空!");
        }
        String fileName = file.getOriginalFilename();
        if (fileName != null && !isExcel2007(fileName)) {
            throw new RuntimeException("导入文件必须是xlsx格式！");
        }
        if (StringUtils.isEmpty(fileName) || file.getSize() == 0) {
            throw new RuntimeException("文件内容不能为空");
        }
    }


}

捕获类型转换异常

导入的第一步就是处理字段类型错误，因为如果出现类型转换错误，会直接导致程序异常，这里通过try,catch捕获ExcelAnalysisException异常来获取出现错误的列和行。

这里通过exception对象获取到了field，再获取字段上的ExcelProperty注解。

在AnalysisEventListener中实现校验逻辑

在listener中的invoke方法中为每一行数据做校验，这里主要使用了反射

获取到Error后，根据错误信息生成Excel

这里是拿导入的原本Excel文件，在最后追加一列错误信息列，并将错误信息与行对应，代码如下