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 | 183 +++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 183 insertions(+), 0 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 5fc7b11..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
@@ -50,6 +50,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) {
if (wb == null || keepMonths <= 0) return 0;
@@ -837,6 +840,186 @@
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);
--
Gitblit v1.9.1