题目导入导出

xiangpei

2024-07-02 78b94b6a8eb94af251450a89b1f133e92423ec84

题目导入导出

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

@@ -30,6 +30,7 @@
import com.mindskip.xzs.viewmodel.admin.education.SubjectPageRequestVM;
import com.mindskip.xzs.viewmodel.admin.question.*;
import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.vo.QuestionExportData;
import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.commons.lang3.StringUtils;
@@ -159,103 +160,75 @@

        // 构建模板样例数据
        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(new ArrayList<>(), Boolean.TRUE);
        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());

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

    @PostMapping("/question/export")
    public void exportQuestion(@RequestBody QuestionExportVO query, HttpServletResponse response) throws IOException {
        // 查询导出数据
        List<QuestionImportVO> exportData = questionService.export(query);
        // 构建数据
        List<QuestionImportVO> exportList = new ArrayList<>(exportData.size() * 4);
        // 行合并规则
        List<RowItem> mergeRowList = new ArrayList<>(exportData.size());
        int j = 2;
        for (QuestionImportVO data : exportData) {
            QuestionObject questionContent = JSON.parseObject(data.getQuestionContent(), QuestionObject.class);

            RowItem rowItem = new RowItem();
            rowItem.setStart(j);
            int end = j + questionContent.getQuestionItemObjects().size() - 1;
            rowItem.setEnd(end);
            mergeRowList.add(rowItem);
            j = end + 1;
            int i = 0;
            for (QuestionItemObject option : questionContent.getQuestionItemObjects()) {
                if (i == 0) {
                    QuestionImportVO master = new QuestionImportVO();
                    BeanUtils.copyProperties(data, master);
                    if (org.springframework.util.StringUtils.hasText(data.getQuestionType())) {
                        master.setQuestionType(QuestionTypeEnum.fromCode(Integer.valueOf(data.getQuestionType())).getName());
                    }
                    master.setOptionName(option.getPrefix());
                    master.setOptionValue(option.getContent());
                    master.setTitle(questionContent.getTitleContent());
                    master.setAnalyze(questionContent.getAnalyze());
                    master.setSubjectName(data.getSubjectList().stream().collect(Collectors.joining("、")));
                    master.setCorrect(data.getCorrect().replaceAll(",", "、"));
                    BigDecimal score = BigDecimal.valueOf(master.getScore());
                    master.setScore(score.divide(BigDecimal.TEN).intValue());
                    exportList.add(master);
                } else {
                    QuestionImportVO optionItem = new QuestionImportVO();
                    optionItem.setOptionName(option.getPrefix());
                    optionItem.setOptionValue(option.getContent());
                    exportList.add(optionItem);
                }
                i++;
            }
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("题目导出数据", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 查出所有的课目（excel下拉数据）
        List<Subject> subjects = subjectMapper.allSubject(new ArrayList<>(), Boolean.TRUE);
        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());
        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class)
                .sheet("题目导出数据")
                .registerWriteHandler(new SelectExcel(subjectNameList))
                .registerWriteHandler(new DynamicMergeCellStrategy(mergeRowList, Arrays.asList(0, 1, 2, 5, 6, 7, 8)))
                .doWrite(exportList);
    }
//    @PostMapping("/question/export")
//    public void exportQuestion(@RequestBody QuestionExportVO query, HttpServletResponse response) throws IOException {
//        // 查询导出数据
//        List<QuestionImportVO> exportData = questionService.export(query);
//        // 构建数据
//        List<QuestionImportVO> exportList = new ArrayList<>(exportData.size() * 4);
//        // 行合并规则
//        List<RowItem> mergeRowList = new ArrayList<>(exportData.size());
//        int j = 2;
//        for (QuestionImportVO data : exportData) {
//            QuestionObject questionContent = JSON.parseObject(data.getQuestionContent(), QuestionObject.class);
//
//            RowItem rowItem = new RowItem();
//            rowItem.setStart(j);
//            int end = j + questionContent.getQuestionItemObjects().size() - 1;
//            rowItem.setEnd(end);
//            mergeRowList.add(rowItem);
//            j = end + 1;
//            int i = 0;
//            for (QuestionItemObject option : questionContent.getQuestionItemObjects()) {
//                if (i == 0) {
//                    QuestionImportVO master = new QuestionImportVO();
//                    BeanUtils.copyProperties(data, master);
//                    if (org.springframework.util.StringUtils.hasText(data.getQuestionType())) {
//                        master.setQuestionType(QuestionTypeEnum.fromCode(Integer.valueOf(data.getQuestionType())).getName());
//                    }
//                    master.setOptionName(option.getPrefix());
//                    master.setOptionValue(option.getContent());
//                    master.setTitle(questionContent.getTitleContent());
//                    master.setAnalyze(questionContent.getAnalyze());
//                    master.setSubjectName(data.getSubjectList().stream().collect(Collectors.joining("、")));
//                    master.setCorrect(data.getCorrect().replaceAll(",", "、"));
//                    BigDecimal score = BigDecimal.valueOf(master.getScore());
//                    master.setScore(score.divide(BigDecimal.TEN).intValue());
//                    exportList.add(master);
//                } else {
//                    QuestionImportVO optionItem = new QuestionImportVO();
//                    optionItem.setOptionName(option.getPrefix());
//                    optionItem.setOptionValue(option.getContent());
//                    exportList.add(optionItem);
//                }
//                i++;
//            }
//        }
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//        response.setCharacterEncoding("utf-8");
//        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
//        String fileName = URLEncoder.encode("题目导出数据", "UTF-8").replaceAll("\\+", "%20");
//        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//
//        // 查出所有的课目（excel下拉数据）
//        List<Subject> subjects = subjectMapper.allSubject(new ArrayList<>(), Boolean.TRUE);
//        List<String> subjectNameList = subjects.stream().map(Subject::getName).collect(Collectors.toList());
//        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class)
//                .sheet("题目导出数据")
//                .registerWriteHandler(new SelectExcel(subjectNameList))
//                .registerWriteHandler(new DynamicMergeCellStrategy(mergeRowList, Arrays.asList(0, 1, 2, 5, 6, 7, 8)))
//                .doWrite(exportList);
//    }

    /**
     * 题目导入
@@ -429,4 +402,46 @@
        }
        return RestResponse.ok();
    }

    @PostMapping("/question/export")
    public void exportQuestion(@RequestBody QuestionExportVO query, HttpServletResponse response) throws IOException {
        // 查询导出数据
        List<QuestionExportData> exportData = questionService.exportData(query);
        // 处理完善数据
        exportData.stream().forEach(question -> {
            if (StringUtils.isNotBlank(question.getContent())) {
                QuestionObject questionContent = JSON.parseObject(question.getContent(), QuestionObject.class);
                // 设置选项
                for (QuestionItemObject option : questionContent.getQuestionItemObjects()) {
                    String optionContent = option.getContent();
                    if ("A".equals(option.getPrefix())) {
                        question.setOptionA(optionContent);
                    } else if ("B".equals(option.getPrefix())) {
                        question.setOptionB(optionContent);
                    } else if ("C".equals(option.getPrefix())) {
                        question.setOptionC(optionContent);
                    } else if ("D".equals(option.getPrefix())) {
                        question.setOptionD(optionContent);
                    }
                }
                // 设置题干、解析
                question.setAnalyze(questionContent.getAnalyze());
                question.setTitle(questionContent.getTitleContent());
                // 设置课目
                question.setSubject(question.getSubjectList().stream().collect(Collectors.joining(",")));
                // 设置题型
                question.setQuestionType(QuestionTypeEnum.fromCode(Integer.valueOf(question.getQuestionType())).getName());
            }
        });
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("题目导出数据", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 查出所有的课目（excel下拉数据）
        EasyExcel.write(response.getOutputStream(), QuestionExportData.class)
                .sheet("题目导出数据")
                .doWrite(exportData);
    }
}

 src/main/java/com/mindskip/xzs/repository/QuestionMapper.java

@@ -1,11 +1,13 @@
package com.mindskip.xzs.repository;

import com.mindskip.xzs.domain.Question;
import com.mindskip.xzs.domain.Subject;
import com.mindskip.xzs.domain.other.KeyValue;
import com.mindskip.xzs.domain.vo.QuestionVO;
import com.mindskip.xzs.viewmodel.admin.question.QuestionPageRequestVM;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentRequestVM;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentResponseVM;
import com.mindskip.xzs.vo.QuestionExportData;
import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.ibatis.annotations.Mapper;
@@ -28,6 +30,14 @@

    List<Question> getAll();

    /**
     * 查询导出数据
     *
     * @param query
     * @return
     */
    List<QuestionExportData> exportData(@Param("query") QuestionExportVO query);

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

    List<QuestionPageStudentResponseVM> selectQuestion(QuestionPageStudentRequestVM model);
@@ -35,4 +45,7 @@
    QuestionVO selectContentById(Integer id);

    QuestionVO getAnswer(Integer id);



}

 src/main/java/com/mindskip/xzs/service/QuestionService.java

@@ -9,6 +9,7 @@
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionAnswerVO;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentRequestVM;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentResponseVM;
import com.mindskip.xzs.vo.QuestionExportData;
import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.ibatis.annotations.Param;
@@ -44,6 +45,14 @@
     */
    List<QuestionImportVO> export(QuestionExportVO query);

    /**
     * 题目导出数据
     *
     * @param query
     * @return
     */
    List<QuestionExportData> exportData(QuestionExportVO query);

    PageInfo<QuestionPageStudentResponseVM> selectQuestion(QuestionPageStudentRequestVM model);

    /** 查出题目主体内容 */
@@ -51,4 +60,5 @@

    /** 获取题目答案、解析 */
    RestResponse getAnswer(Integer id);

}

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

@@ -32,6 +32,7 @@
import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentRequestVM;
import com.mindskip.xzs.viewmodel.student.question.answer.QuestionPageStudentResponseVM;
import com.mindskip.xzs.vo.QuestionExportData;
import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import lombok.RequiredArgsConstructor;
@@ -298,6 +299,11 @@
    }

    @Override
    public List<QuestionExportData> exportData(QuestionExportVO query) {
        return questionMapper.exportData(query);
    }

    @Override
    public PageInfo<QuestionPageStudentResponseVM> selectQuestion(QuestionPageStudentRequestVM model) {
        return PageHelper.startPage(model.getPageIndex(), model.getPageSize()).doSelectPageInfo(() ->
                questionMapper.selectQuestion(model).stream().peek(

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

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

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

import java.util.List;

/**
 * @author：xp
 * @date：2024/7/2 14:05
 */
@Data
public class QuestionExportData {

    @ExcelIgnore
    private Integer id;

    @ExcelIgnore
    private String content;

    @ExcelIgnore
    private List<String> subjectList;

    @ExcelProperty(value = "题型")
    private String questionType;

    @ExcelProperty(value = "课目")
    @ColumnWidth(30)
    private String subject;

    @ExcelProperty(value = "题干")
    @ColumnWidth(40)
    private String title;

    @ExcelProperty(value = "选项A")
    @ColumnWidth(40)
    private String optionA;

    @ColumnWidth(40)
    @ExcelProperty(value = "选项B")
    private String optionB;

    @ColumnWidth(40)
    @ExcelProperty(value = "选项C")
    private String optionC;

    @ColumnWidth(40)
    @ExcelProperty(value = "选项D")
    private String optionD;

    @ColumnWidth(120)
    @ExcelProperty(value = "解析")
    private String analyze;

    @ColumnWidth(10)
    @ExcelProperty(value = "答案")
    private String answer;
}

 src/main/resources/mapper/QuestionMapper.xml

@@ -268,6 +268,36 @@
        </where>
    </select>

    <select id="exportData" resultMap="exportDataMap">
        SELECT
        DISTINCT
        q.id,
        q.question_type,
        q.correct as answer,
        ttc.content
        FROM
        t_question q
        INNER JOIN t_text_content ttc on q.info_text_content_id = ttc.id AND q.deleted = 0
        INNER JOIN t_question_subject tqs on q.id = tqs.question_id
        <where>
            <if test="query.subjectIds != null and query.subjectIds.size > 0">
                AND tqs.subject_id IN
                <foreach collection="query.subjectIds" open="(" separator="," close=")" item="subjectId">
                    #{subjectId}
                </foreach>
            </if>
        </where>
    </select>

    <resultMap id="exportDataMap"  type="com.mindskip.xzs.vo.QuestionExportData">
        <result column="question_type" property="questionType"/>
        <result column="title" property="title"/>
        <result column="analyze" property="analyze"/>
        <result column="content" property="content"/>
        <result column="correct" property="answer"/>
        <collection property="subjectList" column="id" ofType="string" select="selectSubjects"/>
    </resultMap>

    <resultMap id="exportMap" type="com.mindskip.xzs.vo.QuestionImportVO">
        <result column="question_type" property="questionType"/>
        <result column="title" property="title"/>
@@ -287,6 +317,13 @@
                            ON tqs.subject_id = ts.id AND tqs.question_id = #{id} AND tqs.deleted = 0 AND ts.deleted = 0
    </select>

    <select id="getQuestionSubjects" resultType="string">
        SELECT ts.name
        FROM t_question_subject tqs
                 INNER JOIN t_subject ts
                            ON tqs.subject_id = ts.id AND tqs.question_id = #{questionId} AND tqs.deleted = 0 AND ts.deleted = 0
    </select>

    <select id="getVoByIds" resultType="com.mindskip.xzs.domain.vo.QuestionVO">
        SELECT
        tq.id,