题目导入模板下载

xiangpei

2024-03-24 54a64e9a13344e39470de8eff1d28a0eec669cf6

题目导入模板下载

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

@@ -194,6 +194,18 @@
                .doWrite(data);
    }

    @PostMapping("/question/export")
    public void importQuestion(QuestionPageRequestVM query, HttpServletResponse response) throws IOException {
        // 查询导出数据
        List<QuestionImportVO> exportData = questionService.export(query);
        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);
    }

    /**
     * 题目导入
     *

 src/main/java/com/mindskip/xzs/excel/SelectExcel.java

@@ -2,16 +2,11 @@

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
@@ -45,7 +40,7 @@
        Sheet sheet = writeSheetHolder.getSheet();


        // 获取数据校验helper
        // 获取数据校验helper，excel的下拉就是通过数据校验设置
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        // 设置题目下拉范围，第一列，1500行内

 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.QuestionImportVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -21,4 +22,6 @@
    List<KeyValue> selectCountByDate(@Param("startTime") Date startTime,@Param("endTime") Date endTime);

    List<Question> getAll();

    List<QuestionImportVO> export(@Param("query") QuestionPageRequestVM 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.QuestionImportVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;
@@ -30,4 +31,11 @@
    List<Question> getAll();

    List<Question> selectByIds(@Param("ids") List<Integer> ids);

    /**
     * 题目导出数据
     * @param query
     * @return
     */
    List<QuestionImportVO> export(QuestionPageRequestVM 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.QuestionImportVO;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -251,4 +252,8 @@
    }


    @Override
    public List<QuestionImportVO> export(QuestionPageRequestVM query) {
        return questionMapper.export(query);
    }
}

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

@@ -1,5 +1,6 @@
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 com.alibaba.excel.annotation.write.style.ContentStyle;
@@ -27,6 +28,8 @@

    @ExcelProperty("课目(多个用、隔开)")
    private String subjectName;
    @ExcelIgnore
    private List<Integer> subjectIds;

    @ColumnWidth(80)
    @ExcelProperty("题干")

 src/main/resources/mapper/QuestionMapper.xml

@@ -242,4 +242,13 @@
    SELECT <include refid="Base_Column_List"/>
        from t_question where  deleted=0
  </select>

  <select id="export" resultMap="exportMap">
    SELECT * FROM t_question q INNER JOIN t_question_subject qs on q.id = qs.question_id
  </select>

  <resultMap id="exportMap" type="com.mindskip.xzs.vo.QuestionImportVO">

  </resultMap>

</mapper>