From 54a64e9a13344e39470de8eff1d28a0eec669cf6 Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期日, 24 三月 2024 21:45:01 +0800
Subject: [PATCH] 题目导入模板下载
---
src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java | 115 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 113 insertions(+), 2 deletions(-)
diff --git a/src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java b/src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java
index f2f1b2d..eebdba0 100644
--- a/src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java
+++ b/src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java
@@ -4,8 +4,10 @@
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
-import org.apache.poi.hssf.usermodel.HSSFDataValidation;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+import com.mindskip.xzs.domain.vo.AnswerVO;
+import com.mindskip.xzs.domain.vo.UserCountExcelVO;
+import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
@@ -14,15 +16,18 @@
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
+import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
+import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -870,6 +875,12 @@
cell.setCellValue("");
return CELL_OTHER;
}
+ // 鏁版嵁涓�""鏃�
+ if ("".equals(o)) {
+ cell.setCellType(CellType.STRING);
+ cell.setCellValue("");
+ return CELL_OTHER;
+ }
// 鏄惁涓哄瓧绗︿覆
if (o instanceof String) {
String s = o.toString();
@@ -999,5 +1010,105 @@
return s.trim();
}
+ /**
+ * 鎷嗚В骞跺鍑哄閲岴xcel
+ */
+ public static void exportManySheetExcel(String fileName, List<ExcelSheet> mysheets, HttpServletResponse response, HttpServletRequest request) {
+ //鍒涘缓宸ヤ綔钖�
+ HSSFWorkbook wb = new HSSFWorkbook();
+ //琛ㄥご鏍峰紡
+ HSSFCellStyle style = wb.createCellStyle();
+ // 鍨傜洿
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 姘村钩
+ style.setAlignment(HorizontalAlignment.CENTER);
+ //瀛椾綋鏍峰紡
+ HSSFFont fontStyle = wb.createFont();
+ fontStyle.setFontName("寰蒋闆呴粦");
+ fontStyle.setFontHeightInPoints((short) 12);
+ style.setFont(fontStyle);
+ for (ExcelSheet excel : mysheets) {
+ //鏂板缓涓�涓猻heet
+ //鑾峰彇璇heet鍚嶇О
+ HSSFSheet sheet = wb.createSheet(excel.getFileName());
+ //鑾峰彇sheet鐨勬爣棰樺悕
+ String[] handers = excel.getHanders();
+ //绗竴涓猻heet鐨勭涓�琛屼负鏍囬
+ HSSFRow rowFirst = sheet.createRow(0);
+ //鍐欐爣棰�
+ for (int i = 0; i < handers.length; i++) {
+ //鑾峰彇绗竴琛岀殑姣忎釜鍗曞厓鏍�
+ HSSFCell cell = rowFirst.createCell(i);
+ //寰�鍗曞厓鏍奸噷鍐欐暟鎹�
+ cell.setCellValue(handers[i]);
+ //鍔犳牱寮�
+ cell.setCellStyle(style);
+ //璁剧疆姣忓垪鐨勫垪瀹�
+ sheet.setColumnWidth(i, 4000);
+ }
+ //鍐欐暟鎹泦
+ List<String[]> dataset = excel.getDataset();
+ for (int i = 0; i < dataset.size(); i++) {
+ //鑾峰彇璇ュ璞�
+ String[] data = dataset.get(i);
+ //鍒涘缓鏁版嵁琛�
+ HSSFRow row = sheet.createRow(i + 1);
+ for (int j = 0; j < data.length; j++) {
+ //璁剧疆瀵瑰簲鍗曞厓鏍肩殑鍊�
+ row.createCell(j).setCellValue(data[j]);
+ }
+ }
+ }
+
+ // 涓嬭浇鏂囦欢璋锋瓕鏂囦欢鍚嶄細涔辩爜,鐢↖E
+ try {
+
+// String agent = request.getHeader("USER-AGENT").toLowerCase();
+// response.setContentType("application/vnd.ms-excel");
+// String codedFileName = URLEncoder.encode(fileName, "UTF-8");
+// if (agent.contains("firefox")) {
+// response.addHeader("content-type", "application/x-msdownload;");
+// response.setCharacterEncoding("utf-8");
+// response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx" );
+// } else {
+// response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
+// }
+// response.flushBuffer();
+// wb.write(response.getOutputStream());
+// wb.close();
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
+ response.setHeader("Cache-Control", "No-cache");
+ response.flushBuffer();
+ wb.write(response.getOutputStream());
+ wb.close();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ public static List<String[]> listEntitiesToStringArray(List<UserCountExcelVO> entityList) {
+ List<String[]> list = new ArrayList<>();
+ entityList.forEach(e->{
+ String[] strings = new String[3];
+ strings[0] = e.getName();
+ strings[1] = e.getUserName();
+ strings[2] = e.getCount();
+ list.add(strings);
+ });
+ return list;
+ }
+
+ public static List<String[]> answerToString(List<AnswerVO> entityList) {
+ List<String[]> list = new ArrayList<>();
+ entityList.forEach(e->{
+ String[] strings = new String[3];
+ strings[0] = e.getPaperScore();
+ strings[1] = e.getUserScore();
+ strings[2] = e.getDoTime();
+ list.add(strings);
+ });
+ return list;
+ }
}
--
Gitblit v1.8.0