From 80e08795ef502404dd4e343a9779c68cd8567846 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期三, 16 九月 2026 17:31:28 +0800
Subject: [PATCH] fix(summary): 修 2026-07 导出件「Excel 打不开」+ 补齐 7 月母版 2021/2022/2023 累计列
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 841 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 839 insertions(+), 2 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 fb82de4..46ae09f 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
@@ -53,6 +53,7 @@
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
+import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
@@ -3347,15 +3348,847 @@
cleanSummarySheetPresentation(wb); // 鍙栨秷鍚勯〉绛涢�夈�佽繕鍘熼殣钘忚锛堝惁鍒欌�滅彮绾垮寘杞︹�濈瓑椤电湅璧锋潵鍍忕己鏁版嵁锛�
int effMonth = (toMonth != null && toMonth > 0 && toMonth < month) ? toMonth : month;
List<String> dbProblems = new ArrayList<>();
- int dbFilled = summaryWorkbookFiller.fillMonthlyLedger(wb, year, effMonth, dbProblems);
- log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ彴璐﹂〉褰撴湀鍊兼牸鏁�={}锛岀己鏁版彁绀�={}", dbFilled, dbProblems);
+ String effPeriod = String.format("%04d-%02d", year, effMonth);
+ int dbFilled = summaryWorkbookFiller.fillMonthlyLedger(wb, effPeriod, year, effMonth, dbProblems);
+ int yoyFilled = summaryWorkbookFiller.ensureMonthYoyFormulas(wb, year, effMonth);
+ int monthFormulaFilled = summaryWorkbookFiller.ensureMonthFormulas(wb, year, effMonth);
+ int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month");
+ int cumulativeRefreshed = refreshSummaryCumulativeSheets(wb, year, effMonth); // 鍥涘紶璺ㄩ〉鍚堟垚椤甸殢鎶ヨ〃鏈熷埛鏂�
+ int widthApplied = applySummaryReferenceLayout(wb);
+ log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ綋鏈堝�兼牸鏁�={}锛岃ˉ鍐欏綋鏈堝悓姣斿叕寮忔牸鏁�={}锛岃ˉ鍐欏綋鏈堝悎璁″叕寮忔牸鏁�={}锛�"
+ + "鍒锋柊鎺掑悕椤垫暟鍊兼牸鏁�={}锛屽埛鏂板洓椤碉紙鍩庡競姹囨��/鍩庡競鍒嗗競宸�/涓彛寰勫垎鏋�/閬撹矾杩愯緭鍛ㄨ浆閲忥級鏁板�兼牸鏁�={}锛�"
+ + "鎸夈�婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪榻愬垪瀹藉垪鏁�={}锛岀己鏁版彁绀�={}",
+ dbFilled, yoyFilled, monthFormulaFilled, rankRefreshed, cumulativeRefreshed, widthApplied, dbProblems);
applyTwoDecimalFormat(wb); // 鏁板�兼樉绀轰袱浣嶅皬鏁帮紙淇濈暀鍏ㄧ簿搴︼級锛�% / 鏃ユ湡绛夋棦鏈夋牱寮忎笉鏀瑰彉
+ int inlineFixed = normalizeStaleInlineStrings(wb); // 娓呮帀銆岄潪 inlineStr 鍗存畫鐣� <is>銆嶇殑闈炴硶鏍硷紝鍚﹀垯 Excel 鎷掔粷鎵撳紑
+ if (inlineFixed > 0) log.warn("姹囨�诲伐浣滅翱娓呮帀娈嬬暀 inlineStr 闈炴硶鏍� {} 涓紙姣嶇増鐢� openpyxl 鍐欏嚭鏃剁殑鍏稿瀷闂锛�", inlineFixed);
if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
return toBytes(wb);
} finally {
ZipSecureFile.setMinInflateRatio(savedZipRatio);
}
+ }
+
+ /**
+ * 娓呮帀銆岄潪 inlineStr 鍗存畫鐣� <is>銆嶇殑闈炴硶鍗曞厓鏍笺��
+ *
+ * 姣嶇増鑻ョ敱 openpyxl 鍐欏嚭锛屾枃鏈崟鍏冩牸鐢� <is>锛坕nlineStr锛夊瓨鍌紱POI 鐨� setCellValue 鍙敼鍐� t 涓� <v>锛�
+ * 涓嶄細娓呮帀鏃� <is>锛屼簬鏄骇鍑� <c t="n"><v>67.0</v><is><t>67.00</t></is></c> 杩欑闈炴硶鏍硷紝
+ * Excel 鍒ゅ畾涓恒�屾棤娉曡鍙栫殑鍐呭銆嶈�岀洿鎺ユ嫆缁濇墦寮�锛圕OM 琛ㄧ幇涓恒�屼笉鑳藉彇寰楃被 Workbooks 鐨� Open 灞炴�с�嶏級銆�
+ * 2026-07 瀵煎嚭浠跺嵆鍥犳鎵撲笉寮�锛堛�婂叕浜ゃ��17 鏍� +銆婅建閬撱�佽疆娓°��2 鏍硷級銆傝惤鐩樺墠缁熶竴娓呯悊锛屽箓绛夈��
+ */
+ private int normalizeStaleInlineStrings(XSSFWorkbook wb) {
+ int fixed = 0;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet ctw = wb.getSheetAt(i).getCTWorksheet();
+ if (ctw == null || ctw.getSheetData() == null) continue;
+ for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow row : ctw.getSheetData().getRowArray()) {
+ for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell c : row.getCArray()) {
+ if (!c.isSetIs()) continue;
+ STCellType.Enum t = c.getT();
+ if (t == null || !STCellType.INLINE_STR.equals(t)) {
+ c.unsetIs();
+ fixed++;
+ }
+ }
+ }
+ }
+ return fixed;
+ }
+
+ /**
+ * 鐗堝紡瀵归綈锛氭寜 docs/鐢熸垚姹囨�诲ぇ琛�/銆婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx銆嬶紙鐢ㄦ埛鎻愪緵鐨勭増寮忓弬鐓э級閫愬垪濂楃敤鍒楀涓庨殣钘忔爣璁帮紝
+ * 浣跨敓鎴愪欢涓庡弬鐓т欢鈥滄牸寮忓畬鍏ㄤ竴鑷粹�濓紝骞惰姣嶇増娌″畾涔夊搴︾殑鈥滃悗缁墿鍒椻�濓紙濡� 8 鏈堝垪锛変篃鏈変笌 8 鏈堜竴鑷寸殑鍒楀銆�
+ * 鍙傜収浠剁己澶辨椂闈欓粯璺宠繃锛堣繑鍥� 0锛夛紝涓嶅奖鍝嶅師鏈夊鍑恒��
+ */
+ private int applySummaryReferenceLayout(XSSFWorkbook wb) {
+ File ref;
+ try {
+ ref = resolveSummaryLayoutRef();
+ } catch (Exception e) {
+ return 0;
+ }
+ if (ref == null) return 0;
+ double savedZipRatio = ZipSecureFile.getMinInflateRatio();
+ ZipSecureFile.setMinInflateRatio(0.0001);
+ int applied = 0;
+ try (InputStream in = new FileInputStream(ref); XSSFWorkbook rwb = new XSSFWorkbook(in)) {
+ for (int i = 0; i < rwb.getNumberOfSheets(); i++) {
+ org.apache.poi.xssf.usermodel.XSSFSheet rs = rwb.getSheetAt(i);
+ if (rs == null) continue;
+ org.apache.poi.xssf.usermodel.XSSFSheet ts = wb.getSheet(rs.getSheetName());
+ if (ts == null) continue;
+ int maxCol = Math.min(usedColumnCount(rs), usedColumnCount(ts) + 4);
+ if (maxCol <= 0) continue;
+ double[] width = new double[maxCol];
+ boolean[] hasWidth = new boolean[maxCol];
+ boolean[] hidden = new boolean[maxCol];
+ for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols cols : rs.getCTWorksheet().getColsArray()) {
+ for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol col : cols.getColArray()) {
+ long min = col.getMin();
+ long max = col.getMax();
+ for (long c = min; c <= max; c++) {
+ int idx = (int) (c - 1);
+ if (idx < 0 || idx >= maxCol) {
+ if (idx >= maxCol) break;
+ continue;
+ }
+ if (col.isSetWidth()) {
+ width[idx] = col.getWidth();
+ hasWidth[idx] = true;
+ }
+ hidden[idx] = col.isSetHidden() && col.getHidden();
+ }
+ }
+ }
+ for (int c = 0; c < maxCol; c++) {
+ if (hasWidth[c]) {
+ int w = (int) Math.round(width[c] * 256d);
+ if (ts.getColumnWidth(c) != w) {
+ ts.setColumnWidth(c, w);
+ applied++;
+ }
+ }
+ if (hidden[c] != ts.isColumnHidden(c)) ts.setColumnHidden(c, hidden[c]);
+ }
+ }
+ log.info("姹囨�诲伐浣滅翱鐗堝紡瀵归綈銆婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪畬鎴愶細濂楃敤鍒楀 {} 鍒楋紙鍙傜収浠讹細{}锛�", applied, ref.getName());
+ } catch (Exception e) {
+ log.warn("姹囨�诲伐浣滅翱鐗堝紡瀵归綈澶辫触锛堝拷鐣ワ紝缁х画鎸夋瘝鐗堢増寮忚緭鍑猴級锛歿}", e.getMessage());
+ return 0;
+ } finally {
+ ZipSecureFile.setMinInflateRatio(savedZipRatio);
+ }
+ return applied;
+ }
+
+ /** 涓�椤靛凡鐢ㄥ埌鐨勬渶澶у垪鏁帮紙0 鍩鸿鏁帮級 */
+ private int usedColumnCount(org.apache.poi.ss.usermodel.Sheet sh) {
+ int max = 0;
+ for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
+ org.apache.poi.ss.usermodel.Row row = sh.getRow(r);
+ if (row == null) continue;
+ if (row.getLastCellNum() > max) max = row.getLastCellNum();
+ }
+ return max;
+ }
+
+ // ==================== 姹囨�诲伐浣滅翱鍐呯殑鎺掑悕椤靛埛鏂� ====================
+
+ /**
+ * 姹囨�诲伐浣滅翱閲岀殑銆岃揣杩愰噺鎺掑悕 / 璐ц繍鍛ㄨ浆閲忔帓鍚� / 涓彛寰勬帓鍚嶃�嶅湪姣嶇増閲屾槸涓婁竴鎶ヨ〃鏈熺殑闈欐�佸�硷紝
+ * 瀵煎嚭鏃剁敤鐙珛鎺掑悕琛紙銆婄敓鎴恄璐ц繍閲忔帓鍚嶃�嬨�婄敓鎴恄鍛ㄨ浆閲忔帓鍚嶃�嬨�婄敓鎴恄涓彛寰勬帓鍚嶃�嬶紝涓庣敤鎴锋棩甯告牳瀵圭殑鍙ｅ緞涓�鑷达級
+ * 鐨勭粨鏋滃埛鏂版暟鍊硷紝閬垮厤姹囨�昏〃閲岀殑鎺掑悕椤靛仠鐣欏湪鏃ф湀浠姐��
+ * 鏁板�兼牸鎼�笺�佸叕寮忔牸鎸夊亸绉诲钩绉诲叕寮忓悗鍐欏叆锛涚洰鏍囨牸鏈韩宸叉槸鍏紡鐨勶紙SUM/RANK/鍗犳瘮/琛ユ暟锛変繚鐣欎笉鍔紝鎵撳紑鏃舵寜鏂版暟鎹噸绠椼��
+ */
+ private int refreshSummaryRankSheets(XSSFWorkbook wb, String period, String mode) throws Exception {
+ int n = 0;
+ n += copyRankValues(wb, "璐ц繍閲忔帓鍚�", exportFreightRank(period, mode), new int[][]{{0, 0}, {22, 0}});
+ n += copyRankValues(wb, "璐ц繍鍛ㄨ浆閲忔帓鍚�", exportTurnoverRank(period, mode), new int[][]{{0, 0}, {22, 0}});
+ n += copyRankValues(wb, "涓彛寰勬帓鍚�", exportPassengerMidRank(period, mode), new int[][]{{0, 0}, {0, 10}});
+ return n;
+ }
+
+ /**
+ * 鎶婄嫭绔嬫帓鍚嶈〃鐨勫悇涓潡鎸夋爣棰樺榻愭惉杩涙眹鎬诲ぇ琛ㄥ搴旈〉銆�
+ * srcBlocks[i] = 婧愬潡 i 鐨勮捣濮� {琛�, 鍒梷锛�0 鍩猴級锛涚洰鏍囧潡鎸夋爣棰橀『搴忥紙绱鍧楀湪鍓嶃�佸綋鏈堝潡鍦ㄥ悗锛夊搴斻��
+ * 鏁板�兼牸鐩存帴鎼�硷紱婧愪晶鏄叕寮忕殑锛堟帓鍚�/鍗犳瘮绛夛級鎸夌洰鏍囧潡鍋忕Щ鏁翠綋骞崇Щ鍚庡啓鍏ュ叕寮忥紝鎵撳紑鏃舵寜姹囨�昏〃鑷韩鏁版嵁閲嶇畻銆�
+ */
+ private int copyRankValues(XSSFWorkbook dstWb, String dstSheetName, byte[] srcBytes, int[][] srcBlocks) throws Exception {
+ XSSFSheet dst = dstWb.getSheet(dstSheetName);
+ if (dst == null) return 0;
+ List<XSSFCell> dstTitles = rankTitleCells(dst);
+ if (dstTitles.isEmpty()) return 0;
+ int copied = 0;
+ FormulaEvaluator dstEv = dstWb.getCreationHelper().createFormulaEvaluator();
+ try (InputStream sin = new ByteArrayInputStream(srcBytes);
+ XSSFWorkbook srcWb = new XSSFWorkbook(sin)) {
+ XSSFSheet src = srcWb.getSheetAt(0);
+ FormulaEvaluator ev = srcWb.getCreationHelper().createFormulaEvaluator();
+ int blocks = Math.min(srcBlocks.length, dstTitles.size());
+ for (int b = 0; b < blocks; b++) {
+ int sr0 = srcBlocks[b][0];
+ int sc0 = srcBlocks[b][1];
+ XSSFCell dstTitle = dstTitles.get(b);
+ int rowOff = dstTitle.getRowIndex() - sr0;
+ int colOff = dstTitle.getColumnIndex() - sc0;
+ int h = rankBlockHeight(src, sr0, sc0);
+ int w = rankBlockWidth(src, sr0, sc0);
+ for (int r = 0; r < h; r++) {
+ Row srcRow = src.getRow(sr0 + r);
+ if (srcRow == null) continue;
+ Row dstRow = dst.getRow(sr0 + r + rowOff);
+ if (dstRow == null) dstRow = dst.createRow(sr0 + r + rowOff);
+ for (int c = 0; c < w; c++) {
+ Cell sc = srcRow.getCell(sc0 + c);
+ if (sc == null) continue;
+ int dc = sc0 + c + colOff;
+ if (r == 0) {
+ // 鏍囬琛岋細鍚屾鍧楁爣棰樻枃妗堛�傛瘝鐗堟爣棰樻槸 inlineStr锛孭OI 鐨� setCellValue 鍙啓 <v>銆�
+ // 涓嶆洿鏂� <is>锛孍xcel 浼氱户缁樉绀烘棫鏈堜唤锛屽繀椤诲厛 setBlank 娓呮帀鍐嶅啓銆�
+ Cell dt = dstRow.getCell(dc);
+ if (dt == null) dt = rankCell(dstRow, dc);
+ if (sc.getCellType() == CellType.STRING && dt.getCellType() != CellType.FORMULA) {
+ String text = sc.getStringCellValue();
+ String old = dt.getCellType() == CellType.STRING ? dt.getStringCellValue() : null;
+ if (text != null && !text.equals(old)) {
+ dt.setBlank();
+ dt.setCellValue(text);
+ }
+ }
+ continue;
+ }
+ Cell dcCell = dstRow.getCell(dc);
+ if (dcCell != null && dcCell.getCellType() == CellType.FORMULA) continue; // 淇濈暀姣嶇増鑷繁鐨勫叕寮�
+ if (sc.getCellType() == CellType.FORMULA) {
+ // 婧愪晶鍏紡锛堟帓鍚�/鍗犳瘮锛夛細鎸夌洰鏍囧潡鍋忕Щ骞崇Щ鍚庡師鏍峰啓鍏ワ紝鍐嶆寜姹囨�昏〃鑷韩鏁版嵁姹傚�肩紦瀛樸��
+ // 涓嶇洿鎺ユ惉 POI 瀵规簮琛ㄧ殑姹傚�肩粨鏋滐紙濡� RANK 瀵规墜宸ョ暀绌虹殑鍚屾瘮涓�寰嬭繑鍥� 1锛夈��
+ String f = sc.getCellFormula();
+ if (f == null || f.isEmpty()) continue;
+ String shifted = shiftFormula(f, colOff, rowOff);
+ if (dcCell == null) dcCell = rankCell(dstRow, dc);
+ dcCell.setCellFormula(shifted);
+ try {
+ dstEv.evaluateFormulaCell(dcCell);
+ } catch (Exception ignore) {
+ // 姹傚�煎け璐ヤ笉闃诲瀵煎嚭锛氭墦寮�鏃剁敱 Excel/WPS 閲嶇畻
+ }
+ copied++;
+ continue;
+ }
+ Double v = rankNumeric(sc, ev);
+ if (v == null) continue;
+ if (dcCell == null) dcCell = rankCell(dstRow, dc);
+ dcCell.setCellValue(v);
+ copied++;
+ }
+ }
+ }
+ }
+ return copied;
+ }
+
+ /** 鐩爣鍧楃己鏍兼椂鏂板缓锛屽苟娌跨敤鍚岃宸﹂偦鏍兼牱寮忥紙闃叉柊鏍间涪杈规/鐧惧垎鏁版牸寮忥級 */
+ private Cell rankCell(Row row, int col) {
+ Cell c = row.createCell(col);
+ Cell left = col > 0 ? row.getCell(col - 1) : null;
+ if (left != null) c.setCellStyle(left.getCellStyle());
+ return c;
+ }
+
+ /** 鍏紡鍐呮墍鏈� A1 寮曠敤鏁翠綋骞崇Щ锛氬垪 +dCol銆佽 +dRow锛圫UM/RANK 绛夊嚱鏁板悕鍚庢帴鎷彿涓嶄細琚尮閰嶏級 */
+ private String shiftFormula(String formula, int dCol, int dRow) {
+ if (formula == null || (dCol == 0 && dRow == 0)) return formula;
+ java.util.regex.Matcher m = java.util.regex.Pattern
+ .compile("(?<![A-Za-z0-9_$])([$]?)([A-Z]{1,3})([$]?)([0-9]+)")
+ .matcher(formula);
+ StringBuffer sb = new StringBuffer();
+ while (m.find()) {
+ String col = m.group(2);
+ int idx = 0;
+ for (int i = 0; i < col.length(); i++) idx = idx * 26 + (col.charAt(i) - 'A' + 1);
+ int nidx = Math.max(1, idx + dCol);
+ StringBuilder nc = new StringBuilder();
+ while (nidx > 0) {
+ int rem = (nidx - 1) % 26;
+ nc.insert(0, (char) ('A' + rem));
+ nidx = (nidx - 1) / 26;
+ }
+ int nrow = Math.max(1, Integer.parseInt(m.group(4)) + dRow);
+ m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(m.group(1) + nc + m.group(3) + nrow));
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
+ /** 鎺掑悕椤甸噷鐨勨�滃潡鏍囬鈥濆崟鍏冩牸锛堝惈鈥滃叏鐪佸垎甯傚窞鈥濈殑璇存槑鏂囧瓧锛夛紝鎸夎銆佸垪椤哄簭杩斿洖 */
+ private List<XSSFCell> rankTitleCells(Sheet sh) {
+ List<XSSFCell> out = new ArrayList<>();
+ for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != CellType.STRING) continue;
+ String t = cell.getStringCellValue();
+ if (t != null && t.contains("鍏ㄧ渷鍒嗗競宸�")) out.add((XSSFCell) cell);
+ }
+ }
+ return out;
+ }
+
+ /** 鍧楅珮搴︼細浠庤捣濮嬭鍚戜笅鐩村埌鏁磋涓虹┖锛堝湪鍧楀垪鑼冨洿鍐咃級 */
+ private int rankBlockHeight(Sheet sh, int r0, int c0) {
+ int h = 0;
+ for (int r = r0; r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ boolean any = false;
+ if (row != null) {
+ int last = row.getLastCellNum();
+ for (int c = c0; c < last; c++) {
+ if (rankHasContent(row.getCell(c))) { any = true; break; }
+ }
+ }
+ if (!any) break;
+ h++;
+ }
+ return h;
+ }
+
+ /** 鍧楀搴︼細浠庤捣濮嬪垪鍚戝彸鐩村埌鏁村垪涓虹┖锛堝湪鍧楄鑼冨洿鍐咃紝鎵弿涓婇檺 40 鍒楋級 */
+ private int rankBlockWidth(Sheet sh, int r0, int c0) {
+ int w = 0;
+ for (int c = c0; c < c0 + 40; c++) {
+ boolean any = false;
+ for (int r = r0; r < r0 + 30; r++) {
+ Row row = sh.getRow(r);
+ if (row != null && rankHasContent(row.getCell(c))) { any = true; break; }
+ }
+ if (!any) break;
+ w++;
+ }
+ return w;
+ }
+
+ private boolean rankHasContent(Cell c) {
+ if (c == null) return false;
+ CellType t = c.getCellType();
+ if (t == CellType.BLANK) return false;
+ if (t == CellType.STRING) {
+ String v = c.getStringCellValue();
+ return v != null && !v.trim().isEmpty();
+ }
+ return true;
+ }
+
+ /** 鍙栧崟鍏冩牸鏁板�硷紙鍏紡鍙栨眰鍊肩粨鏋滐紝绾暟瀛楁枃鏈寜鏁板�艰В鏋愶級锛屽彇涓嶅埌杩斿洖 null */
+ private Double rankNumeric(Cell c, FormulaEvaluator ev) {
+ try {
+ CellType t = c.getCellType();
+ if (t == CellType.NUMERIC) return c.getNumericCellValue();
+ if (t == CellType.FORMULA) {
+ org.apache.poi.ss.usermodel.CellValue cv = ev.evaluate(c);
+ return cv != null && cv.getCellType() == CellType.NUMERIC ? cv.getNumberValue() : null;
+ }
+ if (t == CellType.STRING) {
+ String v = c.getStringCellValue();
+ if (v == null) return null;
+ String x = v.replace(",", "").trim();
+ if (x.isEmpty()) return null;
+ try { return Double.parseDouble(x); } catch (NumberFormatException ignore) { return null; }
+ }
+ } catch (Exception ignore) {
+ return null;
+ }
+ return null;
+ }
+
+ /** 瀹氫綅鐗堝紡鍙傜収浠� docs/鐢熸垚姹囨�诲ぇ琛�/鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx锛堜紭鍏堢骇鍚屾瘝鐗堬細閰嶇疆鐩綍 鈫� user.dir 鐩稿 鈫� 涓婄骇锛� */
+ // ==================== 姹囨�诲ぇ琛細鍥涘紶鈥滆法椤靛悎鎴愨�濋〉闅忔姤琛ㄦ湡鍒锋柊 ====================
+ // 鍩庡競姹囨�� / 鍩庡競鍒嗗競宸� / 涓彛寰勫垎鏋� / 閬撹矾杩愯緭鍛ㄨ浆閲� 姝ゅ墠鏁撮〉娌跨敤姣嶇増闈欐�佸�硷紙鏍囬鍋滃湪 1-7 鏈堬級銆�
+ // 鏈鎸� 2026-09-13 宸查獙璇佸彛寰勶紙涓� 8 鏈堟瘝鐗堥�愭牸 0 宸紓锛変粠姣嶇増鍙拌处椤电幇绠楋細
+ // 鍩庡競瀹㈣繍閲� = 鍩庡競鍐呭叕浜� + 鍩庡競鍐呭贰娓稿嚭绉� + 鍩庡競鍐呯綉绾﹁溅 + 杞ㄩ亾 + 杞浮
+ // 閬撹矾瀹㈣繍鍛ㄨ浆閲� = 鍏矾鐝嚎 + 鍩庨檯鍩庝埂鍏氦 + 鍩庨檯鍩庝埂宸℃父鍑虹 + 鍩庨檯鍩庝埂缃戠害杞�
+ // 閬撹矾璐ц繍鍛ㄨ浆閲� = 璐ц繍椤� 甯傚窞 瑙勪笂璐х墿鍛ㄨ浆閲� + 瑙勪笅璐х墿鍛ㄨ浆閲�
+ // 2026 骞� 1..N 鏈堝彇鍙拌处椤� 2026 鍒楋紙C/E/G鈥︼級锛屽幓骞村悓鏈熷彇姣嶇増缂撳瓨 2025 鍒楋紙V/X/Z鈥︼級鈥斺�斿簱閲屾病鏈� 2025 骞村煄甯傚杩愭暟鎹��
+
+ /** 鍩庡競瀹㈣繍鍙ｅ緞鎸囨爣浣嶏紙0..17 鍩庡競瀹㈣繍锛�18 璐ц繍鍛ㄨ浆閲忥級 */
+ private static final int SM_BUS_PAX = 0, SM_BUS_TURN = 1;
+ private static final int SM_TAXI_PAX = 2, SM_TAXI_TURN = 3;
+ private static final int SM_WYC_PAX = 4, SM_WYC_TURN = 5;
+ private static final int SM_RAIL_PAX = 6, SM_RAIL_TURN = 7;
+ private static final int SM_FERRY_PAX = 8, SM_FERRY_TURN = 9;
+ private static final int SM_BX_PAX = 10, SM_BX_TURN = 11;
+ private static final int SM_BUS_CX_PAX = 12, SM_BUS_CX_TURN = 13;
+ private static final int SM_TAXI_CX_PAX = 14, SM_TAXI_CX_TURN = 15;
+ private static final int SM_WYC_CX_PAX = 16, SM_WYC_CX_TURN = 17;
+ private static final int SM_FREIGHT_TURN = 18;
+ private static final int SM_DIM = 19;
+ /** 2026 骞� 1 鏈堝綋鏈堝�煎垪锛�0 鍩猴紝C 鍒楋級锛�2025 骞� 1 鏈堝綋鏈堝�煎垪锛�0 鍩猴紝V 鍒楋級 */
+ private static final int SM_CUR_COL0 = 2;
+ private static final int SM_LAST_COL0 = 21;
+
+ /**
+ * 鍒锋柊鍥涘紶璺ㄩ〉鍚堟垚椤碉細褰撳勾绱锛�1..month 鏈堬級銆佸綋鏈堬紙month 鏈堬級鍙婂搴斿悓姣旓紱
+ * 2026 涓� 2025 鍚屾湡閮藉彇鑷瘝鐗堝彴璐﹂〉缂撳瓨锛岃繑鍥炲啓鍏ュ崟鍏冩牸鏁般��
+ */
+ private int refreshSummaryCumulativeSheets(XSSFWorkbook wb, int year, int month) {
+ if (wb == null || month <= 0) return 0;
+ int[] cumMonths = new int[month];
+ for (int m = 1; m <= month; m++) cumMonths[m - 1] = m;
+ int[] oneMonth = new int[]{month};
+ Map<String, double[]> curCum = smReadLedgerMetrics(wb, true, cumMonths);
+ Map<String, double[]> lastCum = smReadLedgerMetrics(wb, false, cumMonths);
+ Map<String, double[]> curMon = smReadLedgerMetrics(wb, true, oneMonth);
+ Map<String, double[]> lastMon = smReadLedgerMetrics(wb, false, oneMonth);
+ if (curCum.isEmpty()) {
+ log.warn("姹囨�诲ぇ琛ㄥ洓椤靛埛鏂帮細鍙拌处椤垫湭璇诲埌甯傚窞鏁版嵁锛岃烦杩囷紙淇濇寔姣嶇増鍘熷�硷級");
+ return 0;
+ }
+ double[] pCurCum = smProvinceSum(curCum);
+ double[] pLastCum = smProvinceSum(lastCum);
+ double[] pCurMon = smProvinceSum(curMon);
+ double[] pLastMon = smProvinceSum(lastMon);
+ int n = 0;
+ n += smWriteCitySummary(wb, year, month, pCurCum, pLastCum, pCurMon, pLastMon);
+ n += smWriteCityByRegion(wb, month, curCum, lastCum, curMon, lastMon);
+ n += smWriteMidAnalysis(wb, month, pCurCum, pLastCum, pCurMon, pLastMon);
+ n += smWriteTurnoverSheets(wb, year, month, curCum, lastCum, curMon, lastMon);
+ return n;
+ }
+
+ /** 17 甯傚窞鍚堣锛堜笉鍚彴璐﹂〉鈥滃叏鐪佲�濆叕寮忚锛� */
+ private double[] smProvinceSum(Map<String, double[]> m) {
+ double[] p = new double[SM_DIM];
+ for (String c : RegionUtil.cityList()) {
+ double[] a = m.get(c);
+ if (a == null) continue;
+ for (int i = 0; i < SM_DIM; i++) p[i] += a[i];
+ }
+ return p;
+ }
+
+ /** 鍙拌处椤电疮璁″彇鏁帮細甯傚窞瑙勮寖鍚� -> 19 缁存寚鏍� */
+ private Map<String, double[]> smReadLedgerMetrics(XSSFWorkbook wb, boolean curYear, int[] months) {
+ Map<String, double[]> out = new LinkedHashMap<>();
+ smReadCityPaxPage(wb, "鍏氦", curYear, months, out, SM_BUS_PAX, SM_BUS_TURN, SM_BUS_CX_PAX, SM_BUS_CX_TURN);
+ smReadCityPaxPage(wb, "鍑虹", curYear, months, out, SM_TAXI_PAX, SM_TAXI_TURN, SM_TAXI_CX_PAX, SM_TAXI_CX_TURN);
+ smReadCityPaxPage(wb, "缃戠害杞�", curYear, months, out, SM_WYC_PAX, SM_WYC_TURN, SM_WYC_CX_PAX, SM_WYC_CX_TURN);
+ smReadRailFerry(wb, curYear, months, out);
+ smReadBanxian(wb, curYear, months, out);
+ smReadFreightTurnover(wb, curYear, months, out);
+ return out;
+ }
+
+ /** 鍏氦/鍑虹/缃戠害杞﹂〉锛氭瘡甯傚窞 4 琛岋紙瀹㈣繍閲�/鏃呭鍛ㄨ浆閲�/鍏朵腑鍩庡競鍐呭杩愰噺/鍏朵腑鍩庡競鍐呮梾瀹㈠懆杞噺锛� */
+ private void smReadCityPaxPage(XSSFWorkbook wb, String sheetName, boolean curYear, int[] months,
+ Map<String, double[]> out, int paxIdx, int turnIdx, int cxPaxIdx, int cxTurnIdx) {
+ Sheet sh = wb.getSheet(sheetName);
+ if (sh == null) return;
+ int last = sh.getLastRowNum();
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String city = smCityOf(row.getCell(0));
+ if (city == null) continue;
+ int paxRow = -1, turnRow = -1, cxPaxRow = -1, cxTurnRow = -1;
+ for (int k = r; k <= Math.min(last, r + 4); k++) {
+ Row kr = sh.getRow(k);
+ if (kr == null) continue;
+ String b = smText(kr.getCell(1));
+ if (b == null || b.contains("涓綋")) continue;
+ boolean inner = b.contains("鍩庡競鍐�");
+ boolean turn = b.contains("鍛ㄨ浆閲�");
+ if (inner) {
+ if (turn) { if (cxTurnRow < 0) cxTurnRow = k; }
+ else if (cxPaxRow < 0) cxPaxRow = k;
+ } else {
+ if (turn) { if (turnRow < 0) turnRow = k; }
+ else if (paxRow < 0) paxRow = k;
+ }
+ }
+ double totalPax = smSumMonths(sh, paxRow, curYear, months);
+ double totalTurn = smSumMonths(sh, turnRow, curYear, months);
+ double cityPax = smSumMonths(sh, cxPaxRow, curYear, months);
+ double cityTurn = smSumMonths(sh, cxTurnRow, curYear, months);
+ double[] arr = out.computeIfAbsent(city, k -> new double[SM_DIM]);
+ arr[paxIdx] += cityPax; // 鍩庡競瀹㈣繍鍙ｅ緞鍙栤�滃煄甯傚唴鈥�
+ arr[turnIdx] += cityTurn;
+ arr[cxPaxIdx] += totalPax - cityPax; // 鍩庨檯鍩庝埂 = 鎬婚噺 - 鍩庡競鍐�
+ arr[cxTurnIdx] += totalTurn - cityTurn;
+ }
+ }
+
+ /** 杞ㄩ亾銆佽疆娓￠〉锛氳建閬撳潡锛堟姹�/榛勭煶锛�+ 杞浮鍧楋紙姝︽眽锛� */
+ private void smReadRailFerry(XSSFWorkbook wb, boolean curYear, int[] months, Map<String, double[]> out) {
+ Sheet sh = wb.getSheet("杞ㄩ亾銆佽疆娓�");
+ if (sh == null) return;
+ int last = sh.getLastRowNum();
+ int ferryTitle = -1;
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String a = smText(row.getCell(0));
+ if (a != null && a.contains("杞浮瀹㈣繍閲�")) { ferryTitle = r; break; }
+ }
+ smReadRailFerryBlock(sh, curYear, months, out, sh.getFirstRowNum(),
+ ferryTitle < 0 ? last : ferryTitle - 1, SM_RAIL_PAX);
+ if (ferryTitle >= 0) smReadRailFerryBlock(sh, curYear, months, out, ferryTitle, last, SM_FERRY_PAX);
+ }
+
+ private void smReadRailFerryBlock(Sheet sh, boolean curYear, int[] months, Map<String, double[]> out,
+ int start, int end, int baseIdx) {
+ for (int r = start; r <= end; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String city = smCityOf(row.getCell(0));
+ if (city == null) continue;
+ int paxRow = -1, turnRow = -1;
+ for (int k = r; k <= Math.min(end, r + 3); k++) {
+ Row kr = sh.getRow(k);
+ if (kr == null) continue;
+ String b = smText(kr.getCell(1));
+ if (b == null) continue;
+ if (b.contains("鍛ㄨ浆閲�")) { if (turnRow < 0) turnRow = k; }
+ else if (paxRow < 0) paxRow = k;
+ }
+ double[] arr = out.computeIfAbsent(city, k -> new double[SM_DIM]);
+ arr[baseIdx] += smSumMonths(sh, paxRow, curYear, months);
+ arr[baseIdx + 1] += smSumMonths(sh, turnRow, curYear, months);
+ }
+ }
+
+ /** 鐝嚎鍖呰溅椤碉細姣忓競宸� 瀹㈣繍閲� / 鏃呭鍛ㄨ浆閲忥紙鈥滃叾涓釜浣撯�濊涓嶉噸澶嶈鍏ワ級 */
+ private void smReadBanxian(XSSFWorkbook wb, boolean curYear, int[] months, Map<String, double[]> out) {
+ Sheet sh = wb.getSheet("鐝嚎鍖呰溅");
+ if (sh == null) return;
+ int last = sh.getLastRowNum();
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String city = smCityOf(row.getCell(0));
+ if (city == null) continue;
+ int paxRow = -1, turnRow = -1;
+ for (int k = r; k <= Math.min(last, r + 4); k++) {
+ Row kr = sh.getRow(k);
+ if (kr == null) continue;
+ String b = smText(kr.getCell(1));
+ if (b == null || b.contains("涓綋")) continue;
+ if (b.contains("鍛ㄨ浆閲�")) { if (turnRow < 0) turnRow = k; }
+ else if (b.contains("瀹㈣繍閲�") && paxRow < 0) paxRow = k;
+ }
+ double[] arr = out.computeIfAbsent(city, k -> new double[SM_DIM]);
+ arr[SM_BX_PAX] += smSumMonths(sh, paxRow, curYear, months);
+ arr[SM_BX_TURN] += smSumMonths(sh, turnRow, curYear, months);
+ }
+ }
+
+ /** 璐ц繍椤碉細姣忓競宸� 瑙勪笂璐х墿鍛ㄨ浆閲� + 瑙勪笅璐х墿鍛ㄨ浆閲忥紙鈥滆涓�+瑙勪笅鈥濊鏈韩鏄眰鍜屽叕寮忥紝涓嶇洿鎺ュ彇锛� */
+ private void smReadFreightTurnover(XSSFWorkbook wb, boolean curYear, int[] months, Map<String, double[]> out) {
+ Sheet sh = wb.getSheet(" 璐ц繍");
+ if (sh == null) sh = wb.getSheet("璐ц繍");
+ if (sh == null) return;
+ int last = sh.getLastRowNum();
+ for (int r = sh.getFirstRowNum(); r <= last; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String city = smCityOf(row.getCell(0));
+ if (city == null) continue;
+ int above = -1, below = -1;
+ for (int k = r; k <= Math.min(last, r + 6); k++) {
+ Row kr = sh.getRow(k);
+ if (kr == null) continue;
+ String b = smText(kr.getCell(1));
+ if (b == null || b.contains("鍏朵腑")) continue;
+ if (b.contains("瑙勪笂璐х墿鍛ㄨ浆閲�")) { if (above < 0) above = k; }
+ else if (b.contains("瑙勪笅璐х墿鍛ㄨ浆閲�")) { if (below < 0) below = k; }
+ }
+ double[] arr = out.computeIfAbsent(city, k -> new double[SM_DIM]);
+ arr[SM_FREIGHT_TURN] += smSumMonths(sh, above, curYear, months) + smSumMonths(sh, below, curYear, months);
+ }
+ }
+
+ private double smSumMonths(Sheet sh, int rowIdx, boolean curYear, int[] months) {
+ if (sh == null || rowIdx < 0) return 0;
+ Row row = sh.getRow(rowIdx);
+ if (row == null) return 0;
+ double s = 0;
+ for (int m : months) {
+ int col = (curYear ? SM_CUR_COL0 : SM_LAST_COL0) + 2 * (m - 1);
+ s += smNum(row.getCell(col));
+ }
+ return s;
+ }
+
+ /** 鍙拌处椤靛崟鍏冩牸鍙栨暟锛堟暟瀛� / 鏁板瓧鏂囨湰 / 鍏紡缂撳瓨锛� */
+ private double smNum(Cell c) {
+ if (c == null) return 0;
+ try {
+ CellType t = c.getCellType();
+ if (t == CellType.NUMERIC) return c.getNumericCellValue();
+ if (t == CellType.FORMULA) {
+ return c.getCachedFormulaResultType() == CellType.NUMERIC ? c.getNumericCellValue() : 0;
+ }
+ if (t == CellType.STRING) {
+ String s = c.getStringCellValue();
+ if (s == null) return 0;
+ s = s.replace(",", "").replace("%", "").trim();
+ if (s.isEmpty()) return 0;
+ return Double.parseDouble(s);
+ }
+ } catch (Exception ignore) {
+ return 0;
+ }
+ return 0;
+ }
+
+ private String smText(Cell c) {
+ if (c == null) return null;
+ try {
+ return c.getCellType() == CellType.STRING ? c.getStringCellValue() : null;
+ } catch (Exception e) {
+ return null;
+ }
+ }
+
+ /** 鍒� A 鏂囨 -> 瑙勮寖甯傚窞鍚嶏紙鈥滃叏鐪�/婀栧寳鐪佲�濅笌闈炲競宸炶杩斿洖 null锛� */
+ private String smCityOf(Cell a) {
+ String t = smText(a);
+ if (t == null || t.trim().isEmpty()) return null;
+ String norm = RegionUtil.normalizeCityName(t.trim());
+ if (norm == null) return null;
+ return RegionUtil.cityList().contains(norm) ? norm : null;
+ }
+
+ private double smYoy(double cur, double last) {
+ return last == 0.0 ? 0.0 : (cur - last) / last;
+ }
+
+ /** 鍐欏�硷細鍏紡鏍间笉鍔紙淇濈暀姣嶇増鍏紡锛屾墦寮�鍗抽噸绠楋級锛�0 涓斿師涓虹┖鏍煎垯涓嶆柊澧� */
+ private int smSetVal(Sheet sh, int r0, int c0, double v) {
+ Row row = sh.getRow(r0);
+ if (row == null) return 0;
+ Cell c = row.getCell(c0);
+ if (c != null && c.getCellType() == CellType.FORMULA) return 0;
+ if (c == null) {
+ if (v == 0.0) return 0;
+ c = row.createCell(c0);
+ Cell left = row.getCell(c0 - 1);
+ if (left != null) c.setCellStyle(left.getCellStyle());
+ } else if (v == 0.0 && c.getCellType() == CellType.BLANK) {
+ return 0;
+ }
+ c.setBlank();
+ c.setCellValue(v);
+ return 1;
+ }
+
+ private void smSetTitle(Sheet sh, int r0, int c0, String text) {
+ if (sh == null) return;
+ Row row = sh.getRow(r0);
+ if (row == null) return;
+ Cell c = row.getCell(c0);
+ if (c == null || c.getCellType() == CellType.FORMULA) return;
+ c.setBlank();
+ c.setCellValue(text);
+ }
+
+ /** 鍩庡競姹囨�婚〉锛氬潡1锛�1..N 鏈堢疮璁★級琛� 3-9锛屽潡2锛堝綋鏈堬級琛� 14-20锛涘垪 B/C/D/E = 瀹㈣繍閲�/鍚屾瘮/鍛ㄨ浆閲�/鍚屾瘮 */
+ private int smWriteCitySummary(XSSFWorkbook wb, int year, int month,
+ double[] pCurCum, double[] pLastCum, double[] pCurMon, double[] pLastMon) {
+ Sheet sh = wb.getSheet("鍩庡競姹囨��");
+ if (sh == null) return 0;
+ smSetTitle(sh, 0, 0, year + "骞�1-" + month + "鏈堝叏鐪佺疮璁″畬鎴愬煄甯傚杩愰噺鎯呭喌");
+ smSetTitle(sh, 11, 0, year + "骞�" + month + "鏈堝叏鐪佸畬鎴愬煄甯傚杩愰噺鎯呭喌");
+ int n = 0;
+ int[] rows = {2, 3, 4, 5, 6, 7, 8};
+ for (int i = 0; i < rows.length; i++) {
+ double[] v = smCitySummaryRow(pCurCum, pLastCum, i);
+ n += smSetVal(sh, rows[i], 1, v[0]);
+ n += smSetVal(sh, rows[i], 2, v[2]);
+ n += smSetVal(sh, rows[i], 3, v[1]);
+ n += smSetVal(sh, rows[i], 4, v[3]);
+ }
+ for (int i = 0; i < rows.length; i++) {
+ double[] v = smCitySummaryRow(pCurMon, pLastMon, i);
+ n += smSetVal(sh, rows[i] + 11, 1, v[0]);
+ n += smSetVal(sh, rows[i] + 11, 2, v[2]);
+ n += smSetVal(sh, rows[i] + 11, 3, v[1]);
+ n += smSetVal(sh, rows[i] + 11, 4, v[3]);
+ }
+ return n;
+ }
+
+ /** i=0 鎬诲杩愰噺 1 鍏氦 2 鍑虹杞� 3 宸℃父鍑虹 4 缃戠害杞� 5 杞ㄩ亾 6 杞浮锛涜繑鍥� [瀹㈣繍閲�, 鍛ㄨ浆閲�, 瀹㈣繍閲忓悓姣�, 鍛ㄨ浆閲忓悓姣擼 */
+ private double[] smCitySummaryRow(double[] p, double[] pl, int i) {
+ double pax, turn, lPax, lTurn;
+ if (i == 0) {
+ pax = p[SM_BUS_PAX] + p[SM_TAXI_PAX] + p[SM_WYC_PAX] + p[SM_RAIL_PAX] + p[SM_FERRY_PAX];
+ turn = p[SM_BUS_TURN] + p[SM_TAXI_TURN] + p[SM_WYC_TURN] + p[SM_RAIL_TURN] + p[SM_FERRY_TURN];
+ lPax = pl[SM_BUS_PAX] + pl[SM_TAXI_PAX] + pl[SM_WYC_PAX] + pl[SM_RAIL_PAX] + pl[SM_FERRY_PAX];
+ lTurn = pl[SM_BUS_TURN] + pl[SM_TAXI_TURN] + pl[SM_WYC_TURN] + pl[SM_RAIL_TURN] + pl[SM_FERRY_TURN];
+ } else if (i == 1) {
+ pax = p[SM_BUS_PAX]; turn = p[SM_BUS_TURN]; lPax = pl[SM_BUS_PAX]; lTurn = pl[SM_BUS_TURN];
+ } else if (i == 2) {
+ pax = p[SM_TAXI_PAX] + p[SM_WYC_PAX]; turn = p[SM_TAXI_TURN] + p[SM_WYC_TURN];
+ lPax = pl[SM_TAXI_PAX] + pl[SM_WYC_PAX]; lTurn = pl[SM_TAXI_TURN] + pl[SM_WYC_TURN];
+ } else if (i == 3) {
+ pax = p[SM_TAXI_PAX]; turn = p[SM_TAXI_TURN]; lPax = pl[SM_TAXI_PAX]; lTurn = pl[SM_TAXI_TURN];
+ } else if (i == 4) {
+ pax = p[SM_WYC_PAX]; turn = p[SM_WYC_TURN]; lPax = pl[SM_WYC_PAX]; lTurn = pl[SM_WYC_TURN];
+ } else if (i == 5) {
+ pax = p[SM_RAIL_PAX]; turn = p[SM_RAIL_TURN]; lPax = pl[SM_RAIL_PAX]; lTurn = pl[SM_RAIL_TURN];
+ } else {
+ pax = p[SM_FERRY_PAX]; turn = p[SM_FERRY_TURN]; lPax = pl[SM_FERRY_PAX]; lTurn = pl[SM_FERRY_TURN];
+ }
+ return new double[]{pax, turn, smYoy(pax, lPax), smYoy(turn, lTurn)};
+ }
+
+ /** 鍩庡競鍒嗗競宸為〉锛�4 鍧楋紙绱瀹㈣繍閲�/绱鍛ㄨ浆閲�/褰撴湀瀹㈣繍閲�/褰撴湀鍛ㄨ浆閲忥級锛屾瘡鍧� 鍏ㄧ渷 1 琛� + 17 甯傚窞 */
+ private int smWriteCityByRegion(XSSFWorkbook wb, int month,
+ Map<String, double[]> curCum, Map<String, double[]> lastCum,
+ Map<String, double[]> curMon, Map<String, double[]> lastMon) {
+ Sheet sh = wb.getSheet("鍩庡競鍒嗗競宸�");
+ if (sh == null) return 0;
+ smSetTitle(sh, 0, 0, "鍩庡競瀹㈣繍瀹㈣繍閲忓垎甯傚窞1-" + month + "鏈堢疮璁℃儏鍐�");
+ smSetTitle(sh, 22, 0, "鍩庡競瀹㈣繍瀹㈣繍鍛ㄨ浆閲忓垎甯傚窞1-" + month + "鏈堢疮璁℃儏鍐�");
+ smSetTitle(sh, 45, 0, "鍩庡競瀹㈣繍瀹㈣繍閲忓垎甯傚窞" + month + "鏈堟儏鍐�");
+ smSetTitle(sh, 67, 0, "鍩庡競瀹㈣繍瀹㈣繍鍛ㄨ浆閲忓垎甯傚窞" + month + "鏈堟儏鍐�");
+ int n = 0;
+ n += smWriteCityByRegionBlock(sh, 4, true, curCum, lastCum);
+ n += smWriteCityByRegionBlock(sh, 26, false, curCum, lastCum);
+ n += smWriteCityByRegionBlock(sh, 49, true, curMon, lastMon);
+ n += smWriteCityByRegionBlock(sh, 71, false, curMon, lastMon);
+ return n;
+ }
+
+ private int smWriteCityByRegionBlock(Sheet sh, int provRow, boolean pax,
+ Map<String, double[]> cur, Map<String, double[]> last) {
+ int n = 0;
+ double[] provCur = smProvinceSum(cur);
+ double[] provLast = smProvinceSum(last);
+ List<String> cities = RegionUtil.cityList();
+ for (int i = -1; i < cities.size(); i++) {
+ int r = provRow + i + 1;
+ double[] c = i < 0 ? provCur : cur.get(cities.get(i));
+ double[] l = i < 0 ? provLast : last.get(cities.get(i));
+ if (c == null) c = new double[SM_DIM];
+ if (l == null) l = new double[SM_DIM];
+ double[] g = smCityGroupValues(c, l, pax);
+ n += smSetVal(sh, r, 1, g[0]);
+ n += smSetVal(sh, r, 4, g[6]);
+ n += smSetVal(sh, r, 6, g[1]);
+ n += smSetVal(sh, r, 9, g[7]);
+ n += smSetVal(sh, r, 11, g[2]);
+ n += smSetVal(sh, r, 14, g[8]);
+ n += smSetVal(sh, r, 16, g[3]);
+ n += smSetVal(sh, r, 19, g[9]);
+ n += smSetVal(sh, r, 21, g[4]);
+ n += smSetVal(sh, r, 22, g[10]);
+ n += smSetVal(sh, r, 23, g[5]);
+ n += smSetVal(sh, r, 24, g[11]);
+ }
+ return n;
+ }
+
+ /** 6 缁勫�� + 6 缁勫閫燂細[鎬婚噺, 鍏氦, 鍑虹, 缃戠害杞�, 杞ㄩ亾, 杞浮, 鎬婚噺澧為��, 鍏氦鈥�, 杞浮] */
+ private double[] smCityGroupValues(double[] c, double[] l, boolean pax) {
+ int busI = pax ? SM_BUS_PAX : SM_BUS_TURN;
+ int taxiI = pax ? SM_TAXI_PAX : SM_TAXI_TURN;
+ int wycI = pax ? SM_WYC_PAX : SM_WYC_TURN;
+ int railI = pax ? SM_RAIL_PAX : SM_RAIL_TURN;
+ int ferryI = pax ? SM_FERRY_PAX : SM_FERRY_TURN;
+ double bus = c[busI], taxi = c[taxiI], wyc = c[wycI], rail = c[railI], ferry = c[ferryI];
+ double lBus = l[busI], lTaxi = l[taxiI], lWyc = l[wycI], lRail = l[railI], lFerry = l[ferryI];
+ double total = bus + taxi + wyc + rail + ferry;
+ double lTotal = lBus + lTaxi + lWyc + lRail + lFerry;
+ return new double[]{total, bus, taxi, wyc, rail, ferry,
+ smYoy(total, lTotal), smYoy(bus, lBus), smYoy(taxi, lTaxi), smYoy(wyc, lWyc),
+ smYoy(rail, lRail), smYoy(ferry, lFerry)};
+ }
+
+ /** 涓彛寰勫垎鏋愰〉锛氬潡1锛堢疮璁★級琛� 3-7锛屽潡2锛堝綋鏈堬級琛� 12-16锛涘垪 B/C/F/G = 瀹㈣繍閲�/鍚屾瘮/鍛ㄨ浆閲�/鍚屾瘮 */
+ private int smWriteMidAnalysis(XSSFWorkbook wb, int month,
+ double[] pCurCum, double[] pLastCum, double[] pCurMon, double[] pLastMon) {
+ Sheet sh = wb.getSheet("涓彛寰勫垎鏋�");
+ if (sh == null) return 0;
+ smSetTitle(sh, 0, 0, "1-" + month + "鏈堜腑鍙ｅ緞瀹㈣繍閲忓強鍛ㄨ浆閲�");
+ smSetTitle(sh, 9, 0, month + "鏈堜腑鍙ｅ緞瀹㈣繍閲忓強鍛ㄨ浆閲�");
+ int n = 0;
+ int[] rows = {2, 3, 4, 5, 6};
+ for (int i = 0; i < rows.length; i++) {
+ double[] v = smMidRow(pCurCum, pLastCum, i);
+ n += smSetVal(sh, rows[i], 1, v[0]);
+ n += smSetVal(sh, rows[i], 2, v[2]);
+ n += smSetVal(sh, rows[i], 5, v[1]);
+ n += smSetVal(sh, rows[i], 6, v[3]);
+ }
+ for (int i = 0; i < rows.length; i++) {
+ double[] v = smMidRow(pCurMon, pLastMon, i);
+ n += smSetVal(sh, rows[i] + 9, 1, v[0]);
+ n += smSetVal(sh, rows[i] + 9, 2, v[2]);
+ n += smSetVal(sh, rows[i] + 9, 5, v[1]);
+ n += smSetVal(sh, rows[i] + 9, 6, v[3]);
+ }
+ return n;
+ }
+
+ /** i=0 鎬� 1 鍏矾鐝嚎 2 鍩庨檯鍩庝埂鍏氦 3 鍩庨檯鍩庝埂宸℃父鍑虹 4 鍩庨檯鍩庝埂缃戠害杞︼紱杩斿洖 [瀹㈣繍閲�, 鍛ㄨ浆閲�, 瀹㈣繍閲忓悓姣�, 鍛ㄨ浆閲忓悓姣擼 */
+ private double[] smMidRow(double[] p, double[] pl, int i) {
+ int[] paxIdx = {SM_BX_PAX, SM_BX_PAX, SM_BUS_CX_PAX, SM_TAXI_CX_PAX, SM_WYC_CX_PAX};
+ int[] turnIdx = {SM_BX_TURN, SM_BX_TURN, SM_BUS_CX_TURN, SM_TAXI_CX_TURN, SM_WYC_CX_TURN};
+ double pax, turn, lPax, lTurn;
+ if (i == 0) {
+ pax = p[SM_BX_PAX] + p[SM_BUS_CX_PAX] + p[SM_TAXI_CX_PAX] + p[SM_WYC_CX_PAX];
+ turn = p[SM_BX_TURN] + p[SM_BUS_CX_TURN] + p[SM_TAXI_CX_TURN] + p[SM_WYC_CX_TURN];
+ lPax = pl[SM_BX_PAX] + pl[SM_BUS_CX_PAX] + pl[SM_TAXI_CX_PAX] + pl[SM_WYC_CX_PAX];
+ lTurn = pl[SM_BX_TURN] + pl[SM_BUS_CX_TURN] + pl[SM_TAXI_CX_TURN] + pl[SM_WYC_CX_TURN];
+ } else {
+ pax = p[paxIdx[i]]; turn = p[turnIdx[i]];
+ lPax = pl[paxIdx[i]]; lTurn = pl[turnIdx[i]];
+ }
+ return new double[]{pax, turn, smYoy(pax, lPax), smYoy(turn, lTurn)};
+ }
+
+ /** 閬撹矾杩愯緭鍛ㄨ浆閲忛〉锛氬潡1锛堢疮璁★級琛� 4-21锛屽潡2锛堝綋鏈堬級琛� 26-43锛涘彧鍐欏�煎垪锛圔/E/V 绛夊叕寮忎繚鐣欙級 */
+ private int smWriteTurnoverSheets(XSSFWorkbook wb, int year, int month,
+ Map<String, double[]> curCum, Map<String, double[]> lastCum,
+ Map<String, double[]> curMon, Map<String, double[]> lastMon) {
+ Sheet sh = wb.getSheet("閬撹矾杩愯緭鍛ㄨ浆閲�");
+ if (sh == null) return 0;
+ smSetTitle(sh, 0, 0, year + "骞�1-" + month + "鏈堝叏鐪佺疮璁″畬鎴愪氦閫氬懆杞噺鎯呭喌");
+ smSetTitle(sh, 22, 0, year + "骞�" + month + "鏈堝叏鐪佸畬鎴愪氦閫氬懆杞噺鎯呭喌");
+ int n = 0;
+ n += smWriteTurnoverBlock(sh, 3, curCum, lastCum);
+ n += smWriteTurnoverBlock(sh, 25, curMon, lastMon);
+ return n;
+ }
+
+ private int smWriteTurnoverBlock(Sheet sh, int provRow, Map<String, double[]> cur, Map<String, double[]> last) {
+ int n = 0;
+ double[] provCur = smProvinceSum(cur);
+ double[] provLast = smProvinceSum(last);
+ List<String> cities = RegionUtil.cityList();
+ for (int i = -1; i < cities.size(); i++) {
+ int r = provRow + i + 1;
+ double[] c = i < 0 ? provCur : cur.get(cities.get(i));
+ double[] l = i < 0 ? provLast : last.get(cities.get(i));
+ if (c == null) c = new double[SM_DIM];
+ if (l == null) l = new double[SM_DIM];
+ double freight = c[SM_FREIGHT_TURN], lFreight = l[SM_FREIGHT_TURN];
+ double pax = c[SM_BUS_PAX] + c[SM_TAXI_PAX] + c[SM_WYC_PAX] + c[SM_RAIL_PAX] + c[SM_FERRY_PAX];
+ double lPax = l[SM_BUS_PAX] + l[SM_TAXI_PAX] + l[SM_WYC_PAX] + l[SM_RAIL_PAX] + l[SM_FERRY_PAX];
+ double road = c[SM_BX_TURN] + c[SM_BUS_CX_TURN] + c[SM_TAXI_CX_TURN] + c[SM_WYC_CX_TURN];
+ double lRoad = l[SM_BX_TURN] + l[SM_BUS_CX_TURN] + l[SM_TAXI_CX_TURN] + l[SM_WYC_CX_TURN];
+ n += smSetVal(sh, r, 6, freight);
+ n += smSetVal(sh, r, 9, smYoy(freight, lFreight));
+ n += smSetVal(sh, r, 11, pax);
+ n += smSetVal(sh, r, 14, smYoy(pax, lPax));
+ n += smSetVal(sh, r, 16, road);
+ n += smSetVal(sh, r, 19, smYoy(road, lRoad));
+ n += smSetVal(sh, r, 22, lFreight);
+ n += smSetVal(sh, r, 23, lPax);
+ n += smSetVal(sh, r, 24, lRoad);
+ }
+ return n;
+ }
+
+
+ private File resolveSummaryLayoutRef() {
+ String rel = summaryTemplateDir;
+ while (rel != null && 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 f = new File(root, "鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx");
+ if (f.isFile()) return f;
+ }
+ return null;
}
/** 姹囨�昏〃姣嶇増瀹氫綅锛氫粎绮剧‘鍖归厤 {year}骞磠month}鏈堚�︼紙P0-P1 楠ㄦ灦鏈熶笉鍏佽璺ㄦ湀/璺ㄥ勾闈欓粯鍥為��锛�
@@ -3650,6 +4483,9 @@
if (bs == null) continue;
// 涓彛寰勬帓鍚嶉〉 A..H 闄堟棫宸﹀潡宸蹭簬 2026-09-09 鎷嶆澘鏁村潡鍒犻櫎锛氳鍖哄煙姘镐笉浠庡浠芥瘝鐗堝洖濉紝閬垮厤鈥滅敓鎴愬張澶嶆椿鈥�
boolean skipMidRankLeftBlock = "涓彛寰勬帓鍚�".equals(ds.getSheetName());
+ // 銆� 璐ц繍銆嬮〉 2025..2021 骞村害鍧楋紙T 鍒楄捣锛屽惈鍚勫勾绱/绱鍚屾瘮鍒楋級鍦ㄦ瘝鐗堥噷鏄�屽競宸炴淳鐢熷叕寮忋�嶏紝
+ // 澶囦唤姣嶇増鍚屽尯浠嶆槸鏃х殑鍐欐甯搁噺锛堝叏鐪佽涓嬬瓑锛夛紝鍥炲～浼氳 2021/2022/2023 绱鍙堝涓嶄笂锛堚�滅敓鎴愬張澶嶆椿鈥濓級锛屾晠璇ュ尯涓嶅洖濉��
+ boolean skipFreightHistoryBlocks = " 璐ц繍".equals(ds.getSheetName());
for (int r = ds.getFirstRowNum(); r <= ds.getLastRowNum(); r++) {
Row dr = ds.getRow(r);
if (dr == null) continue;
@@ -3657,6 +4493,7 @@
for (Cell dc : dr) {
if (dc == null) continue;
if (skipMidRankLeftBlock && dc.getColumnIndex() < 8) continue;
+ if (skipFreightHistoryBlocks && dc.getColumnIndex() >= 19) continue; // T 鍒楀強浠ュ悗 = 2025..2021 骞村害鍧�
CellType t = dc.getCellType();
if (t == CellType.BLANK) continue;
String dtx = summaryCellText(dc);
--
Gitblit v1.9.1