From da614c0b85465f92453405928534d5fa2adc3c8c Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期五, 11 九月 2026 16:43:08 +0800
Subject: [PATCH] feat: 网约车订单运行监测报告(docx)导入 + 汇总大表8月数据驱动回填与版式对齐
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 459 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 451 insertions(+), 8 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 b52289d..c8ae866 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
@@ -29,6 +29,9 @@
import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.PassengerIndividualMonthlyMapper;
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
+import com.trafficaudit.reportexport.calc.MidCalc;
+import com.trafficaudit.reportexport.calc.PaxCalc;
+import com.trafficaudit.reportexport.calc.SummaryWorkbookFiller;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
@@ -44,6 +47,7 @@
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
@@ -91,6 +95,8 @@
private CityBusMonthlyMapper cityBusMapper;
@Resource
private CityTaxiMonthlyMapper cityTaxiMapper;
+ @Resource
+ private SummaryWorkbookFiller summaryWorkbookFiller;
@Resource
private AuditResultMapper auditResultMapper;
@Resource
@@ -333,12 +339,7 @@
if (resolveSummaryMother(period, mode) == null) {
problems.add("docs/鐢熸垚姹囨�诲ぇ琛� 涓嬬己灏戝彲澶嶅埗鐨勬瘝鐗堟眹鎬昏〃");
} else {
- Map<Integer, Map<String, double[]>> busM = loadCityBusByMonth(yearPrefix, month);
- Map<Integer, Map<String, double[]>> taxiM = loadCityTaxiByMonth(yearPrefix, month);
- Map<String, double[]> bc = busM.get(month);
- Map<String, double[]> tc = taxiM.get(month);
- if (bc != null && bc.get("鍏ㄧ渷") != null) addSum(summary, "鍩庡競鍏氦瀹㈣繍閲忥紙鍏ㄧ渷褰撴湀锛�", bc.get("鍏ㄧ渷")[0], "涓囦汉");
- if (tc != null && tc.get("鍏ㄧ渷") != null) addSum(summary, "宸℃父鍑虹瀹㈣繍閲忥紙鍏ㄧ渷褰撴湀锛�", tc.get("鍏ㄧ渷")[0], "涓囦汉");
+ summaryWorkbookPreview(period, yearNum, month, summary, problems);
}
} catch (Exception ex) {
problems.add(ex.getMessage());
@@ -442,6 +443,118 @@
return sb.toString();
}
+ /** 姹囨�绘暣鏈叧閿寚鏍囬瑙堬細璐ц繍閲�/鍛ㄨ浆閲忋�佸煄甯傚杩愩�佷腑鍙ｅ緞鐨� 褰撴湀銆�1..M 绱銆佸綋鏈堝悓姣斻�佺疮璁″悓姣旓紙鍚屾瘮鍙栧簱鍐呭幓骞村悓鏈燂紝缂哄垯鏄剧ず鈥旓級 */
+ private void summaryWorkbookPreview(String period, int year, int month,
+ List<Map<String, Object>> summary, List<String> problems) {
+ String yearPrefix = year + "-";
+ // ---- 璐ц繍锛堥噺=妯℃澘_璐ц繍閲忓懆杞噺鍏ㄧ渷琛岋紱鍛ㄨ浆閲�=瑙勪笂瑙勪笅鎷嗗垎琛ㄥ叏鐪佽锛� ----
+ FreightTurnoverImport ft = loadFreightTurnover(period).get("婀栧寳鐪�");
+ Double fm = ft == null ? null : freightMonth(ft, month);
+ Double fcum = ft == null ? null : freightCum(ft, month);
+ Double fy = ft == null ? null : freightYoy(ft, month);
+ Double fcy = ft == null ? null : freightCumYoy(ft, month);
+ addKpi(summary, "鍏ㄧ渷璐ц繍閲�", fm, fcum, fy, fcy, "涓囧惃");
+ if (fm != null && fy == null) {
+ problems.add((year - 1) + "-" + String.format("%02d", month) + " 鍚屾湡璐ц繍閲忔暟鎹湭瀵煎叆搴擄紝璐ц繍閲忓綋鏈�/绱鍚屾瘮鏆備笉鏄剧ず锛屽緟 2025 骞村畾绋挎暟鎹鍏ュ悗鑷姩鍑虹幇");
+ }
+ ScaleSplitTransport tm = loadProvinceMonthMap(period).get(month);
+ ScaleSplitTransport tc = getProvinceCumulative(period);
+ Double tvM = tm == null ? null : tm.getTotalTurnover();
+ Double tvC = tc == null ? null : tc.getTotalTurnover();
+ Double tvMY = tm == null ? null : getYoyMetric(tm, "turnover", "total");
+ Double tvCY = tc == null ? null : getYoyMetric(tc, "turnover", "total");
+ addKpi(summary, "鍏ㄧ渷璐х墿鍛ㄨ浆閲�", tvM, tvC, tvMY, tvCY, "涓囧惃鍏噷");
+ if (fm == null) problems.add("妯℃澘_璐ц繍閲忓懆杞噺缂� " + period + " 褰撴湀鏁版嵁锛岃揣杩愰噺浠呰兘鎻愪緵鎴嚦鏈�杩戝鍏ユ湀鐨勭疮璁�");
+ if (tvM == null) problems.add("瑙勪笂瑙勪笅鎷嗗垎缂� " + period + " 褰撴湀鏁版嵁锛屽懆杞噺/瑙勪笂瑙勪笅褰撴湀缂猴紝绱涓烘埅鑷虫渶杩戝鍏ユ湀");
+
+ // ---- 鍩庡競瀹㈣繍锛堝叕浜�+鍑虹+杞ㄩ亾+杞浮 鍏ㄧ渷灏忚锛涙寚鏍囦綅 0/2/4/6=瀹㈣繍锛�1/3/5/7=鍛ㄨ浆锛� ----
+ double[] cumM = cityCumProv(yearPrefix, month);
+ double[] cumPrev = cityCumProv(yearPrefix, month - 1);
+ double[] monthVec = diff8(cumM, cumPrev);
+ double cityPaxM = sumIdx(monthVec, new int[]{0, 2, 4, 6});
+ double cityTurnM = sumIdx(monthVec, new int[]{1, 3, 5, 7});
+ double cityPaxC = sumIdx(cumM, new int[]{0, 2, 4, 6});
+ double cityTurnC = sumIdx(cumM, new int[]{1, 3, 5, 7});
+ double[] lastCumM = cityCumProv((year - 1) + "-", month);
+ double[] lastCumPrev = cityCumProv((year - 1) + "-", month - 1);
+ double[] lastVec = diff8(lastCumM, lastCumPrev);
+ double lastPaxM = sumIdx(lastVec, new int[]{0, 2, 4, 6});
+ double lastPaxC = sumIdx(lastCumM, new int[]{0, 2, 4, 6});
+ double lastTurnM = sumIdx(lastVec, new int[]{1, 3, 5, 7});
+ double lastTurnC = sumIdx(lastCumM, new int[]{1, 3, 5, 7});
+ addKpiRaw(summary, "鍩庡競瀹㈣繍閲�", cityPaxM, cityPaxC, lastPaxM, lastPaxC, "涓囦汉娆�");
+ addKpiRaw(summary, "鍩庡競瀹㈣繍鍛ㄨ浆閲�", cityTurnM, cityTurnC, lastTurnM, lastTurnC, "涓囦汉鍏噷");
+
+ // ---- 涓彛寰勶紙鍏矾鏃呭浼佷笟 H203-1 鍏ㄧ渷锛屽崟浣嶆崲绠� /10000锛� ----
+ Map<Integer, Map<String, PassengerAgg>> data = loadPassengerAggMap();
+ double paxM = 0.0, paxC = 0.0, turnM = 0.0, turnC = 0.0;
+ double lastPaxM2 = 0.0, lastPaxC2 = 0.0, lastTurnM2 = 0.0, lastTurnC2 = 0.0;
+ for (int m = 1; m <= month; m++) {
+ PassengerAgg agg = aggOf(data, year, m, "婀栧寳鐪�");
+ if (agg != null) {
+ double pax = agg.passengerTotal / 10000.0;
+ double turn = agg.turnoverTotal / 10000.0;
+ if (m == month) { paxM = pax; turnM = turn; }
+ paxC += pax;
+ turnC += turn;
+ }
+ PassengerAgg last = aggOf(data, year - 1, m, "婀栧寳鐪�");
+ if (last != null) {
+ double lp = last.passengerTotal / 10000.0;
+ double lt = last.turnoverTotal / 10000.0;
+ if (m == month) { lastPaxM2 = lp; lastTurnM2 = lt; }
+ lastPaxC2 += lp;
+ lastTurnC2 += lt;
+ }
+ }
+ addKpiRaw(summary, "涓彛寰勫杩愰噺", paxM, paxC, lastPaxM2, lastPaxC2, "涓囦汉娆�");
+ addKpiRaw(summary, "涓彛寰勫懆杞噺", turnM, turnC, lastTurnM2, lastTurnC2, "涓囦汉鍏噷");
+
+ if (paxM <= 0 && paxC <= 0 && cityPaxC <= 0 && fm == null) {
+ problems.add("璐ц繍閲忓懆杞噺/瑙勪笂瑙勪笅/鍏矾鏃呭/鍩庡競瀹㈣繍褰撴湀鏁版嵁鍧囩己澶�");
+ }
+ if (cityPaxC > 0 && lastPaxC <= 0) {
+ problems.add((year - 1) + "-" + String.format("%02d", month) + " 鍚屾湡鍩庡競瀹㈣繍鏁版嵁鏈鍏ュ簱锛屽煄甯傚杩愬悓姣旀殏涓嶆樉绀猴紝寰� 2025 骞村畾绋挎暟鎹鍏ュ悗鑷姩鍑虹幇");
+ }
+ if (paxC > 0 && lastPaxC2 <= 0) {
+ problems.add((year - 1) + "-" + String.format("%02d", month) + " 鍚屾湡鍏矾鏃呭鏁版嵁鏈鍏ュ簱锛屼腑鍙ｅ緞鍚屾瘮鏆備笉鏄剧ず锛屽緟 2025 骞村畾绋挎暟鎹鍏ュ悗鑷姩鍑虹幇");
+ }
+ }
+
+ /** 鍩庡競瀹㈣繍鍏ㄧ渷绱鍚戦噺锛�8 鎸囨爣锛氬叕浜ゅ杩�/鍛ㄨ浆銆佸嚭绉熷杩�/鍛ㄨ浆銆佽建閬撳杩�/鍛ㄨ浆銆佽疆娓″杩�/鍛ㄨ浆锛� */
+ private double[] cityCumProv(String yearPrefix, int month) {
+ if (month <= 0) return new double[8];
+ return loadCityPassengerCumulative(yearPrefix, month).getOrDefault("鍏ㄧ渷", new double[8]);
+ }
+
+ private double[] diff8(double[] a, double[] b) {
+ double[] out = new double[8];
+ for (int i = 0; i < 8; i++) out[i] = a[i] - b[i];
+ return out;
+ }
+
+ private double sumIdx(double[] arr, int[] idx) {
+ double sum = 0.0;
+ for (int i : idx) sum += (i < arr.length ? arr[i] : 0.0);
+ return sum;
+ }
+
+ /** 鐧惧垎姣斿悓姣旓紙鍊煎凡涓烘瘮鐜� 0.xx锛夛細杈撳嚭鍥涜 褰撴湀/绱/褰撴湀鍚屾瘮/绱鍚屾瘮 */
+ private void addKpi(List<Map<String, Object>> summary, String name, Double m, Double cum, Double yoyM, Double yoyC, String unit) {
+ if (m != null) addSum(summary, name + "路褰撴湀", round(m, 2), unit);
+ if (cum != null) addSum(summary, name + "路绱", round(cum, 2), unit);
+ if (yoyM != null) addSum(summary, name + "路褰撴湀鍚屾瘮", round(yoyM * 100.0, 1), "%");
+ if (yoyC != null) addSum(summary, name + "路绱鍚屾瘮", round(yoyC * 100.0, 1), "%");
+ }
+
+ /** 鍘熷鏁板�肩増锛堟湀/绱涓� 0 瑙嗕负鏃犳暟鎹笉鍑鸿锛� */
+ private void addKpiRaw(List<Map<String, Object>> summary, String name, double m, double cum, double lastM, double lastC, String unit) {
+ if (m > 0) addSum(summary, name + "路褰撴湀", round(m, 2), unit);
+ if (cum > 0) addSum(summary, name + "路绱", round(cum, 2), unit);
+ if (m > 0 && lastM > 0) addSum(summary, name + "路褰撴湀鍚屾瘮", round((m - lastM) / lastM * 100.0, 1), "%");
+ if (cum > 0 && lastC > 0) addSum(summary, name + "路绱鍚屾瘮", round((cum - lastC) / lastC * 100.0, 1), "%");
+ }
+
private void addSum(List<Map<String, Object>> summary, String label, Double value, String unit) {
Map<String, Object> m = new LinkedHashMap<>();
m.put("label", label);
@@ -456,7 +569,7 @@
Map<String, Double> h2032Cum = loadH2032FreightCumulative(period);
ScaleSplitTransport provCum = getProvinceCumulative(period);
Double total = freightCum(ft.get("婀栧寳鐪�"), month);
- Double totalWan = total == null ? null : round(total / 10000.0, 4);
+ Double totalWan = total == null ? null : round(total, 4); // 妯℃澘鍒楀凡鏄竾鍚紝涓嶅啀 梅10000
double above = 0.0;
for (Double v : h2032Cum.values()) if (v != null) above += v;
double aboveWan = round(above / 10000.0, 4);
@@ -3199,17 +3312,150 @@
* 淇濈暀鍏ㄩ儴椤电/鐗堝紡/鍏紡/2025 骞寸紦瀛樺�硷紝浠呮妸琛ㄥご骞翠唤鍔ㄦ�佸寲涓虹洰鏍囧勾锛涙暟鎹～鍏呮寜 P2 璧烽�愭鎺ュ叆銆�
*/
public byte[] exportSummaryWorkbook(String period, String mode) throws Exception {
+ return exportSummaryWorkbook(period, mode, null);
+ }
+
+ /** 鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx锛圥0-P1 楠ㄦ灦锛屾敮鎸� toMonth 瑁佸壀锛夛細
+ * 浠ャ�奩YYY骞碝鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx銆嬶紙M 鐢� period 鍐冲畾锛変负姣嶇増锛屾寜鍚屾湡澶囦唤姣嶇増杩樺師鏈堝害鍧楋紱
+ * toMonth 闈炵┖涓斿皬浜� M 鏃讹紝鎶� 8 涓彴璐�/鍚堟垚椤佃鍒� toMonth 鏈堬紙toMonth+1..M 鐨勫綋鏈堝��/鍚屾瘮鍒楁竻绌恒�佷繚鐣欒〃澶达紝
+ * 绱涓庣疮璁″悓姣斿叕寮忔敼涓烘寜 toMonth 鏈堥噸绠楋級锛岀敤浜庘�滃厛鐢熸垚 1-6 鏈堛��1-7 鏈堟墿鍒椻�濋獙璇併�� */
+ public byte[] exportSummaryWorkbook(String period, String mode, Integer toMonth) throws Exception {
int year = Integer.parseInt(period.contains("-") ? period.split("-")[0] : period);
int month = period.contains("-") ? monthOf(period, mode) : 12;
File template = resolveSummaryMother(period, mode);
if (template == null) {
throw new RuntimeException("鏈壘鍒版眹鎬昏〃姣嶇増銆�" + year + "骞�" + month + "鏈堥亾璺繍杈撻噺姹囨�昏〃.xlsx銆嬶紙docs/鐢熸垚姹囨�诲ぇ琛級");
}
+ double savedZipRatio = ZipSecureFile.getMinInflateRatio();
+ ZipSecureFile.setMinInflateRatio(0.0001); // 鏂扮増姣嶇増 styles.xml 鐢� openpyxl 楂樺帇缂╋紙0.0099<0.01锛夎Е鍙� POI Zip-bomb 璇姤锛岃鍙栧悗杩樺師
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);
+ }
+ if (toMonth != null && toMonth > 0 && toMonth < month) {
+ int trimmed = trimSummaryMonthlySheets(wb, toMonth);
+ log.info("姹囨�诲伐浣滅翱瑁佸壀鑷� {} 鏈堝畬鎴愶紝娓呯┖/鏀瑰啓鍗曞厓鏍兼暟={}", toMonth, trimmed);
+ }
dynamicSummaryYear(wb, year);
+ cleanSummarySheetPresentation(wb); // 鍙栨秷鍚勯〉绛涢�夈�佽繕鍘熼殣钘忚锛堝惁鍒欌�滅彮绾垮寘杞︹�濈瓑椤电湅璧锋潵鍍忕己鏁版嵁锛�
+ int effMonth = (toMonth != null && toMonth > 0 && toMonth < month) ? toMonth : month;
+ List<String> dbProblems = new ArrayList<>();
+ 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 widthApplied = applySummaryReferenceLayout(wb);
+ log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ綋鏈堝�兼牸鏁�={}锛岃ˉ鍐欏綋鏈堝悓姣斿叕寮忔牸鏁�={}锛岃ˉ鍐欏綋鏈堝悎璁″叕寮忔牸鏁�={}锛�"
+ + "鎸夈�婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪榻愬垪瀹藉垪鏁�={}锛岀己鏁版彁绀�={}",
+ dbFilled, yoyFilled, monthFormulaFilled, widthApplied, dbProblems);
+ applyTwoDecimalFormat(wb); // 鏁板�兼樉绀轰袱浣嶅皬鏁帮紙淇濈暀鍏ㄧ簿搴︼級锛�% / 鏃ユ湡绛夋棦鏈夋牱寮忎笉鏀瑰彉
+ if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
+ wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
return toBytes(wb);
+ } finally {
+ ZipSecureFile.setMinInflateRatio(savedZipRatio);
}
+ }
+
+ /**
+ * 鐗堝紡瀵归綈锛氭寜 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;
+ }
+
+ /** 瀹氫綅鐗堝紡鍙傜収浠� docs/鐢熸垚姹囨�诲ぇ琛�/鐢熸垚_閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx锛堜紭鍏堢骇鍚屾瘝鐗堬細閰嶇疆鐩綍 鈫� user.dir 鐩稿 鈫� 涓婄骇锛� */
+ 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 楠ㄦ灦鏈熶笉鍏佽璺ㄦ湀/璺ㄥ勾闈欓粯鍥為��锛�
@@ -3472,6 +3718,175 @@
if (a[5] <= 0 && a[2] > 0) a[5] = round(a[3] / a[2], 2);
}
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;
+ // 涓彛寰勬帓鍚嶉〉 A..H 闄堟棫宸﹀潡宸蹭簬 2026-09-09 鎷嶆澘鏁村潡鍒犻櫎锛氳鍖哄煙姘镐笉浠庡浠芥瘝鐗堝洖濉紝閬垮厤鈥滅敓鎴愬張澶嶆椿鈥�
+ boolean skipMidRankLeftBlock = "涓彛寰勬帓鍚�".equals(ds.getSheetName());
+ 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;
+ if (skipMidRankLeftBlock && dc.getColumnIndex() < 8) 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();
+ applyDonorNumberFormat(wb, tc, dc);
+ 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;
+ }
+
+ /** 杩樺師鍥炲～鏃跺悓姝� donor锛堝凡鍋氱増寮忕粺涓�锛夌殑鏁板瓧鏍煎紡锛屼繚璇佸悓姣�/鏁板��/鎺掑悕鏍兼樉绀烘牸寮忎笌姣嶇増涓�鑷� */
+ private void applyDonorNumberFormat(XSSFWorkbook wb, Cell dst, Cell src) {
+ if (wb == null || dst == null || src == null) return;
+ try {
+ String fmt = src.getCellStyle().getDataFormatString();
+ if (fmt == null || fmt.isEmpty() || "General".equalsIgnoreCase(fmt)) return;
+ org.apache.poi.ss.usermodel.DataFormat df = wb.createDataFormat();
+ short idx = df.getFormat(fmt);
+ CellStyle cur = dst.getCellStyle();
+ if (cur != null && idx == cur.getDataFormat()) return;
+ CellStyle ns = wb.createCellStyle();
+ if (cur != null) ns.cloneStyleFrom(cur);
+ ns.setDataFormat(idx);
+ dst.setCellStyle(ns);
+ } catch (Exception ignore) {
+ // 涓埆鏍煎紡寮傚父涓嶉樆濉炶繕鍘�
+ }
+ }
+ /** 鍗曞厓鏍煎唴瀹规枃鏈寲锛堝叕寮忓甫 = 鍓嶇紑锛夛紝鐢ㄤ簬璺ㄧ翱姣斿 */
+ 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 "";
+ }
+ }
+
+ /** 鎶� 8 涓彴璐�/鍚堟垚椤佃鍓埌 keepMonths 鏈堬細
+ * 鏈堝害鍊煎垪 2026 骞� m 鏈� = 绗� 3+2*(m-1) 鍒楋紙C/E/G/I/K/M/O锛夛紝鍚屾瘮鍒椾负鍏跺悗涓�鍒楋紱
+ * 绱 Q 鍒� = C+E+...+O锛岀疮璁″悓姣� R 鍒楀垎姣� = 2025 骞村悓鏈堝垪 T/V/X/Z/AB/AD/AF銆�
+ * 瑁佸壀 = 鏁版嵁琛岋紙鍚疮璁″叕寮忎笖浠讳竴 2026 鏈堝害鍒楁湁鍐呭锛変腑 keepMonths+1..7 鏈堢殑褰撴湀鍊�/鍚屾瘮鍗曞厓鏍兼竻绌猴紙琛ㄥご淇濈暀锛夛紝
+ * 骞舵妸璇ヨ绱鍏紡鍘绘帀瀵瑰簲 +鍒楋紝绱鍚屾瘮鍒嗘瘝鍘绘帀瀵瑰簲 2025 鍒椼�� */
+ private int trimSummaryMonthlySheets(XSSFWorkbook wb, int keepMonths) {
+ String[] scope = {" 璐ц繍", "鐝嚎鍖呰溅", "鍩庡競瀹㈣繍", "鍏氦", "鍑虹", "缃戠害杞�", "杞ㄩ亾銆佽疆娓�", "涓彛寰勬槑缁�"};
+ java.util.Set<String> scopeSet = new java.util.HashSet<>();
+ for (String s : scope) scopeSet.add(s);
+ int touched = 0;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ Sheet sh = wb.getSheetAt(i);
+ if (sh == null || !scopeSet.contains(sh.getSheetName())) continue;
+ for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ Cell q = row.getCell(16); // Q 鍒� = 绱
+ if (q == null || q.getCellType() != CellType.FORMULA) continue;
+ boolean hasMonthly = false;
+ for (int m = 1; m <= 7 && !hasMonthly; m++) {
+ Cell v = row.getCell(2 + 2 * (m - 1)); // C,E,G,I,K,M,O
+ if (v != null && v.getCellType() != CellType.BLANK) hasMonthly = true;
+ }
+ if (!hasMonthly) continue; // 鎺掗櫎椤甸/椤典腑璺ㄩ〉绛炬牎楠屽叕寮忚锛堟棤鏈堝害鍊硷級
+ int excelRow = r + 1; // 鍏紡鍐呭紩鐢ㄤ负 Excel 1-based 琛屽彿
+ for (int m = keepMonths + 1; m <= 7; m++) {
+ int valueCol = 2 + 2 * (m - 1); // 2026 褰撴湀鍊煎垪
+ int yoyCol = valueCol + 1; // 褰撴湀鍚屾瘮鍒�
+ String vLetter = colLetter(valueCol);
+ Cell vc = row.getCell(valueCol);
+ if (vc != null) {
+ vc.setBlank();
+ touched++;
+ }
+ Cell yc = row.getCell(yoyCol);
+ if (yc != null) {
+ yc.setBlank();
+ touched++;
+ }
+ String fq = q.getCellFormula();
+ if (fq != null && fq.contains("+" + vLetter + excelRow)) {
+ q.setCellFormula(fq.replace("+" + vLetter + excelRow, ""));
+ touched++;
+ }
+ Cell rc = row.getCell(17); // R 鍒� = 绱鍚屾瘮
+ if (rc != null && rc.getCellType() == CellType.FORMULA) {
+ String fr = rc.getCellFormula();
+ int lastCol = 19 + 2 * (m - 1); // 2025 鍚屾湀鍒� T..AF锛�0-based锛歍=19,AF=31锛�
+ String lLetter = colLetter(lastCol);
+ String txt = fr;
+ if (txt != null && txt.contains("," + lLetter + excelRow)) {
+ txt = txt.replace("," + lLetter + excelRow, "");
+ touched++;
+ }
+ if (txt != null && txt.contains("+" + lLetter + excelRow)) {
+ txt = txt.replace("+" + lLetter + excelRow, "");
+ touched++;
+ }
+ if (txt != null && !txt.equals(fr)) rc.setCellFormula(txt);
+ }
+ }
+ }
+ }
+ return touched;
}
/** 姹囨�诲伐浣滅翱骞翠唤鍔ㄦ�佸寲锛氫粎鎶� 2026骞� 鍓嶇紑鏇挎崲涓虹洰鏍囧勾锛�2025/2024 鍙傝�冨垪涓嶅姩锛� */
@@ -4574,7 +4989,13 @@
CellType ct = cell.getCellType();
if (ct != CellType.NUMERIC && ct != CellType.FORMULA) continue;
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) continue;
- if (ct == CellType.NUMERIC && Math.rint(cell.getNumericCellValue()) == cell.getNumericCellValue()) continue;
+ boolean integral = false;
+ if (ct == CellType.NUMERIC) {
+ integral = Math.rint(cell.getNumericCellValue()) == cell.getNumericCellValue();
+ } else if (cell.getCachedFormulaResultType() == CellType.NUMERIC) {
+ integral = Math.rint(cell.getNumericCellValue()) == cell.getNumericCellValue();
+ }
+ if (integral) continue; // 鏁存暟涓嶆樉绀� .00锛堝惈鍏紡缁撴灉涓烘暣鏁扮殑鏍硷級
org.apache.poi.ss.usermodel.CellStyle cs = cell.getCellStyle();
if (cs == null) continue;
String f = cs.getDataFormatString();
@@ -4595,6 +5016,28 @@
}
}
+ /** 姹囨�昏〃瀵煎嚭鍚庢寜鍐呭鑷姩鍔犲鍒楀锛堝彧鍔犲涓嶇缉绐勶紱璺宠繃鍚堝苟鍗曞厓鏍兼爣棰橈紝闃查暱鏍囬鎶婂崟鍒楁拺鐖嗭級 */
+ /** 姹囨�诲ぇ琛ㄨ緭鍑哄墠娓呯悊姣嶇増娈嬬暀涓嬫潵鐨勭瓫閫変笌琛岄殣钘忥細绛涢�夌澶�/闅愯棌琛屼細璁╂暣椤电湅璧锋潵鈥滅己鏁版嵁鈥� */
+ private void cleanSummarySheetPresentation(org.apache.poi.ss.usermodel.Workbook wb) {
+ if (wb == null) return;
+ for (int s = 0; s < wb.getNumberOfSheets(); s++) {
+ org.apache.poi.ss.usermodel.Sheet sh = wb.getSheetAt(s);
+ if (sh == null) continue;
+ if (sh instanceof org.apache.poi.xssf.usermodel.XSSFSheet) {
+ org.apache.poi.xssf.usermodel.XSSFSheet xs = (org.apache.poi.xssf.usermodel.XSSFSheet) sh;
+ if (xs.getCTWorksheet().isSetAutoFilter()) xs.getCTWorksheet().unsetAutoFilter();
+ for (org.apache.poi.ss.usermodel.Row row : xs) {
+ if (row == null) continue;
+ org.apache.poi.xssf.usermodel.XSSFRow xr = (org.apache.poi.xssf.usermodel.XSSFRow) row;
+ if (xr.getCTRow().isSetHidden()) xr.getCTRow().unsetHidden();
+ }
+ } else {
+ sh.setAutoFilter(null);
+ }
+ }
+ }
+
+
private byte[] toBytes(XSSFWorkbook wb) throws Exception {
applyTwoDecimalFormat(wb);
try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
--
Gitblit v1.9.1