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/service/ReportExportService.java | 222 +++++++++++++++++++++++++++++++
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java | 183 ++++++++++++++++++++++++++
2 files changed, 402 insertions(+), 3 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);
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java
index 358a446..1b9671a 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java
@@ -3767,6 +3767,8 @@
int monthFormulaFilled = summaryWorkbookFiller.ensureMonthFormulas(wb, year, effMonth);
// 绱鍚屾瘮鍒嗘瘝淇锛氭瘝鐗堟妸銆屽幓骞村悇鏈堛�嶄笌銆屽幓骞�1-M鏈堢疮璁°�嶈緟鍔╁垪鍚屾椂璁″叆锛堥噸澶嶈/婕忓綋鏈堬級
int cumYoyFixed = summaryWorkbookFiller.repairCumulativeYoyFormulas(wb, year, effMonth);
+ // 2026-10-13锛氱疮璁″垪 / 绱鍚屾瘮鍒嗘瘝鎸夎〃澶撮噸寤猴紙鏍规不鎵╁垪鍣ㄦ棭鏈熲�滅疮璁℃紡鏈堚�濈殑鍘嗗彶娆犺处锛�
+ int cumRebuilt = summaryWorkbookFiller.repairCumulativeFormulas(wb, year, effMonth);
reportProgress(progress, 45, "鍒锋柊鎺掑悕椤典笌璺ㄩ〉鍚堟垚椤�");
int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month");
int cumulativeRefreshed = refreshSummaryCumulativeSheets(wb, year, effMonth); // 鍥涘紶璺ㄩ〉鍚堟垚椤甸殢鎶ヨ〃鏈熷埛鏂�
@@ -3774,23 +3776,209 @@
// 閬垮厤姣嶇増涓庡弬鐓т欢涓ゅ缁存姢锛堟瘝鐗堝凡骞跺叆鍙傜収浠剁殑鍒楀/闅愯棌锛夈��
int widthApplied = 0;
log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ綋鏈堝�兼牸鏁�={}锛岃ˉ鍐欏綋鏈堝悓姣斿叕寮忔牸鏁�={}锛岃ˉ鍐欏綋鏈堝悎璁″叕寮忔牸鏁�={}锛�"
- + "淇绱鍚屾瘮鍒嗘瘝鏍兼暟={}锛屽埛鏂版帓鍚嶉〉鏁板�兼牸鏁�={}锛屽埛鏂板洓椤碉紙鍩庡競姹囨��/鍩庡競鍒嗗競宸�/涓彛寰勫垎鏋�/閬撹矾杩愯緭鍛ㄨ浆閲忥級鏁板�兼牸鏁�={}锛�"
+ + "淇绱鍚屾瘮鍒嗘瘝鏍兼暟={}锛岄噸寤虹疮璁�/绱鍚屾瘮鏍兼暟={}锛屽埛鏂版帓鍚嶉〉鏁板�兼牸鏁�={}锛�"
+ + "鍒锋柊鍥涢〉锛堝煄甯傛眹鎬�/鍩庡競鍒嗗競宸�/涓彛寰勫垎鏋�/閬撹矾杩愯緭鍛ㄨ浆閲忥級鏁板�兼牸鏁�={}锛�"
+ "鎸夈�婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪榻愬垪瀹藉垪鏁�={}锛岀己鏁版彁绀�={}",
- dbFilled, yoyFilled, monthFormulaFilled, cumYoyFixed, rankRefreshed, cumulativeRefreshed, widthApplied, dbProblems);
+ dbFilled, yoyFilled, monthFormulaFilled, cumYoyFixed, cumRebuilt, rankRefreshed, cumulativeRefreshed, widthApplied, dbProblems);
applyTwoDecimalFormat(wb); // 鏁板�兼樉绀轰袱浣嶅皬鏁帮紙淇濈暀鍏ㄧ簿搴︼級锛�% / 鏃ユ湡绛夋棦鏈夋牱寮忎笉鏀瑰彉
int inlineFixed = normalizeStaleInlineStrings(wb); // 娓呮帀銆岄潪 inlineStr 鍗存畫鐣� <is>銆嶇殑闈炴硶鏍硷紝鍚﹀垯 Excel 鎷掔粷鎵撳紑
if (inlineFixed > 0) log.warn("姹囨�诲伐浣滅翱娓呮帀娈嬬暀 inlineStr 闈炴硶鏍� {} 涓紙姣嶇増鐢� openpyxl 鍐欏嚭鏃剁殑鍏稿瀷闂锛�", inlineFixed);
reportProgress(progress, 70, "閲嶇畻鏁存湰鍏紡锛堢害 4 涓囨牸锛�");
int[] recalc = recalcWorkbookFormulas(wb); // 鍏紡閲嶇畻骞跺啓鍥炵紦瀛樺�硷細鍏ㄧ渷琛�(危甯傚窞)/绱鍒�/鎺掑悕 SUM 鎵嶈兘琚瑙堜笌浜屾澶勭悊璇诲埌
+ setSheetViewsToCurrentMonth(wb, year, effMonth); // 鎵撳紑鏃跺畾浣嶅埌鏈湡鏁版嵁鍒楋紙涓嶅奖鍝嶆暟鎹笌鏍煎紡锛�
log.info("姹囨�诲伐浣滅翱鍏紡閲嶇畻瀹屾垚锛氭垚鍔� {} 鏍硷紝澶辫触 {} 鏍硷紙澶辫触鏍间繚鐣欐瘝鐗堟棫缂撳瓨鍊硷紝鎵撳紑鏃舵寜 fullCalcOnLoad 閲嶇畻锛�", recalc[0], recalc[1]);
if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
appendMissingDataSheet(wb, effPeriod, dbProblems); // 2026-10-12锛氱己鏁�/浣撴缁撴灉鍐欒繘鐢熸垚浠讹紙鐢ㄦ埛瑕佹眰锛�
reportProgress(progress, 95, "鍐欏嚭鏂囦欢");
- return toBytes(wb);
+ return normalizeSheetViews(toBytes(wb), wb, year, effMonth);
} finally {
ZipSecureFile.setMinInflateRatio(savedZipRatio);
}
+ }
+
+ /**
+ * 鎵撳紑宸ヤ綔绨挎椂鐨勫畾浣嶏紙2026-10-13锛夛細鎶婃瘡涓〉绛剧殑婊氬姩/閫変腑浣嶇疆褰掍竴鍒般�屾湰鏈熸暟鎹垪銆嶏紝
+ * 骞舵竻鎺夋瘝鐗堬紙琚� Excel/WPS 瀛樿繃锛夋畫鐣欑殑鏃� topLeftCell/activeCell 鈥斺�� 鍚﹀垯姣忎釜椤电浼氬仠鍦�
+ * 涓婃缂栬緫鏃剁殑濂囨�綅缃紙瀹炴祴姣嶇増閲� 鍩庡競瀹㈣繍 BQ38銆佷腑鍙ｅ緞鏄庣粏 CC103銆佽揣杩愭帓鍚� A40鈥︼級銆�
+ * 鍐荤粨鎷嗗垎锛坸Split/ySplit/state锛夊師鏍蜂繚鐣欙紝鍙妸閿氱偣鎸埌鏈湡鏈堜唤鍒椼��
+ */
+ private byte[] normalizeSheetViews(byte[] xlsx, XSSFWorkbook wb, int year, int month) {
+ try {
+ java.util.Map<String, String> targets = sheetViewTargets(wb, year, month);
+ if (targets.isEmpty()) return xlsx;
+ java.util.Map<String, byte[]> entries = new java.util.LinkedHashMap<>();
+ try (java.util.zip.ZipInputStream zin = new java.util.zip.ZipInputStream(new java.io.ByteArrayInputStream(xlsx))) {
+ java.util.zip.ZipEntry e;
+ byte[] buf = new byte[8192];
+ while ((e = zin.getNextEntry()) != null) {
+ java.io.ByteArrayOutputStream b = new java.io.ByteArrayOutputStream();
+ int n;
+ while ((n = zin.read(buf)) > 0) b.write(buf, 0, n);
+ entries.put(e.getName(), b.toByteArray());
+ }
+ }
+ byte[] wbx = entries.get("xl/workbook.xml");
+ byte[] rlx = entries.get("xl/_rels/workbook.xml.rels");
+ if (wbx == null || rlx == null) return xlsx;
+ String wbs = new String(wbx, java.nio.charset.StandardCharsets.UTF_8);
+ String rls = new String(rlx, java.nio.charset.StandardCharsets.UTF_8);
+ java.util.Map<String, String> rid2t = new java.util.HashMap<>();
+ java.util.regex.Matcher rm = java.util.regex.Pattern.compile("Id=\"([^\"]+)\"[^>]*Target=\"([^\"]+)\"").matcher(rls);
+ while (rm.find()) rid2t.put(rm.group(1), rm.group(2));
+ java.util.Map<String, String> name2part = new java.util.LinkedHashMap<>();
+ java.util.regex.Matcher sm = java.util.regex.Pattern.compile("<sheet[^>]*name=\"([^\"]+)\"[^>]*r:id=\"([^\"]+)\"").matcher(wbs);
+ while (sm.find()) {
+ String t = rid2t.get(sm.group(2));
+ if (t == null) continue;
+ if (t.startsWith("/")) t = t.substring(1);
+ else if (!t.startsWith("xl/")) t = "xl/" + t;
+ name2part.put(sm.group(1), t);
+ }
+ int patched = 0;
+ for (java.util.Map.Entry<String, String> en : name2part.entrySet()) {
+ String target = targets.get(en.getKey());
+ byte[] part = entries.get(en.getValue());
+ if (target == null || part == null) continue;
+ String xml = new String(part, java.nio.charset.StandardCharsets.UTF_8);
+ String np = patchSheetView(xml, target);
+ if (!np.equals(xml)) { entries.put(en.getValue(), np.getBytes(java.nio.charset.StandardCharsets.UTF_8)); patched++; }
+ }
+ try (java.io.ByteArrayOutputStream bos = new java.io.ByteArrayOutputStream(xlsx.length);
+ java.util.zip.ZipOutputStream zout = new java.util.zip.ZipOutputStream(bos)) {
+ for (java.util.Map.Entry<String, byte[]> en : entries.entrySet()) {
+ zout.putNextEntry(new java.util.zip.ZipEntry(en.getKey()));
+ zout.write(en.getValue());
+ zout.closeEntry();
+ }
+ zout.finish();
+ log.info("姹囨�诲伐浣滅翱锛氶〉绛炬墦寮�浣嶇疆宸插綊涓�鍒版湰鏈燂紙{}骞磠}鏈堬級锛屽鐞� {} 涓〉绛�", year, month, patched);
+ return bos.toByteArray();
+ }
+ } catch (Exception ex) {
+ log.warn("姹囨�诲伐浣滅翱锛氶〉绛炬墦寮�浣嶇疆褰掍竴澶辫触锛堝拷鐣ワ紝淇濈暀鍘熸牱锛夛細{}", ex.toString());
+ return xlsx;
+ }
+ }
+
+ /** 姣忛〉鐨勫畾浣嶇洰鏍囷細鏈夋湰鏈熸湀浠藉垪 鈫� 璇ユ湀琛ㄥご鏍硷紱娌℃湁锛堟帓鍚�/鍚堟垚椤碉級鈫� A1锛堥『甯︽竻鎺夋畫鐣欎綅缃級 */
+ private java.util.Map<String, String> sheetViewTargets(XSSFWorkbook wb, int year, int month) {
+ java.util.Map<String, String> m = new java.util.LinkedHashMap<>();
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ XSSFSheet sh = wb.getSheetAt(i);
+ if (sh == null) continue;
+ int headerRow = -1, col = -1;
+ int scan = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
+ for (int r = sh.getFirstRowNum(); r <= scan && col < 0; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Integer ym = smYearMonthOf(row.getCell(c));
+ if (ym != null && ym / 100 == year && ym % 100 == month) { headerRow = r; col = c; break; }
+ }
+ }
+ m.put(sh.getSheetName(), col >= 0 ? a1Ref(headerRow, col) : "A1");
+ }
+ return m;
+ }
+
+ /** 0 鍩鸿鍒� -> "S3" */
+ private String a1Ref(int row0, int col0) {
+ StringBuilder sb = new StringBuilder();
+ int n = col0 + 1;
+ while (n > 0) { int rem = (n - 1) % 26; sb.insert(0, (char) ('A' + rem)); n = (n - 1) / 26; }
+ return sb.toString() + (row0 + 1);
+ }
+
+ /** 鍘绘帀灞炴�� */
+ private String stripAttr(String attrs, String name) {
+ int i = attrs.indexOf(name + "=\"");
+ if (i < 0) return attrs;
+ int e = attrs.indexOf('"', i + name.length() + 2);
+ if (e < 0) return attrs;
+ return attrs.substring(0, i) + attrs.substring(e + 1);
+ }
+
+ /** 鎵炬爣绛捐捣鐐癸細瑕佹眰鏍囩鍚嶅悗闈㈢揣璺� 绌烘牸/>锛岄伩鍏� "<sheetView" 鍛戒腑 "<sheetViews" */
+ private int findTag(String xml, String tag) {
+ int from = 0;
+ while (true) {
+ int i = xml.indexOf(tag, from);
+ if (i < 0) return -1;
+ int nx = i + tag.length();
+ char ch = nx < xml.length() ? xml.charAt(nx) : '>';
+ if (ch == ' ' || ch == '>' || ch == '/') return i;
+ from = i + 1;
+ }
+ }
+
+ /** 鍙栧睘鎬у�硷紙涓嶅瓨鍦ㄨ繑鍥� null锛� */
+ private String attrValue(String attrs, String name) {
+ int i = attrs.indexOf(name + "=\"");
+ if (i < 0) return null;
+ int e = attrs.indexOf('"', i + name.length() + 2);
+ return e < 0 ? null : attrs.substring(i + name.length() + 2, e);
+ }
+
+ /** 杩藉姞灞炴�э紙宸插瓨鍦ㄥ垯涓嶉噸澶嶅姞锛� */
+ private String addAttr(String attrs, String name, String v) {
+ if (attrValue(attrs, name) != null) return attrs;
+ String sep = (attrs.isEmpty() || attrs.endsWith(" ")) ? "" : " ";
+ return attrs + sep + name + "=\"" + v + "\"";
+ }
+
+ /**
+ * 鍗曢〉 XML 褰掍竴锛歴heetView / pane 鐨� topLeftCell銆佹椿鍔� pane 鐨� selection 鍏ㄩ儴鎸囧悜 target銆�
+ * 鍐荤粨鎷嗗垎锛坸Split/ySplit/state锛夊師鏍蜂繚鐣欙紱鐮村潖缁撴瀯鍒欒繑鍥炲師鏂囷紙failed-safe锛夈��
+ */
+ private String patchSheetView(String xml, String target) {
+ String orig = xml;
+ int i = findTag(xml, "<sheetView");
+ if (i >= 0) {
+ int j = xml.indexOf('>', i);
+ if (j > 0) {
+ String attrs = addAttr(stripAttr(xml.substring(i + 10, j), "topLeftCell"), "topLeftCell", target);
+ xml = xml.substring(0, i) + "<sheetView" + attrs + ">" + xml.substring(j + 1);
+ }
+ }
+ String activePane = null;
+ int p = findTag(xml, "<pane");
+ if (p >= 0) {
+ int q = xml.indexOf("/>", p);
+ if (q > 0) {
+ String raw = xml.substring(p + 5, q);
+ activePane = attrValue(raw, "activePane");
+ String attrs = addAttr(stripAttr(raw, "topLeftCell"), "topLeftCell", target);
+ xml = xml.substring(0, p) + "<pane" + attrs + "/>" + xml.substring(q + 2);
+ }
+ }
+ StringBuilder sb = new StringBuilder();
+ int last = 0, pos = 0;
+ boolean done = false, any = false;
+ while (true) {
+ int s0 = xml.indexOf("<selection", pos);
+ if (s0 < 0) break;
+ int s1 = xml.indexOf("/>", s0);
+ if (s1 < 0) break;
+ String raw = xml.substring(s0 + 10, s1);
+ String attrs = stripAttr(stripAttr(raw, "activeCell"), "sqref");
+ String pane = attrValue(raw, "pane");
+ boolean hit = (activePane != null) ? (pane != null && pane.equals(activePane)) : !done;
+ if (hit) { attrs = addAttr(addAttr(attrs, "activeCell", target), "sqref", target); done = true; }
+ sb.append(xml, last, s0).append("<selection").append(attrs).append("/>");
+ last = s1 + 2;
+ pos = s1 + 2;
+ any = true;
+ }
+ if (!any) {
+ int close = xml.indexOf("</sheetView>");
+ if (close > 0) xml = xml.substring(0, close) + "<selection activeCell=\"" + target + "\" sqref=\"" + target + "\"/>" + xml.substring(close);
+ } else {
+ sb.append(xml, last, xml.length());
+ xml = sb.toString();
+ }
+ if (!xml.contains("<sheetViews>") || !xml.contains("</sheetViews>")) return orig;
+ return xml;
}
/**
@@ -4563,6 +4751,34 @@
return cols;
}
+ /**
+ * 鎵撳紑宸ヤ綔绨挎椂鎶婃瘡涓〉绛惧畾浣嶅埌銆屾湰鏈熸暟鎹�嶄綅缃紙2026-10-13 鐢ㄦ埛瑕佹眰锛夈��
+ * 鍋氭硶锛氬嚒鑳芥寜琛ㄥご璇嗗埆鍑恒�屽綋骞村綋鏈堝�煎垪銆嶇殑椤碉紝鎶婃椿鍔ㄥ崟鍏冩牸璁惧埌璇ユ湀琛ㄥご鏍硷紱鍏朵綑椤典繚鎸侀粯璁ゃ��
+ * 鍙奖鍝嶆墦寮�鏃剁殑婊氬姩/閫変腑浣嶇疆锛屼笉鏀逛换浣曟暟鎹笌鏍煎紡銆�
+ */
+ private int setSheetViewsToCurrentMonth(XSSFWorkbook wb, int year, int month) {
+ int n = 0;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ XSSFSheet sh = wb.getSheetAt(i);
+ if (sh == null) continue;
+ int headerRow = -1, col = -1;
+ int scan = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
+ for (int r = sh.getFirstRowNum(); r <= scan && col < 0; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ Integer ym = smYearMonthOf(row.getCell(c));
+ if (ym != null && ym / 100 == year && ym % 100 == month) { headerRow = r; col = c; break; }
+ }
+ }
+ if (col < 0) continue;
+ sh.setActiveCell(new org.apache.poi.ss.util.CellAddress(headerRow, col));
+ n++;
+ }
+ if (n > 0) log.info("姹囨�诲伐浣滅翱锛歿} 涓〉绛惧凡瀹氫綅鍒版湰鏈燂紙{}骞磠}鏈堬級鏁版嵁鍒�", n, year, month);
+ return n;
+ }
+
/** 琛ㄥご鍗曞厓鏍� -> 骞�*100+鏈堬紱鍙銆孻YYY骞碝鏈堛�嶆枃鏈垨銆屾煇鏈� 1 鍙枫�嶇殑鏃ユ湡搴忓垪鍙凤紝鎺掗櫎绱/鍚屾瘮/鐜瘮 */
private Integer smYearMonthOf(Cell c) {
if (c == null) return null;
--
Gitblit v1.9.1