基于springBoot+Vue的在线考试系统,在题库管理模块,新增了题库的导入与导出功能:
导入导出题库,使用的是EasyExcel,这里是多个sheet的导入和导出,还是有一点难度的,所以大概说一下(这里只讲后端,前端请自行查看源码):
1.引入依赖:
com.alibabaeasyexcel2.2.62.导出题库:
按题目类型导出Excel,代码如下,注释也在代码中了:
// 导出题库@PostMapping("/exportExcel")public void exportExcel(HttpServletResponse response){ExcelWriter excelWriter = null;try {excelWriter = EasyExcel.write(response.getOutputStream()).autoCloseStream(Boolean.FALSE).build();// 指定要导出的属性Set 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 fillData = fillQuestionService.getListByPage(null);excelWriter.write(fillData,fillSheet);// 2. 判断题WriteSheet judgeSheet = EasyExcel.writerSheet(1, "判断题").head(QuestionDto.class).includeColumnFiledNames(names).build();List 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 singleData = singleQuestionService.getListByPage(null);excelWriter.write(singleData,singleSheet);// 4. 多选题WriteSheet multiSheet = EasyExcel.writerSheet(3, "多选题").head(QuestionDto.class).includeColumnFiledNames(names).build();List 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{private List fillQuestionList;private FillQuestionService fillQuestionService;public FillListener(FillQuestionService questionService){this.fillQuestionService = questionService;this.fillQuestionList = new ArrayList();}@Overridepublic void invoke(Map 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);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 将数据插入数据库fillQuestionService.batchInsert(fillQuestionList);fillQuestionList.clear();}在AnalysisEventListener中,我使用是泛型是Map,表示将每一行数据封装成map,key是第几列,从0开始,value就是值,之所以用map接收,是因为有一些数据字典的值需求自己转换,如题目难度:
导入的时候是:简单、中等、困难,但是存入数据库是:1,2,3,所以需要转换
在invoke方法封装成自己需要的对象:
@Overridepublic void invoke(Map 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 INTO fill_question(id,create_time,question_content,question_answer,question_explain,question_level) VALUES (#{question.id}, #{question.createTime},#{question.questionContent}, #{question.questionAnswer}, #{question.questionExplain}, #{question.questionLevel})