From df57cb713d705c9fa8b3b11db014eba89c630a90 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期三, 14 十月 2026 15:00:39 +0800
Subject: [PATCH] docs(交接): 记录Excel COM打不开部分生成件改用WPS KET.Application的坑与绕法
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java | 160 +++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 147 insertions(+), 13 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java
index 9da5c4a..c9cd5a4 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/layout/SummaryLayoutExpander.java
@@ -3,6 +3,7 @@
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
@@ -41,6 +42,19 @@
private static final int HEADER_SCAN_ROWS = 15;
+ /** 鏍囬鐢辩▼搴忓湪鍑鸿〃鏃剁幇鍐欑殑 7 涓〉绛撅紙姣嶇増閲岀殑鏍囬鏂囧瓧闇�鎸夋瘝鐗堟湡娆￠噸鍐欙級銆� */
+ private static final List<String> TITLE_PAGES = Arrays.asList(
+ "璐ц繍閲忔帓鍚�", "璐ц繍鍛ㄨ浆閲忔帓鍚�", "鍩庡競姹囨��", "鍩庡競鍒嗗競宸�", "涓彛寰勬帓鍚�", "涓彛寰勫垎鏋�", "閬撹矾杩愯緭鍛ㄨ浆閲�");
+
+ /** 鏍囬璇嗗埆鏍囪锛堝懡涓叾涓�鎵嶆敼鍐欙紝閬垮厤璇激澶囨敞绛夋枃瀛楋級銆� */
+ private static final List<String> TITLE_MARKERS = Arrays.asList(
+ "鍏ㄧ渷鍒嗗競宸炵疮璁″畬鎴愬叕璺揣杩愰噺鎯呭喌", "鍏ㄧ渷鍒嗗競宸炲畬鎴愬叕璺揣杩愰噺鎯呭喌",
+ "鍏ㄧ渷鍒嗗競宸炵疮璁″畬鎴愬叕璺揣鐗╄繍杈撳懆杞噺鎯呭喌", "鍏ㄧ渷鍒嗗競宸炲畬鎴愬叕璺揣鐗╄繍杈撳懆杞噺鎯呭喌",
+ "鍏ㄧ渷绱瀹屾垚鍩庡競瀹㈣繍閲忔儏鍐�", "鍏ㄧ渷瀹屾垚鍩庡競瀹㈣繍閲忔儏鍐�",
+ "鍒嗗競宸�1-", "鍏ㄧ渷鍒嗗競宸炵疮璁″畬鎴愰亾璺杩愮敓浜ф儏鍐�", "鍏ㄧ渷鍒嗗競宸炲畬鎴愰亾璺杩愮敓浜ф儏鍐�",
+ "涓彛寰勫杩愰噺鍙婂懆杞噺",
+ "鍏ㄧ渷绱瀹屾垚浜ら�氬懆杞噺鎯呭喌", "鍏ㄧ渷瀹屾垚浜ら�氬懆杞噺鎯呭喌");
+
/** 鍏紡閲岀殑 A1 寮曠敤锛堝彲甯﹀伐浣滆〃鍓嶇紑锛夈�� */
private static final Pattern REF = Pattern.compile(
"(?:('([^']+)'|([A-Za-z_\\u4e00-\\u9fff][A-Za-z0-9_.\\u4e00-\\u9fff\u3001]*))!)?(\\$?)([A-Z]{1,3})(\\$?)(\\d+)");
@@ -67,6 +81,11 @@
if (sh == null || expanded.contains(sh.getSheetName())) continue;
shiftQualifiedRefs(sh, insertColOf);
}
+ recalcAndCache(wb);
+ int titleFixed = applyTitles(wb, year, targetMonth);
+ if (titleFixed > 0) {
+ log.info("鎵╁垪锛氭爣棰樻寜 {}骞磠}鏈� 閲嶅啓 {} 鏍�", year, targetMonth, titleFixed);
+ }
wb.setForceFormulaRecalculation(true);
ByteArrayOutputStream out = new ByteArrayOutputStream();
wb.write(out);
@@ -74,6 +93,66 @@
} catch (Exception e) {
throw new IllegalStateException("姹囨�诲ぇ琛ㄦ墿鍒楀け璐�: " + e.getMessage(), e);
}
+ }
+
+ /** 鍙妸 7 涓爣棰橀〉鐨勬爣棰樻寜缁欏畾鏈熸閲嶅啓锛堢敤浜庝慨姝ｆ瘝鐗堥噷婊炲悗鐨勬爣棰橈級銆� */
+ public byte[] fixTitles(byte[] source, int year, int month) {
+ try (XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(source))) {
+ recalcAndCache(wb);
+ int n = applyTitles(wb, year, month);
+ wb.setForceFormulaRecalculation(true);
+ ByteArrayOutputStream out = new ByteArrayOutputStream();
+ wb.write(out);
+ log.info("姹囨�诲ぇ琛ㄦ爣棰樹慨姝ｅ畬鎴愶細{}骞磠}鏈堬紝鏀瑰啓 {} 鏍�", year, month, n);
+ return out.toByteArray();
+ } catch (Exception e) {
+ throw new IllegalStateException("姹囨�诲ぇ琛ㄦ爣棰樹慨姝ｅけ璐�: " + e.getMessage(), e);
+ }
+ }
+
+ /** 鏁存湰閲嶇畻骞舵妸缁撴灉鍐欏叆缂撳瓨鍊硷細鍚﹀垯姣嶇増閲屽彧鏈夊叕寮忔病鏈夊�硷紝鍚庣画璇诲彇浼氭妸绌哄�煎綋 0锛�2026-10-12 淇級 */
+ private void recalcAndCache(XSSFWorkbook wb) {
+ try {
+ FormulaEvaluator ev = wb.getCreationHelper().createFormulaEvaluator();
+ ev.clearAllCachedResultValues();
+ ev.evaluateAll();
+ } catch (Exception e) {
+ log.warn("鎵╁垪锛氭暣鏈噸绠楁湭瀹屾垚锛堝叕寮忎粛鍦紝Excel 鎵撳紑浼氬啀绠楋級锛歿}", e.getMessage());
+ }
+ }
+
+ /** 鎶� 7 涓〉绛剧殑鏍囬鏂囧瓧鎸夈�岀疮璁�=1-M鏈堛�佸綋鏈�=M鏈堛�嶉噸鍐欙紱鍛戒腑鏍囪鎵嶆敼锛岄伩鍏嶈浼ゅ娉ㄣ�� */
+ private int applyTitles(XSSFWorkbook wb, int year, int month) {
+ int changed = 0;
+ for (String page : TITLE_PAGES) {
+ Sheet sh = wb.getSheet(page);
+ if (sh == null) continue;
+ int lastRow = Math.min(sh.getLastRowNum(), 40);
+ for (int r = 0; r <= lastRow; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ for (int c = 0; c <= 1; c++) {
+ Cell cell = row.getCell(c);
+ if (cell == null || cell.getCellType() != CellType.STRING) continue;
+ String t = cell.getStringCellValue();
+ if (t == null || !looksLikeTitle(t)) continue;
+ String nt = t.replaceAll("(\\d{4})骞�", year + "骞�")
+ .replaceAll("(\\d{1,2})鏈�", month + "鏈�");
+ if (!nt.equals(t)) {
+ cell.setCellValue(nt);
+ changed++;
+ }
+ }
+ }
+ }
+ return changed;
+ }
+
+ private boolean looksLikeTitle(String t) {
+ for (String m : TITLE_MARKERS) {
+ if (t.contains(m)) return true;
+ }
+ return false;
}
/** 瀵瑰崟椤垫墿鍒楋紝杩斿洖鎻掑叆浣嶇疆锛�0 鍩哄垪鍙凤級銆� */
@@ -143,8 +222,8 @@
if (headerRows.contains(r)) continue;
Row row = sh.getRow(r);
if (row == null) continue;
- cloneDataCell(row, srcVal, insertCol, shiftable, insertCol);
- cloneDataCell(row, srcYoy, insertCol + 1, shiftable, insertCol);
+ cloneDataCell(row, srcVal, insertCol, shiftable);
+ cloneYoyCell(row, srcYoy, insertCol + 1, insertCol, lastYearSameMonth + 2);
}
// 5) 绱鍒楄ˉ褰撴湀椤癸紱绱鍚屾瘮鍒嗘瘝琛ュ幓骞村悓鏈堝垪
int cumCol = insertCol + 2, cumYoyCol = insertCol + 3;
@@ -185,21 +264,46 @@
return insertCol;
}
- /** 琛ㄥご琛岋紙0 鍩猴級锛氬惈銆屽勾+鏈堛�嶈〃澶村崟鍏冩牸鐨勮锛涜建閬撱�佽疆娓℃湁涓や釜鍧� 鈫� 涓よ銆� */
+ /**
+ * 琛ㄥご琛岋紙0 鍩猴級锛氳建閬撱�佽疆娓¤繖绫讳竴椤典袱涓潡鐨勬湁涓よ銆�
+ *
+ * <p>2026-10-12 浜屾淇锛氬厛鍙栥�屾湀浠借〃澶存牸鏈�澶氱殑閭ｄ竴琛屻�嶄綔鍩哄噯锛屾妸瀹冪殑鏈堜唤鍒楀綋浣滄爣鍑嗗垪锛�
+ * 鍙湁杩欎簺鍒椾笂澶у崐鏄湀浠芥牸鐨勮鎵嶇畻琛ㄥご琛屻�傚墠鐗堝彧瑕佹眰銆屾暣琛� 鈮�2 涓湀浠芥牸銆嶏紝
+ * 浼氭妸鏁版嵁琛岄噷鎭板ソ钀芥垚鏃ユ湡搴忓垪鐨勬暟鍊煎綋鎴愯〃澶� 鈫� 鎵╁垪鏃舵妸鏈堜唤鏃ユ湡鍐欒繘鏁版嵁琛�
+ * 锛�2026-09 姣嶇増 ` 璐ц繍!S11`銆乣鍑虹!S6` 绛� 13 鏍艰鍐欐垚 46266锛夈��
+ */
private List<Integer> headerRowsOf(Sheet sh) {
- List<Integer> rows = new ArrayList<>();
- int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
- for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
+ int first = sh.getFirstRowNum();
+ int lastRow = Math.min(sh.getLastRowNum(), first + HEADER_SCAN_ROWS);
+ List<Integer> base = new ArrayList<>();
+ for (int r = first; r <= lastRow; r++) {
Row row = sh.getRow(r);
if (row == null) continue;
- for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
- if (yearMonthOf(row.getCell(c)) != null) {
- rows.add(r);
- break;
- }
+ List<Integer> cols = monthColsOf(row);
+ if (cols.size() > base.size()) base = cols;
+ }
+ List<Integer> rows = new ArrayList<>();
+ if (base.size() < 2) return rows;
+ int need = Math.max(2, (base.size() + 1) / 2);
+ for (int r = first; r <= lastRow; r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ int hit = 0;
+ for (int c : base) {
+ if (yearMonthOf(row.getCell(c)) != null) hit++;
}
+ if (hit >= need) rows.add(r);
}
return rows;
+ }
+
+ /** 琛屽唴鎵�鏈夈�屽勾+鏈堛�嶈〃澶存牸鐨勫垪鍙凤紙0 鍩猴級 */
+ private List<Integer> monthColsOf(Row row) {
+ List<Integer> cols = new ArrayList<>();
+ for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
+ if (yearMonthOf(row.getCell(c)) != null) cols.add(c);
+ }
+ return cols;
}
private Map<Integer, Integer> monthCols(Sheet sh, int year) {
@@ -283,17 +387,47 @@
}
}
- private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable, int insertCol) {
+ private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable) {
Cell s = row.getCell(srcCol);
if (s == null) return;
Cell d = getOrCreate(row, dstCol);
d.setBlank();
d.setCellStyle(s.getCellStyle());
if (s.getCellType() == CellType.FORMULA) {
- d.setCellFormula(shiftRefs(s.getCellFormula(), insertCol, shiftable));
+ d.setCellFormula(shiftRefsAll(s.getCellFormula(), shiftable));
}
}
+ /** 褰撴湀鍚屾瘮鍒楋細涓嶅厠闅嗕笂鏈堝叕寮忥紝鐩存帴鎸夈�屾湰鏈堝�� / 鍘诲勾鍚屾湀 鈭� 1銆嶉噸鍐欙紙鍘诲勾鍚屾湀鍒楀凡鎸夎〃澶村畾浣嶏級 */
+ private void cloneYoyCell(Row row, int srcCol, int dstCol, int valCol0, int denCol0) {
+ Cell s = row.getCell(srcCol);
+ if (s == null) return;
+ Cell d = getOrCreate(row, dstCol);
+ d.setBlank();
+ d.setCellStyle(s.getCellStyle());
+ if (s.getCellType() == CellType.FORMULA) {
+ int excelRow = row.getRowNum() + 1;
+ d.setCellFormula(colLetter(valCol0) + excelRow + "/" + colLetter(denCol0) + excelRow + "-1");
+ }
+ }
+
+ /** 鐩稿澶嶅埗鐢ㄧ殑骞崇Щ锛氭妸鏈〉/宸叉墿鍒楅〉鐨勫紩鐢ㄦ暣浣� +2锛堜笉鍋氬垪鍙烽槇鍊煎垽鏂級 */
+ private String shiftRefsAll(String formula, Set<String> shiftable) {
+ Matcher m = REF.matcher(formula);
+ StringBuffer sb = new StringBuffer();
+ while (m.find()) {
+ String sheet = m.group(2) != null ? m.group(2) : m.group(3);
+ if ((sheet != null && !shiftable.contains(sheet)) || isFunctionName(formula, m)) {
+ m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
+ continue;
+ }
+ int colIdx = colIndex(m.group(5));
+ m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
+ }
+ m.appendTail(sb);
+ return sb.toString();
+ }
+
private void shiftAllRefs(Sheet sh, int insertCol, Set<String> shiftable) {
for (int r = 0; r <= sh.getLastRowNum(); r++) {
Row row = sh.getRow(r);
--
Gitblit v1.9.1