From 117ecd77287794026954a5147f196bda81ddbf3b Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期五, 04 九月 2026 14:07:22 +0800
Subject: [PATCH] feat: 汇总工作簿月度块按备份母版还原（1-4月骨架→1-7月）试点 + fullCalcOnLoad + 09-04 测试报告
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 91 ++++++++++++++++++++++++++++++
docs/功能测试报告/功能测试报告_汇总工作簿月度块扩列还原试点_2026-09-04.md | 30 ++++++++++
2 files changed, 121 insertions(+), 0 deletions(-)
diff --git "a/docs/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212_\346\261\207\346\200\273\345\267\245\344\275\234\347\260\277\346\234\210\345\272\246\345\235\227\346\211\251\345\210\227\350\277\230\345\216\237\350\257\225\347\202\271_2026-09-04.md" "b/docs/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212_\346\261\207\346\200\273\345\267\245\344\275\234\347\260\277\346\234\210\345\272\246\345\235\227\346\211\251\345\210\227\350\277\230\345\216\237\350\257\225\347\202\271_2026-09-04.md"
new file mode 100644
index 0000000..acaadd9
--- /dev/null
+++ "b/docs/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212/\345\212\237\350\203\275\346\265\213\350\257\225\346\212\245\345\221\212_\346\261\207\346\200\273\345\267\245\344\275\234\347\260\277\346\234\210\345\272\246\345\235\227\346\211\251\345\210\227\350\277\230\345\216\237\350\257\225\347\202\271_2026-09-04.md"
@@ -0,0 +1,30 @@
+# 鍔熻兘娴嬭瘯鎶ュ憡锛氭眹鎬诲伐浣滅翱鏈堝害鍧楁墿鍒�/杩樺師璇曠偣锛�2026-07锛夛紙2026-09-04锛�
+
+## 鍔熻兘璇存槑
+鐢ㄦ埛鎷嶆澘锛氫互銆�2026骞�7鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx銆嬶紙宸叉竻鐞嗕负鈥�1-4 鏈堥鏋垛�濓級涓烘瘝鐗堬紝鐢辩▼搴忔妸 5-7 鏈堟湀搴﹀潡鎸夊悓鏈熷浠芥瘝鐗堣繕鍘燂紝瀹炵幇鈥滈�愭湀鎵╁垪鐢熸垚 1-N 鏈堟眹鎬昏〃鈥濈殑绗竴姝ヨ瘯鐐癸紙M1锛岀洰鏍囨湡 2026-07锛夈�傚彛寰勶細1-4 鏈堜繚鐣欐瘝鐗堟暟鎹紱绱鍏紡鎭㈠涓� 1-7 鏈堬紱鍚屾瘮 R 鍒嗘瘝鐢� 2025 骞� 1-7 鏈堝垪锛�2025/2024 鍙傝�冨垪淇濇寔涓嶅姩锛涜緭鍑哄彟瀛樻柊鏂囦欢锛屼笉鏀规瘝鐗堛��
+
+浠ｇ爜鏀瑰姩锛�
+- traffic-audit-server/.../reportexport/service/ReportExportService.java
+ - exportSummaryWorkbook 澧炲姞锛氬畾浣嶅悓鏈熷浠芥瘝鐗堬紙_澶囦唤_*_娓呯悊鍓峗*.xlsx锛屾寜 {骞磢骞磠鏈坿鏈� 鍖归厤锛屾悳绱㈤厤缃洰褰�/user.dir/涓婄骇鐩綍涓変釜鍊欓�夋牴锛夆啋 閫愭牸鎶娾�滃浠芥湁鍐呭鑰岄鏋剁己澶辨垨鍐呭涓嶅悓鈥濈殑鍗曞厓鏍煎洖濉紙鍚� 5-7 鏈堣〃澶淬�佹暟鎹�佸叕寮忎笌绱/鍚屾瘮鍏紡锛夛紱
+ - 鏂板 resolveSummaryDonor / restoreSummaryMonthlyBlocks / summaryCellText 涓変釜绉佹湁鏂规硶锛�
+ - 瀵煎嚭鏃剁疆 fullCalcOnLoad=true锛屼繚璇� Excel/WPS 鎵撳紑鍗抽噸绠楄鏀瑰啓/杩樺師鐨勫叕寮忋��
+
+## 娴嬭瘯鐜
+- 鍚庣 8090锛堝娆￠噸鍚悗鏈�鏂颁唬鐮佸氨缁級锛汳ySQL 127.0.0.1:3308銆�
+- 姣嶇増锛歞ocs/鐢熸垚姹囨�诲ぇ琛�/2026骞�7鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx锛�1-4 鏈堥鏋讹紝19 椤电锛夛紱澶囦唤锛歞ocs/鐢熸垚姹囨�诲ぇ琛�/_澶囦唤_2026骞�7鏈堥亾璺繍杈撻噺姹囨�昏〃_娓呯悊鍓峗20260904.xlsx銆�
+- 鏂规硶锛氳皟鐢� GET /api/report/export/summaryWorkbook?period=2026-07&mode=month锛屼繚瀛樹负 docs/鐢熸垚姹囨�诲ぇ琛�/杈撳嚭/閬撹矾杩愯緭閲忔眹鎬昏〃_2026-07.xlsx锛岀敤 openpyxl 涓庡浠芥瘝鐗堥�愭牸姣斿锛堝唴瀹�/鍏紡鏂囨湰锛岄潪鏍峰紡锛夈��
+
+## 鐢ㄤ緥涓庣粨鏋�
+1. 杩樺師骞垮害锛堟鍚戯級锛氭竻鐞嗘椂琚鐨� 7 涓彴璐�/鍚堟垚椤碉紙璐ц繍/鐝嚎鍖呰溅/鍩庡競瀹㈣繍/鍏氦/鍑虹/缃戠害杞�/杞ㄩ亾杞浮/涓彛寰勬槑缁嗭級缂哄け鍗曞厓鏍煎叏閮ㄨˉ榻愶紝missing=0锛涘叾浣欓〉锛堟垚鍝侀〉/闅愯棌椤碉級鏈棤宸紓锛屾湭鏀瑰姩銆傜粨璁� PASS銆�
+2. 閫愭牸瀵圭収锛堟鍚戯級锛氳緭鍑轰笌澶囦唤姣嶇増 19 椤靛叏閲忔瘮瀵癸紝real-diff=0锛涗粎 31 鏍间负鈥滄暣鏁� vs 46143.0鈥濈殑鏁板�肩瓑浠疯〃绀哄樊寮傦紙Excel 鏁板�肩浉鍚岋級銆傜粨璁� PASS銆�
+3. 绱/鍚屾瘮鍏紡锛堟鍚戯級锛氭娊鏌� 鐝嚎鍖呰溅/鍏氦/鍑虹/璐ц繍 绛夎 Q 鎭㈠涓� 1-7 鏈堝悇鏈堝�兼眰鍜岋紙濡� =G4+E4+C4+I4+K4+M4+O4锛夛紝R 鎭㈠涓� =Q/SUM(2025 骞� 1-7 鏈堝垪)-1锛屼笌澶囦唤涓�鑷淬�傜粨璁� PASS銆�
+4. 鎵撳紑鍗抽噸绠楋紙姝ｅ悜锛夛細浜х墿 xl/workbook.xml 鍚� <calcPr fullCalcOnLoad="true"/>銆傜粨璁� PASS銆�
+5. 姣嶇増鏈鏀瑰姩锛堟鍚戯級锛氱敓鎴愬墠鍚庢瘝鐗堟枃浠跺瓧鑺傛湭鍙橈紙鍙澶嶅埗锛夈�傜粨璁� PASS銆�
+6. 缂栬瘧涓庡惎鍔細mvn compile EXIT=0锛涘悗绔噸鍚氨缁紱鎺ュ彛 200 杩斿洖 xlsx銆傜粨璁� PASS銆�
+
+## 鍙戠幇鐨勯棶棰樹笌澶勭疆
+1. 鍒濈増 donor 瀹氫綅鍙煡閰嶇疆鐩綍鐩稿璺緞锛岃�� spring-boot:run 鐨勫伐浣滅洰褰曟槸妯″潡鐩綍锛坱raffic-audit-server锛夛紝瀵艰嚧鈥滄湭鎵惧埌澶囦唤姣嶇増鈥濄�佽緭鍑轰粛涓洪鏋讹細宸叉敼涓轰笌 resolveAnyTemplate 鐩稿悓鐨� 3 鍊欓�夋牴鐩綍鎵弿锛屽娴嬭繕鍘熸垚鍔熴��
+2. POI setForceFormulaRecalculation 鏈啓 calcPr锛氭敼涓虹洿鎺ユ搷浣� XSSFWorkbook.getCTWorkbook() 鐨� calcPr.fullCalcOnLoad=true锛屽凡楠岃瘉鍐欏叆銆�
+
+## 缁撹
+M1 璇曠偣閫氳繃锛氫粠 1-4 鏈堥鏋舵瘝鐗堝彲绋嬪簭鍖栬繕鍘熷嚭涓庝汉宸� 7 鏈堟瘝鐗堝唴瀹归浂宸紓鐨勫畬鏁村伐浣滅翱锛堝惈 5-7 鏈堟湀搴﹀潡涓� 1-7 绱/鍚屾瘮鍏紡锛夛紝杈撳嚭鏂囦欢 docs/鐢熸垚姹囨�诲ぇ琛�/杈撳嚭/閬撹矾杩愯緭閲忔眹鎬昏〃_2026-07.xlsx銆備笅涓�姝� M1b锛氭湀浠�>7锛�8-12 鏈堬級鏃舵寜鈥滀笂涓�鏈堝潡鍏嬮殕+鍒楀紩鐢ㄥ钩绉烩�濊嚜鍔ㄦ彃鍏ユ柊鍒楋紙澶囦唤鏃犲搴旀湀浠芥椂鐢級锛屽啀鎺ュ叆 M2 鍙拌处鏁版嵁浠庡簱鍐呭～鍏呫��
\ No newline at end of file
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 b1b1566..87fe99d 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
@@ -3207,9 +3207,20 @@
}
try (InputStream in = new FileInputStream(template);
XSSFWorkbook wb = new XSSFWorkbook(in)) {
+ File donor = resolveSummaryDonor(year, month);
+ if (donor != null) {
+ try (InputStream din = new FileInputStream(donor); XSSFWorkbook dwb = new XSSFWorkbook(din)) {
+ int restored = restoreSummaryMonthlyBlocks(wb, dwb);
+ log.info("姹囨�诲伐浣滅翱鏈堝害鍧楄繕鍘熷畬鎴愶紝琛ラ綈/瑕嗙洊鍗曞厓鏍兼暟={}锛堟瘝鐗堬細{}锛屽浠芥瘝鐗堬細{}锛�", restored, template.getName(), donor.getName());
+ }
+ } else {
+ log.warn("姹囨�诲伐浣滅翱鏈壘鍒板悓鏈熷浠芥瘝鐗堬紙_澶囦唤_{}骞磠}鏈堚�︼級锛岃緭鍑哄皢淇濇寔姣嶇増鐜扮姸锛堝彲鑳戒负 1-4 鏈堥鏋讹級", year, month);
+ }
dynamicSummaryYear(wb, year);
applyTwoDecimalFormat(wb);
autoFitContentColumns(wb); // 姹囨�昏〃瀵煎嚭鍚庢寜鍐呭鍔犲鍒楀锛堝彧鍔犲涓嶇缉绐勶級
+ if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
+ wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
return toBytes(wb);
}
}
@@ -3476,6 +3487,86 @@
return map;
}
+ /** 瀹氫綅鍚屾湡澶囦唤姣嶇増锛堟竻鐞嗗墠鐨勫畬鏁存湀姣嶇増锛夛紝鐢ㄤ簬鎶婇鏋舵瘝鐗堢己澶辩殑 5-7 鏈堟湀搴﹀潡鎸夊浠借繕鍘燂紱
+ * 涓� resolveAnyTemplate 鐩稿悓绛栫暐锛氬湪閰嶇疆鐩綍銆乽ser.dir 鐩稿鐩綍銆佷笂绾х洰褰曚笁涓�欓�夋牴涓壂鎻� */
+ private File resolveSummaryDonor(int year, int month) {
+ String rel = summaryTemplateDir;
+ while (rel.startsWith("./")) rel = rel.substring(2);
+ String[] roots = {
+ summaryTemplateDir,
+ System.getProperty("user.dir") + "/" + rel,
+ System.getProperty("user.dir") + "/../" + rel
+ };
+ for (String root : roots) {
+ if (root == null || root.trim().isEmpty()) continue;
+ File dir = new File(root);
+ File[] files = dir.listFiles((d, name) ->
+ name.startsWith("_澶囦唤_") && name.contains(year + "骞�" + month + "鏈�") && name.contains("閬撹矾杩愯緭閲忔眹鎬昏〃"));
+ if (files != null && files.length > 0) return files[0];
+ }
+ return null;
+ }
+
+ /** 鎸夊悓鏈熷浠芥瘝鐗堣繕鍘熸湀搴﹀潡锛氬嚒澶囦唤鏈夊唴瀹硅�屾瘝鐗堝悓鏍肩己澶�/鍐呭涓嶅悓锛堟竻鐞嗘椂琚鎺夌殑 5-7 鏈堝�间笌绱鍏紡锛夌殑鍗曞厓鏍硷紝鏁存牸鍥炲～骞惰鐩� */
+ private int restoreSummaryMonthlyBlocks(XSSFWorkbook wb, XSSFWorkbook donor) {
+ int restored = 0;
+ for (int i = 0; i < donor.getNumberOfSheets(); i++) {
+ Sheet ds = donor.getSheetAt(i);
+ if (ds == null) continue;
+ Sheet bs = wb.getSheet(ds.getSheetName());
+ if (bs == null) continue;
+ for (int r = ds.getFirstRowNum(); r <= ds.getLastRowNum(); r++) {
+ Row dr = ds.getRow(r);
+ if (dr == null) continue;
+ Row br = bs.getRow(r);
+ for (Cell dc : dr) {
+ if (dc == null) continue;
+ CellType t = dc.getCellType();
+ if (t == CellType.BLANK) continue;
+ String dtx = summaryCellText(dc);
+ if (dtx == null || dtx.isEmpty()) continue;
+ Cell bc = (br == null) ? null : br.getCell(dc.getColumnIndex());
+ if (bc != null && dtx.equals(summaryCellText(bc))) continue;
+ Row tr = (bc != null) ? br : bs.getRow(r);
+ if (tr == null) tr = bs.createRow(r);
+ Cell tc = (bc != null) ? bc : tr.getCell(dc.getColumnIndex());
+ if (tc == null) tc = tr.createCell(dc.getColumnIndex());
+ tc.setBlank();
+ if (t == CellType.FORMULA) {
+ String f = dc.getCellFormula();
+ if (f != null) tc.setCellFormula(f);
+ } else if (t == CellType.STRING) {
+ tc.setCellValue(dc.getStringCellValue());
+ } else if (t == CellType.NUMERIC) {
+ tc.setCellValue(dc.getNumericCellValue());
+ } else if (t == CellType.BOOLEAN) {
+ tc.setCellValue(dc.getBooleanCellValue());
+ }
+ restored++;
+ }
+ }
+ }
+ return restored;
+ }
+
+ /** 鍗曞厓鏍煎唴瀹规枃鏈寲锛堝叕寮忓甫 = 鍓嶇紑锛夛紝鐢ㄤ簬璺ㄧ翱姣斿 */
+ private String summaryCellText(Cell c) {
+ if (c == null) return null;
+ switch (c.getCellType()) {
+ case FORMULA: {
+ String f = c.getCellFormula();
+ return f == null ? "" : "=" + f;
+ }
+ case STRING:
+ return c.getStringCellValue();
+ case NUMERIC:
+ return Double.toString(c.getNumericCellValue());
+ case BOOLEAN:
+ return Boolean.toString(c.getBooleanCellValue());
+ default:
+ return "";
+ }
+ }
/** 姹囨�诲伐浣滅翱骞翠唤鍔ㄦ�佸寲锛氫粎鎶� 2026骞� 鍓嶇紑鏇挎崲涓虹洰鏍囧勾锛�2025/2024 鍙傝�冨垪涓嶅姩锛� */
private void dynamicSummaryYear(XSSFWorkbook wb, int year) {
String oldPrefix = "2026骞�";
--
Gitblit v1.9.1