feat：题目导入

龚焕茏

2024-07-09 9797dca8f3a12c0e72942b8043c1b225bde2bbaf

feat：题目导入

 src/main/java/com/ycl/jxkg/controller/admin/QuestionController.java

@@ -8,8 +8,8 @@
import com.ycl.jxkg.domain.vo.admin.question.QuestionEditRequestVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionPageRequestVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionResponseVO;
import com.ycl.jxkg.service.QuestionService;
import com.ycl.jxkg.enums.QuestionTypeEnum;
import com.ycl.jxkg.service.QuestionService;
import com.ycl.jxkg.utils.ErrorUtil;
import com.ycl.jxkg.utils.HtmlUtil;
import com.ycl.jxkg.utils.JsonUtil;
@@ -18,7 +18,9 @@
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

@RequiredArgsConstructor
@@ -89,4 +91,14 @@
        return Result.ok("操作成功");
    }

    @GetMapping("/download/importTemplate")
    public void importTemplate(HttpServletResponse response) {
        questionService.importTemplate(response);
    }

    @PostMapping("/import")
    public Result<Boolean> importPaper(MultipartFile file) {
        return questionService.importQuestion(file);
    }

}

 src/main/java/com/ycl/jxkg/domain/vo/admin/exam/ExamPaperImportVO.java

New file
@@ -0,0 +1,72 @@
package com.ycl.jxkg.domain.vo.admin.exam;

import com.alibaba.excel.annotation.ExcelIgnore;
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 lombok.Data;
import org.springframework.util.StringUtils;

/**
 * @author gonghl
 * @since 2024-7-4
 */
@Data
@ColumnWidth(20)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class ExamPaperImportVO {

    @ExcelProperty("标题")
    private String label;

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

    @ExcelProperty("科目")
    private String subject;

    @ColumnWidth(80)
    @ExcelProperty("题干")
    private String title;

    /** 题目内容 **/
    @ExcelIgnore
    private String questionContent;

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

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

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

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

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

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

    /**
     * 返回该条数据是不是题，因为还有选项。选项的这些值是空的
     */
    public boolean master() {
        return StringUtils.hasText(questionType) && StringUtils.hasText(label);
    }

    public boolean intact() {
        return StringUtils.hasText(title) && StringUtils.hasText(correct);
    }

}

 src/main/java/com/ycl/jxkg/domain/vo/admin/exam/QuestionImportVO.java

@@ -9,8 +9,6 @@
import lombok.Data;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * @author gonghl
 * @since 2024-7-4
@@ -20,19 +18,11 @@
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class QuestionImportVO {

    @ExcelProperty("标题")
    private String label;

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

    @ExcelProperty("科目")
    private String subject;

    @ExcelIgnore
    private List<Integer> subjectIds;
    @ExcelIgnore
    private List<String> subjectList;

    @ColumnWidth(80)
    @ExcelProperty("题干")
@@ -68,10 +58,6 @@
    /**
     * 返回该条数据是不是题，因为还有选项。选项的这些值是空的
     */
    public boolean master() {
        return StringUtils.hasText(questionType) && StringUtils.hasText(label);
    }

    public boolean intact() {
        return StringUtils.hasText(title) && StringUtils.hasText(correct);
    }

 src/main/java/com/ycl/jxkg/excel/PaperSelectExcel.java

New file
@@ -0,0 +1,77 @@
package com.ycl.jxkg.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;

/**
 * 设置easy-excel下拉处理器
 *
 * @author：xp
 * @date：2024/3/15 17:27
 */
public class PaperSelectExcel implements CellWriteHandler {

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

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

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 获取sheet对象
        Sheet sheet = writeSheetHolder.getSheet();


        // 获取数据校验helper，excel的下拉就是通过数据校验设置
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

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


        // 设置课目下拉范围，第2列，1500行内
        CellRangeAddressList subjectTypeRange = new CellRangeAddressList(1, 1500, 2, 2);
        // 添加课目下拉
        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, 4, 4);
        // 添加选项下拉
        DataValidationConstraint optionConstraint = dataValidationHelper.createExplicitListConstraint(optionList);
        DataValidation optionValidation = dataValidationHelper.createValidation(optionConstraint, optionRange);
        sheet.addValidationData(optionValidation);

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

    }
}

 src/main/java/com/ycl/jxkg/mapper/QuestionMapper.java

@@ -4,8 +4,8 @@
import com.ycl.jxkg.domain.entity.Question;
import com.ycl.jxkg.domain.other.KeyValue;
import com.ycl.jxkg.domain.question.RandomQuestionDTO;
import com.ycl.jxkg.domain.vo.admin.exam.ExamPaperImportVO;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionExportVO;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionImportVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionPageRequestVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -44,6 +44,6 @@
     */
    List<Question> getRandomQuestion(@Param("subjectId") Integer subjectId, @Param("questionType") Integer questionType,@Param("difficult") Integer difficult, @Param("num") Integer num);

    List<QuestionImportVO> export(@Param("query") QuestionExportVO query);
    List<ExamPaperImportVO> export(@Param("query") QuestionExportVO query);

}

 src/main/java/com/ycl/jxkg/service/QuestionService.java

@@ -2,13 +2,16 @@

import com.baomidou.mybatisplus.extension.service.IService;
import com.github.pagehelper.PageInfo;
import com.ycl.jxkg.base.Result;
import com.ycl.jxkg.domain.entity.Question;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionExportVO;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionImportVO;
import com.ycl.jxkg.domain.vo.admin.exam.ExamPaperImportVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionEditRequestVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionPageRequestVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionResponseVO;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

public interface QuestionService extends IService<Question> {
@@ -39,6 +42,19 @@
     * @param query 查询条件
     * @return 题目
     */
    List<QuestionImportVO> export(QuestionExportVO query);
    List<ExamPaperImportVO> export(QuestionExportVO query);

    /**
     * 导入题目
     * @param file excel
     * @return 导入结果
     */
    Result<Boolean> importQuestion(MultipartFile file);

    /**
     * 下载导入题目模板
     * @param response 结果
     */
    void importTemplate(HttpServletResponse response);

}

 src/main/java/com/ycl/jxkg/service/impl/ExamPaperServiceImpl.java

@@ -350,9 +350,10 @@
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 构建模板样例数据
        List<QuestionImportVO> data = new ArrayList<>(8);
        QuestionImportVO questionImportVO = new QuestionImportVO();
        List<ExamPaperImportVO> data = new ArrayList<>(8);
        ExamPaperImportVO questionImportVO = new ExamPaperImportVO();
        questionImportVO.setLabel("标题1");
        questionImportVO.setSubject("语文");
        questionImportVO.setQuestionType("单选题");
        questionImportVO.setDifficult(2);
        questionImportVO.setCorrect("A");
@@ -363,23 +364,27 @@
        questionImportVO.setOptionValue("选我");
        data.add(questionImportVO);

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

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

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

        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class)
        // 查出所有的课目（excel下拉数据）
        List<Subject> subjects = subjectService.list();
        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());
        EasyExcel.write(response.getOutputStream(), ExamPaperImportVO.class)
                .sheet("模板")
                .registerWriteHandler(new PaperSelectExcel(subjectNameList))
                .registerWriteHandler(new FixedMergeCellStrategy(2, 4, Arrays.asList(2, 3, 6, 7, 8, 9)))
                .registerWriteHandler(new FixedMergeCellStrategy(2, 20, Arrays.asList(0, 1)))
                .doWrite(data);
@@ -389,13 +394,13 @@
    @SneakyThrows
    public void export(QuestionExportVO query, HttpServletResponse response) {
        // 查询导出数据
        List<QuestionImportVO> exportData = questionService.export(query);
        List<ExamPaperImportVO> exportData = questionService.export(query);
        // 构建数据
        List<QuestionImportVO> exportList = new ArrayList<>(exportData.size() * 4);
        List<ExamPaperImportVO> exportList = new ArrayList<>(exportData.size() * 4);
        // 行合并规则
        List<RowItem> mergeRowList = new ArrayList<>(exportData.size());
        int j = 2;
        for (QuestionImportVO data : exportData) {
        for (ExamPaperImportVO data : exportData) {
            QuestionObject questionContent = JSON.parseObject(data.getQuestionContent(), QuestionObject.class);

            RowItem rowItem = new RowItem();
@@ -407,7 +412,7 @@
            int i = 0;
            for (QuestionItemObject option : questionContent.getQuestionItemObjects()) {
                if (i == 0) {
                    QuestionImportVO master = new QuestionImportVO();
                    ExamPaperImportVO master = new ExamPaperImportVO();
                    BeanUtils.copyProperties(data, master);
                    if (org.springframework.util.StringUtils.hasText(data.getQuestionType())) {
                        master.setQuestionType(QuestionTypeEnum.fromCode(Integer.valueOf(data.getQuestionType())).getName());
@@ -421,7 +426,7 @@
                    master.setScore(score.divide(BigDecimal.TEN).intValue());
                    exportList.add(master);
                } else {
                    QuestionImportVO optionItem = new QuestionImportVO();
                    ExamPaperImportVO optionItem = new ExamPaperImportVO();
                    optionItem.setOptionName(option.getPrefix());
                    optionItem.setOptionValue(option.getContent());
                    exportList.add(optionItem);
@@ -438,7 +443,7 @@
        // 查出所有的课目（excel下拉数据）
        List<Subject> subjects = subjectService.list();
        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());
        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class)
        EasyExcel.write(response.getOutputStream(), ExamPaperImportVO.class)
                .sheet("题目导出数据")
                .registerWriteHandler(new SelectExcel(subjectNameList))
                .registerWriteHandler(new DynamicMergeCellStrategy(mergeRowList, Arrays.asList(0, 1, 2, 5, 6, 7, 8)))
@@ -459,11 +464,11 @@
        // 一张试卷多个标题
        ArrayList<PaperFixQuestionDTO> list = new ArrayList<>();

        Consumer<List<QuestionImportVO>> consumer = (data) -> {
        Consumer<List<ExamPaperImportVO>> consumer = (data) -> {
            // 循环每一行
            for (int i = 0; i < data.size(); i++) {
                // 读取的题目
                QuestionImportVO excelQuestion = data.get(i);
                ExamPaperImportVO excelQuestion = data.get(i);
                String questionType = excelQuestion.getQuestionType();
                String label = excelQuestion.getLabel();
                // 判断是否标题
@@ -491,7 +496,7 @@
                                if (i + 1 == data.size()) {
                                    break;
                                }
                                QuestionImportVO nextQuestion = data.get(1 + i);
                                ExamPaperImportVO nextQuestion = data.get(1 + i);
                                if (nextQuestion.intact()) {
                                    break;
                                }
@@ -526,7 +531,7 @@
                            question.setDeleted(0);
                            question.setQuestionType(QuestionTypeEnum.get(questionType));
                            // 根据科目名称获取id
                            QuestionImportVO finalExcelQuestion = excelQuestion;
                            ExamPaperImportVO finalExcelQuestion = excelQuestion;
                            question.setSubjectId(subjects.stream().filter(subject -> subject.getName().equals(finalExcelQuestion.getSubject())).findFirst().get().getId());
                            questions.add(question);

@@ -537,6 +542,7 @@
                            paperQuestion.setScore(BigDecimal.valueOf(excelQuestion.getScore()));
                            paperQuestions.add(paperQuestion);
                        }
                        // 下一条数据是标题
                        if (i + 1 == data.size() || data.get(i + 1).master()) {
                            break;
                        }
@@ -552,7 +558,7 @@
                }
            }
        };
        EasyExcel.read(file.getInputStream(), QuestionImportVO.class, new CurrencyDataListener(consumer)).sheet("模板").doRead();
        EasyExcel.read(file.getInputStream(), ExamPaperImportVO.class, new CurrencyDataListener(consumer)).sheet("模板").doRead();
        // 保存题目
        questionService.saveBatch(questions);
        // 保存试卷

 src/main/java/com/ycl/jxkg/service/impl/QuestionServiceImpl.java

@@ -1,13 +1,18 @@
package com.ycl.jxkg.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.ycl.jxkg.base.Result;
import com.ycl.jxkg.domain.entity.Question;
import com.ycl.jxkg.domain.entity.Subject;
import com.ycl.jxkg.domain.other.KeyValue;
import com.ycl.jxkg.domain.question.QuestionItemObject;
import com.ycl.jxkg.domain.question.QuestionObject;
import com.ycl.jxkg.domain.vo.admin.exam.ExamPaperImportVO;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionExportVO;
import com.ycl.jxkg.domain.vo.admin.exam.QuestionImportVO;
import com.ycl.jxkg.domain.vo.admin.question.QuestionEditItemVO;
@@ -16,19 +21,30 @@
import com.ycl.jxkg.domain.vo.admin.question.QuestionResponseVO;
import com.ycl.jxkg.enums.QuestionTypeEnum;
import com.ycl.jxkg.enums.general.StatusEnum;
import com.ycl.jxkg.excel.CurrencyDataListener;
import com.ycl.jxkg.excel.FixedMergeCellStrategy;
import com.ycl.jxkg.excel.SelectExcel;
import com.ycl.jxkg.mapper.QuestionMapper;
import com.ycl.jxkg.service.QuestionService;
import com.ycl.jxkg.service.SubjectService;
import com.ycl.jxkg.utils.DateTimeUtil;
import com.ycl.jxkg.utils.ExamUtil;
import com.ycl.jxkg.utils.HtmlUtil;
import com.ycl.jxkg.utils.JsonUtil;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.function.Consumer;
import java.util.stream.Collectors;

@Service
@@ -36,7 +52,7 @@
public class QuestionServiceImpl extends ServiceImpl<QuestionMapper, Question> implements QuestionService {

    private final QuestionMapper questionMapper;

    private final SubjectService subjectService;

    @Override
    public PageInfo<QuestionResponseVO> page(QuestionPageRequestVO requestVM) {
@@ -182,8 +198,137 @@
    }

    @Override
    public List<QuestionImportVO> export(QuestionExportVO query) {
    public List<ExamPaperImportVO> export(QuestionExportVO query) {
        return questionMapper.export(query);
    }

    @Override
    @Transactional
    @SneakyThrows
    public Result<Boolean> importQuestion(MultipartFile file) {
        List<Subject> subjects = subjectService.list();
        // 题目集合用于批量保存
        ArrayList<Question> questions = new ArrayList<>();
        Consumer<List<QuestionImportVO>> consumer = (data) -> {
            // 循环每一行
            for (int i = 0; i < data.size(); i++) {
                // 读取的题目
                QuestionImportVO excelQuestion = data.get(i);
                String questionType = excelQuestion.getQuestionType();
                // 判断是否完整题目
                if (excelQuestion.intact()) {
                    // 循环题目
                    while (Boolean.TRUE) {
                        // 更新读取的题目
                        excelQuestion = data.get(i);
                        Question question = new Question();
                        // 该题的选项
                        List<QuestionItemObject> options = new ArrayList<>(8);
                        // 选项内容
                        QuestionItemObject option = new QuestionItemObject();
                        option.setPrefix(excelQuestion.getOptionName());
                        option.setContent(excelQuestion.getOptionValue());
                        options.add(option);
                        // 循环选项
                        while (Boolean.TRUE) {
                            // 判断是否是最后一条
                            if (i + 1 == data.size()) {
                                break;
                            }
                            QuestionImportVO nextQuestion = data.get(1 + i);
                            if (nextQuestion.intact()) {
                                break;
                            }
                            QuestionItemObject nextOption = new QuestionItemObject();
                            nextOption.setPrefix(nextQuestion.getOptionName());
                            nextOption.setContent(nextQuestion.getOptionValue());
                            options.add(nextOption);
                            i++;
                        }
                        // 保存题目内容
                        QuestionObject questionObject = new QuestionObject();
                        questionObject.setQuestionItemObjects(options);
                        questionObject.setAnalyze(excelQuestion.getAnalyze());
                        questionObject.setTitleContent(excelQuestion.getTitle());
                        questionObject.setCorrect(excelQuestion.getCorrect());
                        question.setTitle(excelQuestion.getTitle());
                        question.setContent(JSON.toJSONString(questionObject));
                        question.setQuestionType(QuestionTypeEnum.get(excelQuestion.getQuestionType()));
                        // 答案(多选需要用、分割保存字符串到数据库)
                        String[] corrects = excelQuestion.getCorrect().split("、");
                        if (corrects.length > 1) {
                            question.setCorrect(String.join(",", corrects));
                        } else {
                            question.setCorrect(excelQuestion.getCorrect());
                        }
                        // 难度
                        question.setDifficult(excelQuestion.getDifficult());
                        // 创建人
                        question.setCreateUser(2);
                        question.setStatus(StatusEnum.ENABLE);
                        question.setCreateTime(new Date());
                        question.setDeleted(0);
                        question.setQuestionType(QuestionTypeEnum.get(questionType));
                        // 根据科目名称获取id
                        QuestionImportVO finalExcelQuestion = excelQuestion;
                        question.setSubjectId(subjects.stream().filter(subject -> subject.getName().equals(finalExcelQuestion.getSubject())).findFirst().get().getId());
                        questions.add(question);
                        if (i + 1 == data.size() || data.get(i + 1).intact()) {
                            break;
                        }
                        i++;
                    }
                }
            }
        };
        EasyExcel.read(file.getInputStream(), QuestionImportVO.class, new CurrencyDataListener(consumer)).sheet("模板").doRead();
        // 保存题目
        return Result.ok(saveBatch(questions));
    }

    @Override
    @SneakyThrows
    public void importTemplate(HttpServletResponse response) {
        String fileName = URLEncoder.encode("试卷导入模板", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

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

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

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

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

        // 查出所有的课目（excel下拉数据）
        List<Subject> subjects = subjectService.list();
        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(0, 1, 2, 5, 6, 7, 8)))
                .doWrite(data);
    }

}

 src/main/resources/mapper/QuestionMapper.xml

@@ -128,33 +128,13 @@
        <collection property="subjectList" column="id" ofType="string" select="selectSubjects"/>
    </resultMap>

    <resultMap id="exportMap" type="com.ycl.jxkg.domain.vo.admin.exam.QuestionImportVO">
        <result column="question_type" property="questionType"/>
        <result column="title" property="title"/>
        <result column="analyze" property="analyze"/>
        <result column="score" property="score"/>
        <result column="difficult" property="difficult"/>
        <result column="content" property="questionContent"/>
        <result column="score" property="score"/>
        <result column="correct" property="correct"/>
        <collection property="subjectList" column="id" ofType="string" select="selectSubjects"/>
    </resultMap>

    <select id="export" resultMap="exportMap">
    <select id="export" resultType="com.ycl.jxkg.domain.vo.admin.exam.ExamPaperImportVO">
        SELECT
        DISTINCT
        q.*, ttc.content
        FROM
        t_question q
        INNER JOIN t_ex ttc on q.info_text_content_id = ttc.id AND q.deleted = 0
        <where>
            <if test="query.examPaperId != null">
                AND tqs.subject_id IN
                <foreach collection="query.subjectIds" open="(" separator="," close=")" item="subjectId">
                    #{subjectId}
                </foreach>
            </if>
        </where>
            q.*,
            s.NAME AS SUBJECT
        FROM t_question q
        LEFT JOIN t_subject s ON subject_id = s.id
        ORDER BY q.id
    </select>

</mapper>