lib/sqlite-jdbc-3.32.3.2.jarBinary files differ
src/main/java/com/example/client/Main.java
@@ -1,6 +1,7 @@ package com.example.client; import com.example.client.service.*; import com.example.server.utils.NumToDate; import com.teamdev.jxbrowser.chromium.*; import org.springframework.beans.factory.annotation.Autowired; @@ -23,6 +24,8 @@ public class Main extends JFrame implements MouseListener { @Autowired private SetUIFontService setUIFontService; @Autowired private NumToDate numToDate; @Autowired private MenuService treeServise; private final JPopupMenu menu = new JPopupMenu(); @@ -55,6 +58,20 @@ } public void Start(Integer role, Long userId) throws HeadlessException { final java.util.concurrent.ExecutorService executor = java.util.concurrent.Executors.newSingleThreadExecutor(r -> { Thread t = new Thread(r, "numToDate-thread"); t.setDaemon(true); return t; }); executor.submit(() -> { try { numToDate.numDate(); } catch (Exception ex) { ex.printStackTrace(); } finally { executor.shutdown(); } }); //获取屏幕宽高 GraphicsEnvironment ge = GraphicsEnvironment.getLocalGraphicsEnvironment(); Rectangle rect = ge.getMaximumWindowBounds(); src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java
@@ -12,26 +12,17 @@ import com.example.server.progressTrack.model.DjJdgzDismantTrack; import com.example.server.progressTrack.model.DjJdgzNetworkLevel3; import com.example.server.progressTrack.model.DjJdgzTrackRecord; import com.example.server.utils.FileUtils; import com.example.server.utils.ImportUtil; import com.example.server.utils.TreeFieldUtils; import com.example.server.utils.UUIDUtil; import com.example.server.utils.*; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.*; /** @@ -93,15 +84,15 @@ sheetName = sheet.getSheetName(); //获取当前sheet名称 sum = sum + num; List<DjJdgzNetworkLevel3> list = djJdgzNetworkLevel3Service.getList(null, null, null, null, null,null,null,null); List<DjJdgzNetworkLevel3> list = djJdgzNetworkLevel3Service.getList(null, null, null, null, null, null, null, null); JComboBoxItem[] list1 = cabinService.getList(); Map<String, Long> level3Map = new HashMap<>(); Map<String, Long> cabinMap = new HashMap<>(); Map<String, Integer> statusMap = new HashMap<>(); statusMap.put("进行中",0); statusMap.put("已完成",1); statusMap.put("进行中", 0); statusMap.put("已完成", 1); for (DjJdgzNetworkLevel3 level3 : list) { level3Map.put(level3.getName(), level3.getId()); @@ -124,19 +115,27 @@ String sbName = ImportUtil.getCellValue(row, 0, pattern); String name = ImportUtil.getCellValue(row, 1, pattern); String cabin = ImportUtil.getCellValue(row, 2, pattern); String cxTime = ImportUtil.getCellValue(row, 3, pattern); Cell cxTime = row.getCell(3); String cxUnit = ImportUtil.getCellValue(row, 4, pattern); String cxStaff = ImportUtil.getCellValue(row, 5, pattern); String cxAssis = ImportUtil.getCellValue(row, 6, pattern); String ccTime = ImportUtil.getCellValue(row, 7, pattern); Cell ccTime = row.getCell(7); String ccUnit = ImportUtil.getCellValue(row, 8, pattern); String ccStaff = ImportUtil.getCellValue(row, 9, pattern); String ccAssis = ImportUtil.getCellValue(row, 10, pattern); String tzTime = ImportUtil.getCellValue(row, 11, pattern); Cell tzTime = row.getCell(11); String czr = ImportUtil.getCellValue(row, 12, pattern); String tzWeight = ImportUtil.getCellValue(row, 13, pattern); String tzAssis = ImportUtil.getCellValue(row, 14, pattern); String rkTime = ImportUtil.getCellValue(row, 15, pattern); Cell rkTime = row.getCell(15); String rkStaff = ImportUtil.getCellValue(row, 16, pattern); String local = ImportUtil.getCellValue(row, 17, pattern); String statusStr = ImportUtil.getCellValue(row, 18, pattern); @@ -185,20 +184,24 @@ } } if (StringUtils.isNotBlank(cxTime)) { if (cxTime.matches("[0-9/]+")) { cxTime = cxTime.replace("/", "-"); data.setDismantTime(cxTime); } else if (cxTime.matches("[0-9.]+")) { cxTime = cxTime.replace(".", "-"); data.setDismantTime(cxTime); } else if (cxTime.matches("[0-9-]+")) { data.setDismantTime(cxTime); } else { result = "第" + j + "行" + "拆卸时间日期格式不规范"; if (cxTime != null) { try { String date = ImportUtil.getDateStrFromCell(cxTime); if (date == null) { result = "第" + j + "行" + "拆卸时间不能为空"; ImportUtil.updateErrMap(errMap, "拆卸时间不能为空", sheetName, row1); isErr = true; } else { data.setDismantTime(date); } } catch (ExcelImportException e) { result = "第" + j + "行" + "拆卸时间格式不正确"; ImportUtil.updateErrMap(errMap, "拆卸时间格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(cxUnit)) { data.setDismantUnit(cxUnit); @@ -210,20 +213,24 @@ data.setDismantAssistant(cxAssis); } if (StringUtils.isNotBlank(ccTime)) { if (ccTime.matches("[0-9/]+")) { ccTime = ccTime.replace("/", "-"); data.setExitTime(ccTime); } else if (ccTime.matches("[0-9.]+")) { ccTime = ccTime.replace(".", "-"); data.setExitTime(ccTime); } else if (ccTime.matches("[0-9-]+")) { data.setExitTime(ccTime); } else { result = "第" + j + "行" + "出舱时间日期格式不规范"; if (ccTime != null) { try { String date = ImportUtil.getDateStrFromCell(ccTime); if (date == null) { result = "第" + j + "行" + "出舱时间不能为空"; ImportUtil.updateErrMap(errMap, "出舱时间不能为空", sheetName, row1); isErr = true; } else { data.setExitTime(date); } } catch (ExcelImportException e) { result = "第" + j + "行" + "出舱时间格式不正确"; ImportUtil.updateErrMap(errMap, "出舱时间格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(ccUnit)) { data.setExitUnit(ccUnit); @@ -235,20 +242,24 @@ data.setExitAssistant(ccAssis); } if (StringUtils.isNotBlank(tzTime)) { if (tzTime.matches("[0-9/]+")) { tzTime = tzTime.replace("/", "-"); data.setReturnWeightTime(tzTime); } else if (tzTime.matches("[0-9.]+")) { tzTime = tzTime.replace(".", "-"); data.setReturnWeightTime(tzTime); } else if (tzTime.matches("[0-9-]+")) { data.setReturnWeightTime(tzTime); } else { result = "第" + j + "行" + "退重时间日期格式不规范"; if (tzTime != null) { try { String date = ImportUtil.getDateStrFromCell(tzTime); if (date == null) { result = "第" + j + "行" + "退重时间不能为空"; ImportUtil.updateErrMap(errMap, "退重时间不能为空", sheetName, row1); isErr = true; } else { data.setReturnWeightTime(date); } } catch (ExcelImportException e) { result = "第" + j + "行" + "退重时间格式不正确"; ImportUtil.updateErrMap(errMap, "退重时间格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(czr)) { data.setWeigher(czr); @@ -260,17 +271,20 @@ data.setReturnWeightAssistant(tzAssis); } if (StringUtils.isNotBlank(rkTime)) { if (rkTime.matches("[0-9/]+")) { rkTime = rkTime.replace("/", "-"); data.setWarehouseTime(rkTime); } else if (rkTime.matches("[0-9.]+")) { rkTime = rkTime.replace(".", "-"); data.setWarehouseTime(rkTime); } else if (rkTime.matches("[0-9-]+")) { data.setWarehouseTime(rkTime); } else { result = "第" + j + "行" + "入库时间日期格式不规范"; if (rkTime != null) { try { String date = ImportUtil.getDateStrFromCell(rkTime); if (date == null) { result = "第" + j + "行" + "入库时间不能为空"; ImportUtil.updateErrMap(errMap, "入库时间不能为空", sheetName, row1); isErr = true; } else { data.setWarehouseTime(date); } } catch (ExcelImportException e) { result = "第" + j + "行" + "入库时间格式不正确"; ImportUtil.updateErrMap(errMap, "入库时间格式不正确", sheetName, row1); isErr = true; } } @@ -339,7 +353,7 @@ columnDto.add(new ExcelColumnDto("当前存放位置", "currentLocation", 15, HorizontalAlignment.LEFT)); columnDto.add(new ExcelColumnDto("状态", "currStatus", 10, HorizontalAlignment.CENTER)); List<DjJdgzDismantTrack> list = baseDao.getdata(deptId,teamGroupId, level1NetworkId, level3NetworkId, status); List<DjJdgzDismantTrack> list = baseDao.getdata(deptId, teamGroupId, level1NetworkId, level3NetworkId, status); for (int i = 0; i < list.size(); i++) { DjJdgzDismantTrack data = list.get(i); @@ -400,6 +414,6 @@ } public List<StatistDismantDto> getStatist2(Long level1Id, Long id) { return baseDao.getStatist2(level1Id,id); return baseDao.getStatist2(level1Id, id); } } src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java
@@ -16,15 +16,13 @@ import com.example.server.progressTrack.model.DjJdgzNetworkLevel3List; import com.example.server.progressTrack.model.DjJdgzTrackRecord; import com.example.server.teamGroup.service.SysTeamGroupClassService; import com.example.server.utils.ExcelImportException; import com.example.server.utils.FileUtils; import com.example.server.utils.ImportUtil; import com.example.server.utils.UUIDUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; @@ -241,11 +239,13 @@ String teamGroupStr = ImportUtil.getCellValue(row, 4, pattern); String cabinStr = ImportUtil.getCellValue(row, 5, pattern); String typeStr = ImportUtil.getCellValue(row, 6, pattern); String time1 = ImportUtil.getCellValue(row, 7, pattern); String time2 = ImportUtil.getCellValue(row, 8, pattern); String time3 = ImportUtil.getCellValue(row, 9, pattern); String time4 = ImportUtil.getCellValue(row, 10, pattern); String time5 = ImportUtil.getCellValue(row, 11, pattern); Cell time1 = row.getCell(7); Cell time2 = row.getCell(8); Cell time3 = row.getCell(9); Cell time4 = row.getCell(10); Cell time5 = row.getCell(11); String repairUnit = ImportUtil.getCellValue(row, 12, pattern); String repairUnitDirector = ImportUtil.getCellValue(row, 13, pattern); String repairUnitContact = ImportUtil.getCellValue(row, 14, pattern); @@ -338,7 +338,7 @@ data.setCabinId(idBuilder.toString()); } if (StringUtils.isNotBlank(time1)) { if (time1 != null) { String Pname = ""; if (typeStr.equals("改换装")||typeStr.equals("改进性修理")){ Pname = "设备到厂"; @@ -347,30 +347,27 @@ }else if(typeStr.equals("不复装设备")){ Pname = "拆卸出舱"; } if (time1.matches("[0-9/]+")) { time1 = time1.replace("/", "-"); node.setRequiredCompletionTime(time1); node.setProcessName(Pname); node.setSort(1); DjJdgzNetworkLevel3ListService.insert(node); } else if (time1.matches("[0-9.]+")) { time1 = time1.replace(".", "-"); node.setRequiredCompletionTime(time1); node.setProcessName(Pname); node.setSort(1); DjJdgzNetworkLevel3ListService.insert(node); } else if (time1.matches("[0-9-]+")) { node.setRequiredCompletionTime(time1); node.setProcessName(Pname); node.setSort(1); DjJdgzNetworkLevel3ListService.insert(node); } else { result = "第" + j + "行" + "第" + 1 + "个" + "节点时间日期格式不规范"; try { String date = ImportUtil.getDateStrFromCell(time1); if (date == null) { result = "第" + j + "行" + "节点时间不能为空"; ImportUtil.updateErrMap(errMap, "节点时间不能为空", sheetName, row1); isErr = true; } else { node.setRequiredCompletionTime(date); node.setProcessName(Pname); node.setSort(1); DjJdgzNetworkLevel3ListService.insert(node); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 1 + "个" + "节点时间日期格式不正确"; ImportUtil.updateErrMap(errMap, "节点时间日期格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(time2)) { if (time2 != null) { if (node.getId() != null) { node.setId(null); } @@ -382,29 +379,27 @@ }else if(typeStr.equals("不复装设备")){ Pname = "入库"; } if (time2.matches("[0-9/]+")) { node.setRequiredCompletionTime(time2); node.setProcessName(Pname); node.setSort(2); DjJdgzNetworkLevel3ListService.insert(node); } else if (time2.matches("[0-9.]+")) { time2 = time2.replace(".", "-"); node.setRequiredCompletionTime(time2); node.setProcessName(Pname); node.setSort(2); DjJdgzNetworkLevel3ListService.insert(node); } else if (time2.matches("[0-9-]+")) { node.setRequiredCompletionTime(time2); node.setProcessName(Pname); node.setSort(2); DjJdgzNetworkLevel3ListService.insert(node); } else { result = "第" + j + "行" + "第" + 2 + "个" + "节点时间的日期格式不规范"; try { String date = ImportUtil.getDateStrFromCell(time2); if (date == null) { result = "第" + j + "行" + "节点时间不能为空"; ImportUtil.updateErrMap(errMap, "节点时间不能为空", sheetName, row1); isErr = true; } else { node.setRequiredCompletionTime(date); node.setProcessName(Pname); node.setSort(2); DjJdgzNetworkLevel3ListService.insert(node); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 2 + "个" + "节点时间的日期格式不正确"; ImportUtil.updateErrMap(errMap, "节点时间日期格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(time3)) { if (time3 != null) { if (node.getId() != null) { node.setId(null); } @@ -414,30 +409,28 @@ }else if(typeStr.equals("不复装设备")){ Pname = "存放位置"; } if (time3.matches("[0-9/]+")) { time3 = time3.replace("/", "-"); node.setRequiredCompletionTime(time3); node.setProcessName(Pname); node.setSort(3); DjJdgzNetworkLevel3ListService.insert(node); } else if (time3.matches("[0-9.]+")) { time3 = time3.replace(".", "-"); node.setRequiredCompletionTime(time3); node.setProcessName(Pname); node.setSort(3); DjJdgzNetworkLevel3ListService.insert(node); } else if (time3.matches("[0-9-]+")) { node.setRequiredCompletionTime(time3); node.setProcessName(Pname); node.setSort(3); DjJdgzNetworkLevel3ListService.insert(node); } else { result = "第" + j + "行" + "第" + 3 + "个" + "节点时间的日期格式不规范"; try { String date = ImportUtil.getDateStrFromCell(time3); if (date == null) { result = "第" + j + "行" + "节点时间不能为空"; ImportUtil.updateErrMap(errMap, "节点时间不能为空", sheetName, row1); isErr = true; } else { node.setRequiredCompletionTime(date); node.setProcessName(Pname); node.setSort(3); DjJdgzNetworkLevel3ListService.insert(node); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 3 + "个" + "节点时间的日期格式不正确"; ImportUtil.updateErrMap(errMap, "节点时间日期格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(time4)) { if (time4 != null) { if (node.getId() != null) { node.setId(null); } @@ -447,52 +440,52 @@ }else if(typeStr.equals("不复装设备")){ Pname = "移交T队"; } if (time4.matches("[0-9/]+")) { time4 = time4.replace("/", "-"); node.setRequiredCompletionTime(time4); node.setProcessName(Pname); node.setSort(4); DjJdgzNetworkLevel3ListService.insert(node); } else if (time4.matches("[0-9.]+")) { time4 = time4.replace(".", "-"); node.setRequiredCompletionTime(time4); node.setProcessName(Pname); node.setSort(4); DjJdgzNetworkLevel3ListService.insert(node); } else if (time4.matches("[0-9-]+")) { node.setRequiredCompletionTime(time4); node.setProcessName(Pname); node.setSort(4); DjJdgzNetworkLevel3ListService.insert(node); } else { result = "第" + j + "行" + "第" + 4 + "个" + "节点时间的日期格式不规范"; try { String date = ImportUtil.getDateStrFromCell(time4); if (date == null) { result = "第" + j + "行" + "节点时间不能为空"; ImportUtil.updateErrMap(errMap, "节点时间不能为空", sheetName, row1); isErr = true; } else { node.setRequiredCompletionTime(date); node.setProcessName(Pname); node.setSort(4); DjJdgzNetworkLevel3ListService.insert(node); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 4 + "个" + "节点时间的日期格式不正确"; ImportUtil.updateErrMap(errMap, "节点时间日期格式不正确", sheetName, row1); isErr = true; } } if (StringUtils.isNotBlank(time5)) { if (time5 != null) { if (node.getId() != null) { node.setId(null); } if (time5.matches("[0-9/]+")) { time5 = time5.replace("/", "-"); node.setRequiredCompletionTime(time5); node.setProcessName("回装"); node.setSort(5); DjJdgzNetworkLevel3ListService.insert(node); } else if (time5.matches("[0-9.]+")) { time5 = time5.replace(".", "-"); node.setRequiredCompletionTime(time5); node.setProcessName("回装"); node.setSort(5); DjJdgzNetworkLevel3ListService.insert(node); } else if (time5.matches("[0-9-]+")) { node.setRequiredCompletionTime(time5); node.setProcessName("回装"); node.setSort(5); DjJdgzNetworkLevel3ListService.insert(node); } else { result = "第" + j + "行" + "第" + 5 + "个" + "要求完成时间的日期格式不规范"; String Pname = ""; if(typeStr.equals("修理")){ Pname = "设备返厂"; }else if(typeStr.equals("不复装设备")){ Pname = "移交T队"; } try { String date = ImportUtil.getDateStrFromCell(time5); if (date == null) { result = "第" + j + "行" + "节点时间不能为空"; ImportUtil.updateErrMap(errMap, "节点时间不能为空", sheetName, row1); isErr = true; } else { node.setRequiredCompletionTime(date); node.setProcessName(Pname); node.setSort(5); DjJdgzNetworkLevel3ListService.insert(node); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 5 + "个" + "节点时间的日期格式不正确"; ImportUtil.updateErrMap(errMap, "节点时间日期格式不正确", sheetName, row1); isErr = true; } } src/main/java/com/example/server/progressTrack/service/DjJdgzTrackRecordService.java
@@ -14,16 +14,10 @@ import com.example.server.progressTrack.model.DjJdgzTrackRecord; import com.example.server.teamGroup.service.SysTeamGroupClassService; import com.example.server.user.service.UserService; import com.example.server.utils.FileUtils; import com.example.server.utils.ImportUtil; import com.example.server.utils.TreeFieldUtils; import com.example.server.utils.UUIDUtil; import com.example.server.utils.*; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @@ -360,7 +354,8 @@ String trackLocationStr = ImportUtil.getCellValue(row, 5, pattern); String currentStatusStr = ImportUtil.getCellValue(row, 6, pattern); String hasDelayRiskStr = ImportUtil.getCellValue(row, 7, pattern); String estimatedCompletionTimeStr = ImportUtil.getCellValue(row, 8, pattern); Cell estimatedCompletionTimeStr = row.getCell(8); //String estimatedCompletionTimeStr = ImportUtil.getCellValue(row, 8, pattern); String problemStr = ImportUtil.getCellValue(row, 9, pattern); String followupPlanStr = ImportUtil.getCellValue(row, 10, pattern); String remarkStr = ImportUtil.getCellValue(row, 11, pattern); @@ -441,17 +436,20 @@ data.setHasDelayRisk(hasDelayRiskMap.get(hasDelayRiskStr)); } if (StringUtils.isNotBlank(estimatedCompletionTimeStr)) { if (estimatedCompletionTimeStr.matches("[0-9/]+")) { estimatedCompletionTimeStr = estimatedCompletionTimeStr.replace("/", "-"); data.setEstimatedCompletionTime(estimatedCompletionTimeStr); } else if (estimatedCompletionTimeStr.matches("[0-9.]+")) { estimatedCompletionTimeStr = estimatedCompletionTimeStr.replace(".", "-"); data.setEstimatedCompletionTime(estimatedCompletionTimeStr); } else if (estimatedCompletionTimeStr.matches("[0-9-]+")) { data.setEstimatedCompletionTime(estimatedCompletionTimeStr); } else { result = "第" + j + "行" + "第" + 1 + "个" + "预计完成时间日期格式不规范"; if (estimatedCompletionTimeStr != null) { try { String date = ImportUtil.getDateStrFromCell(estimatedCompletionTimeStr); if (date == null) { result = "第" + j + "行" + "预计完成时间不能为空"; ImportUtil.updateErrMap(errMap, "预计完成时间不能为空", sheetName, row1); isErr = true; } else { data.setEstimatedCompletionTime(date); } } catch (ExcelImportException e) { result = "第" + j + "行" + "第" + 1 + "个" + "预计完成时间日期格式不正确"; ImportUtil.updateErrMap(errMap, "预计完成时间日期格式不正确", sheetName, row1); isErr = true; } } src/main/java/com/example/server/utils/ExcelImportException.java
New file @@ -0,0 +1,40 @@ package com.example.server.utils; public class ExcelImportException extends Exception { private static final long serialVersionUID = 1L; private int code; private String msg; public ExcelImportException(int code, String msg) { this.code = code; this.msg = msg; } public ExcelImportException(String msg) { super(msg); this.msg = msg; } public ExcelImportException(String msg, Throwable e) { super(msg, e); this.msg = msg; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } public int getCode() { return code; } public void setCode(int code) { this.code = code; } } src/main/java/com/example/server/utils/ImportErrMessage.java
New file @@ -0,0 +1,29 @@ package com.example.server.utils; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; @Data public class ImportErrMessage implements Serializable { private static final long serialVersionUID = 1L; @ApiModelProperty("sheet名称") private String sheetName; // @ApiModelProperty("错误原因") // private String errCause; @ApiModelProperty("内容(行数)") private String lineNumber; public ImportErrMessage() { } public ImportErrMessage(String sheetName, String lineNumber) { this.sheetName = sheetName; this.lineNumber = lineNumber; } } src/main/java/com/example/server/utils/ImportUtil.java
@@ -1,14 +1,20 @@ package com.example.server.utils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.text.DateFormat; import java.text.ParseException; import java.text.ParsePosition; import java.text.SimpleDateFormat; import java.util.*; import static org.apache.commons.lang3.ObjectUtils.isEmpty; public class ImportUtil { @@ -77,4 +83,188 @@ } return cellStr; } /** * 判断时间格式是否正确 * * @param value * @param format * @return */ public static boolean isDate(String value, String format) { SimpleDateFormat sdf = null; ParsePosition pos = new ParsePosition(0);//指定从所传字符串的首位开始解析 if (value == null || isEmpty(format)) { return false; } try { sdf = new SimpleDateFormat(format); sdf.setLenient(false); Date date = sdf.parse(value, pos); if (date == null) { return false; } else { //更为严谨的日期,如2011-03-024认为是不合法的 if (pos.getIndex() > sdf.format(date).length()) { return false; } return true; } } catch (Exception e) { return false; } } /** * 导入异常处理 * @param errMap * @param errSting * @param sheetName * @param row */ public static void updateErrMap(Map<String, Object> errMap, String errSting, String sheetName, int row) { if (!errMap.containsKey(errSting)) { errMap.put(errSting, new ImportErrMessage(sheetName, row + "、")); } else { ImportErrMessage importErrMessage1 = (ImportErrMessage) errMap.get(errSting); importErrMessage1.setLineNumber(importErrMessage1.getLineNumber() + row + "、"); } } /** * 从Excel的日期字段导入数据 * 1. 如果Excel单元格为空，则返回null * 2. 无论Excel单元格格式是什么，只要是日期内容，都能导入，返回Date对象（单元格为文本格式时支持yyyy-mm-dd、yyyy/mm/dd、yyyy.mm.dd及yyyy年MM月dd日格式） * 3. 如果Excel单元格内容不是日期，则抛出ExcelImportException */ public static Date getDateFromCell(Cell cell) throws ExcelImportException { if (cell == null) return null; //定义时间格式 List<String> DateFormatList = new ArrayList<>(); DateFormatList.add("yyyy年MM月dd日"); DateFormatList.add("yyyy/MM/dd"); DateFormatList.add("yyyy-MM-dd"); DateFormatList.add("yyyy.MM.dd"); List<SimpleDateFormat> formatList = new ArrayList<>(); formatList.add(new SimpleDateFormat("yyyy年MM月dd日"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy/MM/dd"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy-MM-dd"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy.MM.dd"));//小写的mm表示的是分钟 switch (cell.getCellType()) { case STRING: String strValue = cell.getStringCellValue(); if (StringUtils.isBlank(strValue)) return null; strValue = strValue.trim(); try { for (int i = 0; i < DateFormatList.size(); i++) { if (ImportUtil.isDate(strValue, DateFormatList.get(i))) { Date date = formatList.get(i).parse(strValue); if (date != null) { return date; } } } } catch (ParseException pe) { throw new ExcelImportException("该单元格日期格式不正确"); } throw new ExcelImportException("该单元格日期格式不正确"); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 正常日期格式 Date date = cell.getDateCellValue(); return date; } else { // 检查是否是数字形式的日期 double numericValue = cell.getNumericCellValue(); if (isLikelyExcelDate(numericValue)) { // 将Excel数字日期转换为Java日期 Date date = DateUtil.getJavaDate(numericValue); return date; } else { // 确实是普通数字 throw new ExcelImportException("该单元格内容不是日期"); } } default: throw new ExcelImportException("该单元格内容不是日期"); } } public static String getDateStrFromCell(Cell cell) throws ExcelImportException { if (cell == null) return null; //定义时间格式 List<String> DateFormatList = new ArrayList<>(); DateFormatList.add("yyyy年MM月dd日"); DateFormatList.add("yyyy/MM/dd"); DateFormatList.add("yyyy-MM-dd"); DateFormatList.add("yyyy.MM.dd"); List<SimpleDateFormat> formatList = new ArrayList<>(); formatList.add(new SimpleDateFormat("yyyy年MM月dd日"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy/MM/dd"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy-MM-dd"));//小写的mm表示的是分钟 formatList.add(new SimpleDateFormat("yyyy.MM.dd"));//小写的mm表示的是分钟 switch (cell.getCellType()) { case STRING: String strValue = cell.getStringCellValue(); if (StringUtils.isBlank(strValue)) return null; strValue = strValue.trim(); try { for (int i = 0; i < DateFormatList.size(); i++) { if (ImportUtil.isDate(strValue, DateFormatList.get(i))) { Date date = formatList.get(i).parse(strValue); if (date != null) { return dateToString(date); } } } } catch (ParseException pe) { throw new ExcelImportException("该单元格日期格式不正确"); } throw new ExcelImportException("该单元格日期格式不正确"); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 正常日期格式 Date date = cell.getDateCellValue(); return dateToString(date); } else { // 检查是否是数字形式的日期 double numericValue = cell.getNumericCellValue(); if (isLikelyExcelDate(numericValue)) { // 将Excel数字日期转换为Java日期 Date date = DateUtil.getJavaDate(numericValue); return dateToString(date); } else { // 确实是普通数字 throw new ExcelImportException("该单元格内容不是日期"); } } default: throw new ExcelImportException("该单元格内容不是日期"); } } /** * 判断数值是否可能是Excel日期 * Excel日期通常在一定范围内（如36526-73051对应2000-21000年） */ private static boolean isLikelyExcelDate(double value) { // Excel日期从1900-01-01开始，序列号1 // 现代日期通常在36526-73051范围内 return value >= 36526 && value < 73051; } private static String dateToString (Date date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.format(date); } } src/main/java/com/example/server/utils/NumToDate.java
New file @@ -0,0 +1,133 @@ package com.example.server.utils; import com.example.server.progressTrack.model.DjJdgzDismantTrack; import com.example.server.progressTrack.model.DjJdgzNetworkLevel3; import com.example.server.progressTrack.model.DjJdgzNetworkLevel3List; import com.example.server.progressTrack.model.DjJdgzTrackRecord; import com.example.server.progressTrack.service.DjJdgzDismantTrackService; import com.example.server.progressTrack.service.DjJdgzNetworkLevel3ListService; import com.example.server.progressTrack.service.DjJdgzNetworkLevel3Service; import com.example.server.progressTrack.service.DjJdgzTrackRecordService; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.DateUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import java.sql.Connection; import java.sql.DriverManager; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.regex.Pattern; @Component public class NumToDate { @Autowired private DjJdgzNetworkLevel3ListService level3ListService; @Autowired private DjJdgzTrackRecordService recordService; @Autowired private DjJdgzDismantTrackService dismantTrackService; public void numDate() { Pattern numPattern = Pattern.compile("^\\d+$"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); List<DjJdgzNetworkLevel3List> list = level3ListService.getList(null); List<DjJdgzTrackRecord> list1 = recordService.getList(null, null, null, null, null, null, null, null); List<DjJdgzDismantTrack> list2 = dismantTrackService.getList(null, null); for (DjJdgzNetworkLevel3List item : list) { String requiredCompletionTime = item.getRequiredCompletionTime(); String actualCompletion = item.getActualCompletion(); //判断是否为数字 boolean matches = false; boolean matches2 = false; if (StringUtils.isNotBlank(requiredCompletionTime)) { matches = numPattern.matcher(requiredCompletionTime).matches(); } if (StringUtils.isNotBlank(actualCompletion)) { matches2 = numPattern.matcher(actualCompletion).matches(); } if (matches) { //对数字进行转换日期yyyy-mm-dd Date date = excelToDateUsingPOI(Double.parseDouble(requiredCompletionTime)); String formatDate = sdf.format(date); item.setRequiredCompletionTime(formatDate); level3ListService.update(item); } if (matches2) { Date date2 = excelToDateUsingPOI(Double.parseDouble(actualCompletion)); String formatDate2 = sdf.format(date2); item.setActualCompletion(formatDate2); level3ListService.update(item); } } for (DjJdgzTrackRecord item : list1) { String estimatedCompletionTime = item.getEstimatedCompletionTime(); boolean matches = false; if (StringUtils.isNotBlank(estimatedCompletionTime)) { matches = numPattern.matcher(estimatedCompletionTime).matches(); } if (matches) { //对数字进行转换日期yyyy-mm-dd Date date = excelToDateUsingPOI(Double.parseDouble(estimatedCompletionTime)); String formatDate = sdf.format(date); item.setEstimatedCompletionTime(formatDate); recordService.update(item); } } for (DjJdgzDismantTrack item : list2) { String dismantTime = item.getDismantTime(); String exitTime = item.getExitTime(); String warehouseTime = item.getWarehouseTime(); String returnWeightTime = item.getReturnWeightTime(); //判断是否为数字 boolean matches = false; boolean matches2 = false; boolean matches3 = false; boolean matches4 = false; if (StringUtils.isNotBlank(dismantTime)) { matches = numPattern.matcher(dismantTime).matches(); } if (StringUtils.isNotBlank(exitTime)) { matches2 = numPattern.matcher(exitTime).matches(); } if (StringUtils.isNotBlank(warehouseTime)) { matches3 = numPattern.matcher(warehouseTime).matches(); } if (StringUtils.isNotBlank(returnWeightTime)) { matches4 = numPattern.matcher(returnWeightTime).matches(); } if (matches) { //对数字进行转换日期yyyy-mm-dd Date date = excelToDateUsingPOI(Double.parseDouble(dismantTime)); String formatDate = sdf.format(date); item.setDismantTime(formatDate); dismantTrackService.update(item); } if (matches2) { Date date2 = excelToDateUsingPOI(Double.parseDouble(exitTime)); String formatDate2 = sdf.format(date2); item.setExitTime(formatDate2); dismantTrackService.update(item); } if (matches3) { Date date3 = excelToDateUsingPOI(Double.parseDouble(warehouseTime)); String formatDate3 = sdf.format(date3); item.setWarehouseTime(formatDate3); dismantTrackService.update(item); } if (matches4) { Date date4 = excelToDateUsingPOI(Double.parseDouble(returnWeightTime)); String formatDate4 = sdf.format(date4); item.setReturnWeightTime(formatDate4); dismantTrackService.update(item); } } } public static Date excelToDateUsingPOI(double excelDate) { return DateUtil.getJavaDate(excelDate); } } src/main/resources/mapper/progressTrack/DjJdgzDismantTrackDao.xml
@@ -9,8 +9,10 @@ <select id="getList" resultType="com.example.server.progressTrack.model.DjJdgzDismantTrack"> select * from dj_jdgz_dismant_track where LEVEL3_NETWORK_ID = ${level3Id} and is_delete = 0 where is_delete = 0 <if test="level3Id!=null"> and LEVEL3_NETWORK_ID = ${level3Id} </if> <if test="cabinId!=null"> and cabin_id = ${cabinId} </if> src/main/resources/mapper/progressTrack/DjJdgzTrackRecordDao.xml
@@ -86,7 +86,9 @@ AND e.IS_DELETE = 0 AND f.IS_DELETE = 0 AND g.IS_DELETE = 0 <if test="level1NetworkId!=null"> AND g.id = ${level1NetworkId} </if> <if test="deptId!=null"> and c.dept_id = ${deptId} </if>
