题目导入模板，待完善

xiangpei

2024-03-15 f7196dff53805c9d5e6c945e5334ce4e4423d779

题目导入模板，待完善

 pom.xml

@@ -21,6 +21,7 @@
        <mysql.version>8.0.17</mysql.version>
        <spring.boot.version>2.1.6.RELEASE</spring.boot.version>
        <mapstruct.version>1.4.1.Final</mapstruct.version>
        <easyExcel.version>3.3.2</easyExcel.version>
    </properties>


@@ -69,6 +70,15 @@


    <dependencies>

        <!-- easy excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easyExcel.version}</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>

 src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java

@@ -79,7 +79,7 @@
                    .and().authenticationProvider(restAuthenticationProvider)
                    .authorizeRequests()
                    .antMatchers(securityIgnoreUrls.toArray(ignores)).permitAll()
                    .antMatchers("/api/admin/department/list", "/api/admin/video/getList","/api/admin/user/conversion","/api/admin/examPaperGrade/updates").permitAll()
                    .antMatchers("/api/admin/department/list", "/api/admin/video/getList","/api/admin/user/conversion","/api/admin/examPaperGrade/updates","/api/admin/question/download/question/import/temp").permitAll()
                    // todo 设置部门管理员可以看的请求
                    .antMatchers("/api/admin/**").hasAnyRole(RoleEnum.ADMIN.getName(), RoleEnum.DEPT_ADMIN.getName())
                    .antMatchers("/api/student/**").hasRole(RoleEnum.STUDENT.getName())

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

@@ -1,5 +1,7 @@
package com.mindskip.xzs.controller.admin;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.data.HyperlinkData;
import com.mindskip.xzs.base.BaseApiController;
import com.mindskip.xzs.base.RestResponse;
import com.mindskip.xzs.base.SystemCode;
@@ -9,6 +11,7 @@
import com.mindskip.xzs.domain.enums.QuestionSourceEnum;
import com.mindskip.xzs.domain.enums.QuestionTypeEnum;
import com.mindskip.xzs.domain.question.QuestionObject;
import com.mindskip.xzs.excel.SelectExcel;
import com.mindskip.xzs.repository.DepartmentMapper;
import com.mindskip.xzs.repository.SubjectMapper;
import com.mindskip.xzs.service.*;
@@ -18,12 +21,18 @@
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.QuestionImportVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@@ -121,6 +130,13 @@
        return RestResponse.ok();
    }

    @GetMapping("/download/question/import/temp")
    public void getImportTemp(HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("题目导入模板", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), QuestionImportVO.class).registerWriteHandler(new SelectExcel()).sheet("模板").doWrite(new ArrayList());
    }

    @PostMapping("/import")
    public RestResponse importUser(@RequestPart("file") MultipartFile file) throws Exception {
        List<QuestionEditVO> questionEditVOS = ExcelUtils.readMultipartFile(file, QuestionEditVO.class)

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

New file
@@ -0,0 +1,53 @@
package com.mindskip.xzs.excel;

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;

/**
 * 设置easy-excel下拉处理器
 *
 * @author：xp
 * @date：2024/3/15 17:27
 */
public class SelectExcel implements CellWriteHandler {

    private String[] questionTypeList = {"单选", "多选", "判断"};
    private String[] subjectTypeList = {"a","b"};

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 获取sheet对象
        Sheet sheet = writeSheetHolder.getSheet();


        // 获取数据校验helper
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        // 设置题目下拉范围，第一列，500行内
        CellRangeAddressList questionTypeRange = new CellRangeAddressList(0, 500, 0, 0);
        // 添加题目下拉
        DataValidationConstraint questionConstraint = dataValidationHelper.createExplicitListConstraint(questionTypeList);
        DataValidation questionValidation = dataValidationHelper.createValidation(questionConstraint, questionTypeRange);
        sheet.addValidationData(questionValidation);


        // 设置课目下拉范围，第2列，500行内
        CellRangeAddressList subjectTypeRange = new CellRangeAddressList(0, 500, 1, 1);
        // 添加课目下拉
        DataValidationConstraint subjectConstraint = dataValidationHelper.createExplicitListConstraint(subjectTypeList);
        DataValidation subjectValidation = dataValidationHelper.createValidation(subjectConstraint, subjectTypeRange);
        sheet.addValidationData(subjectValidation);

    }
}

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

New file
@@ -0,0 +1,60 @@
package com.mindskip.xzs.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.mindskip.xzs.viewmodel.admin.question.QuestionEditItemVM;
import lombok.Data;
import java.util.List;

/**
 * @author：xp
 * @date：2024/3/15 17:06
 */
@Data
@ColumnWidth(20)
public class QuestionImportVO {

    @ExcelProperty("题目类型")
    private Integer questionType;

    @ExcelProperty("课目")
    private String subjectName;

    @ColumnWidth(80)
    @ExcelProperty("题干")
    private String title;

    // 选项内容
    @ColumnWidth(60)
    @ExcelProperty("选项和答案")
    private List<QuestionEditItemVM> items;

    // 解析
    @ColumnWidth(30)
    @ExcelProperty("解析")
    private String analyze;

    @ExcelProperty("答案")
    private String correct;

    // 题目分数
    @ExcelProperty("题目分数")
    private String score;

    // 题目难度
    @ExcelProperty("题目难度")
    private Integer difficult;

    @ExcelProperty("选项A")
    private String a;

    @ExcelProperty("选项B")
    private String b;

    @ExcelProperty("选项C")
    private String c;

    @ExcelProperty("选项D")
    private String d;

}