xiangpei
2024-03-29 dcaea52b2c0778ec4f929f60752cc6baf37b5a50
题目导出
6个文件已修改
4个文件已添加
238 ■■■■■ 已修改文件
src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java 61 ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/excel/DynamicMergeCellStrategy.java 45 ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/excel/RowItem.java 18 ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/repository/QuestionMapper.java 3 ●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/service/QuestionService.java 3 ●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/service/impl/QuestionServiceImpl.java 3 ●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/vo/QuestionExportVO.java 43 ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java 14 ●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/java/com/mindskip/xzs/vo/SubjectVO.java 16 ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史
src/main/resources/mapper/QuestionMapper.xml 32 ●●●●● 补丁 | 查看 | 原始文档 | 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>