easyexcel导入(数据校验)

1.引入依赖(实体类引用了lombok,另引入这个依赖)

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>最新版</version>
        </dependency>

2.实体类

package com.innovation.desk.service.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * LearningExaminationQuestions实体类
 *
 * @author admin
 * @since 2023/03/03
 */
@Data
public class LearningExaminationQuestionsImportDTO {

    private static final long serialVersionUID = 1L;

    @ExcelProperty(value = {"题目"},index = 0)
    private String title;

    @ExcelProperty(value = {"选项"},index = 1)
    private String options;

    @ExcelProperty(value = {"标准答案"},index = 2)
    private String standardAnswer;

    @ExcelProperty(value = {"答案解析"},index = 3)
    private String answerAnalysis;

    @ExcelProperty(value = {"开放范围"},index = 4)
    private String openRange;

    @ExcelProperty(value = {"业务领域"},index = 5)
    private String businessArea;

    @ExcelProperty(value = {"难度等级"},index = 6)
    private String difficultyLevel;

    @ExcelProperty(value = {"选题类型"},index = 7)
    private String topicType;


}

3.controller

    @PostMapping("/learningExaminationQuestions/importExcel")
    @ApiOperation(value = "excel批量上传")
    public ResponseVO importExcel(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), LearningExaminationQuestionsImportDTO.class, new UploadDataListener(learningExaminationQuestionsService)).sheet().doRead();
        return buildSuccess();
    }

4.拦截器

package com.innovation.desk.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.innovation.desk.domain.LearningExaminationQuestions;
import com.innovation.desk.domain.Option;
import com.innovation.desk.enums.BusinessAreaEnum;
import com.innovation.desk.enums.DifficultyLevelEnum;
import com.innovation.desk.enums.OpenRangeEnum;
import com.innovation.desk.enums.TopicTypeEnum;
import com.innovation.desk.service.LearningExaminationQuestionsService;
import com.innovation.desk.service.dto.LearningExaminationQuestionsImportDTO;
import lombok.extern.slf4j.Slf4j;

import java.util.*;

/**
 *  上传监听类
 * @author Administrator
 */
@Slf4j
public class UploadDataListener extends AnalysisEventListener<LearningExaminationQuestionsImportDTO> {

    /**
     * 每隔100条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    private final List<LearningExaminationQuestions> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private final LearningExaminationQuestionsService service;


    public UploadDataListener(LearningExaminationQuestionsService service) {
        this.service = service;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context 表格对象
     */
    @Override
    public void invoke(LearningExaminationQuestionsImportDTO data, AnalysisContext context) {
        LearningExaminationQuestions question = new LearningExaminationQuestions();
        //如果是单选或多选题就解析options字段
        if (TopicTypeEnum.isChoiceQuestion(data.getTopicType())){
            String[] split = data.getOptions().split("\\|");
            ArrayList<String> optionStrings = new ArrayList<>(Arrays.asList(split));
            List<Option> options = new ArrayList<>();
            optionStrings.forEach(optionString ->{
                String[] optionStringArray = optionString.split(":");
                Option option = new Option(optionStringArray[0],optionStringArray[1]);
                options.add(option);
            });
            question.setOptions(options);
        }else if (TopicTypeEnum.isJudgeQuestion(data.getTopicType())){
            String[] split = data.getOptions().split("\\|");
            Option option0 = new Option("0",split[0]);
            Option option1 = new Option("1",split[1]);
            question.setOptions(Arrays.asList(option0,option1));
        }
        //如果是填空题
        if (TopicTypeEnum.isCompletionQuestion(data.getTopicType())){
            question.setStandardAnswer(Arrays.asList(data.getStandardAnswer().split("\\|")));
            //替换占位符为括号
            question.setTitle(data.getTitle().replace("%s","( )"));
        }else {
            question.setStandardAnswer(Collections.singletonList(data.getStandardAnswer()));
            question.setTitle(data.getTitle());
        }
        question.setAnswerAnalysis(data.getAnswerAnalysis());
        question.setDifficultyLevel(DifficultyLevelEnum.getNumByDiff(data.getDifficultyLevel()));
        question.setBusinessArea(BusinessAreaEnum.getNumByName(data.getBusinessArea()));
        question.setTopicType(TopicTypeEnum.getNumByType(data.getTopicType()));
        question.setOpenRange(OpenRangeEnum.getNumByDesc(data.getOpenRange()));
        question.setCollect(0);
        question.setViews(0);
        question.setStatus(1);
        question.setReleaseTime(new Date());
        cachedDataList.add(question);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context 表格对象
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        service.saveBatch(cachedDataList);
    }
}