基于springBoot+Vue的在线考试：题库的导入与导出

基于springBoot+Vue的在线考试系统，在题库管理模块，新增了题库的导入与导出功能：

导入导出题库，使用的是EasyExcel，这里是多个sheet的导入和导出，还是有一点难度的，所以大概说一下（这里只讲后端，前端请自行查看源码）：

1.引入依赖：

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

2.导出题库：

按题目类型导出Excel，代码如下，注释也在代码中了：

    // 导出题库
    @PostMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response){
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).autoCloseStream(Boolean.FALSE).build();
            // 指定要导出的属性
            Set<String> names = new HashSet<>(8);
            names.add("questionContent");
            names.add("questionAnswer");
            names.add("questionExplain");
            names.add("questionLevelStr");

            // 1. 填空题
            WriteSheet fillSheet = EasyExcel.writerSheet(0, "填空题").head(QuestionDto.class)
                    .includeColumnFiledNames(names).build();
            List<QuestionDto> fillData = fillQuestionService.getListByPage(null);
            excelWriter.write(fillData,fillSheet);
            // 2. 判断题
            WriteSheet judgeSheet = EasyExcel.writerSheet(1, "判断题").head(QuestionDto.class)
                    .includeColumnFiledNames(names).build();
            List<QuestionDto> judgeData = judgeQuestionService.getListByPage(null);
            excelWriter.write(judgeData,judgeSheet);

            // 选择题导出的属性，增加四个选项
            names.add("choiceA");
            names.add("choiceB");
            names.add("choiceC");
            names.add("choiceD");

            // 3. 单选题
            WriteSheet singleSheet = EasyExcel.writerSheet(2, "单选题").head(QuestionDto.class)
                    .includeColumnFiledNames(names).build();
            List<QuestionDto> singleData = singleQuestionService.getListByPage(null);
            excelWriter.write(singleData,singleSheet);
            // 4. 多选题
            WriteSheet multiSheet = EasyExcel.writerSheet(3, "多选题").head(QuestionDto.class)
                    .includeColumnFiledNames(names).build();
            List<QuestionDto> multiData = multiQuestionService.getListByPage(null);
            excelWriter.write(multiData,multiSheet);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

3.题库导入：

    // 导入题库
    @PostMapping("/import")
    public ResultUtil importExcel(MultipartFile file){
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(file.getInputStream()).build();
            FillListener fillListener = new FillListener(fillQuestionService);
            JudgeListener judgeListener = new JudgeListener(judgeQuestionService);
            SingleListener singleListener = new SingleListener(singleQuestionService);
            MultiListener multiListener = new MultiListener(multiQuestionService);
            ReadSheet sheet0 = EasyExcel.readSheet(0).registerReadListener(fillListener).headRowNumber(1).build();
            ReadSheet sheet1 = EasyExcel.readSheet(1).registerReadListener(judgeListener).headRowNumber(1).build();
            ReadSheet sheet2 = EasyExcel.readSheet(2).registerReadListener(singleListener).headRowNumber(1).build();
            ReadSheet sheet3 = EasyExcel.readSheet(3).registerReadListener(multiListener).headRowNumber(1).build();
            excelReader.read(sheet0,sheet1,sheet2,sheet3);
        } catch (IOException e) {
            e.printStackTrace();
            return new ResultUtil(EnumCode.OK.getValue(),"导入失败");
        }finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
        return new ResultUtil(EnumCode.OK.getValue(),"导入成功");
    }

这里有FillListener 之类的Listener，继承了AnalysisEventListener，主要是为了实现业务数据的组装的，以填空题为例：

public class FillListener extends AnalysisEventListener<Map<Integer,String>>{
    private List<FillQuestion> fillQuestionList;
    private FillQuestionService fillQuestionService;

    public FillListener(FillQuestionService questionService){
        this.fillQuestionService = questionService;
        this.fillQuestionList = new ArrayList<>();
    }

    @Override
    public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
        FillQuestion question = new FillQuestion();
        question.setId(UUID.randomUUID().toString().replace("-",""));
        question.setCreateTime(new Date());
        question.setQuestionContent(map.get(0));
        question.setQuestionAnswer(map.get(1));
        question.setQuestionExplain(map.get(2));
        question.setQuestionLevel(QuestionLevelMap.getLevelByValue(map.get(3)));
        fillQuestionList.add(question);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 将数据插入数据库
        fillQuestionService.batchInsert(fillQuestionList);
        fillQuestionList.clear();
    }

在AnalysisEventListener中，我使用是泛型是Map<Integer,String>，表示将每一行数据封装成map，key是第几列，从0开始，value就是值，之所以用map接收，是因为有一些数据字典的值需求自己转换，如题目难度：

导入的时候是：简单、中等、困难，但是存入数据库是：1,2,3，所以需要转换

在invoke方法封装成自己需要的对象：

    @Override
    public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
        FillQuestion question = new FillQuestion();
        question.setId(UUID.randomUUID().toString().replace("-",""));
        question.setCreateTime(new Date());
        question.setQuestionContent(map.get(0));
        question.setQuestionAnswer(map.get(1));
        question.setQuestionExplain(map.get(2));
        question.setQuestionLevel(QuestionLevelMap.getLevelByValue(map.get(3)));
        fillQuestionList.add(question);
    }

最后执行批量插入即可：

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 将数据插入数据库
        fillQuestionService.batchInsert(fillQuestionList);
        fillQuestionList.clear();
    }

批量插入的实现，这里使用的是foreach循环：

    <!--批量插入-->
    <insert id="batchInsert">
        INSERT INTO fill_question(id,create_time,question_content,question_answer,question_explain,question_level) VALUES
        <foreach collection="list" item="question" separator=",">
          (#{question.id}, #{question.createTime},#{question.questionContent}, #{question.questionAnswer}, #{question.questionExplain}, #{question.questionLevel})
        </foreach>
    </insert>