From 259db33b22095891f436b9ed5a81a21496b11c4c Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期一, 12 十月 2026 23:41:55 +0800
Subject: [PATCH] feat(汇总大表): 缺数清单加导出前弹窗并在生成件附《数据缺失清单》页（待验收）
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java | 181 +++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 173 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 7468281..358a446 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
@@ -42,8 +42,12 @@
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.HorizontalAlignment;
+import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@@ -1439,10 +1443,11 @@
Map<String, ScaleSplitTransport> result = new HashMap<>();
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
new LambdaQueryWrapper<ScaleSplitTransport>()
- .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
+ .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE")
+ .eq(ScaleSplitTransport::getReportPeriod, period)); // 2026-10-12锛氬彧璁ょ洰鏍囨湡锛岀己鏁扮暀绌恒�佷笉闈欓粯鍥為��涓婁竴鏈�
for (ScaleSplitTransport record : list) {
if (record.getReportPeriod() == null) continue;
- if (record.getReportPeriod().compareTo(period) > 0) continue;
+ if (!period.equals(record.getReportPeriod())) continue;
String city = RegionUtil.normalizeCityName(record.getRegionName());
if (city == null) continue;
if ("婀栧寳鐪�".equals(city)) continue;
@@ -1475,11 +1480,12 @@
private ScaleSplitTransport getProvinceCumulative(String period) {
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
new LambdaQueryWrapper<ScaleSplitTransport>()
- .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
+ .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE")
+ .eq(ScaleSplitTransport::getReportPeriod, period)); // 2026-10-12锛氬悓涓婏紙鍙鐩爣鏈燂級
ScaleSplitTransport best = null;
for (ScaleSplitTransport record : list) {
if (record.getReportPeriod() == null) continue;
- if (record.getReportPeriod().compareTo(period) > 0) continue;
+ if (!period.equals(record.getReportPeriod())) continue;
String name = RegionUtil.normalizeCityName(record.getRegionName());
if (!"婀栧寳鐪�".equals(name)) continue;
if (best == null || record.getReportPeriod().compareTo(best.getReportPeriod()) > 0) best = record;
@@ -3764,7 +3770,9 @@
reportProgress(progress, 45, "鍒锋柊鎺掑悕椤典笌璺ㄩ〉鍚堟垚椤�");
int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month");
int cumulativeRefreshed = refreshSummaryCumulativeSheets(wb, year, effMonth); // 鍥涘紶璺ㄩ〉鍚堟垚椤甸殢鎶ヨ〃鏈熷埛鏂�
- int widthApplied = applySummaryReferenceLayout(wb);
+ // 2026-10-12锛氬仠鐢ㄣ�屽鐢ㄥ閮ㄥ弬鐓т欢銆婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃.xlsx銆嬪垪瀹姐�嶁�斺�旂増寮忎竴寰嬩互褰撴湡姣嶇増涓哄噯锛�
+ // 閬垮厤姣嶇増涓庡弬鐓т欢涓ゅ缁存姢锛堟瘝鐗堝凡骞跺叆鍙傜収浠剁殑鍒楀/闅愯棌锛夈��
+ int widthApplied = 0;
log.info("姹囨�诲伐浣滅翱鏁版嵁椹卞姩鍥炲～瀹屾垚锛氬啓鍏ュ綋鏈堝�兼牸鏁�={}锛岃ˉ鍐欏綋鏈堝悓姣斿叕寮忔牸鏁�={}锛岃ˉ鍐欏綋鏈堝悎璁″叕寮忔牸鏁�={}锛�"
+ "淇绱鍚屾瘮鍒嗘瘝鏍兼暟={}锛屽埛鏂版帓鍚嶉〉鏁板�兼牸鏁�={}锛屽埛鏂板洓椤碉紙鍩庡競姹囨��/鍩庡競鍒嗗競宸�/涓彛寰勫垎鏋�/閬撹矾杩愯緭鍛ㄨ浆閲忥級鏁板�兼牸鏁�={}锛�"
+ "鎸夈�婄敓鎴恄閬撹矾杩愯緭閲忔眹鎬昏〃銆嬪榻愬垪瀹藉垪鏁�={}锛岀己鏁版彁绀�={}",
@@ -3777,11 +3785,112 @@
log.info("姹囨�诲伐浣滅翱鍏紡閲嶇畻瀹屾垚锛氭垚鍔� {} 鏍硷紝澶辫触 {} 鏍硷紙澶辫触鏍间繚鐣欐瘝鐗堟棫缂撳瓨鍊硷紝鎵撳紑鏃舵寜 fullCalcOnLoad 閲嶇畻锛�", recalc[0], recalc[1]);
if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 杩樺師/鏀瑰啓鍏紡鍚庢墦寮�鍗抽噸绠�
+ appendMissingDataSheet(wb, effPeriod, dbProblems); // 2026-10-12锛氱己鏁�/浣撴缁撴灉鍐欒繘鐢熸垚浠讹紙鐢ㄦ埛瑕佹眰锛�
reportProgress(progress, 95, "鍐欏嚭鏂囦欢");
return toBytes(wb);
} finally {
ZipSecureFile.setMinInflateRatio(savedZipRatio);
}
+ }
+
+ /**
+ * 鐢熸垚浠躲�婃暟鎹己澶辨竻鍗曘�嬮〉锛�2026-10-12锛岀敤鎴疯姹傦級銆�
+ *
+ * <p>鎶婃湰娆″鍑虹殑銆岀己鏁版竻鍗曘�嶄笌銆岃揣杩愬熀鏁颁綋妫�銆嶇粨鏋滄樉寮忓啓杩涗骇鐗╋細鐢ㄦ埛鎷垮埌鏂囦欢鍗冲彲鐪嬪埌鏈姤琛ㄦ湡
+ * 鏈夊摢浜涙暟娌″埌銆佸摢浜涙暟涓嶅悎鐞嗭紝涓嶅繀缈诲鍑烘棩蹇椼�傚彧璇绘彁绀洪〉锛屼笉鍙備笌鏈〃璁＄畻銆�
+ */
+ private void appendMissingDataSheet(XSSFWorkbook wb, String period, List<String> problems) {
+ String sheetName = "鏁版嵁缂哄け娓呭崟";
+ int old = wb.getSheetIndex(sheetName);
+ if (old >= 0) wb.removeSheetAt(old); // 姣嶇増鐢便�屽綋鏈熶骇鐗┿�嶆淳鐢熸椂鍙兘宸插甫鏈〉锛屽厛绉婚櫎閬垮厤閲嶅悕
+ XSSFSheet sh = wb.createSheet(sheetName);
+
+ CellStyle titleStyle = wb.createCellStyle();
+ Font titleFont = wb.createFont();
+ titleFont.setBold(true);
+ titleFont.setFontHeightInPoints((short) 14);
+ titleStyle.setFont(titleFont);
+
+ CellStyle metaStyle = wb.createCellStyle();
+ Font metaFont = wb.createFont();
+ metaFont.setFontHeightInPoints((short) 10);
+ metaFont.setColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ metaStyle.setFont(metaFont);
+
+ CellStyle sectionStyle = wb.createCellStyle();
+ Font sectionFont = wb.createFont();
+ sectionFont.setBold(true);
+ sectionStyle.setFont(sectionFont);
+
+ CellStyle bodyStyle = wb.createCellStyle();
+ bodyStyle.setWrapText(true);
+ bodyStyle.setVerticalAlignment(VerticalAlignment.TOP);
+
+ List<String> missing = new ArrayList<>();
+ List<String> check = new ArrayList<>();
+ List<String> other = new ArrayList<>();
+ if (problems != null) {
+ for (String p : problems) {
+ if (p == null || p.trim().isEmpty()) continue;
+ if (p.startsWith("缂烘暟")) missing.add(p);
+ else if (p.startsWith("璐ц繍浣撴")) check.add(p);
+ else other.add(p);
+ }
+ }
+
+ int r = 0;
+ Row tRow = sh.createRow(r++);
+ Cell tCell = tRow.createCell(0);
+ tCell.setCellValue("鏁版嵁缂哄け娓呭崟");
+ tCell.setCellStyle(titleStyle);
+
+ Row mRow = sh.createRow(r++);
+ Cell mCell = mRow.createCell(0);
+ mCell.setCellValue("鎶ヨ〃鏈� " + period + "銆�鐢熸垚鏃堕棿 "
+ + new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm").format(new java.util.Date())
+ + "銆�鍏� " + (missing.size() + check.size() + other.size()) + " 鏉�");
+ mCell.setCellStyle(metaStyle);
+
+ Row nRow = sh.createRow(r++);
+ Cell nCell = nRow.createCell(0);
+ nCell.setCellValue("鏈〉鐢辩▼搴忚嚜鍔ㄧ敓鎴愶紙鍙鎻愮ず锛夛細涓�銆佺己鏁版竻鍗曪紳璇ユ湁鐨勬暟娌℃湁锛涗簩銆佹暟鎹綋妫�锛濇暟鍊间笉鍚堢悊锛堝瑙勪笅涓鸿礋锛夈�備笉鍙備笌鏈〃璁＄畻銆�");
+ nCell.setCellStyle(metaStyle);
+
+ r++;
+ r = writeProblemSection(sh, r, "涓�銆佺己鏁版竻鍗曪紙璇ユ湁鐨勬暟娌℃湁锛�", missing, sectionStyle, bodyStyle);
+ r = writeProblemSection(sh, r, "浜屻�佹暟鎹綋妫�锛堟暟鍊间笉鍚堢悊锛�", check, sectionStyle, bodyStyle);
+ if (!other.isEmpty()) {
+ r = writeProblemSection(sh, r, "涓夈�佸叾瀹冩彁绀�", other, sectionStyle, bodyStyle);
+ }
+
+ sh.setColumnWidth(0, 6 * 256);
+ sh.setColumnWidth(1, 120 * 256);
+ sh.createFreezePane(0, 3);
+ }
+
+ /** 鍐欎竴涓竻鍗曞垎鍖猴紙鏍囬 + 閫愭潯锛夛紝杩斿洖涓嬩竴涓彲鍐欒鍙� */
+ private int writeProblemSection(XSSFSheet sh, int rowIdx, String title, List<String> lines,
+ CellStyle sectionStyle, CellStyle bodyStyle) {
+ Row hr = sh.createRow(rowIdx++);
+ Cell hc = hr.createCell(0);
+ hc.setCellValue(title);
+ hc.setCellStyle(sectionStyle);
+ sh.addMergedRegion(new CellRangeAddress(rowIdx - 1, rowIdx - 1, 0, 1));
+ if (lines.isEmpty()) {
+ Row er = sh.createRow(rowIdx++);
+ er.createCell(0).setCellValue("鈥�");
+ er.createCell(1).setCellValue("鏃�");
+ return rowIdx;
+ }
+ int i = 1;
+ for (String line : lines) {
+ Row row = sh.createRow(rowIdx++);
+ row.createCell(0).setCellValue(i++);
+ Cell c = row.createCell(1);
+ c.setCellValue(line);
+ c.setCellStyle(bodyStyle);
+ }
+ return rowIdx;
}
/** 瀵煎嚭杩涘害鍥炶皟锛堝紓姝ュ鍑轰换鍔＄敤锛夛細鍥炶皟鑷韩寮傚父涓嶅奖鍝嶅鍑� */
@@ -3986,15 +4095,38 @@
int colOff = dstTitle.getColumnIndex() - sc0;
int h = rankBlockHeight(src, sr0, sc0);
int w = rankBlockWidth(src, sr0, sc0);
+ // 2026-10-12锛氭暣鍧楃己鏁帮紙婧愪晶涓�涓暟鍊奸兘娌℃湁锛屽 9 鏈堢己鈶♀憿鏃剁殑鍛ㄨ浆閲忔帓鍚嶏級鈫� 鐩爣鍧楀彧鐣欐爣棰樹笌鏍囩锛�
+ // 鏁版嵁/鎺掑悕/鍗犳瘮涓�寰嬬暀绌猴紱鍚﹀垯姣嶇増鐨� RANK/鍗犳瘮鍏紡浼氬湪绌哄潡涓婄畻鍑恒�屽叏 1 + #DIV/0!銆嶃��
+ boolean blockHasData = false;
+ for (int r = 1; r < h && !blockHasData; r++) {
+ Row sr = src.getRow(sr0 + r);
+ if (sr == null) continue;
+ for (int c = 0; c < w; c++) {
+ Cell sc0c = sr.getCell(sc0 + c);
+ if (sc0c != null && rankNumeric(sc0c, ev) != null) {
+ blockHasData = true;
+ break;
+ }
+ }
+ }
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;
+ Cell sc = srcRow.getCell(sc0 + c);
+ if (!blockHasData && r > 0) {
+ // 鏁村潡缂烘暟锛氬彧淇濈暀鏍囩锛堝競宸炲悕/鎸囨爣鍚嶏級锛屾暟鎹笌鎺掑悕/鍗犳瘮閲嶇畻缁撴灉涓�寰嬬暀绌�
+ if (sc == null || !isRankLabel(sc)) clearRankCell(dstRow, dc);
+ continue;
+ }
+ if (sc == null) {
+ // 2026-10-12锛氭簮渚ф暣鏍肩己澶� = 缂烘暟 鈫� 鐩爣鏍兼寜銆岀己鏁扮暀绌恒�嶆竻绌猴紙涓嶅啀娌跨敤姣嶇増涓婁竴鎶ヨ〃鏈熺殑鏃у�硷級
+ if (r > 0) clearRankCell(dstRow, dc);
+ continue;
+ }
if (r == 0) {
// 鏍囬琛岋細鍚屾鍧楁爣棰樻枃妗堛�傛瘝鐗堟爣棰樻槸 inlineStr锛孭OI 鐨� setCellValue 鍙啓 <v>銆�
// 涓嶆洿鏂� <is>锛孍xcel 浼氱户缁樉绀烘棫鏈堜唤锛屽繀椤诲厛 setBlank 娓呮帀鍐嶅啓銆�
@@ -4029,7 +4161,12 @@
continue;
}
Double v = rankNumeric(sc, ev);
- if (v == null) continue;
+ if (v == null) {
+ // 2026-10-12锛氭簮渚ф暟鍊间綅涓虹┖锛堝缂衡憽鍏ㄥ彛寰勬椂鎺掑悕琛ㄧ殑銆岃涓嬨�嶅垪锛�= 缂烘暟 鈫� 鐣欑┖锛�
+ // 婧愪晶鏄爣绛炬枃鏈紙甯傚窞鍚�/鎸囨爣鍚嶏級鏃朵笉鏄己鏁帮紝淇濈暀姣嶇増鑷甫鍐呭銆�
+ if (!isRankLabel(sc)) clearRankCell(dstRow, dc);
+ continue;
+ }
if (dcCell == null) dcCell = rankCell(dstRow, dc);
dcCell.setCellValue(v);
copied++;
@@ -4048,6 +4185,34 @@
return c;
}
+ /**
+ * 鐩爣鏍兼寜銆岀己鏁扮暀绌恒�嶆竻绌恒��
+ *
+ * <p>2026-10-12锛氳繖閲岃繛姣嶇増鑷甫鐨勬帓鍚�/鍗犳瘮鍏紡鏍间竴璧锋竻锛堢己鏁版湡涓嶆樉绀�"涓婁竴鏈熸畫鐣欏�� + 鍏� 1 鎺掑悕 + #DIV/0!"锛夛紝
+ * 涓庛�婄敓鎴恄璐ц繍閲忔帓鍚�/鍛ㄨ浆閲忔帓鍚�/涓彛寰勬帓鍚嶃�嬭繖浜涚敤鎴锋棩甯告牳瀵逛欢鐨勭┖鎬佷竴鑷达紱涓嬫湡婧愪晶鏈夋暟鏃舵寜婧愪晶閲嶅啓鍥炴潵銆�
+ * 鏂囧瓧鏍囩鏍间笉鍔ㄣ��
+ */
+ private void clearRankCell(Row dstRow, int col) {
+ Cell c = dstRow.getCell(col);
+ if (c == null) return;
+ if (c.getCellType() != CellType.STRING) c.setBlank();
+ }
+
+ /** 婧愪晶鏄惁涓烘枃瀛楁爣绛撅紙甯傚窞鍚�/鎸囨爣鍚嶏級鈥斺�旀爣绛句笉鏄己鏁帮紝涓嶆竻绌虹洰鏍囨牸 */
+ private boolean isRankLabel(Cell c) {
+ if (c.getCellType() != CellType.STRING) return false;
+ String v = c.getStringCellValue();
+ if (v == null) return false;
+ String x = v.replace(",", "").trim();
+ if (x.isEmpty()) return false;
+ try {
+ Double.parseDouble(x);
+ return false;
+ } catch (NumberFormatException ignore) {
+ return true;
+ }
+ }
+
/** 鍏紡鍐呮墍鏈� A1 寮曠敤鏁翠綋骞崇Щ锛氬垪 +dCol銆佽 +dRow锛圫UM/RANK 绛夊嚱鏁板悕鍚庢帴鎷彿涓嶄細琚尮閰嶏級 */
private String shiftFormula(String formula, int dCol, int dRow) {
if (formula == null || (dCol == 0 && dRow == 0)) return formula;
--
Gitblit v1.9.1