From 78b94b6a8eb94af251450a89b1f133e92423ec84 Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期二, 02 七月 2024 15:19:11 +0800
Subject: [PATCH] 题目导入导出
---
src/main/java/com/mindskip/xzs/service/QuestionService.java | 10 +
src/main/java/com/mindskip/xzs/repository/QuestionMapper.java | 13 ++
src/main/resources/mapper/QuestionMapper.xml | 37 ++++++
src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java | 6 +
src/main/java/com/mindskip/xzs/vo/QuestionExportData.java | 60 ++++++++++
src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java | 191 +++++++++++++++++--------------
6 files changed, 229 insertions(+), 88 deletions(-)
diff --git a/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java b/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java
index faa4c6c..6c0bb5c 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java
+++ b/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("閫塀");
- data.add(questionImportVO1);
-
- QuestionImportVO questionImportVO2 = new QuestionImportVO();
- questionImportVO2.setOptionName("C");
- questionImportVO2.setOptionValue("閫塁");
- data.add(questionImportVO2);
-
- QuestionImportVO questionImportVO3 = new QuestionImportVO();
- questionImportVO3.setOptionName("D");
- questionImportVO3.setOptionValue("閫塂");
- data.add(questionImportVO3);
// 鏌ュ嚭鎵�鏈夌殑璇剧洰锛坋xcel涓嬫媺鏁版嵁锛�
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鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
- String fileName = URLEncoder.encode("棰樼洰瀵煎嚭鏁版嵁", "UTF-8").replaceAll("\\+", "%20");
- response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
-
- // 鏌ュ嚭鎵�鏈夌殑璇剧洰锛坋xcel涓嬫媺鏁版嵁锛�
- 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鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+// String fileName = URLEncoder.encode("棰樼洰瀵煎嚭鏁版嵁", "UTF-8").replaceAll("\\+", "%20");
+// response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
+//
+// // 鏌ュ嚭鎵�鏈夌殑璇剧洰锛坋xcel涓嬫媺鏁版嵁锛�
+// 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鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+ String fileName = URLEncoder.encode("棰樼洰瀵煎嚭鏁版嵁", "UTF-8").replaceAll("\\+", "%20");
+ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
+
+ // 鏌ュ嚭鎵�鏈夌殑璇剧洰锛坋xcel涓嬫媺鏁版嵁锛�
+ EasyExcel.write(response.getOutputStream(), QuestionExportData.class)
+ .sheet("棰樼洰瀵煎嚭鏁版嵁")
+ .doWrite(exportData);
+ }
}
diff --git a/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java b/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java
index e8a2a9c..854d04c 100644
--- a/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java
+++ b/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);
+
+
+
}
diff --git a/src/main/java/com/mindskip/xzs/service/QuestionService.java b/src/main/java/com/mindskip/xzs/service/QuestionService.java
index b1318c4..7532cdc 100644
--- a/src/main/java/com/mindskip/xzs/service/QuestionService.java
+++ b/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);
+
}
diff --git a/src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java b/src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java
index 3e5ce6b..1edf777 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java
+++ b/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(
diff --git a/src/main/java/com/mindskip/xzs/vo/QuestionExportData.java b/src/main/java/com/mindskip/xzs/vo/QuestionExportData.java
new file mode 100644
index 0000000..50c9274
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/vo/QuestionExportData.java
@@ -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锛歺p
+ * @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;
+}
diff --git a/src/main/resources/mapper/QuestionMapper.xml b/src/main/resources/mapper/QuestionMapper.xml
index 6fe00b0..4129fa3 100644
--- a/src/main/resources/mapper/QuestionMapper.xml
+++ b/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,
--
Gitblit v1.8.0