From b88a288f4f787b509463678e3cd9ccfa3f37014b Mon Sep 17 00:00:00 2001
From: chengf <cgf12138@163.com>
Date: 星期三, 11 三月 2026 12:01:05 +0800
Subject: [PATCH] 导入测试
---
service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java | 519 +++++++++++++++++++++++++++++++++++----------------------
1 files changed, 316 insertions(+), 203 deletions(-)
diff --git a/service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java b/service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java
index 30754a9..c8e2b72 100644
--- a/service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java
+++ b/service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java
@@ -3,18 +3,23 @@
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.java110.core.factory.GenerateCodeFactory;
+import com.java110.dto.importData.Vtil;
import com.java110.dto.log.AssetImportLogDetailDto;
import com.java110.dto.msg.MaintenancePaymentPo;
import com.java110.dto.msg.MpFifthPaymentRecordPo;
import com.java110.dto.msg.MpPaymentRecordPo;
+import com.java110.fee.api.*;
import com.java110.job.dao.IMaintenancePaymentService;
import com.java110.job.dao.IMpFifthPaymentRecordService;
import com.java110.job.dao.IMpPaymentRecordService;
import com.java110.job.importData.DefaultImportData;
import com.java110.job.importData.IImportDataAdapt;
+import com.java110.po.importFee.MaintenancePayment;
+import com.java110.po.importFee.MpFifthPaymentRecord;
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.utils.util.NumberUtil;
+import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -31,67 +36,210 @@
@Service("importReportMainV2QueueData")
public class ImportReportMainV2QueueDataAdapt extends DefaultImportData implements IImportDataAdapt {
- // 鏃ユ湡鏍煎紡鍖栧櫒锛堝鐞咵xcel涓殑澶氱鏃ユ湡鏍煎紡锛�2025/6/11銆�8/11/25銆�2025.5.13绛夛級
- private static final SimpleDateFormat[] DATE_FORMATS = {
- new SimpleDateFormat("yyyy/MM/dd"),
- new SimpleDateFormat("MM/dd/yy"),
- new SimpleDateFormat("yyyy.MM.dd"),
- new SimpleDateFormat("yyyy-MM-dd")
- };
+
+ @Autowired
+ MaintenancePaymentApi maintenancePaymentApi;
+ @Autowired
+ AnnouncementTimeRangeApi announcementTimeRangeApi;
+ @Autowired
+ OwnersCommitteeConventionApi ownersCommitteeConventionApi;
+ @Autowired
+ OwnerWithdrawalInfoApi ownerWithdrawalInfoApi;
+ @Autowired
+ OwnerQualityGuaranteeApi ownerQualityGuaranteeApi;
+ @Autowired
+ MpPaymentRecordApi mpPaymentRecordApi;
+ @Autowired
+ MpFifthPaymentRecordApi mpFifthPaymentRecordApi;
+ @Autowired
+ OwnerRemarkInfoApi ownerRemarkInfoApi;
// 娉ㄥ叆鏁版嵁搴撴湇鍔★紙SqlSessionTemplate鐗圫ervice锛�
- @Autowired
- private IMaintenancePaymentService maintenancePaymentService;
- @Autowired
- private IMpPaymentRecordService mpPaymentRecordService;
- @Autowired
- private IMpFifthPaymentRecordService mpFifthPaymentRecordService;
-
@Override
@Transactional(rollbackFor = Exception.class) // 浜嬪姟鎺у埗锛岀‘淇�3寮犺〃鏁版嵁涓�鑷存��
public void importData(List<AssetImportLogDetailDto> assetImportLogDetailDtos) {
for (AssetImportLogDetailDto logDetailDto : assetImportLogDetailDtos) {
try {
- // 1. 瑙ｆ瀽Excel琛屾暟鎹紙JSONArray瀵瑰簲姣忚鐨勫崟鍏冩牸鍒楄〃锛岄『搴忎笌"鑽ｉ『鑻戠鎶ュ崟"琛ㄥご涓�鑷达級
- JSONArray cellArray = JSONObject.parseArray(logDetailDto.getContent());
-// if (cellArray.size() < 132) { // 鑽ｉ『鑻戠鎶ュ崟琛ㄥご鍏�132鍒楋紝杩囨护鏃犳晥琛�
-// throw new RuntimeException("琛屾暟鎹垪鏁颁笉瓒筹紙闇�132鍒楋級锛屽綋鍓嶅垪鏁帮細" + cellArray.size());
-// }
- // 2. 鏍￠獙鏍稿績蹇呭～瀛楁锛堟祦杞紪鍙枫�侀」鐩悕绉般�佷笟濮斾細閲戦绛夛級
+ JSONArray cellArray = JSONObject.parseArray(logDetailDto.getContent());
+
+
+
String flowNumber = getCellValue(cellArray, 1); // 绗�2鍒楋細娴佽浆缂栧彿
String projectName = getCellValue(cellArray, 4); // 绗�5鍒楋細椤圭洰鍚嶇О
String ownersCommitteeAmountStr = getCellValue(cellArray, 12); // 绗�13鍒楋細涓氬浼氶噾棰�
- if (isBlank(flowNumber) || isBlank(projectName) || isBlank(ownersCommitteeAmountStr)) {
- throw new RuntimeException(
- String.format("鏍稿績瀛楁缂哄け锛氭祦杞紪鍙穂%s]銆侀」鐩悕绉癧%s]銆佷笟濮斾細閲戦[%s]",
- flowNumber, projectName, ownersCommitteeAmountStr)
- );
+ if (Vtil.defaultValue(getCellValue(cellArray, 1)).equals("")) {
+ continue;
}
+ AnnouncementTimeRangePo announcementTimeRangePo = null;
+ OwnersCommitteeConventionPo ocoPo = null;
+ OwnerWithdrawalInfoPo owiPo = null;
+ OwnerQualityGuaranteePo oqgPo = null;
+ OwnerRemarkInfoPo ownerRemarkInfoPo = null;
// 3. 鏋勫缓3寮犺〃鐨凱O瀵硅薄锛堝瓧娈垫槧灏�+涓氬姟璁＄畻锛�
- MaintenancePaymentPo mainPo = buildMaintenancePaymentPo(cellArray);
- List<MpPaymentRecordPo> paymentRecordPos = buildMpPaymentRecordPos(cellArray, mainPo.getFlowNumber(),mainPo.getId());
- MpFifthPaymentRecordPo fifthPo = buildMpFifthPaymentRecordPo(cellArray, mainPo.getFlowNumber(),mainPo.getId());
+ MaintenancePayment mainPo = buildMaintenancePaymentPoV2(cellArray);//涓昏〃
+// if (!getCellValue(cellArray, 22).isEmpty() && !getCellValue(cellArray, 23).isEmpty()) {
+ announcementTimeRangePo = buildAnnouncementTimeRangePo(cellArray, mainPo.getId());//鍏叡鏀剁泭閲�
+// }
+// if (!getCellValue(cellArray, 26).isEmpty() && !getCellValue(cellArray, 27).isEmpty()) {
+ ocoPo = buildOwnersCommitteeConventionPo(cellArray, mainPo.getId());
+// }
+// if (!getCellValue(cellArray, 29).isEmpty()) {
+ owiPo = buildOwnerWithdrawalInfoPo(cellArray, mainPo.getId());
+// }
+// if (!getCellValue(cellArray, 39).isEmpty()) {
+ oqgPo = buildOwnerQualityGuaranteePo(cellArray, mainPo.getId());
+// }
+ List<MpPaymentRecordPo> paymentRecordPos = buildMpPaymentRecordPos(cellArray, mainPo.getFlowNumber(), mainPo.getId());
+ List<MpFifthPaymentRecord> fifthPos = buildMpFifthPaymentRecordPos(cellArray, mainPo.getFlowNumber(), mainPo.getId());
+// if (!getCellValue(cellArray, 137).isEmpty()) {
+ ownerRemarkInfoPo = buildOwnerRemarkInfoPo(cellArray, mainPo.getFlowNumber(), mainPo.getId());
+// }
- // 4. 鎵归噺淇濆瓨鍒版暟鎹簱
- saveToDatabase(mainPo, paymentRecordPos, fifthPo);
+ importData(mainPo, announcementTimeRangePo, ocoPo, owiPo, oqgPo, paymentRecordPos, fifthPos, ownerRemarkInfoPo);
// 5. 鏇存柊瀵煎叆鏃ュ織鐘舵�侊紙鎴愬姛锛�- 璋冪敤鐖剁被鏂规硶
super.updateImportLogDetailState(logDetailDto.getDetailId());
+ try {
+ Thread.sleep(1000);
+ } catch (InterruptedException e) {
+ throw new RuntimeException(e);
+ }
} catch (Exception e) {
- e.printStackTrace();
- // 6. 鏇存柊瀵煎叆鏃ュ織鐘舵�侊紙澶辫触锛�- 璋冪敤鐖剁被寮傚父閲嶈浇鏂规硶
- super.updateImportLogDetailState(logDetailDto.getDetailId(), e);
+ throw new IllegalArgumentException(e.getMessage());
}
}
}
- /**
- * 鏋勫缓 MaintenancePaymentPo锛堢淮淇祫閲戞敮鍙栦俊鎭〃锛�
- * 瀛楁鏄犲皠瀵瑰簲"鑽ｉ『鑻戠鎶ュ崟"琛ㄥご椤哄簭锛屽寘鍚�17涓嚜鍔ㄨ绠楀瓧娈�
- */
+ private void importData(MaintenancePayment mainPo, AnnouncementTimeRangePo announcementTimeRangePo, OwnersCommitteeConventionPo ocoPo, OwnerWithdrawalInfoPo owiPo, OwnerQualityGuaranteePo oqgPo,
+ List<MpPaymentRecordPo> paymentRecordPos, List<MpFifthPaymentRecord> fifthPos, OwnerRemarkInfoPo ownerRemarkInfoPo) {
+ maintenancePaymentApi.saveMaintenancePayment(mainPo);
+ if (announcementTimeRangePo != null) {
+ announcementTimeRangeApi.saveAnnouncementTimeRangeInfo(announcementTimeRangePo);
+ }
+ if (ocoPo != null) {
+ ownersCommitteeConventionApi.saveOwnersCommitteeConvention(ocoPo);
+ }
+ if (owiPo != null) {
+ ownerWithdrawalInfoApi.saveOwnerWithdrawalInfo(owiPo);
+ }
+ if (oqgPo != null) {
+ ownerQualityGuaranteeApi.saveOwnerQualityGuarantee(oqgPo);
+ }
+ for (MpPaymentRecordPo paymentRecordPo : paymentRecordPos) {
+ mpPaymentRecordApi.saveMpPaymentRecord(paymentRecordPo);
+ }
+ for (MpFifthPaymentRecord fifthPo : fifthPos) {
+ mpFifthPaymentRecordApi.saveMpFifthPaymentRecord(fifthPo);
+ }
+ if (ownerRemarkInfoPo != null) {
+ ownerRemarkInfoApi.saveOwnerRemarkInfo(ownerRemarkInfoPo);
+ }
+ }
+
+ private OwnerRemarkInfoPo buildOwnerRemarkInfoPo(JSONArray cellArray, String flowNumber, String id) {
+ OwnerRemarkInfoPo po = new OwnerRemarkInfoPo();
+ long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
+ po.setId(longId);
+ po.setMpId(flowNumber);
+ po.setRemarkPerson("瀵煎叆鍐呭");
+ po.setRemarkContent(getCellValue(cellArray, 137));
+ return po;
+ }
+
+ private OwnerQualityGuaranteePo buildOwnerQualityGuaranteePo(JSONArray cellArray, String aLong) {
+ OwnerQualityGuaranteePo po = new OwnerQualityGuaranteePo();
+ long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
+ po.setId(longId); // 涓婚敭ID
+ po.setMpId(aLong);
+ po.setQualityGuaranteePeriod2Start(Vtil.defaultValueToDate(getCellValue(cellArray, 37)));
+ po.setQualityGuaranteePeriod2End(Vtil.defaultValueToDate(getCellValue(cellArray, 38)));
+ po.setAcceptanceDate(Vtil.defaultValueToDate(getCellValue(cellArray, 41)));
+ po.setAvailableWithdrawalDate(Vtil.defaultValueToDate(getCellValue(cellArray, 43)));
+ po.setQualityGuaranteeRatio(getCellNum(cellArray, 39));
+ po.setQualityGuaranteeAmount(getCellNum(cellArray, 12) * po.getQualityGuaranteeRatio());
+ po.setAuditStatus(getCellValue(cellArray, 42));
+ return po;
+ }
+
+ private OwnerWithdrawalInfoPo buildOwnerWithdrawalInfoPo(JSONArray cellArray, String aLong) {
+ OwnerWithdrawalInfoPo po = new OwnerWithdrawalInfoPo();
+ long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
+ po.setId(longId); // 涓婚敭ID
+ po.setMpId(aLong);
+ po.setWithdrawalEncounteredProblem(getCellValue(cellArray, 28));
+ po.setShortageOrArrears(getCellNum(cellArray, 29));
+ po.setRoadName(getCellValue(cellArray, 30));
+ po.setLane(getCellValue(cellArray, 31));
+ po.setDoor(getCellValue(cellArray, 32));
+ po.setRoom(getCellValue(cellArray, 33));
+ po.setDoorRoomNumber(getCellValue(cellArray, 34));
+ po.setOwnerAddress(getCellValue(cellArray, 35));
+ return po;
+ }
+
+ private OwnersCommitteeConventionPo buildOwnersCommitteeConventionPo(JSONArray cellArray, String mpId) {
+ OwnersCommitteeConventionPo po = new OwnersCommitteeConventionPo();
+
+ long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
+ po.setId(longId); // 涓婚敭ID
+ po.setMpId(mpId);
+ po.setQuota(getCellNum(cellArray, 25));
+ po.setOwnersCommitteeResolution(getCellValue(cellArray, 26));
+ po.setOwnersCommitteeConsultation(getCellValue(cellArray, 27));
+
+ return po;
+ }
+
+ private AnnouncementTimeRangePo buildAnnouncementTimeRangePo(JSONArray cellArray, String mpId) {
+ AnnouncementTimeRangePo po = new AnnouncementTimeRangePo();
+ long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
+ po.setId(longId); // 涓婚敭ID
+ po.setMpId(mpId);
+ po.setPlannedAnnouncementStart(getCellValue(cellArray, 21));
+ po.setPlannedAnnouncementEnd(getCellValue(cellArray, 22));
+ po.setPublishedAnnouncementStart(getCellValue(cellArray, 23));
+ po.setPublishedAnnouncementEnd(getCellValue(cellArray, 24));
+
+ return po;
+ }
+
+ private MaintenancePayment buildMaintenancePaymentPoV2(JSONArray cellArray) throws ParseException {
+ MaintenancePayment po = new MaintenancePayment();
+ po.setId(GenerateCodeFactory.getGeneratorId("10")); // 涓婚敭ID
+ po.setAuxiliaryColumn(getCellValue(cellArray, 0)); // 绗�1鍒楋細杈呭姪鍒�
+ po.setFlowNumber(getCellValue(cellArray, 1)); // 绗�2鍒楋細娴佽浆缂栧彿
+ po.setSerialNumber(parseInteger(getCellValue(cellArray, 2))); // 绗�3鍒楋細搴忓彿
+ po.setProjectCode(getCellValue(cellArray, 3)); // 绗�4鍒楋細椤圭洰缂栫爜
+ po.setProjectName(getCellValue(cellArray, 4)); // 绗�5鍒楋細椤圭洰鍚嶇О
+ po.setYear(parseInteger(getCellValue(cellArray, 5))); // 绗�6鍒楋細骞翠唤
+ po.setMonth(parseInteger(getCellValue(cellArray, 6))); // 绗�7鍒楋細鏈堜唤
+ po.setDay(parseInteger(getCellValue(cellArray, 7))); // 绗�8鍒楋細鏃�
+ po.setDate(parseDate(getCellValue(cellArray, 8))); // 绗�9鍒楋細鏃ユ湡
+ po.setProjectContent(getCellValue(cellArray, 9)); // 绗�10鍒楋細宸ョ▼鍐呭
+ po.setManagementOfficeAmount(getCellValue(cellArray, 10)); // 绗�11鍒楋細绠＄悊澶勯噾棰�
+ po.setManagementOfficeSeal(convertSealStatus(getCellValue(cellArray, 11))); // 绗�12鍒楋細绠＄悊澶勬槸鍚﹀凡鐩栫珷锛堚垰杞�"鏄�"锛屽惁鍒�"鍚�"锛�
+ po.setOwnersCommitteeAmount(getCellValue(cellArray, 12)); // 绗�13鍒楋細涓氬浼氶噾棰�
+ po.setAuditAmount(getCellNum(cellArray, 13).toString()); // 绗�14鍒楋細瀹′环閲戦
+ po.setOwnersCommitteeSeal(convertSealStatus(getCellValue(cellArray, 14))); // 绗�15鍒楋細涓氬浼氭槸鍚﹀凡鐩栫珷
+ po.setReportDepartment(getCellValue(cellArray, 15)); // 绗�16鍒楋細绛炬姤閮ㄩ棬
+ po.setFundTypeLevel1(getCellValue(cellArray, 16)); // 绗�17鍒楋細鍩洪噾绫诲瀷-涓�绾у垎绫�
+ po.setFundTypeLevel2(getCellValue(cellArray, 17)); // 绗�18鍒楋細鍩洪噾绫诲瀷-浜岀骇鍒嗙被
+ po.setMaintenanceType(getCellValue(cellArray, 19)); // 绗�19鍒楋細骞�/鍏ㄤ綋
+ po.setBuildingOrAll(getCellValue(cellArray, 18)); // 绗�20鍒楋細缁翠慨绫诲瀷
+ po.setPayeeName(getCellValue(cellArray, 145)); // 鏀粯鍏徃鍚嶇О/涓汉鍚嶅瓧
+ po.setIdCardNumber(getCellValue(cellArray, 146)); // 涓汉韬唤璇佸彿鐮�
+ po.setBankName(getCellValue(cellArray, 147)); // 寮�鎴烽摱琛�
+ po.setBankAccount(getCellValue(cellArray, 148)); // 寮�鎴疯处鍙�
+ return po;
+ }
+
+ /**
+ * 鏋勫缓 MaintenancePaymentPo锛堢淮淇祫閲戞敮鍙栦俊鎭〃锛�
+ * 瀛楁鏄犲皠瀵瑰簲"鑽ｉ『鑻戠鎶ュ崟"琛ㄥご椤哄簭锛屽寘鍚�17涓嚜鍔ㄨ绠楀瓧娈�
+ */
private MaintenancePaymentPo buildMaintenancePaymentPo(JSONArray cellArray) throws ParseException {
MaintenancePaymentPo po = new MaintenancePaymentPo();
// 鍩虹瀛楁锛堢洿鎺ユ槧灏凟xcel鍗曞厓鏍硷級
@@ -118,7 +266,7 @@
po.setBuildingOrAll(getCellValue(cellArray, 18)); // 绗�19鍒楋細骞�/鍏ㄤ綋
po.setMaintenanceType(getCellValue(cellArray, 19)); // 绗�20鍒楋細缁翠慨绫诲瀷
po.setQualityGuaranteePeriod1(getCellValue(cellArray, 20)); // 绗�21鍒楋細璐ㄤ繚鏈燂紙绗竴涓級
- po.setPublicIncomeAnnouncement(getCellValue(cellArray, 21)); // 绗�22鍒楋細鍏叡鏀剁泭閲戝叕甯冩儏鍐�
+// po.setPublicIncomeAnnouncement(getCellValue(cellArray, 21)); // 绗�22鍒楋細鍏叡鏀剁泭閲戝叕甯冩儏鍐�
// 鍏叡鏀剁泭閲戝叕甯冨瓙瀛楁锛堢23-26鍒楋級
po.setPlannedAnnouncementStart(getCellValue(cellArray, 21)); // 鎷熷叕甯�-璧峰锛堝勾鏈堬級
po.setPlannedAnnouncementEnd(getCellValue(cellArray, 22)); // 鎷熷叕甯�-姝紙骞存湀锛�
@@ -137,104 +285,15 @@
po.setDoorRoomNumber(getCellValue(cellArray, 34)); // 闂ㄥ鍙�
po.setOwnerAddress(getCellValue(cellArray, 35)); // 涓氫富鍦板潃
// po.setWithdrawalProblem(getCellValue(cellArray, 36)); // 绗�38鍒楋細鏀彇瀛樺湪闂
- po.setProblemDifficulty(getCellValue(cellArray, 36)); // 绗�39鍒楋細闂闅惧害
- po.setAvailableWithdrawalDate(parseDate(getCellValue(cellArray, 37))); // 绗�40鍒楋細鍙惎鍔ㄦ敮鍙栨棩鏈燂紙骞存湀鏃ワ級
- po.setQualityGuaranteePeriod2(getCellValue(cellArray, 39) + "~" + getCellValue(cellArray, 40)); // 绗�41鍒楋細璐ㄤ繚鏈燂紙绗簩涓級
- po.setQualityGuaranteeRatio(parseBigDecimal(getCellValue(cellArray, 40))); // 绗�42鍒楋細璐ㄤ繚閲戝崰姣�
- po.setQualityGuaranteeAmount(parseBigDecimal(getCellValue(cellArray, 41))); // 绗�43鍒楋細璐ㄤ繚閲戦噾棰濓紙鍏冿級
- po.setReceivedQualityGuarantee(parseBigDecimal(getCellValue(cellArray, 42))); // 绗�44鍒楋細宸插埌璐﹁川淇濋噾
+ po.setProblemDifficulty(getCellValue(cellArray, 36)); // 绗�36鍒楋細闂闅惧害
+ po.setAvailableWithdrawalDate(parseDate(getCellValue(cellArray, 43))); // 绗�40鍒楋細鍙惎鍔ㄦ敮鍙栨棩鏈燂紙骞存湀鏃ワ級
+ po.setQualityGuaranteePeriod2(getCellValue(cellArray, 37) + "~" + getCellValue(cellArray, 38)); // 绗�41鍒楋細璐ㄤ繚鏈燂紙绗簩涓級
+ po.setQualityGuaranteeRatio(parseBigDecimal(getCellValue(cellArray, 39))); // 绗�42鍒楋細璐ㄤ繚閲戝崰姣�
+ po.setQualityGuaranteeAmount(parseBigDecimal(getCellValue(cellArray, 40))); // 绗�43鍒楋細璐ㄤ繚閲戦噾棰濓紙鍏冿級
+ po.setReceivedQualityGuarantee(parseBigDecimal(getCellValue(cellArray, 44))); // 绗�44鍒楋細宸插埌璐﹁川淇濋噾
// po.setStartDate(parseDate(getCellValue(cellArray, 44))); // 绗�45鍒楋細璧峰锛堝勾鏈堟棩锛�
// po.setEndDate(parseDate(getCellValue(cellArray, 45))); // 绗�46鍒楋細缁堟锛堝勾鏈堟棩锛�
- // -------------------------- 17涓嚜鍔ㄨ绠楀瓧娈碉紙鏍稿績涓氬姟閫昏緫锛�--------------------------
- // 1. 鎵撳嵃鍚堣 = 绗竴娆绗簲娆℃墦鍗伴噾棰濅箣鍜岋紙绗�47鍒楁墦鍗伴噾棰�+绗�54鍒�+绗�61鍒�+绗�68鍒�+绗�75鍒楋級
- BigDecimal print1 = parseBigDecimal(getCellValue(cellArray, 45)); // 绗竴娆℃墦鍗伴噾棰濓紙绗�50鍒楋級
- BigDecimal print2 = parseBigDecimal(getCellValue(cellArray, 57)); // 绗簩娆℃墦鍗伴噾棰濓紙绗�57鍒楋級
- BigDecimal print3 = parseBigDecimal(getCellValue(cellArray, 69)); // 绗笁娆℃墦鍗伴噾棰濓紙绗�64鍒楋級
- BigDecimal print4 = parseBigDecimal(getCellValue(cellArray, 81)); // 绗洓娆℃墦鍗伴噾棰濓紙绗�71鍒楋級
- BigDecimal print5 = parseBigDecimal(getCellValue(cellArray, 93)); // 绗簲娆℃墦鍗伴噾棰濓紙绗�78鍒楋級
-// BigDecimal printTotal = add(print1, print2, print3, print4, print5);
-// po.setPrintTotal(printTotal); // 鑷畾涔夊瓧娈碉細鎵撳嵃鍚堣
-
- // 2. 鍒拌处鍚堣 = 绗竴娆绗簲娆″埌璐﹂噾棰濅箣鍜岋紙绗�51鍒�+绗�58鍒�+绗�65鍒�+绗�72鍒�+绗�79鍒楋級
- BigDecimal arrival1 = parseBigDecimal(getCellValue(cellArray, 48)); // 绗竴娆″埌璐﹂噾棰濓紙绗�52鍒楋級
- BigDecimal arrival2 = parseBigDecimal(getCellValue(cellArray, 60)); // 绗簩娆″埌璐﹂噾棰濓紙绗�59鍒楋級
- BigDecimal arrival3 = parseBigDecimal(getCellValue(cellArray, 72)); // 绗笁娆″埌璐﹂噾棰濓紙绗�66鍒楋級
- BigDecimal arrival4 = parseBigDecimal(getCellValue(cellArray, 84)); // 绗洓娆″埌璐﹂噾棰濓紙绗�73鍒楋級
- BigDecimal arrival5 = parseBigDecimal(getCellValue(cellArray, 96)); // 绗簲娆″埌璐﹂噾棰濓紙绗�80鍒楋級
-// BigDecimal arrivalTotal = add(arrival1, arrival2, arrival3, arrival4, arrival5);
-// po.setArrivalTotal(arrivalTotal); // 鑷畾涔夊瓧娈碉細鍒拌处鍚堣
-
- // 3. 缁翠慨鍩洪噾鍒拌处鐜� = IF(鍒拌处鍚堣<>0, 鍒拌处鍚堣/涓氬浼氶噾棰�, 0)
- BigDecimal ownersAmount = po.getOwnersCommitteeAmount();
-// BigDecimal fundArrivalRate = (arrivalTotal.compareTo(BigDecimal.ZERO) != 0 && ownersAmount.compareTo(BigDecimal.ZERO) != 0)
-// ? arrivalTotal.divide(ownersAmount, 4, BigDecimal.ROUND_HALF_UP) // 淇濈暀4浣嶅皬鏁�
-// : BigDecimal.ZERO;
-// po.setFundArrivalRate(fundArrivalRate); // 鑷畾涔夊瓧娈碉細缁翠慨鍩洪噾鍒拌处鐜�
-
- // 4. 灏氱己閲戦 = IF(鍩洪噾绫诲瀷<>"缁翠慨鍩洪噾", 0, 涓氬浼氶噾棰�-鍒拌处鍚堣)
- String fundType = po.getFundTypeLevel1();
-// BigDecimal shortageAmount = ("缁翠慨鍩洪噾".equals(fundType))
-// ? subtract(ownersAmount, arrivalTotal)
-// : BigDecimal.ZERO;
-// po.setFundShortageAmount(shortageAmount); // 鑷畾涔夊瓧娈碉細灏氱己閲戦
-
- // 5. 绠＄悊璐� = 绠＄悊澶勯噾棰� * 绠＄悊璐瑰崰姣旓紙鐧惧垎姣旇浆灏忔暟锛�
- BigDecimal managementFeeRatio = po.getManagementFeeRatio() == null ? BigDecimal.ZERO : po.getManagementFeeRatio().divide(new BigDecimal(100));
- BigDecimal managementFee = multiply(po.getManagementOfficeAmount(), managementFeeRatio);
-// po.setManagementFee(managementFee); // 鑷畾涔夊瓧娈碉細绠＄悊璐�
-
- // 6. 搴斾粯閲戦A = IF(瀹¤閲戦涓虹┖, 涓氬浼氶噾棰�-绠＄悊璐�, 瀹¤閲戦-绠＄悊璐�)
- BigDecimal auditAmount = po.getAuditAmount() == null ? BigDecimal.ZERO : po.getAuditAmount();
- BigDecimal payableA = (auditAmount.compareTo(BigDecimal.ZERO) == 0)
- ? subtract(ownersAmount, managementFee)
- : subtract(auditAmount, managementFee);
-// po.setPayableAmountA(payableA); // 鑷畾涔夊瓧娈碉細搴斾粯閲戦A
-
- // 7. 搴斾粯閲戦B = 涓氬浼氶噾棰�-绠＄悊璐�-璐ㄤ繚閲戦噾棰�
- BigDecimal qualityGuaranteeAmount = po.getQualityGuaranteeAmount() == null ? BigDecimal.ZERO : po.getQualityGuaranteeAmount();
- BigDecimal payableB = subtract(ownersAmount, managementFee, qualityGuaranteeAmount);
-// po.setPayableAmountB(payableB); // 鑷畾涔夊瓧娈碉細搴斾粯閲戦B
-
- // 8. 搴斾粯閲戦C = 瀹炴敹閲戦-瀹炴敹閲戦脳绠＄悊璐规瘮渚嬶紙瀹炴敹閲戦鍙栧埌璐﹀悎璁★級
-// BigDecimal payableC = multiply(arrivalTotal, BigDecimal.ONE.subtract(managementFeeRatio));
-// po.setPayableAmountC(payableC); // 鑷畾涔夊瓧娈碉細搴斾粯閲戦C
-
- // 9. 鎷熶粯鍚堣 = 绗竴娆绗簲娆℃嫙浠橀噾棰濅箣鍜岋紙绗�82鍒�+绗�88鍒�+绗�94鍒�+绗�100鍒�+绗�106鍒楋級
- BigDecimal plannedPay1 = parseBigDecimal(getCellValue(cellArray, 113)); // 绗竴娆℃嫙浠橀噾棰濓紙绗�83鍒楋級
- BigDecimal plannedPay2 = parseBigDecimal(getCellValue(cellArray, 119)); // 绗簩娆℃嫙浠橀噾棰濓紙绗�89鍒楋級
- BigDecimal plannedPay3 = parseBigDecimal(getCellValue(cellArray, 125)); // 绗笁娆℃嫙浠橀噾棰濓紙绗�95鍒楋級
- BigDecimal plannedPay4 = parseBigDecimal(getCellValue(cellArray, 131)); // 绗洓娆℃嫙浠橀噾棰濓紙绗�101鍒楋級
- BigDecimal plannedPay5 = parseBigDecimal(getCellValue(cellArray, 137)); // 绗簲娆℃嫙浠橀噾棰濓紙绗�107鍒楋級
- BigDecimal plannedPayTotal = add(plannedPay1, plannedPay2, plannedPay3, plannedPay4, plannedPay5);
-// po.setPlannedPayTotal(plannedPayTotal); // 鑷畾涔夊瓧娈碉細鎷熶粯鍚堣
-
- // 10. 瀹炰粯鍚堣 = 绗竴娆绗簲娆″疄浠橀噾棰濅箣鍜岋紙绗�85鍒�+绗�91鍒�+绗�97鍒�+绗�103鍒�+绗�109鍒楋級
- BigDecimal actualPay1 = parseBigDecimal(getCellValue(cellArray, 117)); // 绗竴娆″疄浠橀噾棰濓紙绗�86鍒楋級
- BigDecimal actualPay2 = parseBigDecimal(getCellValue(cellArray, 123)); // 绗簩娆″疄浠橀噾棰濓紙绗�92鍒楋級
- BigDecimal actualPay3 = parseBigDecimal(getCellValue(cellArray, 130)); // 绗笁娆″疄浠橀噾棰濓紙绗�98鍒楋級
- BigDecimal actualPay4 = parseBigDecimal(getCellValue(cellArray, 135)); // 绗洓娆″疄浠橀噾棰濓紙绗�104鍒楋級
- BigDecimal actualPay5 = parseBigDecimal(getCellValue(cellArray, 141)); // 绗簲娆″疄浠橀噾棰濓紙绗�110鍒楋級
- BigDecimal actualPayTotal = add(actualPay1, actualPay2, actualPay3, actualPay4, actualPay5);
-// po.setActualPayTotal(actualPayTotal); // 鑷畾涔夊瓧娈碉細瀹炰粯鍚堣
-
- // 11. 鏈粯鍚堣 = 瀹″畾閲戦-瀹炰粯鍚堣锛堝瀹氶噾棰濆彇瀹¤閲戦锛屾棤鍒欏彇涓氬浼氶噾棰濓級
- BigDecimal approvedAmount = (auditAmount.compareTo(BigDecimal.ZERO) == 0) ? ownersAmount : auditAmount;
- BigDecimal unpaidTotal = subtract(approvedAmount, actualPayTotal);
-// po.setUnpaidTotal(unpaidTotal); // 鑷畾涔夊瓧娈碉細鏈粯鍚堣
-
- // 12. 鍨粯閲戦 = 澶嶆潅鏉′欢鍒ゆ柇锛堝熀閲戠被鍨嬨�佸埌璐﹀悎璁°�佸叕甯冩儏鍐碉級
-// BigDecimal advanceAmount = calculateAdvanceAmount(fundType, arrivalTotal, actualPayTotal, po.getPublicIncomeAnnouncement());
-// po.setAdvanceAmount(advanceAmount); // 鑷畾涔夊瓧娈碉細鍨粯閲戦
-
- // 13. 鏈粯鍚堣锛堝埌璐﹀彛寰勶級 = 鎸夊叕甯冩儏鍐靛垎鏀绠�
-// BigDecimal unpaidByArrival = calculateUnpaidByArrival(po.getPublicIncomeAnnouncement(), ownersAmount, managementFee, actualPayTotal, arrivalTotal);
-// po.setUnpaidByArrival(unpaidByArrival); // 鑷畾涔夊瓧娈碉細鏈粯鍚堣锛堝埌璐﹀彛寰勶級
-
- // 14-16. 寰呬粯閲戦A/B/C
-// po.setPendingPayA(subtract(auditAmount, managementFee)); // 寰呬粯閲戦A = 瀹′环閲戦 - 绠＄悊璐�
-// po.setPendingPayB(subtract(auditAmount, qualityGuaranteeAmount, managementFee)); // 寰呬粯閲戦B = 瀹′环閲戦 - 璐ㄤ繚閲� - 绠＄悊璐�
-// po.setPendingPayC(multiply(arrivalTotal, BigDecimal.ONE.subtract(managementFeeRatio))); // 寰呬粯閲戦C = 鍒拌处鍚堣*(1-绠＄悊璐瑰崰姣�)
// 鏀粯淇℃伅锛堢111-116鍒楋級
po.setPayeeName(getCellValue(cellArray, 143)); // 鏀粯鍏徃鍚嶇О/涓汉鍚嶅瓧
@@ -254,62 +313,91 @@
private List<MpPaymentRecordPo> buildMpPaymentRecordPos(JSONArray cellArray, String flowNumber,String mainPo) throws ParseException {
List<MpPaymentRecordPo> pos = new ArrayList<>();
// 绗竴娆℃墦鍗版暟鎹紙绗�47-53鍒楋級
- pos.add(buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 1, 43, 44, 45, 46, 47, 48, 49));
+ buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 1, 45, pos);
// 绗簩娆℃墦鍗版暟鎹紙绗�54-60鍒楋級
- pos.add(buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 2, 55, 56, 57, 58, 59, 60, 61));
+ buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 2, 57, pos);
// 绗笁娆℃墦鍗版暟鎹紙绗�61-67鍒楋級
- pos.add(buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 3, 67, 68, 69, 70, 71, 72, 73));
+ buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 3, 69, pos);
// 绗洓娆℃墦鍗版暟鎹紙绗�68-74鍒楋級
- pos.add(buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 4, 79, 80, 81, 82, 83, 84, 85));
+ buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 4, 81, pos);
// 绗簲娆℃墦鍗版暟鎹紙绗�75-81鍒楋級
- pos.add(buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 5, 91, 92, 93, 94, 95, 96, 97));
+ buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 5, 93, pos);
return pos;
}
/**
* 鏋勫缓鍗曟鎵撳嵃鐨� MpPaymentRecordPo
*/
- private MpPaymentRecordPo buildSinglePaymentRecord(String mpId, JSONArray cellArray, String flowNumber, int printTimes,
- int dateCol, int resolutionCol, int amountCol,
- int arrivalDateCol, int arrivalAmountCol,
- int withdrawerCol, int shortageCol) throws ParseException {
+ private void buildSinglePaymentRecord(String mpId, JSONArray cellArray, String flowNumber, int printTimes,
+ int index, List<MpPaymentRecordPo> pos) throws ParseException {
+
+// if (getCellValue(cellArray, index).isEmpty()) {
+// return;
+// }
+
MpPaymentRecordPo po = new MpPaymentRecordPo();
long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
po.setId(longId+"");
po.setMpId(mpId); // mp_id = 娴佽浆缂栧彿_鎵撳嵃娆℃暟锛堢‘淇濆敮涓�锛�
- po.setFirstPrintDate(parseDate(getCellValue(cellArray, dateCol)));
- po.setResolutionNumber(getCellValue(cellArray, resolutionCol));
- po.setPrintAmount(parseBigDecimal(getCellValue(cellArray, amountCol)));
- po.setArrivalDate(parseDate(getCellValue(cellArray, arrivalDateCol)));
- po.setArrivalAmount(parseBigDecimal(getCellValue(cellArray, arrivalAmountCol)));
- po.setWithdrawer(getCellValue(cellArray, withdrawerCol));
- po.setShortageOrArrears(parseBigDecimal(getCellValue(cellArray, shortageCol)));
+ po.setFirstPrintDate(parseDate(getCellValue(cellArray, index)));
+ po.setResolutionNumber(getCellValue(cellArray, index + 1));
+ po.setPrintAmount(parseBigDecimal(getCellValue(cellArray, index + 2)));
+ po.setArrivalDate(parseDate(getCellValue(cellArray, index + 3)));
+ po.setArrivalAmount(parseBigDecimal(getCellValue(cellArray, index + 4)));
+ po.setWithdrawer(getCellValue(cellArray, index + 5));
+ po.setShortageOrArrears(parseBigDecimal(getCellValue(cellArray, index + 6)));
// 涓氫富鍦板潃淇℃伅锛堝鐢╩ain琛ㄧ殑鍦板潃锛�
- po.setRoadName(getCellValue(cellArray, 31));
- po.setLane(getCellValue(cellArray, 32));
- po.setDoor(getCellValue(cellArray, 33));
- po.setRoom(getCellValue(cellArray, 34));
- po.setOwnerAddress(getCellValue(cellArray, 36));
- return po;
+ po.setRoadName(getCellValue(cellArray, index + 7));
+ po.setLane(getCellValue(cellArray, index + 8));
+ po.setDoor(getCellValue(cellArray, index + 9));
+ po.setRoom(getCellValue(cellArray, index + 10));
+ po.setOwnerAddress(getCellValue(cellArray, index + 11));
+ po.setPrintAmount(new BigDecimal(flowNumber));
+ po.setSnakeCase(getCellValue(cellArray, 105));
+ pos.add(po);
}
/**
* 鏋勫缓 MpFifthPaymentRecordPo锛堢浜旀鎷熶粯瀹炰粯锛�
*/
- private MpFifthPaymentRecordPo buildMpFifthPaymentRecordPo(JSONArray cellArray, String flowNumber,String mainPo) throws ParseException {
- MpFifthPaymentRecordPo po = new MpFifthPaymentRecordPo();
+ private List<MpFifthPaymentRecord> buildMpFifthPaymentRecordPos(JSONArray cellArray, String flowNumber,String mainPo) throws ParseException {
+ List<MpFifthPaymentRecord> pos = new ArrayList<>();
+
+ buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 115);
+
+ buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 121);
+
+ buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 127);
+
+ buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 133);
+
+ buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 139);
+
+ return pos;
+ }
+
+ private void buildMpFifthPaymentRecordPo(List<MpFifthPaymentRecord> pos, JSONArray cellArray, String flowNumber, String mainPo, int index) {
+
+// if (getCellValue(cellArray, index).isEmpty()) {
+// return;
+// }
+
+ MpFifthPaymentRecord po = new MpFifthPaymentRecord();
+
long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 閬垮厤璐熸暟
po.setId(longId+"");
- po.setMpId(mainPo); // mp_id = 娴佽浆缂栧彿_FIFTH
- // 绗簲娆℃嫙浠樺疄浠樻暟鎹紙绗�106-111鍒楋級
- po.setFifthPlannedPaymentAmount(parseBigDecimal(getCellValue(cellArray, 137))); // 绗簲娆℃嫙浠橀噾棰�
- po.setPlannedPaymentDate(parseDate(getCellValue(cellArray, 138))); // 绗簲娆℃嫙浠樻棩鏈�
- po.setCategory(getCellValue(cellArray, 139)); // 绫诲埆
- po.setReimburser(getCellValue(cellArray, 140)); // 鎶ラ攢浜�
- po.setActualPaymentAmount(parseBigDecimal(getCellValue(cellArray, 141))); // 绗簲娆″疄浠橀噾棰�
- po.setActualPaymentDate(parseDate(getCellValue(cellArray, 142))); // 绗簲娆″疄浠樻棩鏈�
- return po;
+ po.setMpId(flowNumber);
+ po.setFifthPlannedPaymentAmount(Vtil.defaultValue(getCellValue(cellArray, index)));
+ try {
+ po.setPlannedPaymentDate(parseDate(getCellValue(cellArray, index + 1)));
+ po.setActualPaymentDate(parseDate(getCellValue(cellArray, index + 5)));
+ } catch (ParseException e) {
+ throw new RuntimeException(e);
+ }
+ po.setCategory(getCellValue(cellArray, index + 2));
+ po.setReimburser(getCellValue(cellArray, index + 3));
+ po.setActualPaymentAmount(getCellValue(cellArray, index + 4));
}
/**
@@ -351,31 +439,6 @@
}
}
- /**
- * 鏁版嵁搴撲繚瀛橈紙璋冪敤SqlSessionTemplate鐗圫ervice锛�
- */
- private void saveToDatabase(MaintenancePaymentPo mainPo,
- List<MpPaymentRecordPo> paymentRecordPos,
- MpFifthPaymentRecordPo fifthPo) {
- // 1. 淇濆瓨涓昏〃锛堝崟鏉★級
- int mainFlag = maintenancePaymentService.saveMaintenancePayment(mainPo);
- if (mainFlag <= 0) {
- throw new RuntimeException("缁翠慨璧勯噾鏀彇涓昏〃鎻掑叆澶辫触锛屾祦杞紪鍙凤細" + mainPo.getFlowNumber());
- }
-
- // 2. 鎵归噺淇濆瓨5娆℃墦鍗拌褰�
- int batchFlag = mpPaymentRecordService.batchSaveMpPaymentRecord(paymentRecordPos);
- if (batchFlag <= 0) {
- throw new RuntimeException("鎵撳嵃鏀彇鍒拌处璁板綍鎵归噺鎻掑叆澶辫触锛屾祦杞紪鍙凤細" + mainPo.getFlowNumber());
- }
-
- // 3. 淇濆瓨绗簲娆℃嫙浠樺疄浠樿褰�
- int fifthFlag = mpFifthPaymentRecordService.saveMpFifthPaymentRecord(fifthPo);
- if (fifthFlag <= 0) {
- throw new RuntimeException("绗簲娆℃嫙浠樺疄浠樿褰曟彃鍏ュけ璐ワ紝娴佽浆缂栧彿锛�" + mainPo.getFlowNumber());
- }
- }
-
// -------------------------- 宸ュ叿鏂规硶 --------------------------
/**
* 鑾峰彇鍗曞厓鏍煎�硷紙澶勭悊null/绌哄瓧绗︿覆锛�
@@ -386,6 +449,15 @@
}
Object value = cellArray.get(index);
return value == null ? "" : value.toString().trim();
+ }
+
+
+ private Double getCellNum(JSONArray cellArray, int index) {
+ if (index < 0 || index >= cellArray.size()) {
+ return 0.0;
+ }
+ Object value = cellArray.get(index);
+ return value == null ? 0 : Double.parseDouble((String) value);
}
/**
@@ -425,40 +497,81 @@
}
}
+ private String parseDateToString(String str) throws ParseException{
+ return parseDate(str) == null ? "" : str;
+ }
+
/**
* 瑙ｆ瀽鏃ユ湡锛堟敮鎸佸绉嶆牸寮忥紝绌哄�艰繑鍥瀗ull锛�
*/
+ // 鎵╁睍鏀寔鐨勬棩鏈熸牸寮忥細瑕嗙洊妯嚎/鏂滄潬鍒嗛殧銆佸勾鏈堟棩/鏈堟棩骞寸瓑甯歌鏍煎紡
+ private static final List<SimpleDateFormat> DATE_FORMATS = new ArrayList<SimpleDateFormat>() {{
+ add(new SimpleDateFormat("yyyy-MM-dd")); // 2025-12-12
+ add(new SimpleDateFormat("yyyy/MM/dd")); // 2025/12/12
+ add(new SimpleDateFormat("yyyyMMdd")); // 20251212
+ add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")); // 甯︽椂鍒嗙鐨勬牸寮忥紙鍏煎锛�
+ add(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
+ add(new SimpleDateFormat("MM/dd/yyyy")); // 鍏煎鍥藉鏍煎紡锛堝彲閫夛級
+ }};
+
+ /**
+ * 瑙ｆ瀽鏃ユ湡锛氭敮鎸丒xcel鏃ユ湡搴忓垪鍙枫��2025-12-12銆�2025/12/12绛夋牸寮�
+ * @param str 寰呰В鏋愮殑鏃ユ湡瀛楃涓诧紙鎴朎xcel搴忓垪鍙凤級
+ * @return 瑙ｆ瀽鍚庣殑Date瀵硅薄锛岀┖瀛楃涓茶繑鍥瀗ull
+ * @throws ParseException 涓嶆敮鎸佺殑鏍煎紡鎶涘嚭寮傚父
+ */
private Date parseDate(String str) throws ParseException {
- if (isBlank(str)) {
+ // 绌哄�煎鐞�
+ if (StringUtils.isBlank(str)) {
return null;
}
+ String trimStr = str.trim();
- // 鎵嬪姩瑙ｆ瀽Excel鏃ユ湡搴忓垪鍙凤紙1900-01-01涓哄熀鍑嗭紝娉ㄦ剰Excel鐨�1900闂板勾bug锛�
+ // 绗竴姝ワ細灏濊瘯瑙ｆ瀽Excel鏃ユ湡搴忓垪鍙凤紙鏁板瓧鏍煎紡锛�
try {
- double excelDateNum = Double.parseDouble(str.trim());
- // Excel 1900鏃ユ湡绯荤粺锛氬簭鍒楀彿1=1900-01-01锛屼笖閿欒璁や负1900鏄棸骞达紙澶氱畻1澶╋級
- Calendar cal = Calendar.getInstance();
- cal.set(1900, 0, 1); // 1900-01-01
- cal.add(Calendar.DAY_OF_YEAR, (int) excelDateNum - 2); // 淇闂板勾bug锛堝噺2锛�
- return cal.getTime();
+ double excelDateNum = Double.parseDouble(trimStr);
+ return convertExcelSerialToDate(excelDateNum);
} catch (NumberFormatException e) {
- // 涓嶆槸鏁板瓧锛岀户缁В鏋愭枃鏈牸寮�
+ // 涓嶆槸鏁板瓧锛岀户缁В鏋愭枃鏈棩鏈熸牸寮�
}
- // 鏂囨湰鏍煎紡瑙ｆ瀽锛堝悓涔嬪墠閫昏緫锛�
+ // 绗簩姝ワ細灏濊瘯瑙ｆ瀽鏂囨湰鏍煎紡鏃ユ湡锛堣鐩�-鍜�/鍒嗛殧绗︼級
for (SimpleDateFormat format : DATE_FORMATS) {
try {
- format.setLenient(false);
- return format.parse(str.trim());
+ format.setLenient(false); // 涓ユ牸鏍￠獙锛岄伩鍏�2025-13-32杩欑被鏃犳晥鏃ユ湡
+ return format.parse(trimStr);
} catch (ParseException e) {
+ // 璇ユ牸寮忚В鏋愬け璐ワ紝灏濊瘯涓嬩竴涓�
continue;
}
}
+ // 鎵�鏈夋牸寮忛兘涓嶅尮閰嶏紝鎶涘紓甯�
throw new ParseException("涓嶆敮鎸佺殑鏃ユ湡鏍煎紡锛�" + str, 0);
}
/**
+ * 杞崲Excel鏃ユ湡搴忓垪鍙蜂负Date锛堜慨姝�1900闂板勾bug锛�
+ * Excel搴忓垪鍙疯鍒欙細1=1900-01-01锛堥敊璇涓�1900鏄棸骞达紝澶氱畻1澶╋級
+ * @param excelSerial Excel鏃ユ湡搴忓垪鍙凤紙濡�45735锛�
+ * @return 瀵瑰簲鐨凞ate瀵硅薄
+ */
+ private Date convertExcelSerialToDate(double excelSerial) {
+ // 鍒濆鍖朇alendar锛氭椂鍖鸿涓篏MT锛岄伩鍏嶆湰鍦版椂鍖哄亸绉�
+ Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
+ // Excel 1900鏃ユ湡绯荤粺鍩哄噯锛氬簭鍒楀彿1瀵瑰簲1900-01-01
+ cal.set(1900, Calendar.JANUARY, 1, 0, 0, 0);
+ cal.set(Calendar.MILLISECOND, 0);
+
+ // 淇Excel鐨�1900闂板勾bug锛欵xcel璁や负1900鏄棸骞达紝瀹為檯涓嶆槸锛岄渶鍑�2澶�
+ // 娉細濡傛灉搴忓垪鍙�<60锛堝搴�1900-02-28锛夛紝鍑�1澶╁嵆鍙紱閫氱敤鍦烘櫙鍑�2澶╁吋瀹规墍鏈夋儏鍐�
+ int daysToAdd = (int) excelSerial - 2;
+ cal.add(Calendar.DAY_OF_YEAR, daysToAdd);
+
+ return cal.getTime();
+ }
+
+ /**
* 杞崲鐩栫珷鐘舵�侊紙鈭氣啋鏄紝鍏朵粬鈫掑惁锛�
*/
private String convertSealStatus(String str) {
--
Gitblit v1.8.0