From 5fafb6c4451c0716921bcb1d248baa1613befb36 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期二, 13 十月 2026 20:10:49 +0800
Subject: [PATCH] feat(汇总大表): 生成件页签打开定位到本期数据列并重建累计/累计同比公式（待验收）
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java | 485 ++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 474 insertions(+), 11 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 5cf99d7..26268b0 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
@@ -7,6 +7,8 @@
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@@ -34,6 +36,8 @@
@Service
public class SummaryWorkbookFiller {
+ private static final Logger log = LoggerFactory.getLogger(SummaryWorkbookFiller.class);
+
@Resource
private MidCalc midCalc;
@Resource
@@ -45,6 +49,9 @@
/** 2026 骞� 1 鏈堝綋鏈堝�煎垪锛�0 鍩猴紝鍗� C 鍒楋級 */
private static final int FIRST_MONTH_COL = 2;
+
+ /** 琛ㄥご琛屾壂鎻忚寖鍥达紙鍏煎鈥滀竴椤典袱鍧椻�濈殑杞ㄩ亾銆佽疆娓★細绗� 2 鍧楄〃澶村湪绗� 11 琛岋級 */
+ private static final int HDR_SCAN_ROWS = 15;
/** 鍥炲～ 1..keepMonths 鏈堝彴璐﹂〉锛岃繑鍥炲啓鍏ユ牸鏁帮紱缂烘暟鎻愮ず鍐欏叆 problems */
public int fillMonthlyLedger(XSSFWorkbook wb, String period, int year, int keepMonths, List<String> problems) {
@@ -429,9 +436,11 @@
if (wb == null || month <= 1) return 0;
int targetCol = FIRST_MONTH_COL + 2 * (month - 1);
int written = 0;
+ int carried = 0;
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sh = wb.getSheetAt(i);
- if (sh == null || !isFilledLedgerSheet(sh.getSheetName())) continue;
+ if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
+ boolean derivedSheet = isDerivedFormulaSheet(sh.getSheetName());
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
Row row = sh.getRow(r);
if (row == null) continue;
@@ -446,7 +455,21 @@
break;
}
}
- if (srcCell == null) continue;
+ if (srcCell == null) {
+ if (!derivedSheet) continue;
+ // 娲剧敓椤碉紙鍩庡競瀹㈣繍/涓彛寰勬槑缁�/鍏矾鎬诲杩愶級閲屻�屽悇鏈堥兘鏄悓涓�涓父閲忋�嶇殑缁撴瀯鎬у父閲忚
+ // 锛堝涓彛寰勬槑缁� 姝︽眽 鍩庨檯鍩庝埂宸℃父鍑虹=0锛夛紝鎸夋湀娌跨敤璇ュ父閲忥紝閬垮厤褰撴湀鍊煎垪绌虹潃瀵艰嚧绱缂洪」
+ double struct = structuralConstant(row, month);
+ if (Double.isNaN(struct)) continue;
+ Cell target = row.getCell(targetCol);
+ if (target == null) target = row.createCell(targetCol);
+ Cell prev = row.getCell(FIRST_MONTH_COL + 2 * (month - 2));
+ if (prev != null && prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
+ target.setCellValue(struct);
+ carried++;
+ written++;
+ continue;
+ }
String f = srcCell.getCellFormula();
if (f == null || f.isEmpty()) continue;
Cell target = row.getCell(targetCol);
@@ -456,7 +479,31 @@
written++;
}
}
+ if (carried > 0 && log != null) {
+ log.info("姹囨�诲伐浣滅翱锛氭淳鐢熼〉缁撴瀯鎬у父閲忚鎸夋湀娌跨敤 {} 鏍硷紙绗� {} 鏈堬級", carried, month);
+ }
return written;
+ }
+
+ /** 璇ヨ 1..month-1 鏈堢殑褰撴湀鍊煎垪鏄惁鍏ㄤ负鍚屼竴涓暟鍊煎父閲忥紙缁撴瀯鎬у父閲忚锛屽鍚勬湀鎭掍负 0锛� */
+ private double structuralConstant(Row row, int month) {
+ if (row == null || month <= 2) return Double.NaN;
+ Double v = null;
+ for (int s = 1; s < month; s++) {
+ Cell c = row.getCell(FIRST_MONTH_COL + 2 * (s - 1));
+ if (c == null || c.getCellType() != org.apache.poi.ss.usermodel.CellType.NUMERIC) return Double.NaN;
+ double d = c.getNumericCellValue();
+ if (v == null) v = d;
+ else if (Math.abs(d - v) > 1e-9) return Double.NaN;
+ }
+ return v == null ? Double.NaN : v;
+ }
+
+ /** 娲剧敓椤碉紙褰撴湀鍊煎叏閮ㄦ潵鑷叾浠栭〉/甯搁噺锛岀函鍏紡椹卞姩锛夛細鍩庡競瀹㈣繍 / 涓彛寰勬槑缁� / 鍏矾鎬诲杩� / 涓彛寰勫杩愰噺 */
+ private boolean isDerivedFormulaSheet(String name) {
+ if (name == null) return false;
+ String n = name.trim();
+ return "鍩庡競瀹㈣繍".equals(n) || "涓彛寰勬槑缁�".equals(n) || "鍏矾鎬诲杩�".equals(n) || "涓彛寰勫杩愰噺".equals(n);
}
/** 璇ユ牸鏄惁宸叉湁鍐呭锛堟暟鍊�/鏂囨湰/鍏紡锛夛紱浠呭甫鏍峰紡鐨勭┖澹筹紙濡傛瘝鐗堥噷缁ф壙琛ㄥご鏃ユ湡鏍煎紡鐨勬牸瀛愶級瑙嗕负绌� */
@@ -467,14 +514,6 @@
case STRING: return c.getStringCellValue() != null && !c.getStringCellValue().trim().isEmpty();
default: return true;
}
- }
-
- /** 鍙備笌閫愭湀鏁版嵁鍥炲～鐨勫彴璐﹂〉锛堣揣杩�/鐝嚎鍖呰溅/鍏氦/鍑虹/缃戠害杞�/杞ㄩ亾杞浮锛� */
- private boolean isFilledLedgerSheet(String name) {
- if (name == null) return false;
- String n = name.trim();
- return "璐ц繍".equals(n) || "鐝嚎鍖呰溅".equals(n) || "鍏氦".equals(n)
- || "鍑虹".equals(n) || "缃戠害杞�".equals(n) || "杞ㄩ亾銆佽疆娓�".equals(n);
}
/** 0 鍩哄垪鍙� + 1 鍩鸿鍙� -> "Q5" */
@@ -529,7 +568,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());
@@ -579,6 +618,430 @@
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;
+ }
+
+ /**
+ * 銆岀疮璁� / 绱鍚屾瘮銆嶆寜琛ㄥご閲嶅缓锛�2026-10-13 鏂板锛屾牴娌烩�滅疮璁℃紡鏈堚�濓級銆�
+ *
+ * <p>鑳屾櫙锛氬綋鏈堝垪鐢扁�滄寜鏈堟墿鍒楀櫒鈥濇彃鍏ユ椂鍙湪鏃у叕寮忔湯灏捐拷鍔犲綋鏈堥」銆佷笉閲嶅缓锛涗竴鏃﹀巻鍙蹭笂婕忚繃鏌愭湀
+ * 锛堟墿鍒楀櫒鏃╂湡鐗堟湰鎶婂惈宸у悎鏃ユ湡鍊肩殑鏁版嵁琛岃鍒ゆ垚琛ㄥご琛屼粠鑰屾暣琛岃烦杩団�斺��2026-09 鐨�
+ * 鍏氦!U8銆佸嚭绉�!U6銆佷腑鍙ｅ緞鏄庣粏!U5 鍗虫锛夛紝婕忛」浼氭案涔呬繚鐣欏苟閫愭湀绱Н銆�
+ *
+ * <p>鏈柟娉曞湪鐢熸垚鏀跺熬鏃舵寜琛ㄥご閲嶅缓鍏紡锛屽簳绋块噷鐨勫巻鍙叉瑺璐﹁嚜鍔ㄨˉ榻愶紝涓斾笉鍐嶄緷璧栧簳绋垮叕寮忔湰韬纭細
+ * <ul>
+ * <li>銆岀疮璁°�嶏細鍏紡寮曠敤鍏ㄩ儴钀藉湪鏈湀搴﹀潡鐨勨�滃綋骞村綋鏈堝�煎垪鈥濅笂鏃讹紝閲嶅啓涓哄綋骞� 1..month 鍚勬湀鍊间箣鍜岋紱</li>
+ * <li>銆岀疮璁″悓姣斻�嶏細鍏紡寮曠敤鍙敤鍒版湰鏈堝害鍧楃殑鈥滃幓骞存湀浠藉垪 + 鏈绱鍒椻�濇椂锛屽垎姣嶉噸鍐欎负鍘诲勾 1..month 鍚勬湀鍊间箣鍜岋紱</li>
+ * <li>宸茬粡瀹屾暣鐨勫叕寮忛浂鏀瑰姩锛涜法椤靛紩鐢ㄧ瓑鍏跺畠褰㈡�佷笉鍔紝鍙鏁板憡璀︼紝閬垮厤璇激銆�</li>
+ * </ul>
+ * 澶氬潡椤碉紙杞ㄩ亾銆佽疆娓′竴椤典袱鍧楋級鎸夆�滆琛屼笂鏂规渶杩戣〃澶磋鈥濈‘瀹氭墍灞炴湀浠藉潡銆�
+ */
+ public int repairCumulativeFormulas(XSSFWorkbook wb, int year, int month) {
+ if (wb == null || month < 1) return 0;
+ int fixed = 0, skipped = 0;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ Sheet sh = wb.getSheetAt(i);
+ if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
+ List<Integer> hdrs = headerRowsOf(sh, year);
+ for (int hi = 0; hi < hdrs.size(); hi++) {
+ int h = hdrs.get(hi);
+ int blockEnd = (hi + 1 < hdrs.size()) ? hdrs.get(hi + 1) - 1 : sh.getLastRowNum();
+ Map<Integer, Integer> cur = new LinkedHashMap<>();
+ Map<Integer, Integer> lastY = new LinkedHashMap<>();
+ monthColsOfRow(sh, h, year, month, cur, lastY);
+ if (cur.isEmpty() || lastY.isEmpty()) continue;
+ int lastMonthCol = 0;
+ for (int c : cur.values()) lastMonthCol = Math.max(lastMonthCol, c);
+ Integer cumCol = cumulativeColOfRow(sh, h, lastMonthCol);
+ if (cumCol == null) continue;
+ Integer cumYoyCol = cumYoyColOfRow(sh, h, cumCol);
+ java.util.Set<Integer> curSet = new java.util.HashSet<>(cur.values());
+ java.util.Set<Integer> lastSet = new java.util.HashSet<>(lastY.values());
+ for (int r = h + 1; r <= blockEnd; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ Cell cum = row.getCell(cumCol);
+ if (cum != null && cum.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
+ java.util.Set<Integer> used = sameRowRefCols(cum.getCellFormula(), r + 1);
+ if (!used.isEmpty()) {
+ if (used.equals(curSet)) {
+ // 宸插畬鏁达紝闆舵敼鍔�
+ } else if (curSet.containsAll(used)) {
+ String nf = refsJoined(cur, r + 1);
+ if (nf != null && !nf.equals(cum.getCellFormula())) {
+ cum.setCellFormula(nf);
+ fixed++;
+ }
+ } else {
+ skipped++;
+ }
+ }
+ }
+ if (cumYoyCol != null) {
+ Cell cy = row.getCell(cumYoyCol);
+ if (cy != null && cy.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
+ java.util.Set<Integer> used = sameRowRefCols(cy.getCellFormula(), r + 1);
+ used.remove(cumCol);
+ if (!used.isEmpty()) {
+ if (used.equals(lastSet)) {
+ // 宸插畬鏁达紝闆舵敼鍔�
+ } else if (lastSet.containsAll(used)) {
+ String den = refsJoined(lastY, r + 1);
+ if (den != null) {
+ String nf = cellRef(cumCol, r + 1) + "/SUM(" + den + ")-1";
+ if (!nf.equals(cy.getCellFormula())) {
+ cy.setCellFormula(nf);
+ fixed++;
+ }
+ }
+ } else {
+ skipped++;
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ if (fixed > 0 && log != null) {
+ log.info("姹囨�诲伐浣滅翱锛氱疮璁�/绱鍚屾瘮鎸夎〃澶撮噸寤� {} 鏍硷紙绗� {} 鏈堬紱褰㈡�佷笉绗﹁烦杩� {} 鏍硷級", fixed, month, skipped);
+ } else if (skipped > 0 && log != null) {
+ log.warn("姹囨�诲伐浣滅翱锛歿} 鏍肩疮璁�/绱鍚屾瘮鍏紡褰㈡�佷笉绗︼紝鏈嚜鍔ㄩ噸寤猴紙闇�浜哄伐纭锛�", skipped);
+ }
+ return fixed;
+ }
+
+ /** 琛ㄥご琛岋紙0 鍩猴級锛氬厛鍙栤�滄湀浠芥牸鏈�澶氱殑閭ｄ竴琛屸�濅负鍩哄噯锛屽彧鏈夎繖浜涘垪涓婅繃鍗婃槸鏈堜唤鏍肩殑琛屾墠绠楄〃澶磋锛堝悓鎵╁垪鍣ㄥ彛寰勶級 */
+ private List<Integer> headerRowsOf(Sheet sh, int year) {
+ int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HDR_SCAN_ROWS);
+ List<Integer> base = new ArrayList<>();
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ List<Integer> cols = monthHeaderCols(sh.getRow(r), year);
+ if (cols.size() > base.size()) base = cols;
+ }
+ List<Integer> rows = new ArrayList<>();
+ if (base.size() < 2) return rows;
+ int need = Math.max(2, (base.size() + 1) / 2);
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ int hit = 0;
+ for (int c : base) if (yearMonthOf(row.getCell(c), year) != null) hit++;
+ if (hit >= need) rows.add(r);
+ }
+ return rows;
+ }
+
+ private List<Integer> monthHeaderCols(Row row, int year) {
+ List<Integer> cols = new ArrayList<>();
+ if (row == null) return cols;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ if (yearMonthOf(row.getCell(c), year) != null) cols.add(c);
+ }
+ return cols;
+ }
+
+ /** 琛ㄥご琛� -> 褰撳勾 1..month 鏈堝�煎垪 / 鍘诲勾 1..month 鏈堝�煎垪锛堝悓鏈堝彇鏈�宸︿竴鍒楋級 */
+ private void monthColsOfRow(Sheet sh, int hdrRow, int year, int month,
+ Map<Integer, Integer> cur, Map<Integer, Integer> lastY) {
+ Row row = sh.getRow(hdrRow);
+ if (row == null) return;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Integer ym = yearMonthOf(row.getCell(c), year);
+ if (ym == null) continue;
+ int y = ym / 100, m = ym % 100;
+ if (m < 1 || m > month) continue;
+ if (y == year && !cur.containsKey(m)) cur.put(m, c);
+ else if (y == year - 1 && !lastY.containsKey(m)) lastY.put(m, c);
+ }
+ }
+
+ /** 璇ヨ〃澶磋銆岀疮璁°�嶅垪锛�0 鍩猴級锛氭渶鍚庝竴涓湀浠藉垪鍙充晶 3 鍒楀唴鍚�滅疮璁♀�濅笖涓嶅惈鈥滃悓姣斺�濈殑琛ㄥご鏍� */
+ private Integer cumulativeColOfRow(Sheet sh, int hdrRow, int lastMonthCol) {
+ Row row = sh.getRow(hdrRow);
+ if (row == null) return null;
+ int end = Math.min(row.getLastCellNum() - 1, lastMonthCol + 3);
+ for (int c = lastMonthCol + 1; c <= end; c++) {
+ String t = text(row.getCell(c));
+ if (t == null) continue;
+ t = t.trim();
+ if (t.contains("绱") && !t.contains("鍚屾瘮") && !YM_RANGE_CUM.matcher(t).matches()) return c;
+ }
+ return null;
+ }
+
+ /** 銆岀疮璁°�嶅彸閭伙紙鎴栧啀鍙充竴鏍硷級鏄惁涓恒�岀疮璁″悓姣斻�� */
+ private Integer cumYoyColOfRow(Sheet sh, int hdrRow, int cumCol) {
+ Row row = sh.getRow(hdrRow);
+ if (row == null) return null;
+ String t = text(row.getCell(cumCol + 1));
+ if (t != null && t.trim().contains("鍚屾瘮")) return cumCol + 1;
+ String t2 = text(row.getCell(cumCol + 2));
+ return (t2 != null && t2.trim().contains("鍚屾瘮")) ? cumCol + 2 : null;
+ }
+
+ /** 鍏紡涓�滃紩鐢ㄥ悓涓�琛屸�濈殑鍒楀彿闆嗗悎锛�0 鍩猴級锛汼UM 绛夊嚱鏁板悕鍚庢棤鏁板瓧锛屼笉浼氳璇尮閰� */
+ private java.util.Set<Integer> sameRowRefCols(String formula, int row1) {
+ java.util.Set<Integer> cols = new java.util.HashSet<>();
+ if (formula == null) return cols;
+ java.util.regex.Matcher m = A1_REF.matcher(formula);
+ while (m.find()) {
+ if (Integer.parseInt(m.group(4)) == row1) cols.add(colIndexOf(m.group(2)));
+ }
+ return cols;
+ }
+
+ /** 鎶� {鏈�->鍒梷 鎸夋湀浠介『搴忔嫾鎴� "C5+E5+G5..." */
+ private String refsJoined(Map<Integer, Integer> monthToCol, int row1) {
+ StringBuilder sb = new StringBuilder();
+ for (Map.Entry<Integer, Integer> e : new java.util.TreeMap<>(monthToCol).entrySet()) {
+ if (sb.length() > 0) sb.append('+');
+ sb.append(cellRef(e.getValue(), row1));
+ }
+ return sb.length() == 0 ? null : sb.toString();
+ }
+
+ /** 璇ラ〉銆岀疮璁� / 绱鍚屾瘮銆嶄袱鍒楋紙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