easyExcel中导入文件参数校验实现方案

2年前 (2022) 程序员胖胖胖虎阿

433 0 0

    最近在使用easy Excel进行文件导入功能,文件读取完成需要对参数信息进行校验,下面总结一下自己在进行导入文件时参数校验的实现方案.
    首先要清楚文件导入的流程:easy excel对excel进行数据读取时,是按照以行为单位从上往下读取，支持自定义监听器对每行读取的数据进行监听并添加自己的处理逻辑。所以想要实现导入文件的参数校验,可以在每行读取完成之后的监听逻辑中处理。参数校验过程中出现不符合校验规则的信息,一般是希望提示用户哪一行的哪个字段录入有问题.这里面需要使用easy excel读取的索引进行定位行数.下面结合具体的业务场景说明问题处理过程。
业务场景:
    当前有一个在线考试系统,支持选择、填空、简答三种题型,每种题型会提供对应模板,填充数据之后进行导入操作.以选择题为例说明参数校验处理过程。选择题导入模板:
easyExcel中导入文件参数校验实现方案
    选择题导入场景中会有合并单元格读取问题，可参考：easyExcel中合并单元格文件读取实现方案，本文仅介绍参数校验,实际参数检验就是对导入文件映射实体类参数进行校验。

选择题映射实体类：

	public class ImportExamChoiceInfo implements Serializable {

    @ApiModelProperty(value = "题干信息",example = "1",dataType = "String")
    @ExcelProperty("题目描述")
    private String stem;

    @ApiModelProperty(value = "考题id",example = "1",dataType = "Integer")
    @ExcelIgnore
    private Integer examId;

    @ApiModelProperty(value = "考试答案",example = "1",dataType = "String")
    @ExcelProperty("考试答案")
    private String rightAnswer;

    @ApiModelProperty(value = "考试答案分析",example = "1",dataType = "String")
    @ExcelProperty("考试答案分析")
    private String analysis;

    @Range(min = 1,max = 3,message = "题目类型不合法!")
    @ApiModelProperty(value = "题目类型:1.选择;2.填空;3.简单",example = "1",dataType = "Integer")
    @ExcelIgnore
    private Integer type;

    @ApiModelProperty(value = "题目单项分数",example = "1",dataType = "Integer")
    @ExcelProperty("题目单项分数")
    private Integer signScore;

    @ExcelProperty("选项")
    private String choiceType;

    @ExcelProperty("选项内容")
    private String optionContent;

// 省略get/set
}

选择题读取之后需要组装的实体类，分别为选择题题干和选项实体类:

public class ImportTargetExamChoice implements Serializable {
    private static final long serialVersionUID = -833109827799517116L;

    @ApiModelProperty(value = "题干信息",example = "1",dataType = "String")
    private String stem;

    @ApiModelProperty(value = "考题id",example = "1",dataType = "Integer")
    private Integer examId;

    @ApiModelProperty(value = "考试答案",example = "1",dataType = "String")
    private String rightAnswer;

    @ApiModelProperty(value = "考试答案分析",example = "1",dataType = "String")
    private String analysis;

    @ApiModelProperty(value = "题目类型:1.选择;2.填空;3.简单",example = "1",dataType = "Integer")
    private Integer type;

    @ApiModelProperty(value = "题目单项分数",example = "1",dataType = "Integer")
    private Integer signScore;

    @ApiModelProperty(value = "题目id",example = "1",dataType = "Integer")
    private Integer id;

    @ApiModelProperty(value = "选择题选项集合信息",example = "1",dataType = "List.class")
    private List<ImportExamChoiceItem> importExamChoiceItems=new ArrayList<>();

// 省略get/set
    }

public class ExamChoiceItem implements Serializable {
    private static final long serialVersionUID = 5079095161978838071L;

    @ApiModelProperty(value = "题干选项id",example = "1",dataType = "Integer")
    private Integer stemItemId;

    @ApiModelProperty(value = "选择选项:选项(1.A;2.B;3.C;4.D)",example = "1",dataType = "Integer")
    private String choiceType;

    @ApiModelProperty(value = "选择选项具体内容",example = "1",dataType = "String")
    private String optionContent;

  // 省略get/set
}

自定义监听器ExamChoiceListener实现对每行读取数据进行监听并进行参数校验

@Slf4j
public class ExamChoiceListener extends AnalysisEventListener<ImportExamChoiceInfo> {
    // 自定义消费者函数接口用于自定义监听器中数据组装
    private final Consumer<List<ImportTargetExamChoice>> consumer;

    public ExamChoiceListener(Consumer<List<ImportTargetExamChoice>> consumer) {
        this.consumer = consumer;
    }

    // 封装读取对象
    private List<ImportTargetExamChoice> importTargetExamChoiceList=new ArrayList<>();
    // 每行读取完成之后会执行
    @Override
    public void invoke(ImportExamChoiceInfo data, AnalysisContext context) {

        // 导入文件参数校验，校验异常支持显示报错异常的行数
        validChoiceInfo(data, context);

        // 合并单元格数据处理：按照格式组装数据
        if(data.getStem() != null){
            ImportTargetExamChoice importTargetExamChoice = new ImportTargetExamChoice();
            BeanUtils.copyProperties(data,importTargetExamChoice);
            ImportExamChoiceItem importExamChoiceItem = new ImportExamChoiceItem();
            importExamChoiceItem.setChoiceType(data.getChoiceType());
            importExamChoiceItem.setOptionContent(data.getOptionContent());
            importTargetExamChoice.getImportExamChoiceItems().add(importExamChoiceItem);
            importTargetExamChoiceList.add(importTargetExamChoice);
        }else {
            // 倒序添加选择题信息,只对最后一个进行添加选项数据信息
            ImportExamChoiceItem importExamChoiceItem = new ImportExamChoiceItem();
            importExamChoiceItem.setChoiceType(data.getChoiceType());
            importExamChoiceItem.setOptionContent(data.getOptionContent());
            importTargetExamChoiceList.get(importTargetExamChoiceList.size() - 1).getImportExamChoiceItems().add(importExamChoiceItem);
        }
    }

    /**
     * @Author: txm
     * @Description: 导入选择题信息校验
     * @Param: [data, context]
     * @return: void
     * @Date:  2022/5/20 15:29
     **/
    private void validChoiceInfo(ImportExamChoiceInfo data, AnalysisContext context) {
        if(StrUtil.isBlank(data.getStem())){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行题目描述信息为空",context.readRowHolder().getRowIndex()));
        }
        if(StrUtil.isBlank(data.getRightAnswer()) || !StrUtil.containsAny(data.getRightAnswer(),"A","B","C","D")){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行考试答案信息为空或不合法(正常答案为:A、B、C、D)",context.readRowHolder().getRowIndex()));
        }
        if(StrUtil.isBlank(data.getAnalysis())){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行考试答案分析信息为空",context.readRowHolder().getRowIndex()));
        }
        if(null == data.getSignScore() || 0 == data.getSignScore()){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行题目单项分数信息为空或为0",context.readRowHolder().getRowIndex()));
        }
        if(StrUtil.isBlank(data.getChoiceType()) || !StrUtil.containsAny(data.getChoiceType(),"A","B","C","D")){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行选项信息为空或不合法(正常选项为:A、B、C、D)",context.readRowHolder().getRowIndex()));
        }
        if(StrUtil.isBlank(data.getOptionContent())){
            throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行选项内容信息为空,上传失败",context.readRowHolder().getRowIndex()));
        }
    }

    // 每行读取完成之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (CollectionUtils.isNotEmpty(importTargetExamChoiceList)) {
            consumer.accept(importTargetExamChoiceList);
        }
    }
}

自定义EasyExcelUtil工具类

@Component
public class EasyExcelUtil {

  // 导入选择题
   public List<ImportTargetExamChoice> getImportChoiceExam(String filePath){
       List<ImportTargetExamChoice> importTargetExamChoices=new ArrayList<>();

       EasyExcel.read(filePath, ImportExamChoiceInfo.class, new ExamChoiceListener(importTargetExamChoiceList -> {
           for (ImportTargetExamChoice examInfo : importTargetExamChoiceList) {
               importTargetExamChoices.add(examInfo);
           }
       })).sheet().doRead();

       return importTargetExamChoices;
    }
}

		// 导入填空题
    public List<ExamStemDto> importFile(String filePath,Class<?> cls){
        List<ExamStemDto> examStemDtos=new ArrayList<>();

        EasyExcel.read(filePath, ExamStemDto.class, new ExamStemDtoListener(importExamStemDto -> {
            for (ExamStemDto examInfo : importExamStemDto) {
                examStemDtos.add(examInfo);
            }
        })).sheet().doRead();

        return examStemDtos;
    }

业务处理实现类:

@Slf4j
@Service
public class ExamServiceImpl implements ExamService {
 @Override
    public void uploadExamFile(MultipartFile multipartFile, Integer type, Integer examId) throws Exception {

        // 导入文件上传到临时服务器
        String importTemp = trainConfig.getExamFileTemp();
        String filePath = easyExcelUtil.uploadTemp(multipartFile,importTemp);

        try {
            // 组装导入记录数据
            switch (type){
                case 1:  // 选择题导入处理
                    List<ImportTargetExamChoice> importChoiceExamList = easyExcelUtil.getImportChoiceExam(filePath);
                    if(CollectionUtil.isEmpty(importChoiceExamList)){
                        throw new BussinessExcption("获取文件内容为空,上传失败!");
                    }
                    log.info("选择题数据内容:{}",importChoiceExamList);
                    // 省略其他业务处理逻辑
                break；
                case 2: // 填空题导入
                   // 省略其他业务处理逻辑
                    break;
                case 3: // 简答题导入
                   // 省略其他业务处理逻辑
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            // 输出信息:上传失败:第1行考试答案为空
            throw new BussinessExcption(e.getMessage());
        } finally {
            // 删除服务器临时存储文件略
        }
    }
    }

自定义异常信息:

public class BussinessExcption extends RuntimeException {
    public BussinessExcption(String s, String messageUUID, String toJSONString) {
        super();
    }

    public BussinessExcption(String message) {
        super(message);
    }

    public BussinessExcption(String message, Throwable cause) {
        super(message, cause);
    }

// 省略get/set

}

controller请求接口

// type:1.选择;2.填空;3.简答
@RequestMapping("/exam")
@RestController
public class ExamController {

 @Autowired
    private ExamServiceImpl examService;

@PostMapping("/uploadFile")
public ResultVo uploadFile(MultipartFile multipartFile,
                              Integer type) throws Exception {
    examService.uploadExamFile(multipartFile,type,examId);
    return ResultVoUtil.success();
}
}

接口返回工具类:

public class ResultVoUtil {


    public static ResultVo<Boolean> success(){
        return result(ApiCode.SUCCESS,null,null);
    }

    public static <T> ResultVo<T> success(T data){
        return result(ApiCode.SUCCESS,null,data);
    }



    public static ResultVo<Boolean> error() {
        return result(ApiCode.FAIL,null,null);
    }

    public static <T> ResultVo<T> error(ApiCode apiCode,T data){
        return result(apiCode,null,data);
    }

    public static ResultVo error(String errorMsg){
       return result(ApiCode.FAIL,errorMsg,null);
    }

    public static ResultVo error(ApiCode apiCode){
        return result(apiCode,null,null);
    }

    public static ResultVo error(ApiCode apiCode,String errorMsg){
        return result(apiCode,errorMsg,null);
    }

    public static <T> ResultVo<T> result(ApiCode apiCode,String message,T data){
        boolean success = false;
        if (apiCode.getCode() == ApiCode.SUCCESS.getCode()){
            success = true;
        }
        String apiMessage = apiCode.getMessage();
        if (StringUtils.isBlank(message)){
            message = apiMessage;
        }
        return (ResultVo<T>) ResultVo.builder()
                .code(apiCode.getCode())
                .msg(message)
                .data(data)
                .success(success)
                .time(new Date())
                .build();
    }

}

上面的参数校验中会发现一个问题:如果出现异常信息，提示给用户的信息都是写死的， throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行题目描述信息为空",context.readRowHolder().getRowIndex()));一旦参数表示的字段变化之后异常提示信息就需要进行更改。怎样根据导入文件字段动态获取描述信息，这里想到的处理方案就是自定义注解+反射。下面以填空题导入为例进行说明。填空题模板:
easyExcel中导入文件参数校验实现方案
填空题导入实体类:

public class ExamStemDto implements Serializable {
    private static final long serialVersionUID = 4152485820193669949L;

    @NotBlank(message = "题干信息不允许为空!")
    @ApiModelProperty(value = "题干信息",example = "1",dataType = "String")
    @ExcelProperty("题目描述")
    @ExamStemDtoAnnotation(errorDesc ="题目描述为空" ,validIsBlank = true)
    private String stem;

    @ApiModelProperty(value = "考题id",example = "1",dataType = "Integer")
    @NotNull(message = "题目id不允许为空!")
    @Min(value = 1,message = "考题id不允许为0!")
    @ExcelIgnore
    private Integer examId;

    @NotBlank(message = "考试答案不允许为空!")
    @ApiModelProperty(value = "考试答案",example = "1",dataType = "String")
    @ExcelProperty(value = "考试答案")
    @ExamStemDtoAnnotation(errorDesc ="考试答案为空或是间隔不是一个空格" ,validIsBlank = true,validIsInterval = true)
    private String rightAnswer;

    @NotBlank(message = "考试答案分析不允许为空!")
    @ApiModelProperty(value = "考试答案分析",example = "1",dataType = "String")
    @ExcelProperty("考试答案分析")
    @ExamStemDtoAnnotation(errorDesc ="考试答案分析为空",validIsBlank = true)
    private String analysis;

    @NotNull(message = "题目类型不允许为空!")
    @Range(min = 1,max = 3,message = "题目类型不合法!")
    @ApiModelProperty(value = "题目类型:1.选择;2.填空;3.简单",example = "1",dataType = "Integer")
    @ExcelIgnore
    private Integer type;

    @ApiModelProperty(value = "题目单项分数",example = "1",dataType = "Integer")
    @ExcelProperty("题目单项分数")
    @ExamStemDtoAnnotation(errorDesc ="题目单项分数为空或为0",validIsZero = true)
    private Integer signScore;

    @ApiModelProperty(value = "题目id",example = "1",dataType = "Integer",hidden = true)
    @ExcelIgnore
    private Integer id;
	
	// 省略get/set
}

自定义注解ExamStemDtoAnnotation:

@Target(ElementType.FIELD)
@Retention(RUNTIME)
@Documented
public @interface ExamStemDtoAnnotation {

    // 校验是否为空
    boolean validIsBlank() default false;

    // 是否校验间隔为一个空格
    boolean validIsInterval() default false;

    // 是否校验数值是否为空或0
    boolean validIsZero() default false;

    // 字段描述信息
    String errorDesc() default "";

}

自定义填空题监听器读取每行数据进行参数校验并动态获取异常提示信息。

public class ExamStemDtoListener extends AnalysisEventListener<ExamStemDto> {

    // 自定义消费者函数接口用于自定义监听器中数据组装
    private final Consumer<List<ExamStemDto>> consumer;

    public ExamStemDtoListener(Consumer<List<ExamStemDto>> consumer) {
        this.consumer = consumer;
    }

    // 封装读取对象
    private List<ExamStemDto> examStemDtos=new ArrayList<>();


    @SneakyThrows
    @Override
    public void invoke(ExamStemDto examStemDto, AnalysisContext analysisContext) {
        // 参数校验
        Field[] fields = examStemDto.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置可访问
            field.setAccessible(true);

            // 反射进行参数校验,根据自定义注解中的属性值是否为true决定是否校验
            ExamStemDtoAnnotation examStemDtoAnnotation = field.getAnnotation(ExamStemDtoAnnotation.class);
            if (ObjectUtil.isNotNull(examStemDtoAnnotation)) {
                if(examStemDtoAnnotation.validIsBlank()){
                    String readMsg = (String) field.get(examStemDto);  // 字符串是否为空校验
                    if(StrUtil.isBlank(readMsg)) throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行{}",analysisContext.readRowHolder().getRowIndex(),examStemDtoAnnotation.errorDesc()));
                }
                
                if(examStemDtoAnnotation.validIsZero()){   // 数字是否为空或是为0
                    Integer readMsg = (Integer) field.get(examStemDto);
                    if(readMsg == null || readMsg == 0) throw new ExcelAnalysisException(StrUtil.format("上传失败:第{}行{}",analysisContext.readRowHolder().getRowIndex(),examStemDtoAnnotation.errorDesc()));
                }
            }
        }
    }


// 所有数据读取完成之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (CollectionUtils.isNotEmpty(examStemDtos)) {
            consumer.accept(examStemDtos);
        }
    }

}

业务实现类:

@Slf4j
@Service
public class ExamServiceImpl implements ExamService {
 @Override
    public void uploadExamFile(MultipartFile multipartFile, Integer type, Integer examId) throws Exception {

        // 导入文件上传到临时服务器
        String importTemp = trainConfig.getExamFileTemp();
        String filePath = easyExcelUtil.uploadTemp(multipartFile,importTemp);

        try {
            // 组装导入记录数据
            switch (type){
                case 1: //选择题导入
                   // 省略其他业务处理逻辑
                break；
                case 2: // 填空题导入
                 List<ExamStemDto> examFillBlankList = easyExcelUtil.importFile(filePath,ExamStemDto.class);
                   // 省略其他业务处理逻辑
                    break;
                case 3: // 简答题导入
                   // 省略其他业务处理逻辑
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new BussinessExcption(e.getMessage());
//            return ResultVoUtil.error(e.getMessage());
        } finally {
            // 删除服务器临时存储文件略
        }
    }
    }

    总结:文件导入的参数校验处理逻辑在监听器invoke方法中执行,具体处理的方式可以每个字段校验或是反射进行动态获取参数以及异常提示信息。
    以上为处理文件导入时参数校验的实现方案总结，如果对你有帮助欢迎评论区留言或收藏关注！
更多easy excel实战文章:
     Easyexcel实战:读取文件自定义转化器不生效问题处理方案
     EasyExcel实战:实现导入文件参数校验并记录异常信息
     EasyExcel中合并单元格文件读取实现方案