From a267825e23a5389713e42fde9d308df5f621e949 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期六, 10 十月 2026 09:33:53 +0800
Subject: [PATCH] feat(汇总大表): 按月扩列器（8月→9月版式）与只读扩列接口（待验收）
---
HANDOFF.md | 20 ++
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 27 +++
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java | 415 +++++++++++++++++++++++++++++++++++++++++++++++++++
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/controller/ReportExportController.java | 7
4 files changed, 469 insertions(+), 0 deletions(-)
diff --git a/HANDOFF.md b/HANDOFF.md
index 31c5619..6fb70e0 100644
--- a/HANDOFF.md
+++ b/HANDOFF.md
@@ -4,6 +4,26 @@
> **姣忓ぉ缁撴潫鏃�**锛氭妸褰撳ぉ杩涘睍銆佽俯杩囩殑鍧戙�佹柊闇�姹傘�佹湭瀹屾垚浜嬮」鏇存柊鍒版湰鏂囦欢锛岀劧鍚庡彲浠ユ斁蹇冨紑鏂板璇濄��
> **鍗忎綔绾﹀畾锛�2026-08-31锛�**锛氳銆屾彁浜ゃ��= 鏈湴 commit + 鎺ㄩ�佸埌杩滅 git锛坥rigin main锛孏itblit锛夛紝涓嶅啀鍙仛鏈湴 commit銆�
+## 鏈�鏂帮紙2026-10-10锛寊yj 绾匡級
+
+### 10-10锛歋tep 1 鎵╁垪鍣ㄨ惤鍦� + 8鈫�9 鏈堢増寮忛杞骇鍑恒�愬緟楠屾敹銆�
+
+- **鏂板鎵╁垪鍣�**锛歚traffic-audit-server/.../reportexport/layout/SummaryLayoutExpander.java`锛涙柊澧炲彧璇绘帴鍙� `GET /api/report/export/summaryWorkbook/expand?period=YYYY-MM`锛堣繑鍥炲瓧鑺傘��**涓嶈惤鐩�**锛夛紱`ReportExportService.expandSummaryWorkbook(period)` 璐熻矗鍙栧綋鏈熸瘝鐗堝苟璋冪敤銆傝鍒欎緷鎹� `docs/姹囨�诲ぇ琛ㄦ瘝鐗堟墿鍒楅厤缃甠鏍稿鐗坃2026-10-09.md`锛�8 涓彲瑙佸彴璐﹂〉锛�4 涓殣钘忛〉涓嶆墿鍒椾絾鍚屾骞崇Щ鍏惰法椤靛紩鐢級銆�
+- **浜у嚭锛堝緟纭浠讹紝鏈惤鐩樹负姣嶇増锛�**锛歚docs/鐢熸垚姹囨�诲ぇ琛�/杈撳嚭/_鎵╁垪_2026骞�9鏈坃寰呯‘璁�.xlsx`锛�1,036,939 B锛�2026-10-10 09:30:07锛夈�傚彧鍋氱増寮忥紝9 鏈堟暟鎹垪鐣欑┖銆�
+- **涓庢墜宸� 9 鏈堟瘝鐗堥�愭牸姣斿**锛�**` 璐ц繍` 宸紓 = 0**锛堣鍒欐纭殑鏈�寮鸿瘉鎹級锛涘叾浣� 7 椤靛叡 836 鏉″樊寮傦紝**鍏ㄩ儴闈炴暟鍊煎垎姝�**锛屽垎涓哄洓绫伙細
+ 1. **548 鏉�**锛氫骇鍑烘妸銆岀疮璁�/绱鍚屾瘮鍒嗘瘝銆嶆墿鍒� 9 鏈堬紝鎵嬪伐鐗堟湭鎵┾�斺�旀鏄� 09-29銆婃墿鍒楄繘搴︺�嬭瀹氱殑寰呬慨椤� 鈶⑩懀锛�"绱鍒楀彧绠楀埌 8 鏈�""鍘诲勾鍩烘暟鍙埌 8 鏈�"锛夛紝鍗�**鎵嬪伐 9 鏈堟瘝鐗堣繖 7 椤靛皻鏈慨瀹�**锛�
+ 2. 153 鏉★細鍒嗘瘝鏂板椤瑰垎闅旂鍐欐硶锛坄SUM(a+b+鈥�,c)` vs `SUM(a+b+鈥�+c)`锛岀瓑浠峰悓鍊硷級锛�
+ 3. 130 鏉★細琛ㄥ悕寮曞彿鍐欐硶锛坄杞ㄩ亾銆佽疆娓�!C6` vs `'杞ㄩ亾銆佽疆娓�'!C6`锛孍xcel 瑙勮寖鍖栨墍鑷达紝鍚屽�硷級锛�
+ 4. 1 鏉★細娴偣灏惧樊銆�
+- **8 鏈堝強浠ュ墠鍒�**锛氫粎 28 鏉″樊寮備笖鍏ㄩ儴灞炵 3 绫诲紩鍙峰啓娉曪紙闆嗕腑鍦� `鍩庡競瀹㈣繍`锛夛紝鏈姩鏁板��/鍒楀彿銆�
+- **杩囩▼涓慨鎺夌殑涓や釜瀹炵幇缂洪櫡**锛堥噸瑕侊紝閬垮厤澶嶇幇锛夛細
+ 1. POI 5.2.5 鐨勫悎骞跺尯 API 鏄� `addMergedRegion(CellRangeRangeAddress)`銆乣removeMergedRegion(int)`锛堟棤 CellRangeAddress 閲嶈浇锛夆�斺�旀寜涓嬫爣鎽橀櫎銆佹寜瀵硅薄娣诲姞锛�
+ 2. 寮曠敤姝ｅ垯鏈�鍒濆彧璁� ASCII 琛ㄥ悕锛�**婕忔帀姣嶇増閲� 5,574 澶�"鏈甫寮曞彿鐨勪腑鏂囪〃鍚�"寮曠敤**锛堝 `杞ㄩ亾銆佽疆娓�!C6`锛夆啋 宸叉妸琛ㄥ悕瀛楃绫绘墿鍒� CJK锛屼慨姝ｅ悗鍒楀彿涓庢墜宸ョ湡鍊间竴鑷淬��
+ 3. 鍐欎欢鐢� **POI**锛堜笌鐢熸垚閾捐矾鍚屾簮锛夛紝涓嶇敤 openpyxl锛堟湰椤圭洰鍘嗗彶涓婃湁 openpyxl 鍐欏嚭瀵艰嚧 Excel 鎷掔粷鎵撳紑鐨勬渚嬶級銆�
+- **鎶ュ憡锛堟湰鍦般�佷笉鍏ュ簱锛�**锛歚docs/鍔熻兘娴嬭瘯鎶ュ憡/鍔熻兘娴嬭瘯鎶ュ憡_姹囨�诲ぇ琛ㄦ墿鍒楀櫒8鏈堝埌9鏈堢増寮廮2026-10-10.md`锛涙棩缁� `docs/宸ヤ綔鏃ョ粨/宸ヤ綔鏃ョ粨_2026-10-10.md`銆�
+- **寰呯敤鎴烽獙鏀�/纭**锛氣憼 绱鍒椾笌绱鍚屾瘮鍒嗘瘝**鏄惁搴旀墿鍒� 9 鏈�**锛堜骇鍑�=宸叉墿锛涙寜 09-29 寰呬慨娓呭崟锛屾柟鍚戜笌褰撴椂璁ゅ畾涓�鑷达級锛涒憽 纭鍚庢槸鍚︽妸浜у嚭钀界洏涓烘寮� 9 鏈堟瘝鐗堬紙鎸夊凡纭鍙ｅ緞锛氫汉宸ョ‘璁ゅ悗钀界洏锛夈��
+- **鐘舵��**锛�**銆愬緟楠屾敹銆�**锛堢増寮忕被鏀瑰姩锛夈�傛湭瀵煎叆浠讳綍涓氬姟鏁版嵁銆佹湭鏀瑰姩鐢熸垚涓婚摼銆�
+- **涓嬩竴姝�**锛氭寜纭缁撹寰皟锛堝闇�锛夆啋 澶嶈窇 8鈫�9 鈫� 鎺ュ叆涓婚摼锛坄resolveSummaryMother` 鎵句笉鍒扮洰鏍囨湀姣嶇増鏃惰嚜鍔ㄦ墿鍒楋級+ 褰掓。鐩綍涓庣増寮忓弬鐓т欢鎸� C4 鏀跺熬銆�
## 鏈�鏂帮紙2026-10-09锛寊yj 绾匡級
### 10-09锛�2025 鏁版嵁鐜扮姸鏍搁獙锛堢粨璁猴細宸插湪搴擄紝鏃犻渶琛ュ綍锛�+ M0 瑙勬牸琛ョ鍗佷簩鑺�
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/controller/ReportExportController.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/controller/ReportExportController.java
index d344393..00dad87 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/controller/ReportExportController.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/controller/ReportExportController.java
@@ -89,6 +89,13 @@
}
/** 鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx锛堟眹鎬诲伐浣滅翱 P0-P1 楠ㄦ灦锛氭暣鏈鍒舵瘝鐗堬紝椤电/鍏紡/2025 缂撳瓨淇濈暀锛� */
+ /** 姹囨�诲ぇ琛ㄦ寜鏈堟墿鍒楋紙Step 1锛屽彧鍋氱増寮忋�佹暟鎹暀绌猴級锛氳繑鍥炰笅涓�鏈堢増寮� xlsx銆� */
+ @GetMapping("/export/summaryWorkbook/expand")
+ public ResponseEntity<byte[]> expandSummaryWorkbook(@RequestParam("period") String period) throws Exception {
+ byte[] data = reportService.expandSummaryWorkbook(period);
+ return buildResponse(data, String.format("鎵╁垪_閬撹矾杩愯緭閲忔眹鎬昏〃_%s_寰呯‘璁�.xlsx", period));
+ }
+
@GetMapping("/export/summaryWorkbook")
public ResponseEntity<byte[]> exportSummaryWorkbook(@RequestParam("period") String period,
@RequestParam(value = "mode", defaultValue = "month") String mode,
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java
new file mode 100644
index 0000000..9da5c4a
--- /dev/null
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java
@@ -0,0 +1,415 @@
+package com.trafficaudit.reportexport.layout;
+
+import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.springframework.stereotype.Component;
+
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.GregorianCalendar;
+import java.util.HashSet;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+/**
+ * 姹囨�诲ぇ琛ㄣ�屾寜鏈堟墿鍒楀櫒銆嶏紙Step 1锛�2026-10-09 棣栨瀹炵幇锛夈��
+ *
+ * <p>鎶� N 鏈堟瘝鐗堟帹杩涘埌 N+1 鏈堢増寮忥紙涓嶉鐣欑┖鍒楋級锛氬湪銆岀疮璁″垪銆嶅墠鎻掑叆 2 鍒楋紙褰撴湀鍊煎垪 + 鍚屾瘮鍒楋級锛�
+ * 鍏嬮殕涓よ绾靛悜鍚堝苟鏍囬銆侀噸鍐欑疮璁℃灇涓句笌绱鍚屾瘮鍒嗘瘝銆佸彸绉� 2025/2024/2023 鍘嗗彶鍧楀強鍏跺叕寮忓紩鐢紝
+ * 骞跺悓姝ュ钩绉汇�屽叾瀹冮〉鎸囧悜宸叉墿鍒楅〉銆佸垪鍙� >= 鎻掑叆鍒椼�嶇殑璺ㄩ〉寮曠敤銆�
+ *
+ * <p>瑙勫垯渚濇嵁锛歿@code docs/姹囨�诲ぇ琛ㄦ瘝鐗堟墿鍒楅厤缃甠鏍稿鐗坃2026-10-09.md}锛�8 涓彲瑙佸彴璐﹂〉锛�
+ * 4 涓殣钘忛〉淇濈暀闅愯棌銆佷笉鎵╁垪锛屼絾鍏惰法椤靛紩鐢ㄩ渶鍚屾骞崇Щ锛夈�傚彧鍋氱増寮忥紝褰撴湀鏁版嵁鍒楃暀绌恒��
+ */
+@Slf4j
+@Component
+public class SummaryLayoutExpander {
+
+ private static final List<String> EXPAND_PAGES = Arrays.asList(
+ " 璐ц繍", "鐝嚎鍖呰溅", "鍩庡競瀹㈣繍", "鍏氦", "鍑虹", "缃戠害杞�", "杞ㄩ亾銆佽疆娓�", "涓彛寰勬槑缁�");
+
+ private static final int HEADER_SCAN_ROWS = 15;
+
+ /** 鍏紡閲岀殑 A1 寮曠敤锛堝彲甯﹀伐浣滆〃鍓嶇紑锛夈�� */
+ private static final Pattern REF = Pattern.compile(
+ "(?:('([^']+)'|([A-Za-z_\\u4e00-\\u9fff][A-Za-z0-9_.\\u4e00-\\u9fff\u3001]*))!)?(\\$?)([A-Z]{1,3})(\\$?)(\\d+)");
+
+ private static final Pattern MONTH_TEXT = Pattern.compile("^(\\d{4})\\s*骞碶\s*(\\d{1,2})\\s*鏈�$");
+
+ public byte[] expand(byte[] source, int year, int targetMonth) {
+ if (targetMonth < 2 || targetMonth > 12) {
+ throw new IllegalArgumentException("鎵╁垪锛氱洰鏍囨湀浠介渶鍦� 2~12 涔嬮棿");
+ }
+ try (XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(source))) {
+ Set<String> expanded = new HashSet<>(EXPAND_PAGES);
+ Map<String, Integer> insertColOf = new LinkedHashMap<>();
+ for (String name : EXPAND_PAGES) {
+ Sheet sh = wb.getSheet(name);
+ if (sh == null) {
+ log.warn("鎵╁垪锛氭壘涓嶅埌椤电 {}", name);
+ continue;
+ }
+ insertColOf.put(name, expandSheet(sh, year, targetMonth));
+ }
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ Sheet sh = wb.getSheetAt(i);
+ if (sh == null || expanded.contains(sh.getSheetName())) continue;
+ shiftQualifiedRefs(sh, insertColOf);
+ }
+ wb.setForceFormulaRecalculation(true);
+ ByteArrayOutputStream out = new ByteArrayOutputStream();
+ wb.write(out);
+ return out.toByteArray();
+ } catch (Exception e) {
+ throw new IllegalStateException("姹囨�诲ぇ琛ㄦ墿鍒楀け璐�: " + e.getMessage(), e);
+ }
+ }
+
+ /** 瀵瑰崟椤垫墿鍒楋紝杩斿洖鎻掑叆浣嶇疆锛�0 鍩哄垪鍙凤級銆� */
+ private int expandSheet(Sheet sh, int year, int targetMonth) {
+ int lastMonth = targetMonth - 1;
+ Map<Integer, Integer> cur = monthCols(sh, year);
+ Integer lastVal = cur.get(lastMonth);
+ if (lastVal == null) {
+ throw new IllegalStateException("鎵╁垪锛�" + sh.getSheetName() + " 椤垫湭鎸夎〃澶磋瘑鍒埌 " + year + "骞�" + lastMonth + "鏈堝垪");
+ }
+ Map<Integer, Integer> last = monthCols(sh, year - 1);
+ Integer lastYearSameMonth = last.get(targetMonth);
+ if (lastYearSameMonth == null) {
+ throw new IllegalStateException("鎵╁垪锛�" + sh.getSheetName() + " 椤垫湭璇嗗埆鍒� " + (year - 1) + "骞�" + targetMonth + "鏈堝垪锛堢疮璁″悓姣斿垎姣嶏級");
+ }
+ List<Integer> headerRows = headerRowsOf(sh);
+ int insertCol = lastVal + 2; // 绱鍒椾綅缃紙8 鏈堝�煎垪 Q=16 鈫� 绱 S=18锛�
+ int srcVal = lastVal, srcYoy = lastVal + 1;
+ int lastRow = sh.getLastRowNum();
+
+ // 1) 鍖哄煙鍙崇Щ 2 鍒楋紙鑷彸鍚戝乏鎼牸锛屼繚鐣欏��/鍏紡/鏍峰紡锛�
+ for (int r = 0; r <= lastRow; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ int lastCell = row.getLastCellNum() - 1;
+ for (int c = lastCell; c >= insertCol; c--) {
+ Cell src = row.getCell(c);
+ if (src == null) continue;
+ copyCell(src, getOrCreate(row, c + 2));
+ src.setBlank();
+ }
+ }
+ // 2) 鍚堝苟鍖哄彸绉伙紙璺ㄦ彃鍏ョ偣鐨勬暣浣撳姞瀹斤級锛氬厛鎸変笅鏍囨憳闄ゅ叏閮ㄥ悎骞跺尯锛屽啀鎸夎皟鏁村悗鐨勮寖鍥撮噸鏂版坊鍔�
+ List<CellRangeAddress> merges = new ArrayList<>(sh.getMergedRegions());
+ for (int i = merges.size() - 1; i >= 0; i--) {
+ sh.removeMergedRegion(i);
+ }
+ for (CellRangeAddress mr : merges) {
+ CellRangeAddress moved = mr;
+ if (mr.getFirstColumn() >= insertCol) {
+ moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
+ mr.getFirstColumn() + 2, mr.getLastColumn() + 2);
+ } else if (mr.getLastColumn() >= insertCol) {
+ moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
+ mr.getFirstColumn(), mr.getLastColumn() + 2);
+ }
+ sh.addMergedRegion(moved);
+ }
+ // 3) 鏈〉鍏紡寮曠敤鏁翠綋 +2锛堟寚鍚戝凡鎵╁垪椤电殑寮曠敤鍚屾牱 +2锛涙寚鍚戞湭鎵╁垪椤电殑涓嶅姩锛�
+ Set<String> shiftable = new HashSet<>(EXPAND_PAGES);
+ shiftAllRefs(sh, insertCol, shiftable);
+
+ // 4) 鏂板垪锛氬垪瀹�/闅愯棌鐓ф妱涓婃湀锛涜〃澶达紙姣忎釜鍧楃殑琛ㄥご琛岋級鍏嬮殕鍊�+鏍煎紡+鍚堝苟鍖猴紱鏁版嵁鍖哄彧鐣欐牱寮忎笌鍏紡
+ int w = sh.getColumnWidth(srcVal);
+ if (w > 0) sh.setColumnWidth(insertCol, w);
+ int w2 = sh.getColumnWidth(srcYoy);
+ if (w2 > 0) sh.setColumnWidth(insertCol + 1, w2);
+ sh.setColumnHidden(insertCol, sh.isColumnHidden(srcVal));
+ sh.setColumnHidden(insertCol + 1, sh.isColumnHidden(srcYoy));
+ for (int h : headerRows) {
+ cloneHeaderCell(sh, srcVal, insertCol, h, year, targetMonth);
+ cloneHeaderCell(sh, srcYoy, insertCol + 1, h, year, targetMonth);
+ cloneHeaderMerge(sh, srcVal, insertCol, h);
+ cloneHeaderMerge(sh, srcYoy, insertCol + 1, h);
+ }
+ for (int r = 0; r <= lastRow; r++) {
+ if (headerRows.contains(r)) continue;
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ cloneDataCell(row, srcVal, insertCol, shiftable, insertCol);
+ cloneDataCell(row, srcYoy, insertCol + 1, shiftable, insertCol);
+ }
+ // 5) 绱鍒楄ˉ褰撴湀椤癸紱绱鍚屾瘮鍒嗘瘝琛ュ幓骞村悓鏈堝垪
+ int cumCol = insertCol + 2, cumYoyCol = insertCol + 3;
+ int denCol = lastYearSameMonth + 2;
+ String valLetter = colLetter(insertCol);
+ String denLetter = colLetter(denCol);
+ int cumFixed = 0, cumYoyFixed = 0;
+ for (int r = 0; r <= lastRow; r++) {
+ if (headerRows.contains(r)) continue;
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ Cell cum = row.getCell(cumCol);
+ if (cum != null && cum.getCellType() == CellType.FORMULA) {
+ String f = cum.getCellFormula();
+ if (!f.contains(valLetter + (r + 1))) {
+ cum.setCellFormula(f + "+" + valLetter + (r + 1));
+ cumFixed++;
+ }
+ }
+ Cell cumYoy = row.getCell(cumYoyCol);
+ if (cumYoy != null && cumYoy.getCellType() == CellType.FORMULA) {
+ String f = cumYoy.getCellFormula();
+ if (f.contains(denLetter + (r + 1))) continue;
+ int close = f.lastIndexOf(')');
+ if (close > 0) {
+ String inner = f.substring(0, close);
+ String sep = inner.contains(",") ? "," : "+";
+ cumYoy.setCellFormula(inner + sep + denLetter + (r + 1) + f.substring(close));
+ cumYoyFixed++;
+ } else {
+ log.warn("鎵╁垪锛歿} 绗� {} 琛岀疮璁″悓姣斿叕寮忓舰鎬佹湭璇嗗埆锛屾湭琛ュ幓骞村悓鏈堝垪锛歿}", sh.getSheetName(), r + 1, f);
+ }
+ }
+ }
+ log.info("鎵╁垪锛歿} 椤靛畬鎴愨�斺�斿�煎垪 {}鈫抺}锛屽悓姣斿垪 {}鈫抺}锛岀疮璁″垪 {}锛岀疮璁″悓姣斿垪 {}锛屽幓骞村悓鏈堝垪 {}锛涜〃澶磋 {}锛屾湯琛� {}锛涚疮璁¤ˉ {} 鏍笺�佺疮璁″悓姣旇ˉ {} 鏍�",
+ sh.getSheetName(), colLetter(srcVal), colLetter(insertCol), colLetter(srcYoy), colLetter(insertCol + 1),
+ colLetter(cumCol), colLetter(cumYoyCol), denLetter, headerRows, lastRow + 1, cumFixed, cumYoyFixed);
+ return insertCol;
+ }
+
+ /** 琛ㄥご琛岋紙0 鍩猴級锛氬惈銆屽勾+鏈堛�嶈〃澶村崟鍏冩牸鐨勮锛涜建閬撱�佽疆娓℃湁涓や釜鍧� 鈫� 涓よ銆� */
+ private List<Integer> headerRowsOf(Sheet sh) {
+ List<Integer> rows = new ArrayList<>();
+ int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
+ for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ if (yearMonthOf(row.getCell(c)) != null) {
+ rows.add(r);
+ break;
+ }
+ }
+ }
+ return rows;
+ }
+
+ private Map<Integer, Integer> monthCols(Sheet sh, int year) {
+ Map<Integer, Integer> cols = new LinkedHashMap<>();
+ int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
+ for (int r = sh.getFirstRowNum(); r <= lastRow; 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));
+ if (ym == null || ym / 100 != year) continue;
+ int mo = ym % 100;
+ if (!cols.containsKey(mo)) cols.put(mo, c);
+ }
+ }
+ return cols;
+ }
+
+ private Integer yearMonthOf(Cell c) {
+ if (c == null) return null;
+ try {
+ CellType t = c.getCellType();
+ if (t == CellType.STRING) {
+ String v = c.getStringCellValue();
+ if (v == null) return null;
+ v = v.trim();
+ if (v.isEmpty() || v.contains("绱") || v.contains("鍚屾瘮") || v.contains("鐜瘮")) return null;
+ Matcher m = MONTH_TEXT.matcher(v);
+ if (!m.matches()) return null;
+ int mo = Integer.parseInt(m.group(2));
+ return mo >= 1 && mo <= 12 ? Integer.parseInt(m.group(1)) * 100 + mo : null;
+ }
+ if (t == CellType.NUMERIC || t == CellType.FORMULA) {
+ double v;
+ if (t == CellType.NUMERIC) {
+ v = c.getNumericCellValue();
+ } else {
+ if (c.getCachedFormulaResultType() != CellType.NUMERIC) return null;
+ v = c.getNumericCellValue();
+ }
+ if (v < 36526 || v > 73415) return null;
+ 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;
+ return cal.get(java.util.Calendar.YEAR) * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
+ }
+ } catch (Exception ignore) {
+ return null;
+ }
+ return null;
+ }
+
+ private void cloneHeaderCell(Sheet sh, int srcCol, int dstCol, int rowIdx, int year, int targetMonth) {
+ Row row = sh.getRow(rowIdx);
+ if (row == null) return;
+ Cell s = row.getCell(srcCol);
+ if (s == null) return;
+ Cell d = getOrCreate(row, dstCol);
+ d.setBlank();
+ d.setCellStyle(s.getCellStyle());
+ if (s.getCellType() == CellType.STRING) {
+ String text = s.getStringCellValue();
+ if (text != null && text.contains("鍚屾瘮")) {
+ d.setCellValue(text.replaceAll("^\\d{1,2}鏈�", targetMonth + "鏈�"));
+ } else if (text != null && MONTH_TEXT.matcher(text.trim()).matches()) {
+ d.setCellValue(year + "骞�" + targetMonth + "鏈�");
+ } else {
+ d.setCellValue(text);
+ }
+ } else if (s.getCellType() == CellType.NUMERIC || s.getCellType() == CellType.FORMULA) {
+ d.setCellValue(new GregorianCalendar(year, targetMonth - 1, 1).getTime());
+ }
+ }
+
+ private void cloneHeaderMerge(Sheet sh, int srcCol, int dstCol, int headerRow) {
+ for (CellRangeAddress mr : new ArrayList<>(sh.getMergedRegions())) {
+ if (mr.getFirstColumn() == srcCol && mr.getLastColumn() == srcCol && mr.getFirstRow() == headerRow) {
+ sh.addMergedRegion(new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(), dstCol, dstCol));
+ return;
+ }
+ }
+ }
+
+ private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable, int insertCol) {
+ Cell s = row.getCell(srcCol);
+ if (s == null) return;
+ Cell d = getOrCreate(row, dstCol);
+ d.setBlank();
+ d.setCellStyle(s.getCellStyle());
+ if (s.getCellType() == CellType.FORMULA) {
+ d.setCellFormula(shiftRefs(s.getCellFormula(), insertCol, shiftable));
+ }
+ }
+
+ private void shiftAllRefs(Sheet sh, int insertCol, Set<String> shiftable) {
+ for (int r = 0; r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = 0; c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
+ String f = cell.getCellFormula();
+ String nf = shiftRefs(f, insertCol, shiftable);
+ if (!nf.equals(f)) cell.setCellFormula(nf);
+ }
+ }
+ }
+
+ /** 鍙钩绉汇�屾樉寮忓甫宸ヤ綔琛ㄥ墠缂�銆佽椤靛凡鎵╁垪銆佷笖鍒楀彿 >= 璇ラ〉鎻掑叆鍒椼�嶇殑寮曠敤锛堢敤浜庢湭鎵╁垪鐨勯〉锛夈�� */
+ private void shiftQualifiedRefs(Sheet sh, Map<String, Integer> insertColOf) {
+ for (int r = 0; r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = 0; c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
+ String f = cell.getCellFormula();
+ String nf = shiftQualified(f, insertColOf);
+ if (!nf.equals(f)) cell.setCellFormula(nf);
+ }
+ }
+ }
+
+ private String shiftQualified(String formula, Map<String, Integer> insertColOf) {
+ Matcher m = REF.matcher(formula);
+ StringBuffer sb = new StringBuffer();
+ while (m.find()) {
+ String sheet = m.group(2) != null ? m.group(2) : m.group(3);
+ Integer ins = sheet == null ? null : insertColOf.get(sheet);
+ if (sheet == null || ins == null || isFunctionName(formula, m)) {
+ m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
+ continue;
+ }
+ int colIdx = colIndex(m.group(5));
+ if (colIdx >= ins) {
+ m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
+ } else {
+ m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
+ }
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
+ /** 骞崇Щ鍏紡涓墍鏈夈�屽垪鍙� >= insertCol銆嶇殑寮曠敤锛坰heet 涓� null 鎴栧睘浜� shiftable 鏃剁敓鏁堬級銆� */
+ private String shiftRefs(String formula, int insertCol, Set<String> shiftable) {
+ Matcher m = REF.matcher(formula);
+ StringBuffer sb = new StringBuffer();
+ while (m.find()) {
+ String sheet = m.group(2) != null ? m.group(2) : m.group(3);
+ if ((sheet != null && !shiftable.contains(sheet)) || isFunctionName(formula, m)) {
+ m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
+ continue;
+ }
+ int colIdx = colIndex(m.group(5));
+ if (colIdx >= insertCol) {
+ m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
+ } else {
+ m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
+ }
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
+ private boolean isFunctionName(String formula, Matcher m) {
+ return m.group(1) == null && formula.indexOf('(', m.end()) == m.end();
+ }
+
+ private String rebuild(Matcher m, String sheet, int newColIdx0) {
+ String prefix = sheet == null ? ""
+ : (m.group(1).startsWith("'") ? "'" + sheet + "'" : sheet) + "!";
+ return prefix + m.group(4) + colLetter(newColIdx0) + m.group(6) + m.group(7);
+ }
+
+ private void copyCell(Cell src, Cell dst) {
+ dst.setCellStyle(src.getCellStyle());
+ CellType t = src.getCellType();
+ if (t == CellType.FORMULA) {
+ dst.setCellFormula(src.getCellFormula());
+ } else if (t == CellType.NUMERIC) {
+ dst.setCellValue(src.getNumericCellValue());
+ } else if (t == CellType.STRING) {
+ dst.setCellValue(src.getStringCellValue());
+ } else if (t == CellType.BOOLEAN) {
+ dst.setCellValue(src.getBooleanCellValue());
+ } else {
+ dst.setBlank();
+ }
+ }
+
+ private Cell getOrCreate(Row row, int col) {
+ Cell c = row.getCell(col);
+ return c == null ? row.createCell(col) : c;
+ }
+
+ private static int colIndex(String letters) {
+ int n = 0;
+ for (int i = 0; i < letters.length(); i++) n = n * 26 + (letters.charAt(i) - 'A' + 1);
+ return n - 1;
+ }
+
+ private static String colLetter(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();
+ }
+}
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 67a22b4..1888c3d 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
@@ -34,6 +34,7 @@
import com.trafficaudit.reportexport.calc.MidCalc;
import com.trafficaudit.reportexport.calc.PaxCalc;
import com.trafficaudit.reportexport.calc.SummaryWorkbookFiller;
+import com.trafficaudit.reportexport.layout.SummaryLayoutExpander;
import com.trafficaudit.reportexport.calc.WycSplitCalc;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
@@ -101,6 +102,8 @@
private CityTaxiMonthlyMapper cityTaxiMapper;
@Resource
private SummaryWorkbookFiller summaryWorkbookFiller;
+ @Resource
+ private SummaryLayoutExpander summaryLayoutExpander;
@Resource
private WycSplitCalc wycSplitCalc;
@Resource
@@ -3622,6 +3625,30 @@
return exportSummaryWorkbook(period, mode, null, null);
}
+ /**
+ * 姹囨�诲ぇ琛ㄣ�屾寜鏈堟墿鍒椼�嶏紙2026-10-09 Step 1锛夛細鎶� period 褰撴湀姣嶇増鎵╁垪鎴愪笅涓�鏈堢増寮忋��
+ * 鍙仛鐗堝紡鈥斺�斿綋鏈堟暟鎹垪鐣欑┖锛涗笉钀界洏锛岀敱璋冪敤鏂瑰喅瀹氭槸鍚︾‘璁ゅ悗鍙﹀瓨涓轰笅鏈熸瘝鐗堛��
+ */
+ public byte[] expandSummaryWorkbook(String period) throws Exception {
+ if (period == null || !period.contains("-")) {
+ throw new RuntimeException("鎵╁垪浠呮敮鎸佹寜鏈堬紝璇锋寚瀹� period=YYYY-MM");
+ }
+ int year = Integer.parseInt(period.split("-")[0]);
+ int month = monthOf(period, "month");
+ if (month >= 12) {
+ throw new RuntimeException("鎵╁垪锛�12 鏈堜箣鍚庨渶鏂板紑涓�鏈紝璺ㄥ勾瑙勫垯鍙﹁澶勭悊");
+ }
+ File mother = resolveSummaryMother(period, "month");
+ if (mother == null) {
+ throw new RuntimeException("鏈壘鍒版眹鎬昏〃姣嶇増锛坉ocs/鐢熸垚姹囨�诲ぇ琛級锛�" + year + "骞�" + month + "鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx");
+ }
+ try (InputStream in = new FileInputStream(mother)) {
+ byte[] out = summaryLayoutExpander.expand(org.apache.poi.util.IOUtils.toByteArray(in), year, month + 1);
+ log.info("姹囨�诲ぇ琛ㄦ墿鍒楀畬鎴愶細{}骞磠}鏈堟瘝鐗� -> {}骞磠}鏈堢増寮忥紙鍙仛鐗堝紡銆佹暟鎹暀绌猴級", year, month, year, month + 1);
+ return out;
+ }
+ }
+
/** 鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx锛圥0-P1 楠ㄦ灦锛屾敮鎸� toMonth 瑁佸壀锛夛細
* 浠ャ�奩YYY骞碝鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx銆嬶紙M 鐢� period 鍐冲畾锛変负姣嶇増锛屾寜鍚屾湡澶囦唤姣嶇増杩樺師鏈堝害鍧楋紱
* toMonth 闈炵┖涓斿皬浜� M 鏃讹紝鎶� 8 涓彴璐�/鍚堟垚椤佃鍒� toMonth 鏈堬紙toMonth+1..M 鐨勫綋鏈堝��/鍚屾瘮鍒楁竻绌恒�佷繚鐣欒〃澶达紝
--
Gitblit v1.9.1