From f7196dff53805c9d5e6c945e5334ce4e4423d779 Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期五, 15 三月 2024 18:02:23 +0800
Subject: [PATCH] 题目导入模板，待完善
---
src/main/java/com/mindskip/xzs/excel/SelectExcel.java | 53 +++++++++++++++++
src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java | 2
src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java | 60 ++++++++++++++++++++
src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java | 16 +++++
pom.xml | 10 +++
5 files changed, 140 insertions(+), 1 deletions(-)
diff --git a/pom.xml b/pom.xml
index e2408eb..74a97c3 100644
--- a/pom.xml
+++ b/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>
diff --git a/src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java b/src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java
index 62b472d..97e109b 100644
--- a/src/main/java/com/mindskip/xzs/configuration/spring/security/SecurityConfigurer.java
+++ b/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())
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 dec03bc..1cdabdb 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/QuestionController.java
+++ b/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)
diff --git a/src/main/java/com/mindskip/xzs/excel/SelectExcel.java b/src/main/java/com/mindskip/xzs/excel/SelectExcel.java
new file mode 100644
index 0000000..4ad8e22
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/excel/SelectExcel.java
@@ -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锛歺p
+ * @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);
+
+ }
+}
diff --git a/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java b/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java
new file mode 100644
index 0000000..3894850
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/vo/QuestionImportVO.java
@@ -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锛歺p
+ * @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;
+
+}
--
Gitblit v1.8.0