From 47a873454ed83a184c55dd7ee904375cee3d7386 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期一, 21 九月 2026 13:08:00 +0800
Subject: [PATCH] docs: 新增汇总大表软件输出与手工定稿差异报告
---
traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java | 184 ++++++++++++++++++++++++++++++++++++---------
1 files changed, 145 insertions(+), 39 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java b/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
index 19191bd..446043d 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
@@ -1808,6 +1808,35 @@
}
}
+ /** 鎸夎〃澶村叧閿瓧瀹氫綅鍒楀彿锛氫粠绗� 1 鍒楀悜鍙虫壂锛屽懡涓换涓�鍏抽敭瀛楀嵆杩斿洖璇ュ垪锛涙湭鍛戒腑杩斿洖 -1 */
+ private int findColByKeywords(Row header, String... keywords) {
+ if (header == null) return -1;
+ for (int c = 0; c <= header.getLastCellNum(); c++) {
+ Cell cell = header.getCell(c);
+ if (cell == null) continue;
+ String h = FORMATTER.formatCellValue(cell).trim();
+ if (h.isEmpty()) continue;
+ for (String k : keywords) {
+ if (h.contains(k)) return c;
+ }
+ }
+ return -1;
+ }
+
+ /** 琛ㄥご琛屾枃鏈紙鎶ラ敊鎻愮ず鐢級锛屾渶澶氬彇 12 鍒� */
+ private String headerText(Row header) {
+ if (header == null) return "绌�";
+ StringBuilder sb = new StringBuilder();
+ for (int c = 0; c < Math.min(header.getLastCellNum(), 12); c++) {
+ Cell cell = header.getCell(c);
+ String h = cell == null ? "" : FORMATTER.formatCellValue(cell).trim();
+ if (h.isEmpty()) continue;
+ if (sb.length() > 0) sb.append(" | ");
+ sb.append(h);
+ }
+ return sb.length() == 0 ? "绌�" : sb.toString();
+ }
+
/** 鎸夎〃澶村悕绉板缓绔嬪垪鍙锋槧灏勶紝鍏煎鏂版棫妯℃澘鍒椾綅宸紓 */
private Map<String, Integer> buildHeaderMap(Row header) {
Map<String, Integer> colMap = new HashMap<>();
@@ -2465,10 +2494,10 @@
}
if (name == null || name.trim().isEmpty()) continue;
String nt = name.trim().replaceAll("[\r\n]+", "");
- if (nt.contains("鍚堣") || nt.contains("鎬昏")) continue;
+ String ntFlat = nt.replaceAll("\\s+", "");
+ if (ntFlat.contains("鍚堣") || ntFlat.contains("鎬昏") || ntFlat.contains("灏忚")) continue;
if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
if (nt.matches("^锛圼涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+锛�.*")) continue;
- if (nt.replace(" ", "").matches(".*灏廫\s*璁�.*")) continue;
if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
if (hasSeq) {
if (seq == null || seq.trim().isEmpty()) {
@@ -2489,6 +2518,8 @@
project.setProjectName(nt);
project.setCategory(category);
project.setCity(currentCity);
+ String countyCell = cellStr(row, col.get("county"));
+ if (countyCell != null && !countyCell.trim().isEmpty()) project.setCounty(countyCell.trim());
project.setBuilderName(cellStr(row, col.get("builder")));
project.setConstructNature(cellStr(row, col.get("nature")));
project.setStartTime(investTime(cellStr(row, col.get("start"))));
@@ -2524,13 +2555,22 @@
Row row = sh.getRow(r);
if (row == null) continue;
boolean hasName = false;
+ int headerCells = 0;
for (int c = 0; c <= row.getLastCellNum(); c++) {
String v = cellStr(row, c);
- if (v != null && v.contains("椤圭洰鍚嶇О")) { hasName = true; break; }
+ if (v == null) continue;
+ String t = v.trim();
+ if (t.isEmpty() || t.length() > 20) continue; // 璇存槑鎬ч暱鍙ヤ笉绠楄〃澶村瓧娈�
+ String k = investHeaderKey(t);
+ if (k == null) continue;
+ headerCells++;
+ if ("name".equals(k)) hasName = true;
}
- if (!hasName) continue;
+ // 鐪熻〃澶磋鑷冲皯鏈� 2 涓煭琛ㄥご瀛楁锛涘彧鏈変竴鏍奸暱鍙ュ惈銆岄」鐩悕绉般�嶇殑璇存槑椤典細琚帓闄�
+ if (!hasName || headerCells < 2) continue;
Map<Integer, String> colTexts = new HashMap<>();
- for (int rr = r; rr <= Math.min(r + 3, sh.getLastRowNum()); rr++) {
+ int scanEnd = investHeaderScanEnd(sh, r, row);
+ for (int rr = r; rr <= scanEnd; rr++) {
Row rr2 = sh.getRow(rr);
if (rr2 == null) continue;
for (int c = 0; c <= rr2.getLastCellNum(); c++) {
@@ -2541,23 +2581,8 @@
}
Map<String, Integer> col = new HashMap<>();
for (Map.Entry<Integer, String> e : colTexts.entrySet()) {
- int c = e.getKey();
- String v = e.getValue();
- if (v.contains("椤圭洰鍚嶇О")) col.putIfAbsent("name", c);
- else if (v.contains("鎶曡祫寤鸿") || v.contains("寤鸿鍗曚綅") || v.contains("椤圭洰涓氫富")) col.putIfAbsent("builder", c);
- else if (v.contains("寤鸿鎬ц川")) col.putIfAbsent("nature", c);
- else if (v.contains("寮�宸�")) col.putIfAbsent("start", c);
- else if (v.contains("绔ｅ伐") || v.contains("寤烘垚")) col.putIfAbsent("end", c);
- else if ((v.contains("璁″垝鎬绘姇璧�") || v.contains("鎬绘姇璧�")) && !v.contains("绱")) col.putIfAbsent("total", c);
- else if (v.contains("鑷紑濮嬪缓璁�")) col.putIfAbsent("startcum", c);
- else if (v.contains("鏈勾璁″垝")) col.putIfAbsent("yearplan", c);
- else if (v.contains("鑷勾鍒濈疮璁�")) col.putIfAbsent("yearcum", c);
- else if (v.contains("鏈湀瀹屾垚")) col.putIfAbsent("monthdone", c);
- else if (v.contains("寤鸿闃舵")) col.putIfAbsent("stage", c);
- else if (v.contains("褰㈣薄杩涘害")) col.putIfAbsent("desc", c);
- else if (v.contains("宸ュ彲") && !v.contains("椤圭洰鍚嶇О")) col.putIfAbsent("gk", c);
- else if (v.contains("鍒濊") && !v.contains("椤圭洰鍚嶇О")) col.putIfAbsent("cs", c);
- else if (v.contains("寤虹瓚闈㈢Н") && !v.contains("鏂板")) col.putIfAbsent("area", c);
+ String k = investHeaderKey(e.getValue());
+ if (k != null) col.putIfAbsent(k, e.getKey());
}
if (!col.containsKey("name")) continue;
for (int c = 0; c <= row.getLastCellNum(); c++) {
@@ -2574,14 +2599,85 @@
int md = col.get("yearcum") + 1;
if (md <= row.getLastCellNum()) col.put("monthdone", md);
}
+ // desc 缂哄け鏃舵寜 stage+1 鍏滃簳锛氱渷鏍囥�岄」鐩缓璁鹃樁娈点�佽繘搴︽儏鍐点�嶆槸 L/M 鍚堝苟琛ㄥご锛�
+ // 瀛濇劅/鎭╂柦/鏋楀尯/闅忓窞绛夊競宸炲彧鍦� L 鍐欍�屽缓璁鹃樁娈点�嶃�丮 鐩存帴鍐欒繘搴︽枃瀛楄�屼笉鍐欐爣棰�
+ if (!col.containsKey("desc") && col.containsKey("stage") && col.get("stage") >= 0) {
+ int dc = col.get("stage") + 1;
+ if (dc <= row.getLastCellNum()) col.put("desc", dc);
+ }
for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",
- "yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area"}) {
+ "yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area", "county"}) {
col.putIfAbsent(k, -1);
}
col.put("headerRow", r);
return col;
}
return null;
+ }
+
+ /** 鍗曚釜琛ㄥご鍗曞厓鏍� -> 瀛楁閿紙null 琛ㄧず涓嶆槸琛ㄥご瀛楁锛夛紱渚涜〃澶磋鍒ゅ畾涓庡垪鏄犲皠鍏辩敤 */
+ private String investHeaderKey(String v) {
+ if (v == null) return null;
+ String flat = v.replace(" ", "");
+ if (v.contains("椤圭洰鍚嶇О")) return "name";
+ // 銆屽幙锛堝競銆佸尯锛夈�嶇殑鍚勭鍐欐硶锛氬崐/鍏ㄨ鎷彿銆侀】鍙�/閫楀彿銆佹湁鏃犳嫭鍙凤紝缁熶竴褰掍竴鍖栧悗鍐嶅垽瀹�
+ String flatCounty = flat.replace("锛�", "(").replace("锛�", ")").replace("銆�", "").replace("锛�", "").replace(",", "");
+ if (flatCounty.contains("鍘�(甯傚尯)") || flatCounty.contains("鍘垮競鍖�") || flatCounty.contains("鍖哄幙")) return "county";
+ if (v.contains("鎶曡祫寤鸿") || v.contains("寤鸿鍗曚綅") || v.contains("椤圭洰涓氫富")) return "builder";
+ if (v.contains("寤鸿鎬ц川")) return "nature";
+ if (v.contains("寮�宸�")) return "start";
+ if (v.contains("绔ｅ伐") || v.contains("寤烘垚")) return "end";
+ if ((v.contains("璁″垝鎬绘姇璧�") || v.contains("鎬绘姇璧�")) && !v.contains("绱")) return "total";
+ if (v.contains("鑷紑濮嬪缓璁�")) return "startcum";
+ if (v.contains("鏈勾璁″垝")) return "yearplan";
+ if (v.contains("鑷勾鍒濈疮璁�")) return "yearcum";
+ if (v.contains("鏈湀瀹屾垚")) return "monthdone";
+ if (v.contains("寤鸿闃舵")) return "stage";
+ if (v.contains("褰㈣薄杩涘害") || v.contains("寤鸿杩涘睍")) return "desc";
+ if (v.contains("宸ュ彲") && !v.contains("椤圭洰鍚嶇О")) return "gk";
+ if (v.contains("鍒濊") && !v.contains("椤圭洰鍚嶇О")) return "cs";
+ if (v.contains("寤虹瓚闈㈢Н") && !v.contains("鏂板")) return "area";
+ return null;
+ }
+
+ /** 琛ㄥご鏂囨湰鎵弿涓嬬晫锛氶粯璁よ〃澶磋 +3锛涜嫢銆岄」鐩悕绉般�嶅垪棣栦釜鏁版嵁琛屾洿闈犱笅锛屽垯鎵╁睍鍒版暟鎹鍓嶄竴琛岋紙涓婇檺 +12锛夈��
+ * 鑳屾櫙锛氬捀瀹�/鍗佸牥绛夊競宸炴妸銆屽缓璁鹃樁娈�/褰㈣薄杩涘害銆嶆爣绛惧啓鍦ㄨ〃澶翠笅绗� 4~8 琛岋紝鍥哄畾 +3 浼氭紡鍒楋紙褰㈣薄杩涘害璇讳笉鍒帮級銆� */
+ private int investHeaderScanEnd(Sheet sh, int headerRow, Row headerRowObj) {
+ int hardEnd = Math.min(headerRow + 12, sh.getLastRowNum());
+ int nameCol = -1;
+ if (headerRowObj != null) {
+ for (int c = 0; c <= headerRowObj.getLastCellNum(); c++) {
+ String v = cellStr(headerRowObj, c);
+ if (v != null && v.contains("椤圭洰鍚嶇О")) { nameCol = c; break; }
+ }
+ }
+ int firstData = findFirstInvestDataRow(sh, headerRow, nameCol);
+ int scanEnd = (firstData > headerRow)
+ ? Math.min(hardEnd, firstData - 1)
+ : Math.min(headerRow + 3, sh.getLastRowNum());
+ if (scanEnd < headerRow) scanEnd = headerRow;
+ return scanEnd;
+ }
+
+ /** 銆岄」鐩悕绉般�嶅垪棣栦釜鏁版嵁琛屼笅鏍囷紙璺宠繃琛ㄥご缁涓庣┖琛岋級锛涙壘涓嶅埌杩斿洖 -1 */
+ private int findFirstInvestDataRow(Sheet sh, int headerRow, int nameCol) {
+ if (nameCol < 0) return -1;
+ for (int r = headerRow + 1; r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String name = cellStr(row, nameCol);
+ if (name == null || name.trim().isEmpty()) continue;
+ String nt = name.trim().replaceAll("[\r\n]+", "");
+ if (nt.contains("椤圭洰鍚嶇О")) continue;
+ String flat = nt.replaceAll("\\s+", "");
+ if (flat.contains("鍚堣") || flat.contains("鎬昏") || flat.contains("灏忚")) continue;
+ if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
+ if (nt.matches("^锛圼涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+锛�.*")) continue;
+ if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
+ if (investmentCity(nt) != null) continue;
+ return r;
+ }
+ return -1;
}
/** 缁熻 sheet 鍐呴」鐩暟鎹鏁帮紙鐢ㄤ簬澶� sheet 鍘嗗彶鏂囦欢閫夊綋鏈� sheet锛� */
@@ -2596,9 +2692,9 @@
String name = cellStr(row, nameCol);
if (name == null || name.trim().isEmpty()) continue;
String nt = name.trim().replaceAll("[\r\n]+", "");
- if (nt.contains("鍚堣") || nt.contains("鎬昏")) continue;
+ String ntFlat = nt.replaceAll("\\s+", "");
+ if (ntFlat.contains("鍚堣") || ntFlat.contains("鎬昏") || ntFlat.contains("灏忚")) continue;
if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
- if (nt.replace(" ", "").matches(".*灏廫\s*璁�.*")) continue;
if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
if (!hasSeq) {
if (nt.length() > 4) count++;
@@ -2864,32 +2960,42 @@
this.monthly = monthly;
}
}
- /** 鎶曡祫绯荤粺瀵煎嚭瀵煎叆锛堝鏍告瘮瀵瑰熀鍑嗭級锛屽浐瀹�8鍒楋細琛屽彿/鍗曚綅/鏃舵湡/璁″垝鎬绘姇璧�/鑷紑濮嬬疮璁�/鏈勾璁″垝/鑷勾鍒濈疮璁�/褰撴湀瀹屾垚 */
- private static final int[] INVEST_SYSTEM_HEADER_COLS = {0, 1, 2, 3, 4, 5, 6, 7};
- private static final String[] INVEST_SYSTEM_HEADER_KEYWORDS =
- {"琛屽彿", "鍗曚綅", "鏃舵湡", "璁″垝鎬绘姇璧�", "鑷紑濮嬪缓璁�", "鏈勾璁″垝鎶曡祫", "鑷勾鍒�", "褰撴湀瀹屾垚鎶曡祫"};
-
+ /** 鎶曡祫绯荤粺瀵煎嚭瀵煎叆锛堝鏍告瘮瀵瑰熀鍑嗭級銆�
+ * 鍒椾綅**鎸夎〃澶村叧閿瓧瀹氫綅**锛屽吋瀹逛袱绉嶆ā鏉匡細
+ * - 鑰佹ā鏉裤�婃ā鏉縚鏌ヨ缁撴灉(鎶曡祫绯荤粺-瀹㈣繍+鐗╂祦 2026.7).xls銆嬶細琛屽彿/鍗曚綅/鏃舵湡/璁″垝鎬绘姇璧�/鑷紑濮嬪缓璁剧疮璁�/鏈勾璁″垝鎶曡祫/鑷勾鍒濈疮璁�/褰撴湀瀹屾垚鎶曡祫
+ * - 鏂版ā鏉裤��2026骞碭鏈堟姇璧勭郴缁熼」鐩槑缁�.xls銆嬶紙鐢ㄦ埛 2026-09-21 鏇存崲锛夛細琛屽彿/鎶曡祫椤圭洰/鏃舵湡/椤圭洰鎵�澶勯樁娈�/璁″垝鎬绘姇璧�/鏈勾璁″垝鎶曡祫/鑷紑濮嬪缓璁捐嚦鏈勾锛堟湀锛夊簳-绱瀹屾垚鎶曡祫/褰撴湀瀹屾垚鎶曡祫
+ * 鏂版ā鏉�**娌℃湁銆岃嚜骞村垵绱銆嶅垪** 鈫� year_cum 鐣欑┖锛堝鏍告椂鎸夈�屽熀鍑嗙己澶便�嶈烦杩囷紝涓嶅啀褰撴垚 0 鍙備笌姣斿锛夈�� */
public int importInvestmentSystem(MultipartFile file, String period) throws Exception {
List<InvestmentSystem> list = new ArrayList<>();
int failRows = 0;
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
Sheet sheet = wb.getSheetAt(0);
- requireHeaderKeyword(sheet, "鎶曡祫绯荤粺瀵煎嚭", "椤圭洰鍚嶇О", "璁″垝鎬绘姇璧�", "鍗曚綅");
- requireHeaderLayout(sheet, "鎶曡祫绯荤粺瀵煎嚭", "妯℃澘_鏌ヨ缁撴灉(鎶曡祫绯荤粺-瀹㈣繍+鐗╂祦 2026.7).xls",
- INVEST_SYSTEM_HEADER_COLS, INVEST_SYSTEM_HEADER_KEYWORDS);
+ requireHeaderKeyword(sheet, "鎶曡祫绯荤粺瀵煎嚭", "鎶曡祫椤圭洰", "椤圭洰鍚嶇О", "鍗曚綅", "璁″垝鎬绘姇璧�");
+ Row header = sheet.getRow(0);
+ int cName = findColByKeywords(header, "鎶曡祫椤圭洰", "椤圭洰鍚嶇О", "鍗曚綅");
+ int cTotal = findColByKeywords(header, "璁″垝鎬绘姇璧�");
+ int cYearPlan = findColByKeywords(header, "鏈勾璁″垝鎶曡祫");
+ int cStartCum = findColByKeywords(header, "鑷紑濮嬪缓璁�");
+ int cYearCum = findColByKeywords(header, "鑷勾鍒�");
+ int cMonthDone = findColByKeywords(header, "褰撴湀瀹屾垚鎶曡祫");
+ if (cName < 0 || cTotal < 0 || cYearPlan < 0 || cStartCum < 0 || cMonthDone < 0) {
+ throw new RuntimeException("鎵�閫夋暟鎹被鍨嬨�愭姇璧勭郴缁熷鍑恒�戣〃澶村垪涓嶅畬鏁达細闇�瑕併�屾姇璧勯」鐩�(鎴栧崟浣�)銆佽鍒掓�绘姇璧勩�佹湰骞磋鍒掓姇璧勩��"
+ + "鑷紑濮嬪缓璁锯�︾疮璁″畬鎴愭姇璧勩�佸綋鏈堝畬鎴愭姇璧勩�嶇瓑鍒楋紝瀹為檯琛ㄥご涓恒��" + headerText(header)
+ + "銆嶃�傝浣跨敤绯荤粺妯℃澘銆�2026骞碭鏈堟姇璧勭郴缁熼」鐩槑缁嗐�嬫垨銆婃ā鏉縚鏌ヨ缁撴灉(鎶曡祫绯荤粺-瀹㈣繍+鐗╂祦)銆嬫暣鐞嗗悗鍐嶅鍏�");
+ }
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) continue;
- String name = getString(row, 1);
+ String name = getString(row, cName);
if (name == null || name.trim().isEmpty()) continue;
InvestmentSystem sys = new InvestmentSystem();
sys.setReportPeriod(period);
sys.setProjectName(name.trim());
- sys.setTotalInvestment(getDecimal(row, 3));
- sys.setStartCum(getDecimal(row, 4));
- sys.setYearPlan(getDecimal(row, 5));
- sys.setYearCum(getDecimal(row, 6));
- sys.setMonthDone(getDecimal(row, 7));
+ sys.setTotalInvestment(getDecimal(row, cTotal));
+ sys.setStartCum(getDecimal(row, cStartCum));
+ sys.setYearPlan(getDecimal(row, cYearPlan));
+ sys.setYearCum(cYearCum < 0 ? null : getDecimal(row, cYearCum));
+ sys.setMonthDone(getDecimal(row, cMonthDone));
list.add(sys);
}
} catch (Exception e) {
--
Gitblit v1.9.1