From 603853a1290e7d71297c9ce5b64181a090adab87 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期二, 08 九月 2026 17:44:50 +0800
Subject: [PATCH] docs: 09-08 工作日结补记下班前状态（最新代码已重启就绪，明日待办8月填值/9月整链/临时文件清理）
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 132 +++++++++++++++++++++++++++++++++++++++++++
1 files changed, 130 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 70065f6..2f2ebe2 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
@@ -44,6 +44,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;
@@ -456,7 +457,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,12 +3200,22 @@
* 淇濈暀鍏ㄩ儴椤电/鐗堝紡/鍏紡/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);
@@ -3216,11 +3227,19 @@
} 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);
- applyTwoDecimalFormat(wb); // 姹囨�诲ぇ琛ㄤ繚鐣欎汉宸ユ瘝鐗堝垪瀹�/鐗堝紡锛氫笉鍋� autoFit锛堥暱鍏紡浼氭妸鍒楀椤剁垎锛�
+ cleanSummarySheetPresentation(wb); // 鍙栨秷鍚勯〉绛涢�夈�佽繕鍘熼殣钘忚锛堝惁鍒欌�滅彮绾垮寘杞︹�濈瓑椤电湅璧锋潵鍍忕己鏁版嵁锛�
+ applyTwoDecimalFormat(wb); // 鏁板�兼樉绀轰袱浣嶅皬鏁帮紙淇濈暀鍏ㄧ簿搴︼級锛�% / 鏃ユ湡绛夋棦鏈夋牱寮忎笉鏀瑰彉
+ autoFitContentColumns(wb); // 鎸夋暟鎹嚜鍔ㄥ姞瀹藉垪瀹斤紙鍙姞瀹戒笉缂╃獎锛岃烦杩囧悎骞舵爣棰樻牸锛岄槻闀挎枃鏈妸鍗曞垪鎾戠垎锛�
if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
return toBytes(wb);
+ } finally {
+ ZipSecureFile.setMinInflateRatio(savedZipRatio);
}
}
@@ -3514,12 +3533,15 @@
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);
@@ -3531,6 +3553,7 @@
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);
@@ -3548,6 +3571,24 @@
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;
@@ -3566,6 +3607,73 @@
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 鍙傝�冨垪涓嶅姩锛� */
private void dynamicSummaryYear(XSSFWorkbook wb, int year) {
String oldPrefix = "2026骞�";
@@ -4694,6 +4802,26 @@
}
/** 姹囨�昏〃瀵煎嚭鍚庢寜鍐呭鑷姩鍔犲鍒楀锛堝彧鍔犲涓嶇缉绐勶紱璺宠繃鍚堝苟鍗曞厓鏍兼爣棰橈紝闃查暱鏍囬鎶婂崟鍒楁拺鐖嗭級 */
+ /** 姹囨�诲ぇ琛ㄨ緭鍑哄墠娓呯悊姣嶇増娈嬬暀涓嬫潵鐨勭瓫閫変笌琛岄殣钘忥細绛涢�夌澶�/闅愯棌琛屼細璁╂暣椤电湅璧锋潵鈥滅己鏁版嵁鈥� */
+ 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 void autoFitContentColumns(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();
--
Gitblit v1.9.1