import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.excel.EasyExcel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonElement;
import com.google.gson.JsonPrimitive;
import com.google.gson.JsonSerializationContext;
import com.google.gson.JsonSerializer;
import com.google.gson.reflect.TypeToken;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class EasyExcelUtil {
//
// /**
// * 导出数据
// *
// * @param headMapList 表头字段(数据库字段)
// * @param dataMapList 数据list
// * @param fileName 表名
// * @param sheetName
// * @param response
// */
// public static void write(List<Map<String, Object>> headMapList, List<Map<String, Object>> dataMapList,
// String fileName, String sheetName, HttpServletResponse response) {
// long date1 = System.currentTimeMillis();
// Map<String , Object> sendMap = new HashMap<String, Object>();
// sendMap.put("sheetName", sheetName);
// sendMap.put("fileName", fileName);
// sendMap.put("headList", headMapList);
// sendMap.put("dataList", dataMapList);
// String returnString = HttpClient.post(GetEnvironmentPro.excelUrl2+"/easyExcel/writeFile", sendMap);
// downRemoteFile(response, returnString);
//
// long date2 = System.currentTimeMillis();
// long test = date2 - date1;
// log.info("下载需要毫秒时间==>{}", test, "毫秒");
//
// }
//
//
// /**
// * 导出数据
// *
// * @param headMap 表头字段
// * @param dataMapList 数据list
// * @param fileName 表名
// * @param template 模板名称
// * @param response
// */
// public static String jxlsWrite(Map<String, Object> headMap, List<Map<String, Object>> dataMapList,
// String fileName, String template, HttpServletResponse response) {
// long date1 = System.currentTimeMillis();
// Map<String , Object> sendMap = new HashMap<String, Object>();
// sendMap.putAll(headMap);
// sendMap.put("fileName", fileName);
// sendMap.put("dataList", dataMapList);
// sendMap.put("template", template);
String returnString = HttpClient.post("http://192.168.0.41:7882/jxlsExcel/writeFile", sendMap);
String returnString = HttpClient.post2("http://203.195.243.198:7882/jxlsExcel/writeFile", sendMap);
// String excelUrl = GetEnvironmentPro.excelUrl2;
// String returnString = HttpClient.post2(excelUrl+"/jxlsExcel/writeFile", sendMap);
//
String returnString =HttpHelper.post("http://203.195.243.198:7882/jxlsExcel/writeFile", sendMap, String.class);
//
// downRemoteFile(response, returnString);
// long date2 = System.currentTimeMillis();
// long test = date2 - date1;
// log.info("下载需要毫秒时间==>{}", test, "毫秒");
// return returnString;
// }
//
//
// private static Object getClassHead(List<Map<String, Object>> headMapList) {
//
// String content = getContent(headMapList);
//
// String fileName = getScope(headMapList);
//
// File directory = new File(".");
// try {
// String path = directory.getCanonicalPath();// 获取当前路径
// path += (File.separator +"entity");
// File file = new File(path);
// System.out.println(path);
// // 如果文件夹不存在则创建
// if (!file.exists() && !file.isDirectory()) {
// file.mkdir();
// }
// fileName = path + File.separator + fileName;
// createFile(fileName, content);
// Thread.sleep(1000);
//
//
//
// } catch (Exception e) {
// e.printStackTrace();
// }
//
// // TODO Auto-generated method stub
// return null;
// }
//
// private static String getScope(List<Map<String, Object>> headMapList) {
// if (UtilsHelper.isNotEmpty(headMapList)) {
// return UtilsHelper.toUpperCaseFirstOne(headMapList.get(0).get("scope").toString());
// }
// return null;
// }
//
//
// private static String getContent(List<Map<String, Object>> headMapList) {
// // TODO Auto-generated method stub
// return null;
// }
//
/**
* 读取excel的数据
*
* @param request0
* @param headList ==表头
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readerList(HttpServletRequest request0,
List<Map<String, Object>> headList) {
MultipartFile excelFile = FileUtil.getHttpFile(request0);
List<Object> list;
InputStream inputStream = null;
try {
inputStream = excelFile.getInputStream();
list = EasyExcel.read(inputStream).sheet().headRowNumber(0).doReadSync();
Map<Integer, Object> headMap = new HashMap<Integer, Object>();
List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
for (int i = 0; i < list.size(); i++) {
Map<Integer, Object> map = (Map<Integer, Object>) list.get(i);
// 获取表头
if (i == 0) {
headMap = map;
continue;
}
// 根据下标获取信息，再根据表头信息获取英文属性名
Map<String, Object> newDataMap = new HashMap<String, Object>();
for (int j = 0; j < headMap.size(); j++) {
Object dataMap = map.get(j);
if (dataMap!=null) {
String fieldName = (String) headMap.get(j);
String fieldEnName = getEnName(fieldName, headList);
newDataMap.put(fieldEnName, dataMap);
}
}
newList.add(newDataMap);
}
return newList;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
/**
* 读取excel的数据
*
* @param
* @param headList ==表头
* @return
* @throws IOException
*/
public static List<Map<String, Object>> readerList(File excelFile,
List<Map<String, Object>> headList) {
List<Object> list;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(excelFile);
list = EasyExcel.read(inputStream).sheet().headRowNumber(0).doReadSync();
Map<Integer, Object> headMap = new HashMap<Integer, Object>();
List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
for (int i = 0; i < list.size(); i++) {
Map<Integer, Object> map = (Map<Integer, Object>) list.get(i);
// 获取表头
if (i == 0) {
headMap = map;
continue;
}
// 根据下标获取信息，再根据表头信息获取英文属性名
Map<String, Object> newDataMap = new HashMap<String, Object>();
for (int j = 0; j < headMap.size(); j++) {
Object dataMap = map.get(j);
if (dataMap!=null) {
String fieldName = (String) headMap.get(j);
String fieldEnName = getEnName(fieldName, headList);
newDataMap.put(fieldEnName, dataMap);
}
}
if(UtilsHelper.isNotEmpty(newDataMap)) {
newList.add(newDataMap);
}
}
return newList;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
/**
* = 将数据规定的表头字段转成easyExcel需要的表头
*
* @param headMaps （数据库表头）
* @return
*/
private static List<List<String>> getHeadList(List<Map<String, Object>> headMaps) {
// 表头
List<List<String>> list = new ArrayList<List<String>>();
for (int i = 0; i < headMaps.size(); i++) {
Map<String, Object> map = headMaps.get(i);
List<String> head = new ArrayList<String>();
head.add(map.get("fieldName").toString());
list.add(head);
}
return list;
}
/**
* =将需要导出的数据按照，excel数据库表的顺序生成easyExcel需要的数据格式
*
* @param headMaps
* @param dataList
* @return
*/
public static List<List<Object>> getDataList(List<Map<String, Object>> headMaps,
List<Map<String, Object>> dataList) {
List<List<Object>> dataLists = new ArrayList<List<Object>>();
for (Map<String, Object> dataMap : dataList) {
List<Object> data = new ArrayList<Object>();
for (Map<String, Object> headMap : headMaps) {
String fieldEnName = (String) headMap.get("fieldEnName");
data.add(dataMap.get(fieldEnName));
}
dataLists.add(data);
}
return dataLists;
}
/**
* =将需要导出的数据按照，excel数据库表的顺序生成easyExcel需要的数据格式
*
* @param headMaps
* @param dataList
* @return
*/
private static List<List<Object>> getDataList(Class headMaps, List<Map<String, Object>> dataList) {
List<List<Object>> dataLists = new ArrayList<List<Object>>();
for (Map<String, Object> dataMap : dataList) {
List<Object> data = new ArrayList<Object>();
Field[] declaredFields = headMaps.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field field = declaredFields[i];
String name = field.getName();
data.add(dataMap.get(name));
}
dataLists.add(data);
}
return dataLists;
}
/**
* =导出文件时为Writer生成OutputStream
*
* @throws UnsupportedEncodingException
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response)
throws UnsupportedEncodingException {
// 创建本地文件
String filePath = fileName + ".xlsx";
File dbfFile = new File(filePath);
try {
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
ServletOutputStream outputStream = response.getOutputStream();
return outputStream;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据中文表头，获取英文的表头
*
* @param fieldName
* @param newList
* @return
*/
public static String getEnName(String fieldName, List<Map<String, Object>> newList) {
for (Map<String, Object> map : newList) {
if (map.get("fieldName").equals(fieldName)) {
return map.get("fieldEnName").toString();
}
}
return null;
}
/**
* 字符串转换为java.util.Date<br>
* 支持格式为 yyyy.MM.dd G 'at' hh:mm:ss z 如 '2002-1-1 AD at 22:10:59 PSD'<br>
* yy/MM/dd HH:mm:ss 如 '2002/1/1 17:55:00'<br>
* yy/MM/dd HH:mm:ss pm 如 '2002/1/1 17:55:00 pm'<br>
* yy-MM-dd HH:mm:ss 如 '2002-1-1 17:55:00' <br>
* yy-MM-dd HH:mm:ss am 如 '2002-1-1 17:55:00 am' <br>
*
* @param time String 字符串<br>
* @return Date 日期<br>
*/
public static Date stringToDate(String time) {
SimpleDateFormat formatter;
int tempPos = time.indexOf("AD");
time = time.trim();
formatter = new SimpleDateFormat("yyyy.MM.dd G 'at' hh:mm:ss z");
if (tempPos > -1) {
time = time.substring(0, tempPos) + "公元" + time.substring(tempPos + "AD".length());// china
formatter = new SimpleDateFormat("yyyy.MM.dd G 'at' hh:mm:ss z");
}
tempPos = time.indexOf("-");
System.out.println(tempPos);
if (tempPos > -1 && (time.indexOf(" ") < 0)) {
formatter = new SimpleDateFormat("yyyyMMddHHmmssZ");
} else if ((time.indexOf("/") > -1) && (time.indexOf(" ") > -1)) {
formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else if ((time.indexOf("-") > -1) && (time.indexOf(" ") > -1)) {
formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
} else if ((time.indexOf("/") > -1) && (time.indexOf("am") > -1) || (time.indexOf("pm") > -1)) {
formatter = new SimpleDateFormat("yyyy-MM-dd KK:mm:ss a");
} else if ((time.indexOf("-") > -1) && (time.indexOf("am") > -1) || (time.indexOf("pm") > -1)) {
formatter = new SimpleDateFormat("yyyy-MM-dd KK:mm:ss a");
}
ParsePosition pos = new ParsePosition(0);
Date ctime = formatter.parse(time, pos);
return ctime;
}
// 通过文件名称创建文件
public static void createFile(String fileName, String content) {
String path = fileName;
String sourceString = content; // 待写入字符串
byte[] sourceByte = sourceString.getBytes();
if (null != sourceByte) {
try {
File file = new File(path); // 文件路径（路径+文件名）
if (!file.exists()) { // 文件不存在则创建文件，先创建目录
// File dir = new File(file.getParent());
// dir.mkdirs();
file.createNewFile();
FileOutputStream outStream = new FileOutputStream(file); // 文件输出流用于将数据写入文件
outStream.write(sourceByte);
outStream.close(); // 关闭文件输出流
} else {
System.out.println("文件" + fileName + "已存在，不再创建！");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// public static void downRemoteFile(HttpServletResponse response, String result) {
// log.info(result);
//
// Gson gson = new GsonBuilder().registerTypeAdapter(Double.class, new JsonSerializer<Double>() {
// @Override
// public JsonElement serialize(Double src, Type typeOfSrc, JsonSerializationContext context) {
// if (src == src.longValue())
// return new JsonPrimitive(src.longValue());
// return new JsonPrimitive(src);
// }
// }).setDateFormat("yyyy-MM-dd").create();
// BufferedOutputStream bos = null;
// HttpURLConnection conn = null;
// BufferedInputStream bis = null;
// String filePath = "";
// String fileName = "";
//
// try {
// Map<String, Object> map__1 = null;
// if (result != null) {
// map__1 = gson.fromJson(result, new TypeToken<Map<String, Object>>() {
// }.getType());
// }
//
//
// if (map__1 != null) {
// int resultCode = (int) map__1.get("result");
// if (resultCode <= 0) {
// log.info("===<创建失败！>");
// return;
// }
//
// filePath = map__1.get("originalFilePath") != null ? String.valueOf(map__1.get("originalFilePath")) : "";
// fileName = map__1.get("fileName") != null ? String.valueOf(map__1.get("fileName")) : "";
//
// }
// log.info("===<>"+filePath);
//
// URL url = new URL(filePath);
// conn = (HttpURLConnection) url.openConnection();
// conn.setDoInput(true);
// // 建立连接
// conn.connect();
//
// log.info("是否连接成功==》"+conn.getResponseCode());
//
// InputStream inputStream = null;
//
// if (conn.getResponseCode()==200) {
// inputStream = conn.getInputStream();
// }else {
// inputStream = conn.getInputStream();
// }
//
//
// // 连接发起请求,处理服务器响应 (从连接获取到输入流)
// bis = new BufferedInputStream(inputStream);
// bos = new BufferedOutputStream(response.getOutputStream());
// // 设置编码
// fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
// fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// // 设置头文件
// // response.reset();// 清空输出流
// response.addHeader("Content-Disposition"," attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
// response.setContentType("application/x-download");
// byte[] buffer = new byte[4 * 1024];
// int byteRead = -1;
// // 循环读取流
// while ((byteRead = (bis.read(buffer))) != -1) {
// bos.write(buffer, 0, byteRead);
// }
// bos.flush();
//
// } catch (IOException e) {
// e.printStackTrace();
// throw new RuntimeException("下载文件IO异常==>");
// } finally {
//
// try {
// bos.close();
// bis.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// if (!filePath.equals("")) {
// File file = new File(filePath);
// file.delete();
// }
// if (conn != null) {
// conn.disconnect();
// }
// }
//
// }
//{"result":1,"fileName":"年物料统计39y05t.xlsx","filePath":"/var/images/imgPath/facePict/年物料统计39y05t.xlsx","originalFilePath":"http://imgfileserver.one-cm3.com/facePict/年物料统计39y05t.xlsx"}
public static void downRemoteFile(HttpServletResponse response, String result) {
log.info(result);
try {
Gson gson = new GsonBuilder().registerTypeAdapter(Double.class, new JsonSerializer<Double>() {
@Override
public JsonElement serialize(Double src, Type typeOfSrc, JsonSerializationContext context) {
if (src == src.longValue())
return new JsonPrimitive(src.longValue());
return new JsonPrimitive(src);
}
}).setDateFormat("yyyy-MM-dd").create();
BufferedOutputStream bos = null;
HttpURLConnection conn = null;
BufferedInputStream bis = null;
String filePath = "";
String fileName = "";
Map<String, Object> map__1 = null;
if (result != null) {
map__1 = gson.fromJson(result, new TypeToken<Map<String, Object>>() {
}.getType());
}
if (map__1 != null) {
int resultCode = (int)Double.parseDouble( map__1.get("result").toString());
if (resultCode <= 0) {
log.info("===<创建失败！>");
return;
}
filePath = map__1.get("originalFilePath") != null ? String.valueOf(map__1.get("originalFilePath")) : "";
// filePath = URLEncoder.encode(filePath, "UTF-8");// 转码
// filePath = URLDecoder.decode(filePath, "UTF-8");// 解码
fileName = map__1.get("fileName") != null ? String.valueOf(map__1.get("fileName")) : "";
}
log.info("===<>"+filePath);
// try {
// Thread.sleep(500);
// } catch (InterruptedException e) {
// e.printStackTrace();
// }
URL url = new URL(filePath);
conn = (HttpURLConnection) url.openConnection();
conn.setDoInput(true);
// 建立连接
conn.connect();
log.info("是否连接成功==》"+conn.getResponseCode());
InputStream inputStream = inputStream = conn.getInputStream();
// 连接发起请求,处理服务器响应 (从连接获取到输入流)
bis = new BufferedInputStream(inputStream);
bos = new BufferedOutputStream(response.getOutputStream());
// 设置编码
fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// 设置头文件
// response.reset();// 清空输出流
response.addHeader("Content-Disposition"," attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setContentType("application/x-download");
byte[] buffer = new byte[4 * 1024];
int byteRead = -1;
// 循环读取流
while ((byteRead = (bis.read(buffer))) != -1) {
bos.write(buffer, 0, byteRead);
}
bos.flush();
bos.close();
bis.close();
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("下载文件IO异常==>");
}
}
public static void main(String[] args) throws MalformedURLException, IOException {
// //获取系统默认编码
// System.out.println(System.getProperty("file.encoding"));
//
// //获取系统默认的字符编码
// System.out.println(Charset.defaultCharset());
//
// //获取系统默认语言
//
// System.out.println(System.getProperty("user.language"));
//
// //获取系统属性列表
//
// System.getProperties().list(System.out);
//
// //设置编码
//
// System.getProperties().put("file.encoding", "GBK");
ADD("http://imgfileserver.one-cm3.com/facePict/2020%E5%B9%B403%E6%9C%88%E7%89%A9%E6%96%99%E5%BA%93%E5%AD%98%E7%BB%9F%E8%AE%A15XV62T.xlsx");
ADD("http://imgfileserver.one-cm3.com/facePict/2020年03月物料库存统计5XV62T.xlsx");
}
private static void add() throws UnsupportedEncodingException, MalformedURLException, IOException {
String fileNameString = "标院商品码";
// String fileNameString = "http://192.168.0.41:7882/标院商品码2yz63P.xlsx";
fileNameString = URLEncoder.encode(fileNameString, "ISO-8859-1");// 转码
System.out.println(fileNameString);
fileNameString = URLDecoder.decode(fileNameString, "UTF-8");// 解码
// fileNameString = URLEncoder.encode(fileNameString, "UTF-8");// 转码
// fileNameString = URLDecoder.decode(fileNameString, "ISO-8859-1");// 解码
System.out.println(fileNameString);
// HttpURLConnection add = ADD(fileNameString);
// HttpURLConnection add2 = ADD("http://192.168.0.41:7882/%E6%A0%87%E9%99%A2%E5%95%86%E5%93%81%E7%A0%812yz63P.xlsx");
}
private static HttpURLConnection ADD(String filePath) throws MalformedURLException, IOException {
HttpURLConnection conn;
URL url = new URL(filePath);
conn = (HttpURLConnection) url.openConnection();
conn.setDoInput(true);
// 建立连接
conn.connect();
log.info("是否连接成功==》"+conn.getResponseCode());
return conn;
}
// /**
// * 导出表头
// *
// * @param headMapList 表头字段(数据库字段)
// * @param fileName 表名
// * @param sheetName
// * @param response
// */
// public static void writeTemple(List<Map<String, Object>> headMapList,
// String fileName, String sheetName, HttpServletResponse response) {
// long date1 = System.currentTimeMillis();
// List<List<String>> headList = EasyExcelUtil.getHeadList(headMapList);
// log.info("excel模板导出表头 {} " , headList);
// List<List<Object>> dataList = null;
// String fileName1 = fileName + StringRandom.getStringRandom(6, 65, 97) + ".xlsx";
// String filePath = GetEnvironmentPro.filePath +"/"+ fileName1;//GetEnvironmentPro.filePath是本地excel存放的路径
// EasyExcel.write().file(filePath).head(headList)
// .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName)
// .doWrite(dataList);
// downFileByFileName(filePath, response);
// long date2 = System.currentTimeMillis();
// long test = date2 - date1;
// log.info("下载需要毫秒时间==>{}", test, "毫秒");
//
// }
public static void downFileByFileName(String fileName,HttpServletResponse response) {
try {
BufferedOutputStream bos = null;
BufferedInputStream bis = null;
InputStream inputStream =new FileInputStream(fileName);
// 连接发起请求,处理服务器响应 (从连接获取到输入流)
bis = new BufferedInputStream(inputStream );
bos = new BufferedOutputStream(response.getOutputStream());
// 设置编码
fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// 设置头文件
// response.reset();// 清空输出流
response.addHeader("Content-Disposition"," attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setContentType("application/x-download");
byte[] buffer = new byte[4 * 1024];
int byteRead = -1;
// 循环读取流
while ((byteRead = (bis.read(buffer))) != -1) {
bos.write(buffer, 0, byteRead);
}
bos.flush();
bos.close();
bis.close();
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("下载文件IO异常==>");
}
}
/**
* 导出数据
*
* @param headMapList 表头字段(数据库字段)
* @param dataMapList 数据list
* @param fileName 表名
* @param sheetName
* @param response
*/
public static void writePoiExcel(List<Map<String, Object>> headMapList, List<Map<String, Object>> dataMapList,
String fileName, String sheetName, HttpServletResponse response) {
long date1 = System.currentTimeMillis();
// Map<String , Object> sendMap = new HashMap<String, Object>();
sendMap.put("sheetName", sheetName);
// sendMap.put("fileName", fileName);
// sendMap.put("fields", headMapList);
// sendMap.put("listDomain", dataMapList);
// String returnString = HttpHelper.post("http://192.168.0.179:7881"+"/ExcelNew/ExcelTempNew", sendMap);
// downRemoteFile(response, returnString);
createExcel(fileName,sheetName,headMapList,dataMapList,response);
long date2 = System.currentTimeMillis();
long test = date2 - date1;
log.info("下载需要毫秒时间==>{}", test, "毫秒");
}
/**
* 导出数据并压缩
*
* @param headMapList 表头字段(数据库字段)
* @param dataMapList 数据list
* @param fileName 表名
* @param sheetName
* @param response
*/
public static void writePoiExcelAndCompress(List<Map<String, Object>> headMapList, List<Map<String, Object>> dataMapList,
String fileName, String sheetName, HttpServletResponse response) {
long date1 = System.currentTimeMillis();
// Map<String , Object> sendMap = new HashMap<String, Object>();
sendMap.put("sheetName", sheetName);
// sendMap.put("fileName", fileName);
// sendMap.put("fields", headMapList);
// sendMap.put("listDomain", dataMapList);
// String returnString = HttpHelper.post("http://192.168.0.179:7881"+"/ExcelNew/ExcelTempNew", sendMap);
// downRemoteFile(response, returnString);
createExcelAndCompress(fileName,sheetName,headMapList,dataMapList,response);
long date2 = System.currentTimeMillis();
long test = date2 - date1;
log.info("下载需要毫秒时间==>{}", test, "毫秒");
}
public static void writePoiExcelAndCompress(Class clazz, String fileName, String sheetName, HttpServletResponse response) {
long date1 = System.currentTimeMillis();
createExcelAndCompress(fileName, sheetName, clazz, response);
long date2 = System.currentTimeMillis();
long test = date2 - date1;
log.info("下载需要毫秒时间==>{}", test, "毫秒");
}
// /**
// * 创建excel
// * isNull 是否必填字段
// * 适用于导入excel
// *
// * @param fileName
// * @param sheetName
// * @param headList
// * @param dataList
// * @param response
// */
//
// public static void createUploadExcel(String fileName,String sheetName, List<Map<String, Object>> headList , List<Map<String, Object>> dataList,HttpServletResponse response) {
//
// int defaultColwidth = 26;
// float rowHeight_one =60;// 表头行高
// float rowHeight_other = 40;// 内容行高
// short fontHeight_head = (short) 400;//字体大小
// short fontHeight_detail = (short) 240;//字体大小
//
// String fontName_head = "宋体";
// boolean italice_head = false;// 表头是否斜体
// boolean bold = true;// 是否粗体
//
// String chinessName = "fieldName";
// String englishName = "fieldEnName";
//
// Workbook wb = new XSSFWorkbook();
// Sheet sheet = wb.createSheet(sheetName);
// sheet.setDefaultColumnWidth(defaultColwidth);// 默认列宽
//
// // 表头
// Row row = sheet.createRow(0);
// row.setHeightInPoints(rowHeight_one);// 行高（单位：磅）
// Map<Integer, String> feildMap = new HashMap<Integer, String>();
// Map<Integer, Map<String, Object>> feildMsgMap = new HashMap<Integer, Map<String, Object>>();
// // 创建表头
// for (int i = 0; i < headList.size(); i++) {
//
// Map<String, Object> map = headList.get(i);
// Cell cell = row.createCell(i);
// String createSafeSheetName = (String) map.get(chinessName);
// feildMap.put(i, (String) map.get(englishName));
// feildMsgMap.put(i, map);
// cell.setCellValue(createSafeSheetName);
//
// Boolean isNull = (Boolean)map.get("isNull");
//
// CellStyle cellStyle = getHeadCellStyle(wb,isNull);
// Font font = wb.createFont();
// font.setFontName(fontName_head);
// font.setFontHeight(fontHeight_head);//
// font.setItalic(italice_head);// 是否斜体
// font.setBold(bold);// 是否粗体
font.setStrikeout(false);//设置是否在文本中使用划除水平线
font.setColor((short)1);//设置颜色
font.setUnderline((byte)1);//设置下划线
// cellStyle.setFont(font);
//
//
//
//
// cell.setCellStyle(cellStyle);
// }
//
// // 数据
// if (UtilsHelper.isNotEmpty(dataList)) {
// for (int i = 0; i < dataList.size(); i++) {
// Map<String, Object> map = dataList.get(i);
// Row row_i = sheet.createRow(i + 1);// 从第二行开始
// row_i.setHeightInPoints(rowHeight_other);// 行高（单位：磅）
// for (int cell_indx : feildMap.keySet()) {
// Cell cell = row_i.createCell(cell_indx);
// String feildName = feildMap.get(cell_indx);
// if (map.get(feildName) == null || map.get(feildName).equals("")) {
// map.put(feildName, "--");
// }
// Map<String, Object> headMap = feildMsgMap.get(cell_indx);
// short fontHeight_detail_add = fontHeight_detail;
// String valueName = map.get(feildName).toString();
// cell.setCellValue(valueName);
// CellStyle cellStyle = getCellStyle(wb);
// Font font = wb.createFont();
// font.setFontName(fontName_head);
// font.setFontHeight(fontHeight_detail_add);// 12=240/20
// cellStyle.setFont(font);
// cell.setCellStyle(cellStyle);
// }
// }
// }
// fileName = fileName + ".xlsx";
// log.info("fileName=={}",fileName);
// try (OutputStream fileOut = response.getOutputStream()) {
// // 设置编码
// fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
// fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// // 设置头文件
// // response.reset();// 清空输出流
// response.addHeader("Content-Disposition",
// " attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
// response.setContentType("application/x-download");
//
// wb.write(fileOut);
//
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
/**
* 创建excel
*
* @param fileName
* @param sheetName
* @param headList
* @param dataList
* @param response
*/
public static void createExcel(String fileName,String sheetName, List<Map<String, Object>> headList , List<Map<String, Object>> dataList,HttpServletResponse response) {
int defaultColwidth = 26;
float rowHeight_one =60;// 表头行高
float rowHeight_other = 40;// 内容行高
short fontHeight_head = (short) 400;//字体大小
short fontHeight_detail = (short) 240;//字体大小
String fontName_head = "宋体";
boolean italice_head = false;// 表头是否斜体
boolean bold = true;// 是否粗体
String chinessName = "fieldName";
String englishName = "fieldEnName";
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(defaultColwidth);// 默认列宽
// 表头
Row row = sheet.createRow(0);
row.setHeightInPoints(rowHeight_one);// 行高（单位：磅）
Map<Integer, String> feildMap = new HashMap<Integer, String>();
Map<Integer, Map<String, Object>> feildMsgMap = new HashMap<Integer, Map<String, Object>>();
// 创建表头
for (int i = 0; i < headList.size(); i++) {
Map<String, Object> map = headList.get(i);
Cell cell = row.createCell(i);
String createSafeSheetName = (String) map.get(chinessName);
feildMap.put(i, (String) map.get(englishName));
feildMsgMap.put(i, map);
cell.setCellValue(createSafeSheetName);
Boolean isNull = (Boolean)map.get("isNull");//是否必填字段
CellStyle cellStyle = getHeadCellStyle(wb,isNull);
Font font = wb.createFont();
font.setFontName(fontName_head);
font.setFontHeight(fontHeight_head);//
font.setItalic(italice_head);// 是否斜体
font.setBold(bold);// 是否粗体
// font.setStrikeout(false);//设置是否在文本中使用划除水平线
// font.setColor((short)1);//设置颜色
// font.setUnderline((byte)1);//设置下划线
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
if (UtilsHelper.isNotEmpty(dataList)) {
//数据处理
CellStyle cellStyle = getCellStyle(wb);
Font font = wb.createFont();
font.setFontName(fontName_head);
short fontHeight_detail_add = fontHeight_detail;
font.setFontHeight(fontHeight_detail_add);// 12=240/20
cellStyle.setFont(font);
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> map = dataList.get(i);
Row row_i = sheet.createRow(i + 1);// 从第二行开始
row_i.setHeightInPoints(rowHeight_other);// 行高（单位：磅）
for (int cell_indx : feildMap.keySet()) {
Cell cell = row_i.createCell(cell_indx);
String feildName = feildMap.get(cell_indx);
if (map.get(feildName) == null || map.get(feildName).equals("")) {
map.put(feildName, "--");
}
Map<String, Object> headMap = feildMsgMap.get(cell_indx);
String valueName = map.get(feildName).toString();
cell.setCellValue(valueName);
cell.setCellStyle(cellStyle);
}
}
}
fileName = fileName + ".xlsx";
log.info("fileName=={}",fileName);
try (OutputStream fileOut = response.getOutputStream()) {
// 设置编码
fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// 设置头文件
// response.reset();// 清空输出流
response.addHeader("Content-Disposition",
" attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setContentType("application/x-download");
wb.write(fileOut);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private static void createExcelAndCompress(String fileName, String sheetName, Class clazz, HttpServletResponse response) {
fileName = fileName + ".zip";
log.info("fileName=={}", fileName);
try (OutputStream fileOut = response.getOutputStream()) {
// 设置编码
fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// 设置头文件
// response.reset();// 清空输出流
response.addHeader("Content-Disposition",
" attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setContentType("application/x-download");
ZipOutputStream zipOut = new ZipOutputStream(fileOut, Charset.forName("gbk"));
zipOut.putNextEntry(new ZipEntry(sheetName+".xlsx"));
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream, clazz).sheet(sheetName).doWrite(new ArrayList<>());
byteArrayOutputStream.writeTo(zipOut);
byteArrayOutputStream.close();
zipOut.closeEntry();
zipOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 创建excel并压缩
*
* @param fileName
* @param sheetName
* @param headList
* @param dataList
* @param response
*/
public static void createExcelAndCompress(String fileName,String sheetName, List<Map<String, Object>> headList , List<Map<String, Object>> dataList,HttpServletResponse response) {
int defaultColwidth = 26;
float rowHeight_one =60;// 表头行高
float rowHeight_other = 40;// 内容行高
short fontHeight_head = (short) 400;//字体大小
short fontHeight_detail = (short) 240;//字体大小
String fontName_head = "宋体";
boolean italice_head = false;// 表头是否斜体
boolean bold = true;// 是否粗体
String chinessName = "fieldName";
String englishName = "fieldEnName";
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(defaultColwidth);// 默认列宽
// 表头
Row row = sheet.createRow(0);
row.setHeightInPoints(rowHeight_one);// 行高（单位：磅）
Map<Integer, String> feildMap = new HashMap<Integer, String>();
Map<Integer, Map<String, Object>> feildMsgMap = new HashMap<Integer, Map<String, Object>>();
// 创建表头
for (int i = 0; i < headList.size(); i++) {
Map<String, Object> map = headList.get(i);
Cell cell = row.createCell(i);
String createSafeSheetName = (String) map.get(chinessName);
feildMap.put(i, (String) map.get(englishName));
feildMsgMap.put(i, map);
cell.setCellValue(createSafeSheetName);
Boolean isNull = (Boolean)map.get("isNull");
CellStyle cellStyle = getHeadCellStyle(wb,isNull);
Font font = wb.createFont();
font.setFontName(fontName_head);
font.setFontHeight(fontHeight_head);//
font.setItalic(italice_head);// 是否斜体
font.setBold(bold);// 是否粗体
// font.setStrikeout(false);//设置是否在文本中使用划除水平线
// font.setColor((short)1);//设置颜色
// font.setUnderline((byte)1);//设置下划线
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
// 数据
if (UtilsHelper.isNotEmpty(dataList)) {
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> map = dataList.get(i);
Row row_i = sheet.createRow(i + 1);// 从第二行开始
row_i.setHeightInPoints(rowHeight_other);// 行高（单位：磅）
for (int cell_indx : feildMap.keySet()) {
Cell cell = row_i.createCell(cell_indx);
String feildName = feildMap.get(cell_indx);
if (map.get(feildName) == null || map.get(feildName).equals("")) {
map.put(feildName, "--");
}
Map<String, Object> headMap = feildMsgMap.get(cell_indx);
short fontHeight_detail_add = fontHeight_detail;
String valueName = map.get(feildName).toString();
cell.setCellValue(valueName);
CellStyle cellStyle = getCellStyle(wb);
Font font = wb.createFont();
font.setFontName(fontName_head);
font.setFontHeight(fontHeight_detail_add);// 12=240/20
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
}
}
fileName = fileName + ".zip";
log.info("fileName=={}",fileName);
try (OutputStream fileOut = response.getOutputStream()) {
// 设置编码
fileName = URLEncoder.encode(fileName, "UTF-8");// 转码
fileName = URLDecoder.decode(fileName, "UTF-8");// 解码
// 设置头文件
// response.reset();// 清空输出流
response.addHeader("Content-Disposition",
" attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setContentType("application/x-download");
ZipOutputStream zipOut = new ZipOutputStream(fileOut,Charset.forName("gbk"));
zipOut.putNextEntry(new ZipEntry("人员导入模板.xlsx"));
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
wb.write(byteArrayOutputStream);
byteArrayOutputStream.writeTo(zipOut);
byteArrayOutputStream.close();
zipOut.closeEntry();
zipOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 公共样式
*
* @param wb
* @return
*/
private static CellStyle getCellStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
cellStyle.setBorderBottom(BorderStyle.THIN);// 边框线(粗细)-底线
// cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//边框线颜色-底线
cellStyle.setBorderLeft(BorderStyle.THIN);// 边框线(粗细)-左
// cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());//边框线颜色-左
cellStyle.setBorderRight(BorderStyle.THIN);// 边框线(粗细)-右
// cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());//边框线颜色-右
cellStyle.setBorderTop(BorderStyle.THIN);// 边框线(粗细)-上
// cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());//边框线颜色-上
return cellStyle;
}
/**
* 表头样式
*
* @param wb
* @return
*/
private static CellStyle getHeadCellStyle(Workbook wb) {
CellStyle cellStyle = getCellStyle(wb);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景颜色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return cellStyle;
}
/**
* 表头样式
*
* @param wb
* @param isNull 是否必填
* @return
*/
private static CellStyle getHeadCellStyle(Workbook wb , Boolean isNull) {
CellStyle cellStyle = getCellStyle(wb);
if(isNull) {
cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());// 设置背景颜色
}else {
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景颜色
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return cellStyle;
}
/**
* 解压压缩包并创建文件夹
*/
public static Map<String,Object> dpFile(HttpServletRequest req){
Map<String,Object> returnMap = new HashMap<>();
String excelPath = "";
// 根据当前日期来创建文件夹
// String filePath = GetEnvironmentPro.uploadPictPath;// 配置的路径
String filePath = "";// 配置的路径
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
String dirName = df.format(new Date());// 日期字符串
filePath = filePath + dirName + File.separator;// 生成的文件夹路径
// 创建文件夹
Boolean create = FileUtil.createDir(filePath);
if (create) {
String zipName = filePath + df.format(new Date()) + ".zip";
Boolean save = FileUtil.saveHttpFile(req, zipName);
if (save) {
String excelName = FileUtil.unZip(zipName, filePath);// 解压并获取excel文件名
if(excelName.contains("Fail")) {
returnMap.put("errCode", -1);
returnMap.put("errMsg", excelName);
return returnMap;
}
excelPath = filePath + excelName;
new File("").length();
} else {
returnMap.put("errCode", -1);
returnMap.put("errMsg", "文件保存失败");
return returnMap;
}
} else {
returnMap.put("errCode", -1);
returnMap.put("errMsg", "文件创建时发生错误");
return returnMap;
}
returnMap.put("errCode", 1);
returnMap.put("errMsg", "成功");
returnMap.put("excelPath", excelPath);
returnMap.put("filePath", filePath);
return returnMap;
}
}
/**
* 下载模板
*
* @param req
* @param res
* @return
* @throws Exception
*/
@Checktoken
@RequestMapping(value = "/downModel", produces = MediaType.APPLICATION_JSON_VALUE, method = {RequestMethod.GET})
public void downModel(HttpServletRequest req, HttpServletResponse res) throws Exception {
Map<String, Object> parameter = UtilsHelper.getParameter(req);
String fileName = "questionModel";
log.info("下载模板==>{}", parameter);
parameter = ResultTmplateMsg.mapCleanNull(parameter);
String comCode = (String) parameter.get("comCode");
Map<String, Object> m = new HashMap<String, Object>();
m.put("comCode", comCode);
m.put("scope", "personModel");
EasyExcelUtil.writePoiExcelAndCompress(TemplateEntity.class, fileName, "questionModel", res);
}
/**
* 导入题库
*
* @param multipartFile
* @return
*/
@Checktoken
@SuppressWarnings({"rawtypes", "unchecked"})
@RequestMapping(value = "/insertQuestion", method = {RequestMethod.POST,
RequestMethod.GET}, produces = MediaType.APPLICATION_JSON_VALUE)
public ResultTmplateMsg insertQuestion(String comCode, @RequestPart("file") MultipartFile multipartFile) throws Exception {
long startTime = 0;// 导入起始时间
startTime = System.currentTimeMillis();
comCode = "2b4e70fd559f4a36af22972a73fcbb4d";
ResultTmplateMsg resultMsg = null;
List<String> msg = importQuestion(comCode, multipartFile);
log.info("用时==={}", System.currentTimeMillis() - startTime);
log.info("msg=={}", msg);
if (msg.get(msg.size() - 1).equals("0")) {
msg.remove((msg.size() - 1));
String result = String.valueOf(msg);
return ResultTmplateMsg.commResultMsg(0, result);
}
String result = msg.toString();
return ResultTmplateMsg.commResultMsg(1, result);
}
public List<String> importQuestion(String comCode, MultipartFile multipartFile) throws IOException {
List<String> msg = new ArrayList<String>();
Map<String, Object> errMap = new HashMap<String, Object>();// 存放错误信息
errMap.put("action", "导入");
//定义文件存放路径
String filePath = filepath;
String OriginalFilename = multipartFile.getOriginalFilename()+".xlsx";
OriginalFilename=OriginalFilename.substring(0,OriginalFilename.lastIndexOf("."));
log.info(OriginalFilename);
//新建一个目录（文件夹）
File dest = new File(filePath + OriginalFilename);
//判断filePath目录是否存在，如不存在，就新建一个
if (!dest.getParentFile().canExecute()) {
dest.getParentFile().mkdirs(); //新建一个目录
}
try {
//文件输出
multipartFile.transferTo(dest);
} catch (Exception e) {
e.printStackTrace();
//拷贝失败要有提示
String message = "导入异常";
msg.add(message);
errMap.put("errMessage", message);
return msg;
}
int total = 0;
// 读取导入数据
List<TemplateEntity> newList = readerMultilist(new File(filePath+OriginalFilename));
log.info("newList=====>{}>", newList);
int count = 0;
for (TemplateEntity templateEntity : newList) {
if (UtilsHelper.isNotEmpty(templateEntity)) {
count++;
if (UtilsHelper.isEmpty(templateEntity.getQuestionType())) {
String message = "第" + count + "行试题类型为空,上传失败";
msg.add(message);
continue;
}
if (UtilsHelper.isEmpty(templateEntity.getQuestionClass())) {
String message = "第" + count + "行试题类别为空,上传失败";
msg.add(message);
continue;
}
if (UtilsHelper.isEmpty(templateEntity.getQuestionTrouble())) {
String message = "第" + count + "行试题难度为空,上传失败";
msg.add(message);
continue;
}
if (UtilsHelper.isEmpty(templateEntity.getQuestionValue())) {
String message = "第" + count + "行试题分值为空,上传失败";
msg.add(message);
continue;
}
if (UtilsHelper.isEmpty(templateEntity.getQuestionAnswer())) {
String message = "第" + count + "行试题答案为空,上传失败";
msg.add(message);
continue;
}else {
if (templateEntity.getQuestionAnswer().equals("单选题")||templateEntity.getQuestionAnswer().equals("多选题")){
if (UtilsHelper.isEmpty(templateEntity.getChoiceA())) {
String message = "第" + count + "行试题选项为空,上传失败";
msg.add(message);
continue;
}
}
}
if (UtilsHelper.isEmpty(templateEntity.getQuestionTitle())) {
String message = "第" + count + "行试题题目为空,上传失败";
msg.add(message);
continue;
}else {
Map<String, Object> titleMap = new HashMap<>();
titleMap.put("questionTitle",templateEntity.getQuestionTitle());
titleMap.put("comCode",comCode);
Map<String, Object> recNoMap = questionService.getRecNoByQuestionTitle(titleMap);
//真删
questionService.delete(recNoMap);
}
}
questionService.insert(comCode,templateEntity);
total++;
}
String message = "批量成功" + total + "条";
msg.add(message);
if (msg.size() > 1) {
msg.add("0");
}
return msg;
}
/**
* 读取excel的数据(多个Sheet)
*
* @param
* @param
* @return
* @throws IOException
*/
public static List<TemplateEntity> readerMultilist(File excelFile) throws IOException {
InputStream inputStream = new FileInputStream(excelFile);
TemplateListener listener = new TemplateListener(); // 定义的 listener
ExcelReader excelReader = EasyExcel.read(inputStream).build();
// readSheet参数设置读取sheet的序号
// 读取sheet 限制6页 要加再在下面加
ReadSheet readSheet1 = EasyExcel.readSheet(0).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
ReadSheet readSheet2 = EasyExcel.readSheet(1).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
ReadSheet readSheet3 = EasyExcel.readSheet(2).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
ReadSheet readSheet4 = EasyExcel.readSheet(3).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
ReadSheet readSheet5 = EasyExcel.readSheet(4).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
ReadSheet readSheet6 = EasyExcel.readSheet(5).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
excelReader.read(readSheet1, readSheet2, readSheet3, readSheet4,readSheet5,readSheet6);
// 读的时候会创建临时文件，需要关闭
excelReader.finish();
//取出数据
List<TemplateEntity> list = listener.getData();
log.info(list.toString());
inputStream.close();
return list;
}
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
@Data
@ContentRowHeight(18)//内容行高
@HeadRowHeight(50)//标题行高
@ColumnWidth(20)//列宽，可设置成员变量上
public class TemplateEntity {
@ExcelProperty(value = "试题类型")
private String questionType;
@ExcelProperty(value ="试题类别")
private String questionClass;
@ExcelProperty(value ="试题难度")
private String questionTrouble;
@ExcelProperty(value ="试题分值")
private String questionValue;
@ExcelProperty(value ="试题题目")
private String questionTitle;
@ExcelProperty(value ="试题答案")
private String questionAnswer;
@ExcelProperty(value ="选项A")
private String choiceA;
@ExcelProperty(value ="选项B")
private String choiceB;
@ExcelProperty(value ="选项C")
private String choiceC;
@ExcelProperty(value ="选项D")
private String choiceD;
@ExcelProperty(value ="选项E")
private String choiceE;
@ExcelProperty(value ="选项F")
private String choiceF;
}
7600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
