From 978526b474da9a495554ddc924bd736c52bb549b Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期二, 13 十月 2026 20:10:51 +0800
Subject: [PATCH] docs: 汇总大表验收改按四层口径（母版只作版式来源、不再作等价基线）
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 222 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 219 insertions(+), 3 deletions(-)
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