From dcaea52b2c0778ec4f929f60752cc6baf37b5a50 Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期五, 29 三月 2024 13:50:43 +0800
Subject: [PATCH] 题目导出
---
src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java | 45 +++++++++++
src/main/java/com/mindskip/xzs/service/QuestionService.java | 3
src/main/java/com/mindskip/xzs/repository/QuestionMapper.java | 3
src/main/resources/mapper/QuestionMapper.xml | 32 +++++++
src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java | 3
src/main/java/com/mindskip/xzs/excel/RowItem.java | 18 ++++
src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java | 14 ++-
src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java | 61 +++++++++++++-
src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java | 43 ++++++++++
src/main/java/com/mindskip/xzs/vo/SubjectVO.java | 16 ++++
10 files changed, 223 insertions(+), 15 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 3614c69..12cad20 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java
+++ b/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java
@@ -18,9 +18,7 @@
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.excel.*;
import com.mindskip.xzs.repository.DepartmentMapper;
import com.mindskip.xzs.repository.SubjectMapper;
import com.mindskip.xzs.service.*;
@@ -30,6 +28,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.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataValidationHelper;
@@ -44,6 +43,7 @@
import javax.validation.Valid;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
+import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.Consumer;
@@ -194,16 +194,65 @@
.doWrite(data);
}
- @PostMapping("/question/export")
- public void importQuestion(QuestionPageRequestVM query, HttpServletResponse response) throws IOException {
+ @GetMapping("/question/export")
+ public void importQuestion(QuestionExportVO query, HttpServletResponse response) throws IOException {
+ query.formartTime();
// 鏌ヨ瀵煎嚭鏁版嵁
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");
- EasyExcel.write(response.getOutputStream(), QuestionImportVO.class).sheet("棰樼洰瀵煎嚭鏁版嵁").doWrite(exportData);
+
+ // 鏌ュ嚭鎵�鏈夌殑璇剧洰锛坋xcel涓嬫媺鏁版嵁锛�
+ 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 DynamicMergeCellStrategy(mergeRowList, Arrays.asList(0, 1, 2, 5, 6, 7, 8)))
+ .doWrite(exportList);
}
/**
diff --git a/src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java b/src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java
new file mode 100644
index 0000000..5ef30c5
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java
@@ -0,0 +1,45 @@
+package com.mindskip.xzs.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.write.merge.AbstractMergeStrategy;
+import lombok.Data;
+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锛歺p
+ * @date锛�2024/3/16 11:20
+ */
+public class DynamicMergeCellStrategy extends AbstractMergeStrategy {
+
+ /**
+ * 澶氬皯琛屽悎骞朵竴娆★紙璧峰浣嶇疆锛�
+ */
+ private List<RowItem> rowMergeList;
+
+ /**
+ * 鍝簺鍒楅渶瑕佸悎骞惰
+ */
+ private List<Integer> mergeWhichColumn;
+
+ public DynamicMergeCellStrategy(List<RowItem> rowMergeList, List<Integer> mergeWhichColumn) {
+ this.rowMergeList = rowMergeList;
+ this.mergeWhichColumn = mergeWhichColumn;
+ }
+
+ @Override
+ protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
+ // 鍙湁鍗曞厓鏍肩殑琛屽垪鍦ㄥ悎骞惰寖鍥村唴鎵嶅悎骞�
+ if (mergeWhichColumn.contains(cell.getColumnIndex())) {
+ rowMergeList.stream().forEach(rowItem -> {
+ if (rowItem.getStart() <= relativeRowIndex && rowItem.getEnd() >= relativeRowIndex) {
+ CellRangeAddress cellRangeAddress = new CellRangeAddress(rowItem.getStart(), rowItem.getEnd(), cell.getColumnIndex(), cell.getColumnIndex());
+ sheet.addMergedRegionUnsafe(cellRangeAddress);
+ }
+ });
+ }
+ }
+}
diff --git a/src/main/java/com/mindskip/xzs/excel/RowItem.java b/src/main/java/com/mindskip/xzs/excel/RowItem.java
new file mode 100644
index 0000000..b13ea98
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/excel/RowItem.java
@@ -0,0 +1,18 @@
+package com.mindskip.xzs.excel;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.RequiredArgsConstructor;
+
+/**
+ * @author锛歺p
+ * @date锛�2024/3/29 9:12
+ */
+@Data
+public class RowItem {
+
+ private Integer start;
+
+ private Integer end;
+
+}
diff --git a/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java b/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java
index 2972edb..dbb8924 100644
--- a/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/QuestionMapper.java
@@ -3,6 +3,7 @@
import com.mindskip.xzs.domain.other.KeyValue;
import com.mindskip.xzs.domain.Question;
import com.mindskip.xzs.viewmodel.admin.question.QuestionPageRequestVM;
+import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -23,5 +24,5 @@
List<Question> getAll();
- List<QuestionImportVO> export(@Param("query") QuestionPageRequestVM query);
+ List<QuestionImportVO> export(@Param("query") QuestionExportVO query);
}
diff --git a/src/main/java/com/mindskip/xzs/service/QuestionService.java b/src/main/java/com/mindskip/xzs/service/QuestionService.java
index be5706a..a098ffc 100644
--- a/src/main/java/com/mindskip/xzs/service/QuestionService.java
+++ b/src/main/java/com/mindskip/xzs/service/QuestionService.java
@@ -5,6 +5,7 @@
import com.mindskip.xzs.viewmodel.admin.question.QuestionEditRequestVM;
import com.mindskip.xzs.viewmodel.admin.question.QuestionPageRequestVM;
import com.github.pagehelper.PageInfo;
+import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.apache.ibatis.annotations.Param;
@@ -37,5 +38,5 @@
* @param query
* @return
*/
- List<QuestionImportVO> export(QuestionPageRequestVM query);
+ List<QuestionImportVO> export(QuestionExportVO query);
}
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 fd2cddd..7e8b0cc 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java
@@ -25,6 +25,7 @@
import com.mindskip.xzs.viewmodel.admin.question.QuestionPageRequestVM;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
+import com.mindskip.xzs.vo.QuestionExportVO;
import com.mindskip.xzs.vo.QuestionImportVO;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
@@ -253,7 +254,7 @@
@Override
- public List<QuestionImportVO> export(QuestionPageRequestVM query) {
+ public List<QuestionImportVO> export(QuestionExportVO query) {
return questionMapper.export(query);
}
}
diff --git a/src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java b/src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java
new file mode 100644
index 0000000..b3a8c13
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java
@@ -0,0 +1,43 @@
+package com.mindskip.xzs.vo;
+
+import com.fasterxml.jackson.annotation.JsonFormat;
+import com.mindskip.xzs.utility.DateTimeUtil;
+import lombok.Data;
+import org.springframework.util.StringUtils;
+
+import java.util.Date;
+
+/**
+ * @author锛歺p
+ * @date锛�2024/3/28 16:40
+ */
+@Data
+public class QuestionExportVO {
+
+ /**
+ * 棰樺瀷
+ */
+ private Integer questionType;
+
+ /**
+ * 寮�濮嬫椂闂�
+ */
+ private String startStr;
+ private Date start;
+
+ /**
+ * 缁撴潫鏃堕棿
+ */
+ private String endStr;
+ private Date end;
+
+ public void formartTime() {
+ if (StringUtils.hasText(startStr)) {
+ start = DateTimeUtil.parse(startStr, "yyyy-MM-dd HH:mm:ss");
+ }
+ if (StringUtils.hasText(endStr)) {
+ end = DateTimeUtil.parse(endStr, "yyyy-MM-dd HH:mm:ss");
+ }
+ }
+
+}
diff --git a/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java b/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java
index 390e872..4c6035e 100644
--- a/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java
+++ b/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java
@@ -6,10 +6,8 @@
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 com.mindskip.xzs.domain.vo.QuestionSubjectVO;
import lombok.Data;
-import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.util.StringUtils;
import java.util.List;
@@ -28,12 +26,21 @@
@ExcelProperty("璇剧洰(澶氫釜鐢ㄣ�侀殧寮�)")
private String subjectName;
+
@ExcelIgnore
private List<Integer> subjectIds;
+ @ExcelIgnore
+ private List<String> subjectList;
@ColumnWidth(80)
@ExcelProperty("棰樺共")
private String title;
+
+ /***
+ * 棰樼洰鍐呭
+ */
+ @ExcelIgnore
+ private String questionContent;
// 閫夐」鍐呭
@ExcelProperty({"棰樼洰閫夐」", "閫夐」"})
@@ -49,7 +56,6 @@
@ColumnWidth(30)
@ExcelProperty("瑙ｆ瀽")
private String analyze;
-
// 棰樼洰鍒嗘暟
@ExcelProperty("棰樼洰鍒嗘暟")
diff --git a/src/main/java/com/mindskip/xzs/vo/SubjectVO.java b/src/main/java/com/mindskip/xzs/vo/SubjectVO.java
new file mode 100644
index 0000000..5b22752
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/vo/SubjectVO.java
@@ -0,0 +1,16 @@
+package com.mindskip.xzs.vo;
+
+import lombok.Data;
+
+/**
+ * @author锛歺p
+ * @date锛�2024/3/28 8:57
+ */
+@Data
+public class SubjectVO {
+
+ private Long id;
+
+ private String subjectName;
+
+}
diff --git a/src/main/resources/mapper/QuestionMapper.xml b/src/main/resources/mapper/QuestionMapper.xml
index 6a2e725..fe253d0 100644
--- a/src/main/resources/mapper/QuestionMapper.xml
+++ b/src/main/resources/mapper/QuestionMapper.xml
@@ -244,11 +244,39 @@
</select>
<select id="export" resultMap="exportMap">
- SELECT * FROM t_question q INNER JOIN t_question_subject qs on q.id = qs.question_id
+ SELECT
+ q.*, ttc.content
+ FROM
+ t_question q
+ INNER JOIN t_text_content ttc on q.info_text_content_id = ttc.id AND q.deleted = 0
+ <where>
+ <if test="query.questionType != null">
+ q.question_type = #{query.questionType}
+ </if>
+ <if test="query.start != null and query.end != null">
+ q.create_time between #{query.start} and #{query.end}
+ </if>
+ </where>
</select>
<resultMap id="exportMap" type="com.mindskip.xzs.vo.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="selectSubjects" 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 = #{id} AND tqs.deleted = 0 AND ts.deleted = 0
+ </select>
+
</mapper>
--
Gitblit v1.8.0