From a76a8cce476ceccbc061a3f51681b6090a7d3741 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期五, 11 九月 2026 17:32:41 +0800
Subject: [PATCH] feat: 汇总大表排名页随报表期刷新 + 同比列统一百分数显示
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 498 +++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 447 insertions(+), 51 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 dce943c..ceea4f1 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;
@@ -50,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;
@@ -92,6 +96,8 @@
private CityBusMonthlyMapper cityBusMapper;
@Resource
private CityTaxiMonthlyMapper cityTaxiMapper;
+ @Resource
+ private SummaryWorkbookFiller summaryWorkbookFiller;
@Resource
private AuditResultMapper auditResultMapper;
@Resource
@@ -334,12 +340,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());
@@ -441,6 +442,118 @@
sb.append(m).append("鏈�");
}
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) {
@@ -3232,13 +3345,329 @@
log.info("姹囨�诲伐浣滅翱瑁佸壀鑷� {} 鏈堝畬鎴愶紝娓呯┖/鏀瑰啓鍗曞厓鏍兼暟={}", toMonth, trimmed);
}
dynamicSummaryYear(wb, year);
- applyTwoDecimalFormat(wb); // 姹囨�诲ぇ琛ㄤ繚鐣欎汉宸ユ瘝鐗堝垪瀹�/鐗堝紡锛氫笉鍋� autoFit锛堥暱鍏紡浼氭妸鍒楀椤剁垎锛�
+ 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 rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month");
+ int widthApplied = applySummaryReferenceLayout(wb);
+ log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ綋鏈堝�兼牸鏁�={}锛岃ˉ鍐欏綋鏈堝悓姣斿叕寮忔牸鏁�={}锛岃ˉ鍐欏綋鏈堝悎璁″叕寮忔牸鏁�={}锛�"
+ + "鍒锋柊鎺掑悕椤垫暟鍊兼牸鏁�={}锛屾寜銆婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪榻愬垪瀹藉垪鏁�={}锛岀己鏁版彁绀�={}",
+ dbFilled, yoyFilled, monthFormulaFilled, rankRefreshed, 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;
+ }
+
+ // ==================== 姹囨�诲伐浣滅翱鍐呯殑鎺掑悕椤靛埛鏂� ====================
+
+ /**
+ * 姹囨�诲伐浣滅翱閲岀殑銆岃揣杩愰噺鎺掑悕 / 璐ц繍鍛ㄨ浆閲忔帓鍚� / 涓彛寰勬帓鍚嶃�嶅湪姣嶇増閲屾槸涓婁竴鎶ヨ〃鏈熺殑闈欐�佸�硷紝
+ * 瀵煎嚭鏃剁敤鐙珛鎺掑悕琛紙銆婄敓鎴恄璐ц繍閲忔帓鍚嶃�嬨�婄敓鎴恄鍛ㄨ浆閲忔帓鍚嶃�嬨�婄敓鎴恄涓彛寰勬帓鍚嶃�嬶紝涓庣敤鎴锋棩甯告牳瀵圭殑鍙ｅ緞涓�鑷达級
+ * 鐨勭粨鏋滃埛鏂版暟鍊硷紝閬垮厤姹囨�昏〃閲岀殑鎺掑悕椤靛仠鐣欏湪鏃ф湀浠姐��
+ * 鏁板�兼牸鎼�笺�佸叕寮忔牸鎸夊亸绉诲钩绉诲叕寮忓悗鍐欏叆锛涚洰鏍囨牸鏈韩宸叉槸鍏紡鐨勶紙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 鐩稿 鈫� 涓婄骇锛� */
+ 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 楠ㄦ灦鏈熶笉鍏佽璺ㄦ湀/璺ㄥ勾闈欓粯鍥為��锛�
@@ -4800,59 +5229,26 @@
}
/** 姹囨�昏〃瀵煎嚭鍚庢寜鍐呭鑷姩鍔犲鍒楀锛堝彧鍔犲涓嶇缉绐勶紱璺宠繃鍚堝苟鍗曞厓鏍兼爣棰橈紝闃查暱鏍囬鎶婂崟鍒楁拺鐖嗭級 */
- private void autoFitContentColumns(org.apache.poi.ss.usermodel.Workbook wb) {
+ /** 姹囨�诲ぇ琛ㄨ緭鍑哄墠娓呯悊姣嶇増娈嬬暀涓嬫潵鐨勭瓫閫変笌琛岄殣钘忥細绛涢�夌澶�/闅愯棌琛屼細璁╂暣椤电湅璧锋潵鈥滅己鏁版嵁鈥� */
+ private void cleanSummarySheetPresentation(org.apache.poi.ss.usermodel.Workbook wb) {
if (wb == null) return;
- org.apache.poi.ss.usermodel.DataFormatter dfmt = new org.apache.poi.ss.usermodel.DataFormatter();
for (int s = 0; s < wb.getNumberOfSheets(); s++) {
org.apache.poi.ss.usermodel.Sheet sh = wb.getSheetAt(s);
if (sh == null) continue;
- java.util.Set<String> merged = new java.util.HashSet<>();
- for (org.apache.poi.ss.util.CellRangeAddress ra : sh.getMergedRegions()) {
- for (int r = ra.getFirstRow(); r <= ra.getLastRow(); r++) {
- for (int c = ra.getFirstColumn(); c <= ra.getLastColumn(); c++) {
- merged.add(r + ":" + c);
- }
+ 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();
}
- }
- int maxCol = -1;
- for (org.apache.poi.ss.usermodel.Row row : sh) {
- if (row == null) continue;
- maxCol = Math.max(maxCol, (int) row.getLastCellNum() - 1);
- }
- if (maxCol < 0) continue;
- int[] need = new int[maxCol + 1];
- for (org.apache.poi.ss.usermodel.Row row : sh) {
- if (row == null) continue;
- for (org.apache.poi.ss.usermodel.Cell cell : row) {
- int c = cell.getColumnIndex();
- if (c > maxCol || merged.contains(row.getRowNum() + ":" + c)) continue;
- String txt;
- try {
- txt = dfmt.formatCellValue(cell);
- } catch (Exception ignore) {
- continue;
- }
- if (txt == null || txt.isEmpty()) continue;
- int units = 0;
- for (int i = 0; i < txt.length(); i++) {
- units += isWideChar(txt.charAt(i)) ? 2 : 1;
- }
- if (units > need[c]) need[c] = Math.min(units, 34); // 闀挎枃鏈笉鎶婂崟鍒楁拺鐖�
- }
- }
- for (int c = 0; c <= maxCol; c++) {
- if (need[c] <= 0) continue;
- int target = Math.min(need[c] + 2, 46) * 256;
- if (target > sh.getColumnWidth(c)) sh.setColumnWidth(c, target);
+ } else {
+ sh.setAutoFilter(null);
}
}
}
- /** CJK / 鍏ㄨ瀛楃鎸変袱鍊嶅搴﹁锛堣繎浼� Excel 鏄剧ず鍒楀锛� */
- private boolean isWideChar(char ch) {
- return (ch >= 0x2E80 && ch <= 0x9FFF) || (ch >= 0xF900 && ch <= 0xFAFF)
- || (ch >= 0xFF00 && ch <= 0xFFEF);
- }
private byte[] toBytes(XSSFWorkbook wb) throws Exception {
applyTwoDecimalFormat(wb);
--
Gitblit v1.9.1