From 53577ae0fcba9342b22ad1758303aa61409160d1 Mon Sep 17 00:00:00 2001
From: zhangherong <571457620@qq.com>
Date: 星期五, 12 九月 2025 19:10:58 +0800
Subject: [PATCH] art: 设备保养标准导入问题修改
---
src/main/java/org/jeecg/modules/eam/controller/EamMaintenanceStandardController.java | 251 +++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 225 insertions(+), 26 deletions(-)
diff --git a/src/main/java/org/jeecg/modules/eam/controller/EamMaintenanceStandardController.java b/src/main/java/org/jeecg/modules/eam/controller/EamMaintenanceStandardController.java
index 1546bcd..a9025c6 100644
--- a/src/main/java/org/jeecg/modules/eam/controller/EamMaintenanceStandardController.java
+++ b/src/main/java/org/jeecg/modules/eam/controller/EamMaintenanceStandardController.java
@@ -1,6 +1,7 @@
package org.jeecg.modules.eam.controller;
import cn.hutool.core.collection.CollectionUtil;
+import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.annotations.Api;
@@ -8,7 +9,7 @@
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
-import org.apache.poi.xssf.usermodel.*;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.AutoLog;
import org.jeecg.common.constant.CommonConstant;
@@ -24,10 +25,14 @@
import org.jeecg.modules.eam.entity.EamMaintenanceStandardDetail;
import org.jeecg.modules.eam.request.EamMaintenanceStandardRequest;
import org.jeecg.modules.eam.service.IEamEquipmentService;
+import org.jeecg.modules.eam.service.IEamMaintenanceStandardDetailService;
import org.jeecg.modules.eam.service.IEamMaintenanceStandardService;
+import org.jeecg.modules.eam.vo.MaintenanceStandardDetailVo;
+import org.jeecg.modules.eam.vo.MaintenanceStandardVo;
import org.jeecg.modules.system.service.ISysBusinessCodeRuleService;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ImportParams;
+import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
@@ -37,10 +42,11 @@
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
-import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
@@ -60,6 +66,8 @@
private ISysBusinessCodeRuleService businessCodeRuleService;
@Autowired
private IEamEquipmentService eamEquipmentService;
+ @Autowired
+ private IEamMaintenanceStandardDetailService eamMaintenanceStandardDetailService;
/**
* 鍒嗛〉鍒楄〃鏌ヨ
@@ -242,6 +250,33 @@
return Result.OK(eamMaintenanceStandard);
}
+ @AutoLog(value = "淇濆吇鏍囧噯-閫氳繃璁惧id鏌ヨ淇濆吇鏍囧噯鍙婃槑缁嗛」")
+ @ApiOperation(value = "淇濆吇鏍囧噯-閫氳繃璁惧id鏌ヨ淇濆吇鏍囧噯鍙婃槑缁嗛」", notes = "淇濆吇鏍囧噯-閫氳繃璁惧id鏌ヨ淇濆吇鏍囧噯鍙婃槑缁嗛」")
+ @GetMapping(value = "/queryByEquipmentId")
+ public Result<MaintenanceStandardVo> queryByEquipmentId(@RequestParam("equipmentId") String equipmentId) {
+ EamMaintenanceStandard maintenanceStandard = eamMaintenanceStandardService.list(new LambdaQueryWrapper<EamMaintenanceStandard>()
+ .eq(EamMaintenanceStandard::getEquipmentId, equipmentId)
+ .eq(EamMaintenanceStandard::getDelFlag, CommonConstant.DEL_FLAG_0)
+ .eq(EamMaintenanceStandard::getStandardStatus, MaintenanceStandardStatusEnum.NORMAL.name())
+ .eq(EamMaintenanceStandard::getMaintenanceCategory, MaintenanceCategoryEnum.POINT_INSPECTION.name()))
+ .stream().findFirst().orElse(null);
+ if (maintenanceStandard == null) {
+ return Result.error("鏈壘鍒拌璁惧涓嬬殑淇濆吇鏍囧噯锛�");
+ }
+ MaintenanceStandardVo maintenanceStandardVo = new MaintenanceStandardVo();
+ BeanUtils.copyProperties(maintenanceStandard, maintenanceStandardVo);
+ List<EamMaintenanceStandardDetail> maintenanceStandardDetails = eamMaintenanceStandardDetailService
+ .selectByStandardId(maintenanceStandard.getId());
+ List<MaintenanceStandardDetailVo> maintenanceStandardDetailVos = CollectionUtil.newArrayList();
+ maintenanceStandardDetails.forEach(item -> {
+ MaintenanceStandardDetailVo maintenanceStandardDetailVo = new MaintenanceStandardDetailVo();
+ BeanUtils.copyProperties(item, maintenanceStandardDetailVo);
+ maintenanceStandardDetailVos.add(maintenanceStandardDetailVo);
+ });
+ maintenanceStandardVo.setMaintenanceStandardDetailList(maintenanceStandardDetailVos);
+ return Result.OK(maintenanceStandardVo);
+ }
+
/**
* 瀵煎嚭excel
*
@@ -261,37 +296,58 @@
* @return
*/
@RequestMapping(value = "/inspectionImportExcel", method = RequestMethod.POST)
- public Result<?> inspectionImportExcel(HttpServletRequest request, HttpServletResponse response) {
+ public Result<?> inspectionImportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
+
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
// 鑾峰彇涓婁紶鏂囦欢瀵硅薄
MultipartFile file = entity.getValue();
+ byte[] bytes = file.getBytes();
+ ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
+
ImportParams params = new ImportParams();
- params.setTitleRows(2);
- params.setHeadRows(1);
- params.setSheetNum(1);
- params.setLastOfInvalidRow(23);
+ params.setTitleRows(2); // 璺宠繃鍓�2琛屾爣棰�
+ params.setHeadRows(2); // 绗�3琛屾槸琛ㄥご
+ params.setSheetNum(1); // 璇诲彇绗竴涓伐浣滆〃
params.setNeedSave(true);
+
EamMaintenanceStandardRequest standardRequest = new EamMaintenanceStandardRequest();
try {
//璇诲彇璁惧缂栧彿锛屽浘鐗囩瓑
readExcel(file, standardRequest);
+ log.info("璇诲彇鍒扮殑璁惧缂栫爜: {}", standardRequest.getEquipmentCode());
+
EamEquipment equipment = eamEquipmentService.selectByEquipmentCode(standardRequest.getEquipmentCode());
if(equipment == null) {
log.error("璁惧涓嶅瓨鍦細{}", standardRequest.getEquipmentCode());
continue;
}
+
standardRequest.setStandardName(standardRequest.getEquipmentName() + "鐐规鏍囧噯");
standardRequest.setMaintenanceCategory(MaintenanceCategoryEnum.POINT_INSPECTION.name());
standardRequest.setEquipmentId(equipment.getId());
- //璇诲彇淇濆吇鏄庣粏鍐呭
- List<MaintenanceStandardImport> list = ExcelImportUtil.importExcel(file.getInputStream(), MaintenanceStandardImport.class, params);
+ // 璇诲彇淇濆吇鏄庣粏鍐呭鍓嶆坊鍔犺皟璇曚俊鎭�
+ log.info("Excel瀵煎叆鍙傛暟: titleRows={}, headRows={}, lastOfInvalidRow={}",
+ params.getTitleRows(), params.getHeadRows(), params.getLastOfInvalidRow());
+ List<MaintenanceStandardImport> list = ExcelImportUtil.importExcel(file.getInputStream(), MaintenanceStandardImport.class, params);
+ log.info("瀹為檯璇诲彇鍒扮殑鏄庣粏鏁伴噺: {}", list.size());
//鏄庣粏椤�
- List<EamMaintenanceStandardDetail> tableList = list.stream().map(EamMaintenanceStandardDetail::new).collect(Collectors.toList());
+ List<EamMaintenanceStandardDetail> tableList = new ArrayList<>();
+ for(MaintenanceStandardImport maintenanceStandardImport : list) {
+ try {
+ Integer.valueOf(maintenanceStandardImport.getItemCode());
+ } catch (NumberFormatException e) {
+ break;
+ }
+ tableList.add(new EamMaintenanceStandardDetail(maintenanceStandardImport));
+ }
+
standardRequest.setTableDetailList(tableList);
+ log.info("杞崲鍚庣殑鏄庣粏鏁伴噺: {}", tableList.size());
+
String codeSeq = businessCodeRuleService.generateBusinessCodeSeq(BusinessCodeConst.MAINTENANCE_STANDARD_CODE_RULE);
standardRequest.setStandardCode(codeSeq);
boolean b = eamMaintenanceStandardService.addMaintenanceStandard(standardRequest);
@@ -299,10 +355,8 @@
log.error("淇濆瓨澶辫触锛� {}", standardRequest.getEquipmentCode());
}
} catch (Exception e) {
- //update-begin-author:taoyan date:20211124 for: 瀵煎叆鏁版嵁閲嶅澧炲姞鎻愮ず
String msg = e.getMessage();
log.error("鏂囦欢 {} 澶勭悊寮傚父锛� {}", file.getOriginalFilename(), msg, e);
- //update-end-author:taoyan date:20211124 for: 瀵煎叆鏁版嵁閲嶅澧炲姞鎻愮ず
} finally {
try {
file.getInputStream().close();
@@ -315,8 +369,10 @@
}
+
+
/**
- * 閫氳繃excel瀵煎叆鏁版嵁
+ * 瀛ｄ繚閫氳繃excel瀵煎叆鏁版嵁
*
* @param request
* @param response
@@ -333,6 +389,7 @@
params.setTitleRows(2);
params.setHeadRows(1);
params.setSheetNum(1);
+ params.setLastOfInvalidRow(23);
params.setNeedSave(true);
EamMaintenanceStandardRequest standardRequest = new EamMaintenanceStandardRequest();
try {
@@ -344,6 +401,7 @@
continue;
}
standardRequest.setStandardName(standardRequest.getEquipmentName() + "淇濆吇鏍囧噯");
+
standardRequest.setMaintenanceCategory(MaintenanceCategoryEnum.QUARTERLY_MAINTENANCE.name());
standardRequest.setEquipmentId(equipment.getId());
//璇诲彇淇濆吇鏄庣粏鍐呭
@@ -373,6 +431,68 @@
return Result.ok("鏂囦欢瀵煎叆瀹屾垚锛�");
}
+
+
+ /**
+ * 骞翠繚閫氳繃excel瀵煎叆鏁版嵁
+ *
+ * @param request
+ * @param response
+ * @return
+ */
+ @RequestMapping(value = "/annualMaintenanceImportExcel", method = RequestMethod.POST)
+ public Result<?> annualMaintenanceImportExcel(HttpServletRequest request, HttpServletResponse response) {
+ MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
+ Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
+ for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
+ // 鑾峰彇涓婁紶鏂囦欢瀵硅薄
+ MultipartFile file = entity.getValue();
+
+ ImportParams params = new ImportParams();
+ params.setTitleRows(2);
+ params.setHeadRows(1);
+ params.setSheetNum(1);
+ params.setLastOfInvalidRow(23);
+ params.setNeedSave(true);
+ EamMaintenanceStandardRequest standardRequest = new EamMaintenanceStandardRequest();
+ try {
+ //璇诲彇璁惧缂栧彿锛屽浘鐗囩瓑
+ readWeekExcel(file, standardRequest);
+ EamEquipment equipment = eamEquipmentService.selectByEquipmentCode(standardRequest.getEquipmentCode());
+ if(equipment == null) {
+ log.error("璁惧涓嶅瓨鍦細{}", standardRequest.getEquipmentCode());
+ continue;
+ }
+ standardRequest.setStandardName(standardRequest.getEquipmentName() + "淇濆吇鏍囧噯");
+
+ standardRequest.setMaintenanceCategory(MaintenanceCategoryEnum.ANNUAL_MAINTENANCE.name());
+ standardRequest.setEquipmentId(equipment.getId());
+ //璇诲彇淇濆吇鏄庣粏鍐呭
+ List<WeekMaintenanceStandardImport> list = ExcelImportUtil.importExcel(file.getInputStream(), WeekMaintenanceStandardImport.class, params);
+ //鏄庣粏椤�
+ List<EamMaintenanceStandardDetail> tableList = list.stream().map(EamMaintenanceStandardDetail::new).collect(Collectors.toList());
+ standardRequest.setTableDetailList(tableList);
+ String codeSeq = businessCodeRuleService.generateBusinessCodeSeq(BusinessCodeConst.MAINTENANCE_STANDARD_CODE_RULE);
+ standardRequest.setStandardCode(codeSeq);
+ boolean b = eamMaintenanceStandardService.addMaintenanceStandard(standardRequest);
+ if (!b) {
+ log.error("淇濆瓨澶辫触锛� {}", standardRequest.getEquipmentCode());
+ }
+ } catch (Exception e) {
+ //update-begin-author:taoyan date:20211124 for: 瀵煎叆鏁版嵁閲嶅澧炲姞鎻愮ず
+ String msg = e.getMessage();
+ log.error("鏂囦欢 {} 澶勭悊寮傚父锛� {}", file.getOriginalFilename(), msg, e);
+ //update-end-author:taoyan date:20211124 for: 瀵煎叆鏁版嵁閲嶅澧炲姞鎻愮ず
+ } finally {
+ try {
+ file.getInputStream().close();
+ } catch (IOException e) {
+ log.error(e.getMessage(), e);
+ }
+ }
+ }
+ return Result.ok("鏂囦欢瀵煎叆瀹屾垚锛�");
+ }
/**
* 璇诲彇Excel 绗竴琛岋紝 绗簩琛岀殑淇℃伅锛屽寘鎷浘鐗囦俊鎭�
* @param file
@@ -391,7 +511,7 @@
//绗簩琛岃鍙�
Row row = sheet.getRow(1);
//璁惧缂栫爜
- Cell equipmentCode = row.getCell(5);
+ Cell equipmentCode = row.getCell(8);
Cell targetCell = row.getCell(0);
//鏂囦欢缂栫爜
String fileCodeValue = getCellValue(targetCell);
@@ -399,11 +519,16 @@
throw new JeecgBootException("Excel銆�" + file.getOriginalFilename() + "銆戠浜岃绗竴鍒楄幏鍙栧埌鐨勮澶囩紪鍙蜂负绌猴紒");
}
request.setFileCode(fileCodeValue.trim());
- if(CellType.NUMERIC.equals(equipmentCode.getCellType())) {
- request.setEquipmentCode(String.valueOf((int) equipmentCode.getNumericCellValue()));
- }else if(CellType.STRING.equals(equipmentCode.getCellType())) {
- request.setEquipmentCode(equipmentCode.getStringCellValue());
+// if(CellType.NUMERIC.equals(equipmentCode.getCellType())) {
+// request.setEquipmentCode(String.valueOf((int) equipmentCode.getNumericCellValue()));
+// }else if(CellType.STRING.equals(equipmentCode.getCellType())) {
+// request.setEquipmentCode(equipmentCode.getStringCellValue());
+// }
+ String equipmentCodeStr = extractEquipmentCode(equipmentCode);
+ if (StringUtils.isBlank(equipmentCodeStr)) {
+ throw new JeecgBootException("Excel銆� " + file.getOriginalFilename() + "銆戞病鏈夎鍙栧埌鏈夋晥鐨勮澶囩紪鍙凤紝瀵煎叆澶辫触锛�");
}
+ request.setEquipmentCode(equipmentCodeStr);
if (StringUtils.isBlank(request.getEquipmentCode())) {
throw new JeecgBootException("Excel銆� " + file.getOriginalFilename() + "銆戞病鏈夎鍙栧埌璁惧缂栧彿锛屽鍏ュけ璐ワ紒");
}
@@ -467,10 +592,16 @@
}
Sheet sheet = book.getSheetAt(0);
- //绗竴琛岃鍙�
- Row row = sheet.getRow(0);
+ //绗簩琛岃鍙�
+ Row row = sheet.getRow(1);
//璁惧缂栫爜
- Cell equipmentCode = row.getCell(10);
+ Cell equipmentCode = row.getCell(13);
+ Cell targetCell = row.getCell(0);
+ String fileCodeValue = getCellValue(targetCell);
+ if (fileCodeValue == null || fileCodeValue.trim().isEmpty()) {
+ throw new JeecgBootException("Excel銆�" + file.getOriginalFilename() + "銆戠浜岃绗竴鍒楄幏鍙栧埌鐨勮澶囩紪鍙蜂负绌猴紒");
+ }
+ request.setFileCode(fileCodeValue.trim());
if(CellType.NUMERIC.equals(equipmentCode.getCellType())) {
request.setEquipmentCode(String.valueOf((int) equipmentCode.getNumericCellValue()));
}else if(CellType.STRING.equals(equipmentCode.getCellType())) {
@@ -480,20 +611,20 @@
throw new JeecgBootException("Excel銆� " + file.getOriginalFilename() + "銆戞病鏈夎鍙栧埌璁惧缂栧彿锛屽鍏ュけ璐ワ紒");
}
//鍒濆鏃ユ湡
- Cell initialDate = row.getCell(6);
+ Cell initialDate = row.getCell(11);
if (DateUtil.isCellDateFormatted(initialDate)) {
request.setInitialDate(initialDate.getDateCellValue());
} else {
request.setInitialDate(new Date());
}
//璁惧鍚嶇О
- Cell equipmentName = row.getCell(8);
- request.setEquipmentName(equipmentName.getStringCellValue());
+// Cell equipmentName = row.getCell(8);
+// request.setEquipmentName(equipmentName.getStringCellValue());
//绗簩琛岃鍙�
- row = sheet.getRow(1);
+ row = sheet.getRow(4);
//淇濆吇鍛ㄦ湡
- Cell period = row.getCell(6);
+ Cell period = row.getCell(7);
if (CellType.NUMERIC.equals(period.getCellType())) {
request.setMaintenancePeriod((int) period.getNumericCellValue());
} else {
@@ -506,4 +637,72 @@
log.error("璇诲彇Excel淇℃伅澶辫触锛歿}", e.getMessage(), e);
}
}
+
+ private int findDataEndRow(InputStream inputStream) throws IOException {
+ Workbook workbook = null;
+ try {
+ workbook = WorkbookFactory.create(inputStream);
+ Sheet sheet = workbook.getSheetAt(0);
+ int lastRowNum = sheet.getLastRowNum();
+ log.info("Excel鏂囦欢鎬昏鏁�: {}", lastRowNum);
+
+ // 鎵惧埌"瀹炴柦瑕侀"琛岋紝浣滀负鏁版嵁缁撴潫鐨勬爣蹇�
+ for (int i = 0; i <= lastRowNum; i++) {
+ Row row = sheet.getRow(i);
+ if (row == null) continue;
+
+ // 妫�鏌ョA鍒楁槸鍚﹀寘鍚�"瀹炴柦瑕侀"
+ Cell cell = row.getCell(0);
+ if (cell != null && cell.getCellType() == CellType.STRING) {
+ String value = getCellValue(cell).replaceAll("\\s+", "");
+ if ("瀹炴柦瑕侀".equals(value)) {
+ log.info("鎵惧埌'瀹炴柦瑕侀'鍦ㄧ{}琛�", i);
+ return i - 1; // 杩斿洖"瀹炴柦瑕侀"琛屼箣鍓嶇殑琛屽彿
+ }
+ }
+ }
+
+ // 濡傛灉娌℃湁鎵惧埌"瀹炴柦瑕侀"锛岃繑鍥炴渶鍚庝竴琛�
+ log.info("鏈壘鍒�'瀹炴柦瑕侀'锛岃繑鍥炴渶鍚庝竴琛�: {}", lastRowNum);
+ return lastRowNum;
+ } finally {
+ if (workbook != null) {
+ workbook.close();
+ }
+ }
+ }
+
+
+
+
+
+ /**
+ * 浠庡崟鍏冩牸涓彁鍙栬澶囩紪鍙凤紝鍘婚櫎鍓嶇紑濡�"璁惧缂栧彿:"
+ * @param cell 鍗曞厓鏍煎璞�
+ * @return 绾澶囩紪鍙峰瓧绗︿覆
+ */
+ private String extractEquipmentCode(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+
+ String cellValue = getCellValue(cell);
+ if (StringUtils.isBlank(cellValue)) {
+ return null;
+ }
+
+ // 鍘婚櫎鍓嶅悗绌烘牸
+ cellValue = cellValue.trim();
+
+ // 浣跨敤姝ｅ垯琛ㄨ揪寮忔彁鍙栨暟瀛楅儴鍒�
+ Pattern pattern = Pattern.compile("\\d+");
+ Matcher matcher = pattern.matcher(cellValue);
+
+ if (matcher.find()) {
+ return matcher.group();
+ }
+
+ // 濡傛灉娌℃湁鎵惧埌鏁板瓧锛岃繑鍥炲師鍊硷紙鍙兘鏈夊叾浠栨牸寮忥級
+ return cellValue;
+ }
}
--
Gitblit v1.9.3