From 3b3652e3418ccfbbd21d9f7a2d8769d09c4f18f9 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期一, 12 十月 2026 13:08:54 +0800
Subject: [PATCH] fix(汇总大表): 扩列公式克隆修正（值列相对右移、同比列重写）并落盘 9 月母版 待验收
---
HANDOFF.md | 12 ++++++++++++
docs/生成汇总大表/2026年9月道路运输量汇总表.xlsx | 0
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java | 38 ++++++++++++++++++++++++++++++++++----
3 files changed, 46 insertions(+), 4 deletions(-)
diff --git a/HANDOFF.md b/HANDOFF.md
index fe53f3d..d007c6b 100644
--- a/HANDOFF.md
+++ b/HANDOFF.md
@@ -17,6 +17,18 @@
- **鍙ｅ緞缁撹锛堝啓杩涜璁★級**锛�**鏂版瘝鐗堜竴寰嬩粠"褰撴湡浜х墿"娲剧敓**锛堜骇鐗╁甫褰撴湡鏁版嵁涓庡叕寮忥級锛屼笉鍐嶄粠鏃ф瘝鐗堟淳鐢熲�斺�斿惁鍒欐瘡鏈堥兘浼氫涪"褰撴湡鍒�"锛岀疮璁￠�愭湀婕忔暟銆�
- **閬楃暀**锛氣憼 寰呯敤鎴风‘璁ゆ槸鍚︽妸 v3 钀界洏涓烘寮� 9 鏈堟瘝鐗堬紙浼氬厛澶囦唤鎵嬪伐鐗堬級锛涒憽 鎵╁垪浜х墿涓閲嶅啓鐨勫叕寮忔殏鏃犵紦瀛樺�硷紙鍙啓鍏紡 + `fullCalcOnLoad`锛孍xcel/WPS 鎵撳紑鑷姩绠楋級鈥斺�斿缓璁ˉ"鏁存湰閲嶇畻鍐欑紦瀛�"锛涒憿 `涓彛寰勬槑缁哷 琛ㄥご琛岃瘑鍒繑鍥� `[2,4]`锛岄渶鏍告煡銆�
- **鎶ュ憡锛堟湰鍦般�佷笉鍏ュ簱锛�**锛歚docs/鍔熻兘娴嬭瘯鎶ュ憡/鍔熻兘娴嬭瘯鎶ュ憡_鎵╁垪浠ュ綋鏈熶骇鐗╀负搴昣2026-10-12.md`銆傜姸鎬侊細**銆愬緟楠屾敹銆�**銆�
+### 10-12锛�4锛夛細鎵╁垪鍏紡鍏嬮殕涓ゅ淇 + 9 鏈堟瘝鐗堣惤鐩� + 绔埌绔獙璇侀�氳繃銆愬緟楠屾敹銆�
+
+- **淇墠闂锛堝悓涓�鎵规鐨勪笁涓級**锛氣憼 鍏嬮殕"褰撴湀鍊煎垪"鏃跺叕寮忔湭鐩稿鍙崇Щ锛堟柊鍒楀啓鎴� `=Q7+Q9`锛屽簲 `=S7+S9`锛夆啋 鍏ㄧ渷琛屽彇鍒颁笂鏈堟暟锛涒憽 鍏嬮殕"褰撴湀鍚屾瘮鍒�"鏃跺垎瀛愭寚鍚戞棫鏈堜唤锛坄=Q5/AN5-1`锛屽簲 `=S5/AN5-1`锛夆啋 绠楃殑鏄�"涓婃湀鍊济峰幓骞村悓鏈�"锛涒憿 POI `setCellFormula` **涓嶆帴鍙楀墠瀵� `=`** 鈫� 鎺ュ彛 500锛坄FormulaParseException`锛屄�6.3 鍘嗗彶鍧戯級銆�
+- **淇硶**锛氭柊澧� `shiftRefsAll`锛堝厠闅嗘椂瀵�"鏈〉/宸叉墿鍒楅〉"寮曠敤**鏃犳潯浠� +2**锛岀浉瀵瑰鍒讹級涓� `cloneYoyCell`锛堜笉鍏嬮殕锛岀洿鎺ュ啓 `鏈湀鍊�/琛ㄤ腑瀹氫綅鐨勫幓骞村悓鏈�-1`锛屽嵆 C3 鍙ｅ緞锛夛紱鍏紡涓插幓鎺夊墠瀵� `=`銆�
+- **9 鏈堟瘝鐗堣惤鐩橈紙鐢ㄦ埛宸茬‘璁わ級**锛氭墜宸ョ増澶囦唤涓� `_bak_2026骞�9鏈堟瘝鐗坃鎵嬪伐鐗坃20261012.xlsx`锛涙柊姣嶇増 = "8 鏈堜骇鐗� 鈫� 鎵╁垪"锛�1,083,567 B锛涜緭鍑虹洰褰曞懡鍚嶇悊椤猴紙鏈�鏂颁欢 `_鎵╁垪_2026骞�9鏈坃寰呯‘璁�.xlsx`锛屾棫鐗堝姞 `_bak_` 鍓嶇紑锛夈��
+- **绔埌绔獙璇侊紙鐢ㄦ柊姣嶇増鐢熸垚 2026-09锛�**锛�
+ - 璐ц繍 8 鏈堝垪鏈夊�硷細鍏ㄥ彛寰� **16888.7615**銆佽涓� **3665.3638**銆佽涓� **13223.3977**锛�
+ - 9 鏈堝垪锛氬叏鍙ｅ緞 3575.1211锛堚憽琛ㄦ湭瀵硷紝鍙湁鈶犺涓婏級銆佽涓� 3575.1211锛�
+ - **绱锛氬叏鍙ｅ緞 133887.0138锛�=1-8鏈� 130311.89 + 9鏈� 3575.12锛夈�佽涓� 34974.9459锛�=27734.46+3665.36+3575.12锛夈�佽涓� 98912.0679** 鉁� 涓庢墜绠椾竴鑷达紱
+ - 鍏氦锛�8 鏈� 12315.37銆佺疮璁� 110776.34 鉁撱��
+- **閬楃暀**锛氣憼 鎵╁垪浜х墿琚噸鍐欑殑鍏紡鏃犵紦瀛樺�硷紙寤鸿琛�"鏁存湰閲嶇畻鍐欑紦瀛�"锛夛紱鈶� `涓彛寰勬槑缁哷 琛ㄥご琛岃瘑鍒繑鍥� `[2,4]` 寰呮牳鏌ワ紱鈶� 鈶¤〃锛堝叏鍙ｅ緞锛�9 鏈堟湭瀵煎叆鍓嶏紝"鍏ㄥ彛寰�"琛屾樉绀虹瓑浜庤涓婏紙鍏紡 =瑙勪笂+瑙勪笅銆佽涓嬩负绌猴級锛屼綋妫�宸叉彁绀猴紝瀵兼暟鎹悗鑷姩姝ｅ父銆�
+- **鎶ュ憡锛堟湰鍦般�佷笉鍏ュ簱锛�**锛歚docs/鍔熻兘娴嬭瘯鎶ュ憡/鍔熻兘娴嬭瘯鎶ュ憡_鎵╁垪鍏紡鍏嬮殕淇涓�9鏈堥獙璇乢2026-10-12.md`銆傜姸鎬侊細**銆愬緟楠屾敹銆�**銆�
## 鏈�鏂帮紙2026-10-12锛寊yj 绾匡級
### 10-12锛氭墿鍒楀櫒琛ャ�屾爣棰樻寜姣嶇増鏈熸閲嶅啓銆�+ 淇 8 鏈堟瘝鐗堟爣棰樸�愬緟楠屾敹銆�
diff --git "a/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/2026\345\271\2649\346\234\210\351\201\223\350\267\257\350\277\220\350\276\223\351\207\217\346\261\207\346\200\273\350\241\250.xlsx" "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/2026\345\271\2649\346\234\210\351\201\223\350\267\257\350\277\220\350\276\223\351\207\217\346\261\207\346\200\273\350\241\250.xlsx"
new file mode 100644
index 0000000..b8ad248
--- /dev/null
+++ "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/2026\345\271\2649\346\234\210\351\201\223\350\267\257\350\277\220\350\276\223\351\207\217\346\261\207\346\200\273\350\241\250.xlsx"
Binary files differ
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
index 2f98afa..20c94ff 100644
--- 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
@@ -208,8 +208,8 @@
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);
+ cloneDataCell(row, srcVal, insertCol, shiftable);
+ cloneYoyCell(row, srcYoy, insertCol + 1, insertCol, lastYearSameMonth + 2);
}
// 5) 绱鍒楄ˉ褰撴湀椤癸紱绱鍚屾瘮鍒嗘瘝琛ュ幓骞村悓鏈堝垪
int cumCol = insertCol + 2, cumYoyCol = insertCol + 3;
@@ -348,17 +348,47 @@
}
}
- private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable, int insertCol) {
+ private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable) {
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));
+ d.setCellFormula(shiftRefsAll(s.getCellFormula(), shiftable));
}
}
+ /** 褰撴湀鍚屾瘮鍒楋細涓嶅厠闅嗕笂鏈堝叕寮忥紝鐩存帴鎸夈�屾湰鏈堝�� / 鍘诲勾鍚屾湀 鈭� 1銆嶉噸鍐欙紙鍘诲勾鍚屾湀鍒楀凡鎸夎〃澶村畾浣嶏級 */
+ private void cloneYoyCell(Row row, int srcCol, int dstCol, int valCol0, int denCol0) {
+ 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) {
+ int excelRow = row.getRowNum() + 1;
+ d.setCellFormula(colLetter(valCol0) + excelRow + "/" + colLetter(denCol0) + excelRow + "-1");
+ }
+ }
+
+ /** 鐩稿澶嶅埗鐢ㄧ殑骞崇Щ锛氭妸鏈〉/宸叉墿鍒楅〉鐨勫紩鐢ㄦ暣浣� +2锛堜笉鍋氬垪鍙烽槇鍊煎垽鏂級 */
+ private String shiftRefsAll(String formula, 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));
+ m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
private void shiftAllRefs(Sheet sh, int insertCol, Set<String> shiftable) {
for (int r = 0; r <= sh.getLastRowNum(); r++) {
Row row = sh.getRow(r);
--
Gitblit v1.9.1