From a08c70db67e49bf6cd125df04ebaf68c26f0d8dd Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期日, 20 九月 2026 16:19:16 +0800
Subject: [PATCH] feat(投资): 市州月报统一填报模板 + 县(市、区)列解析 + 表头行判定收紧
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java | 246 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 245 insertions(+), 1 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java
index f40a596..5fc7b11 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java
@@ -565,7 +565,7 @@
if (target != null && target.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
String f = prev.getCellFormula();
if (f == null || f.isEmpty()) continue;
- String shifted = shiftFormulaColumns(f, 2);
+ String shifted = shiftFormulaToNextMonth(sh, f, year, month);
if (target == null) target = row.createCell(yoyCol);
// 姣嶇増鎶婃柊鏈堜唤鐨勫悓姣斿垪鍋氭垚浜嗘櫘閫氭暟鍊兼牸寮忥紙鏄剧ず鎴愬皬鏁帮級锛岀粺涓�娌跨敤涓婃湀鍚屾瘮鍒楃殑鏍峰紡锛堢櫨鍒嗘暟锛�
if (prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
@@ -615,6 +615,250 @@
return sb.toString();
}
+ /** 鍏紡鍐� A1 鍒楀紩鐢ㄥ尮閰嶏紙SUM 绛夊嚱鏁板悕鍚庢棤鏁板瓧锛屼笉浼氳鍖归厤锛� */
+ private static final java.util.regex.Pattern A1_REF =
+ java.util.regex.Pattern.compile("(?<![A-Za-z0-9_$])([$]?)([A-Z]{1,3})([$]?)([0-9]+)");
+ /** 鈥�2025骞�8鏈堚�� 杩欑鍗曟湀琛ㄥご */
+ private static final java.util.regex.Pattern YM_HEADER =
+ java.util.regex.Pattern.compile("^(\\d{4})骞�(\\d{1,2})鏈�$");
+ /** 鈥�2025骞�1-7鏈堢疮璁♀�� 杩欑鍖洪棿绱杈呭姪鍒楋紙涓嶅睘浜庝换浣曞崟鏈堬級 */
+ private static final java.util.regex.Pattern YM_RANGE_CUM =
+ java.util.regex.Pattern.compile("^\\d{4}骞碶\d{1,2}-\\d{1,2}鏈堢疮璁�$");
+
+ /** A1 鍒楀瓧姣� -> 0 鍩哄垪鍙凤紙涓� POI 鐨� Cell.getColumnIndex() 瀵归綈锛孉=0銆丄J=35锛� */
+ private int colIndexOf(String letters) {
+ int idx = 0;
+ for (int i = 0; i < letters.length(); i++) idx = idx * 26 + (letters.charAt(i) - 'A' + 1);
+ return idx - 1;
+ }
+
+ /** 0 鍩哄垪鍙� -> "Q" */
+ private String colLettersOf(int idx0) {
+ StringBuilder sb = new StringBuilder();
+ int n = idx0 + 1;
+ while (n > 0) {
+ int rem = (n - 1) % 26;
+ sb.insert(0, (char) ('A' + rem));
+ n = (n - 1) / 26;
+ }
+ return sb.toString();
+ }
+
+ /** 琛ㄥご锛堝墠 5 琛岋級寤虹珛 (骞�*100+鏈�) -> 鍒楀彿锛�0 鍩猴級锛氳瘑鍒� 鈥�2025骞�8鏈堚�� 鏂囨湰鏍间笌 2025-08-01 杩欑被鏃ユ湡鏍硷紱
+ * 鍚屼竴 (骞�,鏈�) 鍙栨渶宸︿竴鍒楋紱鈥�2025骞�1-7鏈堢疮璁♀�� 杩欑被鍖洪棿绱杈呭姪鍒椾笉灞炰簬浠讳綍鍗曟湀锛屼笉鍙備笌鏄犲皠 */
+ private Map<Integer, Integer> yearMonthCols(Sheet sh, int year) {
+ Map<Integer, Integer> m = new LinkedHashMap<>();
+ if (sh == null) return m;
+ int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Integer ym = yearMonthOf(row.getCell(c), year);
+ if (ym != null && !m.containsKey(ym)) m.put(ym, c);
+ }
+ }
+ return m;
+ }
+
+ /**
+ * 鍗曚釜鏈堜唤琛ㄥご -> 骞�*100+鏈堬紱鍙浠婂勾/鍘诲勾锛屽叾浣欎竴寰嬩笉璁わ紙閬垮厤鎶� 2024/2023 瀵规瘮鍧椼�佹潅鏍煎綋鏈堜唤鍒楋級銆�
+ * 涓ょ鍐欐硶锛氭枃鏈� 鈥�2026骞�8鏈堚�濓紱鏁板�兼棩鏈熷簭鍒楋紙姣嶇増鍩庡競瀹㈣繍椤� 3..8 鏈堢殑琛ㄥご鏄� 46235 杩欑瑁稿簭鍒椼��
+ * 鏁板瓧鏍煎紡杩樻槸 General锛屽彧鎸夆�滃綋鏈� 1 鍙封�濆垽鏂紝涓嶈兘渚濊禆 isCellDateFormatted锛夈��
+ */
+ private Integer yearMonthOf(Cell cell, int year) {
+ if (cell == null) return null;
+ org.apache.poi.ss.usermodel.CellType t = cell.getCellType();
+ if (t == org.apache.poi.ss.usermodel.CellType.STRING) {
+ String v = cell.getStringCellValue();
+ if (v == null || v.isEmpty()) return null;
+ v = v.trim();
+ if (v.contains("绱") || v.contains("鍚屾瘮")) return null;
+ java.util.regex.Matcher m = YM_HEADER.matcher(v);
+ if (!m.matches()) return null;
+ int y = Integer.parseInt(m.group(1));
+ if (y != year && y != year - 1) return null;
+ return y * 100 + Integer.parseInt(m.group(2));
+ }
+ if (t == org.apache.poi.ss.usermodel.CellType.NUMERIC) {
+ double v = cell.getNumericCellValue();
+ if (v < 1 || v > 200000) return null;
+ try {
+ java.util.Calendar cal = java.util.Calendar.getInstance();
+ cal.setTime(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v));
+ if (cal.get(java.util.Calendar.DAY_OF_MONTH) != 1) return null;
+ int y = cal.get(java.util.Calendar.YEAR);
+ if (y != year && y != year - 1) return null;
+ return y * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
+ } catch (Exception ignore) {
+ return null;
+ }
+ }
+ return null;
+ }
+
+ /** 琛ㄥご閲� 鈥�20XX骞碼-b鏈堢疮璁♀�� 杩欑被鍖洪棿绱杈呭姪鍒楃殑 0 鍩哄垪鍙烽泦鍚� */
+ private java.util.Set<Integer> cumulativeHelperCols(Sheet sh) {
+ java.util.Set<Integer> set = new java.util.HashSet<>();
+ if (sh == null) return set;
+ int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
+ String v = cell.getStringCellValue();
+ if (v != null && YM_RANGE_CUM.matcher(v.trim()).matches()) set.add(c);
+ }
+ }
+ return set;
+ }
+
+ /** 鏈堜唤鏄犲皠鐨勫彲鐢ㄦ�у畧鍗細鍘诲勾涓庝粖骞� 1..month 鍚勬湀鍒楀繀椤婚綈鍏ㄤ笖鍒楀彿涓ユ牸閫掑锛�
+ * 浠讳竴涓嶆弧瓒筹紙琛ㄥご娌″啓鏈堜唤銆佹垨鎶撳埌鏉傛牸锛夊氨杩斿洖绌鸿〃锛岃皟鐢ㄦ柟閫�鍥炪�屾暣浣撳彸绉� 2 鍒椼�嶇殑鏃ц涓� */
+ private Map<Integer, Integer> usableMonthCols(Sheet sh, int year, int month) {
+ Map<Integer, Integer> usable = new LinkedHashMap<>();
+ Map<Integer, Integer> all = yearMonthCols(sh, year);
+ int prevLy = -1, prevCy = -1;
+ for (int m = 1; m <= month; m++) {
+ Integer ly = all.get((year - 1) * 100 + m);
+ Integer cy = all.get(year * 100 + m);
+ if (ly == null || cy == null || ly <= prevLy || cy <= prevCy) return new LinkedHashMap<>();
+ prevLy = ly;
+ prevCy = cy;
+ usable.put((year - 1) * 100 + m, ly);
+ usable.put(year * 100 + m, cy);
+ }
+ return usable;
+ }
+
+ /**
+ * 銆屼笂鏈堝悓姣斻�嶅叕寮� 鈫� 銆屾湰鏈堝悓姣斻�嶅叕寮忥細鍏紡閲屽紩鐢ㄥ埌鏈堜唤鍒楃殑寮曠敤鎸夎〃澶� (骞�,鏈�) 椤哄欢涓�涓湀锛�
+ * 鍏朵綑寮曠敤閫�鍥炪�屾暣浣撳彸绉� 2 鍒椼�嶃��
+ *
+ * 涓轰粈涔堜笉鑳戒竴寰嬪彸绉� 2 鍒楋細姣嶇増銆婂煄甯傚杩愩�嬮〉鍦� 2025 骞存鎻掍簡涓�鍒椼��2025骞�1-7鏈堢疮璁°�嶈緟鍔╁垪锛�
+ * 2025 鍚勬湀鍒椾粠 8 鏈堣捣涓嶅啀绛夎窛锛�2025骞�8鏈堝湪 AK銆佷笉鍦� AJ锛夛紝鍙崇Щ 2 鍒椾細钀藉埌杈呭姪鍒椾笂锛�
+ * 浜庢槸 8 鏈堝悓姣旇绠楁垚銆�8鏈堝�� 梅 2025骞�1-7鏈堢疮璁� 鈭� 1銆嶏紙2026-09-20 鐢ㄦ埛涓婃姤鐨勯棶棰橈級銆�
+ */
+ private String shiftFormulaToNextMonth(Sheet sh, String formula, int year, int month) {
+ if (formula == null || formula.isEmpty()) return formula;
+ Map<Integer, Integer> cols = usableMonthCols(sh, year, month);
+ if (cols.isEmpty()) return shiftFormulaColumns(formula, 2);
+ Map<Integer, Integer> colToYm = new LinkedHashMap<>();
+ for (Map.Entry<Integer, Integer> e : cols.entrySet()) colToYm.put(e.getValue(), e.getKey());
+ java.util.regex.Matcher m = A1_REF.matcher(formula);
+ StringBuffer sb = new StringBuffer();
+ while (m.find()) {
+ int idx = colIndexOf(m.group(2));
+ Integer ym = colToYm.get(idx);
+ Integer next = null;
+ if (ym != null) {
+ next = cols.get((ym / 100) * 100 + (ym % 100) + 1);
+ }
+ m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(
+ m.group(1) + colLettersOf(next != null ? next : idx + 2) + m.group(3) + m.group(4)));
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
+ /**
+ * 淇銆岀疮璁″悓姣斻�嶅垎姣嶃�傛瘝鐗堛�婂煄甯傚杩愩�嬮〉鎶� 2025 骞� 1..M 鏈堝悇鏈堝垪**鍜�**瀹冧滑鐨�
+ * 銆�2025骞�1-M鏈堢疮璁°�嶈緟鍔╁垪涓�璧峰啓杩涘垎姣嶏紙1..M-1 鏈堥噸澶嶈涓�娆°�佸綋鏈堟紡璁★級锛�
+ * 鍙︽湁鍦ㄦ瘝鐗堥噷鍒犳帀杈呭姪鍒楀悗娈嬬暀 #REF! 鐨勬儏褰紱涓ょ閮戒細璁╃疮璁″悓姣斾弗閲嶅け鐪�
+ * 锛�2026-09-20 鐢ㄦ埛涓婃姤锛氬煄甯傚杩� 8 鏈堝悓姣� 鈭�86.5%銆佺疮璁″悓姣� 鈭�43.5%锛夈��
+ * 缁熶竴鎸夎〃澶存妸鍒嗘瘝閲嶅啓涓恒�屽幓骞� 1..褰撴湀 鍚勬湀鍊煎垪涔嬪拰銆嶃��
+ *
+ * 2026-09-20 宸插湪銆�2026骞�8鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx銆嬫瘝鐗堥噷鍒犳帀璇ヨ緟鍔╁垪锛堝浠借
+ * docs/鐢熸垚姹囨�诲ぇ琛�/_bak_202608姣嶇増鍒犻櫎AJ鍒楀墠_20260920.xlsx锛夛紝姝ｅ父鎯呭喌鏈柟娉曞懡涓� 0 鏍硷紱
+ * 淇濈暀瀹冩槸涓轰簡鍏滀綇銆屽悓鏈熷浠芥瘝鐗� / 鍘嗗彶閮ㄧ讲鍖� / 涔嬪悗鍙堟彃浜嗚緟鍔╁垪鐨勬瘝鐗堛�嶈繖绫绘儏鍐点��
+ */
+ public int repairCumulativeYoyFormulas(XSSFWorkbook wb, int year, int month) {
+ if (wb == null || month < 1) return 0;
+ int fixed = 0;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ Sheet sh = wb.getSheetAt(i);
+ if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
+ int[] cum = cumulativeCols(sh, year);
+ if (cum == null) continue;
+ java.util.Set<Integer> helperCols = cumulativeHelperCols(sh);
+ Map<Integer, Integer> all = yearMonthCols(sh, year);
+ List<Integer> lastYearValueCols = new ArrayList<>();
+ int prev = -1;
+ for (int m = 1; m <= month; m++) {
+ Integer c = all.get((year - 1) * 100 + m);
+ if (c == null || c <= prev) {
+ lastYearValueCols.clear();
+ break;
+ }
+ prev = c;
+ lastYearValueCols.add(c);
+ }
+ if (lastYearValueCols.isEmpty()) continue;
+ for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ Cell cc = row.getCell(cum[1]);
+ if (cc == null || cc.getCellType() != org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
+ String f = cc.getCellFormula();
+ if (f == null || !cumulativeYoyBroken(f, helperCols)) continue;
+ // 娉ㄦ剰锛歅OI 鐨� setCellFormula 涓嶆帴鍙椾互 "=" 寮�澶寸殑鍏紡涓诧紙浼氭姏 FormulaParseException锛�
+ StringBuilder sb = new StringBuilder();
+ sb.append(cellRef(cum[0], r + 1)).append("/(");
+ for (int k = 0; k < lastYearValueCols.size(); k++) {
+ if (k > 0) sb.append('+');
+ sb.append(cellRef(lastYearValueCols.get(k), r + 1));
+ }
+ sb.append(")-1");
+ try {
+ cc.setCellFormula(sb.toString());
+ fixed++;
+ } catch (Exception ex) {
+ // 涓埆鍏紡鏀瑰啓澶辫触涓嶉樆濉炲鍑猴紙鎵撳紑鏃朵粛鎸夊師鍏紡閲嶇畻锛�
+ if (log != null) log.warn("绱鍚屾瘮鍏紡鏀瑰啓澶辫触锛歴heet={} cell={} 鍘熷紡={} 鏂板紡={}锛坽}锛�",
+ sh.getSheetName(), cellRef(cum[1], r + 1), f, sb, ex.toString());
+ }
+ }
+ }
+ if (fixed > 0 && log != null) {
+ log.info("姹囨�诲伐浣滅翱锛氱疮璁″悓姣斿垎姣嶄慨姝� {} 鏍硷紙绗� {} 鏈堬紝鍒嗘瘝鍙栧幓骞� 1..{} 鏈堝悇鏈堝�煎垪锛�", fixed, month, month);
+ }
+ return fixed;
+ }
+
+ /** 绱鍚屾瘮鍏紡鏄惁鈥滃潖浜嗏�濓細鍚� #REF!锛屾垨鍒嗘瘝寮曠敤浜嗗尯闂寸疮璁¤緟鍔╁垪锛堜笌鍚勬湀鍒楅噸澶嶈鍏ワ級 */
+ private boolean cumulativeYoyBroken(String formula, java.util.Set<Integer> helperCols) {
+ if (formula.contains("#REF!")) return true;
+ java.util.regex.Matcher m = A1_REF.matcher(formula);
+ while (m.find()) {
+ if (helperCols.contains(colIndexOf(m.group(2)))) return true;
+ }
+ return false;
+ }
+
+ /** 璇ラ〉銆岀疮璁� / 绱鍚屾瘮銆嶄袱鍒楋紙0 鍩猴級锛屾寜琛ㄥご鍓� 5 琛屾壘 鈥滅疮璁♀�� 鎴� 鈥渰year}骞寸疮璁♀�濓紝涓斿叾鍙冲垪鍚� 鈥滃悓姣斺�� */
+ private int[] cumulativeCols(Sheet sh, int year) {
+ int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
+ String v = cell.getStringCellValue();
+ if (v == null) continue;
+ v = v.trim();
+ if (!"绱".equals(v) && !(year + "骞寸疮璁�").equals(v)) continue;
+ Cell nxt = row.getCell(c + 1);
+ if (nxt == null || nxt.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
+ String nt = nxt.getStringCellValue();
+ if (nt != null && nt.contains("鍚屾瘮")) return new int[]{c, c + 1};
+ }
+ }
+ return null;
+ }
+
/** 鏈堜唤瀹归噺锛氫紭鍏堝彇琛ㄥご锛堝墠 5 琛岋級鈥�2026鈥︾疮璁♀�濆垪鍙嶆帹锛涜揣杩愰〉鏃犺鏂囨锛�
* 鍐嶇敤琛ㄥご閲屾渶鍚庝竴涓��2026骞碝鏈堚��/鏃ユ湡鏍煎紡鐨勬湀鍒楀厹搴曪紙濡傝揣杩� Q3=2026-08-01 -> 8锛� */
private int monthColumnCapacity(Sheet sh) {
--
Gitblit v1.9.1