一、匯入
1.引入esayExcel JAR包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
2.建立Model類
package com.bjsasc.avmom.listener;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class ZytzModel {
// 資源名稱
@ExcelProperty(value = "資源名稱")
private String res_name;
// 資源型別
@ExcelProperty(value = "資源型別")
private String res_classname;
// 崗位呼號
@ExcelProperty(value = "崗位呼號")
private String callSign;
// 型號
@ExcelProperty(value = "型號")
private String modelNum;
// 出廠編號
@ExcelProperty(value = "出廠編號")
private String factoryId;
// 有效期
@ExcelProperty(value = "有效期")
private String validityInspTime;
// 檢定日期
@ExcelProperty(value = "檢定日期")
private String docimasyTime;
// 引數
@ExcelProperty(value = "引數")
private String parameter;
// 精度
@ExcelProperty(value = "精度")
private String accuracy;
// 量程
@ExcelProperty(value = "量程")
private String capacity;
// 圖號
@ExcelProperty(value = "圖號")
private String drawNum;
// 位置
@ExcelProperty(value = "位置")
private String location;
// 資源狀態
@ExcelProperty(value = "資源狀態")
private String status;
}
3.建立Listener
package com.bjsasc.avmom.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.bjsasc.adp.datastorage.entity.PtObject;
import com.bjsasc.asp.dev.common.utils.IdUtils;
import com.bjsasc.avmom.core.service.emsExtended.IEmsBaseService;
import com.bjsasc.avmom.util.Code165Utils;
import lombok.SneakyThrows;
import org.apache.commons.beanutils.BeanUtils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class ZytzReadListener implements ReadListener<ZytzModel> {
private final List<PtObject> errorList;
private final IEmsBaseService emsBaseService;
private final Code165Utils code165Utils;
private static final int BATCH_COUNT = 100;
/**
* 快取的資料
*/
private List<PtObject> cachedDataList = new ArrayList<>(BATCH_COUNT);
public ZytzReadListener(List<PtObject> errorList, IEmsBaseService emsBaseService, Code165Utils code165Utils) {
this.errorList = errorList;
this.emsBaseService = emsBaseService;
this.code165Utils = code165Utils;
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
}
@SneakyThrows
@Override
public void invoke(ZytzModel data, AnalysisContext context) {
boolean flag = true;
Integer rowNum = context.readRowHolder().getRowIndex() + 1;
StringBuilder stringBuilder = new StringBuilder();
PtObject ptObject = new PtObject();
PtObject errorPtObject = new PtObject();
Map<String, String> describe = BeanUtils.describe(data);
ptObject.putAll(describe);
// 查詢對應的資源分類classId與treeInnerId
if (data.getRes_classname() != null && !data.getRes_classname().equals("")){
this.findResClassId(data.getRes_classname(), ptObject);
// 如果為""字串表示為查詢到
if (ptObject.get("classId").toString().equals("")){
flag = false;
stringBuilder.append(String.format("第%s行資源分類名稱錯誤請檢查;", rowNum));
}
} else {
flag = false;
stringBuilder.append(String.format("第%s行資源分類名稱為空請檢查;", rowNum));
}
// 如果出廠編號不為空查詢資料庫是否已存在
if (data.getFactoryId() != null && !data.getFactoryId().equals("")){
String factoryId = data.getFactoryId();
String sql = "select * from prod_base where factoryId = '" + factoryId +"' and treeInnerId = '" + ptObject.get("treeInnerId")+"'";
List<Map<String, Object>> maps = emsBaseService.selectDataBySql(sql);
if (!maps.isEmpty()){
flag = false;
stringBuilder.append(String.format("第%s行資源出廠編號已存在請檢查;", rowNum));
}
}
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
String validityInspTime = data.getValidityInspTime();
String docimasyTime = data.getDocimasyTime();
if (docimasyTime != null && !Objects.equals(docimasyTime, "")){
try {
ptObject.put("docimasyTime", simpleDateFormat.parse(docimasyTime).getTime());
} catch (ParseException e) {
flag = false;
stringBuilder.append(String.format("第%s行檢定日期格式不正確請檢查;", rowNum));
}
}
if (validityInspTime != null && !Objects.equals(validityInspTime, "")){
try {
ptObject.put("validityInspTime", simpleDateFormat.parse(validityInspTime).getTime());
} catch (ParseException e) {
flag = false;
stringBuilder.append(String.format("第%s行有效期格式不正確請檢查;", rowNum));
}
}
String status = data.getStatus();
if (status != null && !status.equals("")){
switch (status){
case "正常":
ptObject.put("status", "0");
break;
case "已使用":
ptObject.put("status", "1");
break;
case "檢定中":
ptObject.put("status", "2");
break;
case "報廢":
ptObject.put("status", "3");
break;
default:
ptObject.put("status", "");
}
} else {
flag = false;
stringBuilder.append(String.format("第%s行資源狀態為空請檢查;", rowNum));
}
if (flag){
ptObject.setInnerId(IdUtils.randomUUID().replace("-", ""));
ptObject.put("createTime", new Date().getTime());
String classId = ptObject.get("classId").toString();
classId=classId.replace("prod_base_","");
classId=classId.split("_")[0].toUpperCase();
String code = code165Utils.getRunningValue("prod_base", "物資編碼", classId+"-");
ptObject.put("code", code);
ptObject.put("useNumber", 0);
ptObject.put("useTime", 0);
ptObject.put("varyTime", 0);
ptObject.put("varyNumber", 0);
cachedDataList.add(ptObject);
if (cachedDataList.size() >= BATCH_COUNT) {
emsBaseService.saveBatch(cachedDataList);
// 儲存完成清理 list
cachedDataList = new ArrayList<>(BATCH_COUNT);
}
} else {
errorPtObject.put("rowNum", rowNum);
errorPtObject.put("msg", stringBuilder.toString());
errorList.add(errorPtObject);
}
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
emsBaseService.saveBatch(cachedDataList);
}
@Override
public boolean hasNext(AnalysisContext context) {
return true;
}
@Override
public void invokeHead(Map headMap, AnalysisContext context) {
}
private void findResClassId(String resClassName, PtObject ptObject){
switch (resClassName){
case "安全閥":
ptObject.put("classId", "prod_base_aqfj_aqf");
ptObject.put("treeInnerId", "d742aae3471409f936b3f719ccddfece");
break;
case "爆破膜片":
ptObject.put("classId", "prod_base_aqfj_bpmp");
ptObject.put("treeInnerId", "0faa67fba8af86d3b69ba227e9431ab3");
break;
case "煤油泵前短管":
ptObject.put("classId", "prod_base_bqdg_my");
ptObject.put("treeInnerId", "38ec8c63ed6800d9275954b191ebc235");
break;
case "液氧泵前短管":
ptObject.put("classId", "prod_base_bqdg_yy");
ptObject.put("treeInnerId", "f8a6cbc241ca2ffc0afef6bdfceab52a");
break;
case "推力感測器":
ptObject.put("classId", "prod_base_cgq_bjlcgq");
ptObject.put("treeInnerId", "0a08c925e86b3b3931ef25e1d6a27b5b");
break;
case "流量感測器":
ptObject.put("classId", "prod_base_cgq_llcgq");
ptObject.put("treeInnerId", "203413527e1fde553e3606545b51e347");
break;
case "溫度感測器":
ptObject.put("classId", "prod_base_cgq_wdcgq");
ptObject.put("treeInnerId", "1f20a8835afa9d42bab3f2d924d844d8");
break;
case "壓力感測器":
ptObject.put("classId", "prod_base_cgq_ylcgq");
ptObject.put("treeInnerId", "c37ed98839e906da973d1204610f41d5");
break;
case "振動感測器":
ptObject.put("classId", "prod_base_cgq_zdcgq");
ptObject.put("treeInnerId", "270803326e77bf55a94fdb25e22e9258");
break;
case "工藝驗收電纜":
ptObject.put("classId", "prod_base_cldl_gyys");
ptObject.put("treeInnerId", "548f6a8c281092b36e2b2b28559c245b");
break;
case "彙總電纜":
ptObject.put("classId", "prod_base_cldl_hzdl");
ptObject.put("treeInnerId", "3d86f693db59bfbc47aee4638425802b");
break;
case "遙測電纜":
ptObject.put("classId", "prod_base_cldl_ycdl");
ptObject.put("treeInnerId", "7beece1a83e251bb124ca5c185826fa5");
break;
case "振動電纜":
ptObject.put("classId", "prod_base_cldl_zd");
ptObject.put("treeInnerId", "369e4d504ae9c30192772a2a85cda498");
break;
case "直流電源":
ptObject.put("classId", "prod_base_dy");
ptObject.put("treeInnerId", "d5644c7b072c9edff67d881a1a31a38b");
break;
case "閥門":
ptObject.put("classId", "prod_base_fm");
ptObject.put("treeInnerId", "58d25260b525fe70bc1090c3ecb8c997");
break;
case "輔助管道":
ptObject.put("classId", "prod_base_fzgd");
ptObject.put("treeInnerId", "79fdbc322d71d8a528ceb9595d0d8a3e");
break;
case "煤油泵前過濾器":
ptObject.put("classId", "prod_base_glq_myjzglq");
ptObject.put("treeInnerId", "a423c0a34e78824624e459411ae20476");
break;
case "氣路系統過濾器":
ptObject.put("classId", "prod_base_glq_qlxtglq");
ptObject.put("treeInnerId", "fc62b373ce65ae940b369599a9b0bfb3");
break;
case "液氧泵前過濾器":
ptObject.put("classId", "prod_base_glq_yyjzglq");
ptObject.put("treeInnerId", "1e75f9e4154fbe8710f51552ba19221b");
break;
case "金屬軟管":
ptObject.put("classId", "prod_base_jsrg");
ptObject.put("treeInnerId", "744da2253d94dc493062e53244c9b371");
break;
case "晶振板":
ptObject.put("classId", "prod_base_jzg");
ptObject.put("treeInnerId", "8cd2ff71038aec212a3e67a6a8ab4038");
break;
case "控制電纜":
ptObject.put("classId", "prod_base_kzdl");
ptObject.put("treeInnerId", "d5b49a075f131244f2f0948b29420dfc");
break;
case "氣瓶":
ptObject.put("classId", "prod_base_qp");
ptObject.put("treeInnerId", "aabb3ad2d955264a5d7529ddc5f134ef");
break;
case "儀表":
ptObject.put("classId", "prod_base_yb");
ptObject.put("treeInnerId", "a63f4bd75015d3b73674daeffb58eff6");
break;
case "壓力錶":
ptObject.put("classId", "prod_base_ylb");
ptObject.put("treeInnerId", "f291332eb8711662bd97e703a4bbee2f");
break;
case "壓力管道":
ptObject.put("classId", "prod_base_ylgd");
ptObject.put("treeInnerId", "d67365c26a7b7c3c90077fa7fefc220d");
break;
case "引壓管":
ptObject.put("classId", "prod_base_yyg");
ptObject.put("treeInnerId", "4f6e8fa2447e3f0be01e5f132f0f64ab");
break;
case "轉接管":
ptObject.put("classId", "prod_base_zjg");
ptObject.put("treeInnerId", "fcf4e27a20f16776a027398885e1f2a2");
break;
default:
ptObject.put("classId", "");
ptObject.put("treeInnerId", "");
}
}
}
4.建立Controller介面和實現
/**
* 匯入資源資料
*
* @param file
* @return
*/
@Override
public List<PtObject> impordProd(MultipartFile file) throws IOException {
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new RuntimeException("上傳檔案格式不正確");
}
List<PtObject> errorList = new ArrayList<>();
ZytzReadListener zytzReadListener = new ZytzReadListener(errorList, emsBaseService, code165Utils);
EasyExcel.read(file.getInputStream(), ZytzModel.class, zytzReadListener).sheet().doRead();
return errorList;
}
二、匯出
1.引入esayExcel JAR包
/**
* 匯出資源excel
* @param response
* @param innerIds
*/
@Override
public void exportProd(HttpServletResponse response, String innerIds) {
String[] split = innerIds.split(",");
String sql = "select * from prod_base ";
if (!innerIds.equals("") && split.length > 0){
sql += "where INNERID in ('" + innerIds.replaceAll(",","','") +"')";
}
List<Map<String, Object>> maps = emsBaseService.selectDataBySql(sql);
String[] head = {"資源名稱", "資源型別", "崗位呼號", "型號", "資源狀態", "出廠編號", "有效期","檢定日期", "引數", "精度", "量程", "圖號", "位置", "製造單位", "公稱直徑", "整定壓力","累計使用時間", "累計使用次數", "單次使用時間", "單次使用次數", "備註"};
List<List<String>> headList = new ArrayList<>();
for (String s : head) {
List<String> list = new ArrayList<>();
list.add(s);
headList.add(list);
}
List<List<Object>> dataList = new ArrayList<>();
for (Map<String, Object> map : maps) {
List<Object> data = new ArrayList<>();
data.add(map.get("res_name") != null ? map.get("res_name").toString() : "");
data.add(map.get("res_classname") != null ? map.get("res_classname").toString() : "");
data.add(map.get("callSign") != null ? map.get("callSign").toString() : "");
data.add(map.get("modelNum") != null ? map.get("modelNum").toString() : "");
if (map.get("status") != null){
switch (map.get("status").toString()){
case "0": data.add("正常"); break;
case "1": data.add("已使用"); break;
case "2": data.add("檢定中"); break;
case "3": data.add("報廢"); break;
default: data.add("");
}
} else {
data.add("");
}
data.add(map.get("factoryId") != null ? map.get("factoryId").toString() : "");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
// 有效期
if (map.get("validityInspTime") != null){
Date date = new Date(Long.parseLong(map.get("validityInspTime").toString()));
String format = simpleDateFormat.format(date);
data.add(format);
} else {
data.add("");
}
// 檢定日期
if (map.get("docimasyTime") != null){
Date date = new Date(Long.parseLong(map.get("docimasyTime").toString()));
String format = simpleDateFormat.format(date);
data.add(format);
} else {
data.add("");
}
data.add(map.get("parameter") != null ? map.get("parameter").toString() : "");
data.add(map.get("accuracy") != null ? map.get("accuracy").toString() : "");
data.add(map.get("capacity") != null ? map.get("capacity").toString() : "");
data.add(map.get("drawNum") != null ? map.get("drawNum").toString() : "");
data.add(map.get("location") != null ? map.get("location").toString() : "");
data.add(map.get("zzdw") != null ? map.get("zzdw").toString() : "");
data.add(map.get("gczj") != null ? map.get("gczj").toString() : "");
data.add(map.get("zdyl") != null ? map.get("zdyl").toString() : "");
data.add(map.get("useTime") != null ? map.get("useTime").toString() : "");
data.add(map.get("useNumber") != null ? map.get("useNumber").toString() : "");
data.add(map.get("varyTime") != null ? map.get("varyTime").toString() : "");
data.add(map.get("varyNumber") != null ? map.get("varyNumber").toString() : "");
data.add(map.get("remark") != null ? map.get("remark").toString() : "");
dataList.add(data);
}
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("試驗資源", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + System.currentTimeMillis() + ".xlsx");
EasyExcel.write(response.getOutputStream())
.registerConverter(new LongStringConverter())
.head(headList)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet("模板")
.doWrite(dataList);
} catch (IOException e) {
throw new RuntimeException(e);
}
}