src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/excel/RowItem.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/repository/QuestionMapper.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/service/QuestionService.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/java/com/mindskip/xzs/vo/SubjectVO.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
src/main/resources/mapper/QuestionMapper.xml | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 |
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可以防止中文乱码 当然和easyexcel没有关系 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); // 查出所有的课目(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 DynamicMergeCellStrategy(mergeRowList, Arrays.asList(0, 1, 2, 5, 6, 7, 8))) .doWrite(exportList); } /** src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.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 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:xp * @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); } }); } } } src/main/java/com/mindskip/xzs/excel/RowItem.java
New file @@ -0,0 +1,18 @@ package com.mindskip.xzs.excel; import lombok.AllArgsConstructor; import lombok.Data; import lombok.RequiredArgsConstructor; /** * @author:xp * @date:2024/3/29 9:12 */ @Data public class RowItem { private Integer start; private Integer end; } 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); } 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); } 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); } } src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java
New file @@ -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:xp * @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"); } } } 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("题目分数") src/main/java/com/mindskip/xzs/vo/SubjectVO.java
New file @@ -0,0 +1,16 @@ package com.mindskip.xzs.vo; import lombok.Data; /** * @author:xp * @date:2024/3/28 8:57 */ @Data public class SubjectVO { private Long id; private String subjectName; } 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>