题目导入

xiangpei

2024-03-16 4ba127459ff1b77df9014444fdac7636d3f091bc

题目导入

 src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java

@@ -79,7 +79,13 @@
                    .and().authenticationProvider(restAuthenticationProvider)
                    .authorizeRequests()
                    .antMatchers(securityIgnoreUrls.toArray(ignores)).permitAll()
                    .antMatchers("/api/admin/department/list", "/api/admin/video/getList","/api/admin/user/conversion","/api/admin/examPaperGrade/updates","/api/admin/question/download/question/import/temp").permitAll()
                    .antMatchers("/api/admin/department/list",
                            "/api/admin/video/getList",
                            "/api/admin/user/conversion",
                            "/api/admin/examPaperGrade/updates",
                            "/api/admin/question/download/question/import/temp",
                            "/api/admin/question/question/import"
                    ).permitAll()
                    // todo 设置部门管理员可以看的请求
                    .antMatchers("/api/admin/**").hasAnyRole(RoleEnum.ADMIN.getName(), RoleEnum.DEPT_ADMIN.getName())
                    .antMatchers("/api/student/**").hasRole(RoleEnum.STUDENT.getName())

 src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java

@@ -1,16 +1,25 @@
package com.mindskip.xzs.controller.admin;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.data.HyperlinkData;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.mindskip.xzs.base.BaseApiController;
import com.mindskip.xzs.base.RestResponse;
import com.mindskip.xzs.base.SystemCode;
import com.mindskip.xzs.context.WebContext;
import com.mindskip.xzs.domain.Question;
import com.mindskip.xzs.domain.QuestionSubject;
import com.mindskip.xzs.domain.Subject;
import com.mindskip.xzs.domain.TextContent;
import com.mindskip.xzs.domain.enums.QuestionSourceEnum;
import com.mindskip.xzs.domain.enums.QuestionStatusEnum;
import com.mindskip.xzs.domain.enums.QuestionTypeEnum;
import com.mindskip.xzs.domain.question.QuestionItemObject;
import com.mindskip.xzs.domain.question.QuestionObject;
import com.mindskip.xzs.excel.CurrencyDataListener;
import com.mindskip.xzs.excel.FixedMergeCellStrategy;
import com.mindskip.xzs.excel.SelectExcel;
import com.mindskip.xzs.repository.DepartmentMapper;
import com.mindskip.xzs.repository.SubjectMapper;
@@ -24,7 +33,10 @@
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

@@ -33,9 +45,8 @@
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

@RestController("AdminQuestionController")
@@ -50,6 +61,8 @@
    private final DepartmentMapper departmentMapper;

    private final QuestionSubjectService questionSubjectService;

    private static final String SPLIT = "、";

    @Autowired
    public QuestionController(QuestionService questionService, TextContentService textContentService, SubjectMapper subjectMapper, DepartmentMapper departmentMapper, QuestionSubjectService questionSubjectService) {
@@ -130,11 +143,168 @@
        return RestResponse.ok();
    }

    /**
     * 下载题目导入模板
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/download/question/import/temp")
    public void getImportTemp(HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("题目导入模板", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class).registerWriteHandler(new SelectExcel()).sheet("模板").doWrite(new ArrayList());

        // 构建模板样例数据
        List<QuestionImportVO> data = new ArrayList<>(4);
        QuestionImportVO questionImportVO = new QuestionImportVO();
        questionImportVO.setQuestionType("单选题");
        questionImportVO.setDifficult(2);
        questionImportVO.setCorrect("B");
        questionImportVO.setScore(2);
        questionImportVO.setSubjectName("测试课目");
        questionImportVO.setAnalyze("B是对的");
        questionImportVO.setTitle("这是一道测试题目，使用该模板请删除或替换这道题");
        questionImportVO.setOptionName("A");
        questionImportVO.setOptionValue("选我");
        data.add(questionImportVO);

        QuestionImportVO questionImportVO1 = new QuestionImportVO();
        questionImportVO1.setOptionName("B");
        questionImportVO1.setOptionValue("选B");
        data.add(questionImportVO1);

        QuestionImportVO questionImportVO2 = new QuestionImportVO();
        questionImportVO2.setOptionName("C");
        questionImportVO2.setOptionValue("选C");
        data.add(questionImportVO2);

        QuestionImportVO questionImportVO3 = new QuestionImportVO();
        questionImportVO3.setOptionName("D");
        questionImportVO3.setOptionValue("选D");
        data.add(questionImportVO3);

        // 查出所有的课目（excel下拉数据）
        List<Subject> subjects = subjectMapper.allSubject();
        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());

        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class)
                .sheet("模板")
                .registerWriteHandler(new SelectExcel(subjectNameList))
                .registerWriteHandler(new FixedMergeCellStrategy(2, 4, Arrays.asList(1, 2, 3, 6, 7, 8, 9)))
                .doWrite(data);
    }

    /**
     * 题目导入
     *
     * easyexcel导入一对多，比如一个题目，四个选项。那么读取到的数据就有四条，第一条数据是齐全的。后面三条只有选项有值
     * @param file
     * @throws IOException
     */
    @PostMapping("/question/import")
    @Transactional(rollbackFor = Exception.class)
    public RestResponse importQuestion(@RequestPart("file") MultipartFile file) throws IOException {
        Consumer<List<QuestionImportVO>> consumer = (data) -> {
            // 题目的课目信息
            List<QuestionSubject> questionSubjectsList = new ArrayList<>(48);
            for (int i = 0; i < data.size(); i++) {
                // 题目实体
                Question question = new Question();
                // 读取的题目
                QuestionImportVO excelQuestion = data.get(i);

                // 如果是第一条完整数据，那么继续往后读取选项内容
                if (excelQuestion.master()) {
                    // 该题的选项
                    List<QuestionItemObject> options = new ArrayList<>(4);
                    // 选项内容
                    QuestionItemObject option = new QuestionItemObject();
                    option.setPrefix(excelQuestion.getOptionName());
                    option.setContent(excelQuestion.getOptionValue());
                    options.add(option);
                    int next = 1;
                    // 继续往后读选项
                    while (Boolean.TRUE) {
                        // 判断是否是最后一条
                        if (next + i == data.size()) {
                            break;
                        }
                        QuestionImportVO nextQuestion = data.get(next + i);
                        if (nextQuestion.master()) {
                            break;
                        }
                        QuestionItemObject nextOption = new QuestionItemObject();
                        nextOption.setPrefix(nextQuestion.getOptionName());
                        nextOption.setContent(nextQuestion.getOptionValue());
                        options.add(nextOption);
                        next++;
                    }
                    i += next;

                    // 保存题目内容
                    QuestionObject questionObject = new QuestionObject();
                    questionObject.setQuestionItemObjects(options);
                    questionObject.setAnalyze(excelQuestion.getAnalyze());
                    questionObject.setTitleContent(excelQuestion.getTitle());
                    questionObject.setCorrect(excelQuestion.getCorrect());

                    TextContent textContent = new TextContent();
                    textContent.setContent(JSON.toJSONString(questionObject));
                    textContent.setCreateTime(new Date());
                    textContentService.insert(textContent);

                    // 保存题目信息
                    // 设置题型
                    question.setQuestionType(QuestionTypeEnum.get(excelQuestion.getQuestionType()));
                    // 答案(多选需要用,分割保存字符串到数据库)
                    String[] corrects = excelQuestion.getCorrect().split(SPLIT);
                    if (corrects.length > 1) {
                        question.setCorrect(Arrays.asList(corrects).stream().collect(Collectors.joining(",")));
                    } else {
                        question.setCorrect(excelQuestion.getCorrect());
                    }

                    // 难度
                    question.setDifficult(excelQuestion.getDifficult());
                    // 分数
                    question.setScore(ExamUtil.scoreFromVM(String.valueOf(excelQuestion.getScore())));
                    // 创建人
                    question.setCreateUser(1);
                    question.setStatus(QuestionStatusEnum.OK.getCode());
                    question.setCreateTime(new Date());
                    question.setDeleted(Boolean.FALSE);
                    question.setInfoTextContentId(textContent.getId());
                    questionService.insert(question);


                    // 查出所有的课目
                    List<Subject> subjects = subjectMapper.allSubject();
                    List<String> subjectNames = Arrays.asList(excelQuestion.getSubjectName().split(SPLIT));
                    List<Subject> targetSubject = subjects.stream()
                            .filter(subject -> subjectNames.contains(subject.getName()))
                            .collect(Collectors.toList());
                    if (CollectionUtils.isEmpty(targetSubject)) {
                        // todo 记录这个错误
                        continue;
                    }
                    // 构建课目-题目信息
                    questionSubjectsList = targetSubject.stream().map(subject -> {
                        QuestionSubject questionSubject = new QuestionSubject();
                        questionSubject.setQuestionId(question.getId());
                        questionSubject.setSubjectId(subject.getId());
                        questionSubject.setDeleted(0);
                        return questionSubject;
                    }).collect(Collectors.toList());
                }
                System.out.println(question);
            }
            // 批量保存题目-课目信息
            if (! CollectionUtils.isEmpty(questionSubjectsList)) {
                questionSubjectService.saves(questionSubjectsList);
            }
        };
        EasyExcel.read(file.getInputStream(), QuestionImportVO.class, new CurrencyDataListener(consumer)).sheet("模板").doRead();
        return RestResponse.ok();
    }

    @PostMapping("/import")

 src/main/java/com/mindskip/xzs/excel/CurrencyDataListener.java

New file
@@ -0,0 +1,105 @@
package com.mindskip.xzs.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

/**
 * easyExcel 多表通用读取监听器
 *
 * @author xp
 */
public class CurrencyDataListener<T> implements ReadListener<T> {


    private Consumer consumer;

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

    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private final static Logger log = LoggerFactory.getLogger(CurrencyDataListener.class);

    public CurrencyDataListener(Consumer<List<T>> consumer) {
        this.consumer = consumer;
    }

    /**
     * 读取出现异常处理
     *
     * @param e
     * @param analysisContext
     * @throws Exception
     */
    @Override
    public void onException(Exception e, AnalysisContext analysisContext) throws Exception {

    }

    /**
     * 处理表头
     *  @param map
     * @param analysisContext
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> map, AnalysisContext analysisContext) {

    }

    /**
     * 读取数据,每一条数据解析都会来调用
     *
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            try {
                // 必须要捕获异常，否则列表不会清空
                saveData();
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 存储完成清理 list
            cachedDataList = new ArrayList<>(BATCH_COUNT);
        }
    }

    /**
     * 读取完成
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        return true;
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        consumer.accept(cachedDataList);
        log.info("存储数据库成功！");
    }
}

 src/main/java/com/mindskip/xzs/excel/FixedMergeCellStrategy.java

New file
@@ -0,0 +1,45 @@
package com.mindskip.xzs.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * @author：xp
 * @date：2024/3/16 11:20
 */
public class FixedMergeCellStrategy extends AbstractMergeStrategy {

    /**
     * 起始位置
     */
    private Integer startRow;

    /**
     * 合并多少行
     */
    private Integer mergeRowNumber;

    /**
     * 哪些列需要合并行
     */
    private List<Integer> mergeWhichColumn;

    public FixedMergeCellStrategy(Integer startRow, Integer mergeRowNumber, List<Integer> mergeWhichColumn) {
        this.startRow = startRow;
        this.mergeRowNumber = mergeRowNumber;
        this.mergeWhichColumn = mergeWhichColumn;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        for (Integer whichColumn : mergeWhichColumn) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, startRow + mergeRowNumber - 1, whichColumn - 1, whichColumn - 1);
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }
    }
}

 src/main/java/com/mindskip/xzs/excel/SelectExcel.java

@@ -22,8 +22,22 @@
 */
public class SelectExcel implements CellWriteHandler {

    private String[] questionTypeList = {"单选", "多选", "判断"};
    private String[] subjectTypeList = {"a","b"};
    /**
     * 题目类型
     */
    private String[] questionTypeList = {"单选题", "多选题", "判断题"};
    /**
     * 课目
     */
    private List<String> subjectNameList;
    /**
     * 选项下拉数据
     */
    private String[] optionList = {"A","B","C","D","E","F","G","H"};

    public SelectExcel(List subjectNameList) {
        this.subjectNameList = subjectNameList;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
@@ -34,20 +48,34 @@
        // 获取数据校验helper
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        // 设置题目下拉范围，第一列，500行内
        CellRangeAddressList questionTypeRange = new CellRangeAddressList(0, 500, 0, 0);
        // 设置题目下拉范围，第一列，1500行内
        CellRangeAddressList questionTypeRange = new CellRangeAddressList(1, 1500, 0, 0);
        // 添加题目下拉
        DataValidationConstraint questionConstraint = dataValidationHelper.createExplicitListConstraint(questionTypeList);
        DataValidation questionValidation = dataValidationHelper.createValidation(questionConstraint, questionTypeRange);
        sheet.addValidationData(questionValidation);


        // 设置课目下拉范围，第2列，500行内
        CellRangeAddressList subjectTypeRange = new CellRangeAddressList(0, 500, 1, 1);
        // 设置课目下拉范围，第2列，1500行内
        CellRangeAddressList subjectTypeRange = new CellRangeAddressList(1, 1500, 1, 1);
        // 添加课目下拉
        DataValidationConstraint subjectConstraint = dataValidationHelper.createExplicitListConstraint(subjectTypeList);
        DataValidationConstraint subjectConstraint = dataValidationHelper.createExplicitListConstraint(subjectNameList.toArray((new String[0])));
        DataValidation subjectValidation = dataValidationHelper.createValidation(subjectConstraint, subjectTypeRange);
        sheet.addValidationData(subjectValidation);

        // 设置选项下拉范围，第4列，1500行内
        CellRangeAddressList optionRange = new CellRangeAddressList(2, 1500, 3, 3);
        // 添加选项下拉
        DataValidationConstraint optionConstraint = dataValidationHelper.createExplicitListConstraint(optionList);
        DataValidation optionValidation = dataValidationHelper.createValidation(optionConstraint, optionRange);
        sheet.addValidationData(optionValidation);

        // 设置选项下拉范围，第4列，1500行内
        CellRangeAddressList answerRange = new CellRangeAddressList(2, 1500, 5, 5);
        // 添加选项下拉
        DataValidationConstraint answerConstraint = dataValidationHelper.createExplicitListConstraint(optionList);
        DataValidation answerValidation = dataValidationHelper.createValidation(answerConstraint, answerRange);
        sheet.addValidationData(answerValidation);

    }
}

 src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java

@@ -156,27 +156,27 @@
        questionEditRequestVM.setTitle(questionObject.getTitleContent());

        //答案
//        QuestionTypeEnum questionTypeEnum = QuestionTypeEnum.fromCode(question.getQuestionType());
//        switch (questionTypeEnum) {
//            case SingleChoice:
//            case TrueFalse:
//                questionEditRequestVM.setCorrect(question.getCorrect());
//                break;
//            case MultipleChoice:
//                questionEditRequestVM.setCorrectArray(ExamUtil.contentToArray(question.getCorrect()));
//                break;
//            case GapFilling:
//                List<String> correctContent = questionObject.getQuestionItemObjects().stream().map(d -> d.getContent()).collect(Collectors.toList());
//                questionEditRequestVM.setCorrectArray(correctContent);
//                break;
//            case ShortAnswer:
//                questionEditRequestVM.setCorrect(questionObject.getCorrect());
//                break;
//            default:
//                break;
//        }
//        questionEditRequestVM.setScore(ExamUtil.scoreToVM(question.getScore()));
//        questionEditRequestVM.setAnalyze(questionObject.getAnalyze());
        QuestionTypeEnum questionTypeEnum = QuestionTypeEnum.fromCode(question.getQuestionType());
        switch (questionTypeEnum) {
            case SingleChoice:
            case TrueFalse:
                questionEditRequestVM.setCorrect(question.getCorrect());
                break;
            case MultipleChoice:
                questionEditRequestVM.setCorrectArray(ExamUtil.contentToArray(question.getCorrect()));
                break;
            case GapFilling:
                List<String> correctContent = questionObject.getQuestionItemObjects().stream().map(d -> d.getContent()).collect(Collectors.toList());
                questionEditRequestVM.setCorrectArray(correctContent);
                break;
            case ShortAnswer:
                questionEditRequestVM.setCorrect(questionObject.getCorrect());
                break;
            default:
                break;
        }
        questionEditRequestVM.setScore(ExamUtil.scoreToVM(question.getScore()));
        questionEditRequestVM.setAnalyze(questionObject.getAnalyze());


        //题目项映射

 src/main/java/com/mindskip/xzs/viewmodel/admin/question/ExamQuestionVO.java

@@ -28,21 +28,43 @@
     */
    private String title;

    /**
     * 分数
     */
    private String score;

    /**
     * 解析
     */
    private String analyze;

    /**
     * 难度
     */
    private Integer difficult;

    /**
     * 多选题答案
     */
    private List<String> correctArray;

    /**
     * 单选答案
     */
    private String correct;

    private String sbNames;

    private List<QuestionSubject> questionSubjects;

    //答案
    /**
     * 选项
     */
    private List<QuestionEditItemVM> items;

    private Integer itemOrder;

    private String department;


    private String a;
    private String b;
    private String c;
    private String d;

}

 src/main/java/com/mindskip/xzs/vo/OptionAndValueVO.java

New file
@@ -0,0 +1,20 @@
package com.mindskip.xzs.vo;

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

/**
 * 题目选项
 *
 * @author：xp
 * @date：2024/3/15 22:53
 */
@Data
public class OptionAndValueVO {

    @ExcelProperty({"题目选项", "选项"})
    private String optionName;

    @ExcelProperty({"题目选项", "选项值"})
    private String optionValue;
}

 src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java

@@ -2,8 +2,15 @@

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.mindskip.xzs.viewmodel.admin.question.QuestionEditItemVM;
import io.swagger.models.auth.In;
import lombok.Data;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.util.StringUtils;

import java.util.List;

/**
@@ -12,12 +19,13 @@
 */
@Data
@ColumnWidth(20)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)//内容样式
public class QuestionImportVO {

    @ExcelProperty("题目类型")
    private Integer questionType;
    private String questionType;

    @ExcelProperty("课目")
    @ExcelProperty("课目(多个用、隔开)")
    private String subjectName;

    @ColumnWidth(80)
@@ -25,36 +33,35 @@
    private String title;

    // 选项内容
    @ColumnWidth(60)
    @ExcelProperty("选项和答案")
    private List<QuestionEditItemVM> items;
    @ExcelProperty({"题目选项", "选项"})
    private String optionName;

    @ExcelProperty({"题目选项", "选项值"})
    private String optionValue;

    @ExcelProperty("答案(多个用、隔开)")
    private String correct;

    // 解析
    @ColumnWidth(30)
    @ExcelProperty("解析")
    private String analyze;

    @ExcelProperty("答案")
    private String correct;

    // 题目分数
    @ExcelProperty("题目分数")
    private String score;
    private Integer score;

    // 题目难度
    @ExcelProperty("题目难度")
    private Integer difficult;

    @ExcelProperty("选项A")
    private String a;

    @ExcelProperty("选项B")
    private String b;

    @ExcelProperty("选项C")
    private String c;

    @ExcelProperty("选项D")
    private String d;
    /**
     * 返回该条数据是不是题，因为还有选项。选项的这些值是空的
     * @return
     */
    public boolean master() {
        return StringUtils.hasText(questionType) && StringUtils.hasText(subjectName) && StringUtils.hasText(title);
    }

}