package com.trafficaudit.reportexport.calc;
|
|
import com.trafficaudit.common.util.RegionUtil;
|
import com.trafficaudit.reportexport.calc.FreightCalc.FreightMatrix;
|
import com.trafficaudit.reportexport.calc.FreightCalc.FreightMetrics;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 汇总整本“数据驱动回填”引擎（M2 台账层，2026-09-09 接入）。
|
*
|
* 目标：生成任意月份《道路运输量汇总表（整本）》时不再依赖当月人工母版；
|
* 母版只当版式底稿，当月值按已验证口径从库现算写入（班线包车 2026-07 与人工母版
|
* 350 格 0 差异、公交/出租/轨道轮渡同批 0 差异）。
|
*
|
* 本版覆盖 4 张台账页：班线包车、公交、出租、轨道轮渡；
|
* 网约车依赖《网约车订单及全省总量》拆分输入、货运页在 M3 接入，后续版本补充。
|
*
|
* 写入规则：
|
* 1) 只写“市州明细行”的 2026 年 m 月当月值列（m 月值列 = 第 2+2*(m-1) 列，0 基，即 C 起）；
|
* 2) 全省行（=17 市州求和公式）与跨页合成页均为公式，不动，Excel 打开自动重算；
|
* 3) 2025 同期参照列保留母版缓存值（同比公式自动引用）；
|
* 4) 源数据整月缺失时收集提示、不回填；市州当月全为 0 时保持母版空单元格。
|
*/
|
@Service
|
public class SummaryWorkbookFiller {
|
|
@Resource
|
private MidCalc midCalc;
|
@Resource
|
private PaxCalc paxCalc;
|
@Resource
|
private FreightCalc freightCalc;
|
@Resource
|
private WycSplitCalc wycSplitCalc;
|
|
/** 2026 年 1 月当月值列（0 基，即 C 列） */
|
private static final int FIRST_MONTH_COL = 2;
|
|
/** 回填 1..keepMonths 月台账页，返回写入格数；缺数提示写入 problems */
|
public int fillMonthlyLedger(XSSFWorkbook wb, String period, int year, int keepMonths, List<String> problems) {
|
if (wb == null || keepMonths <= 0) return 0;
|
if (problems == null) problems = new ArrayList<>();
|
String yearPrefix = year + "-";
|
int filled = 0;
|
filled += fillCityRows(wb, "班线包车", midCalc.banxianMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
|
filled += fillCityRows(wb, "公交", paxCalc.busMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
|
filled += fillCityRows(wb, "出租", paxCalc.taxiMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
|
filled += fillTrackFerry(wb, paxCalc.railFerryMonthly(yearPrefix, keepMonths), year, keepMonths, problems);
|
filled += fillFreightSheet(wb, period, keepMonths, problems);
|
filled += fillWycSheet(wb, period, keepMonths, problems);
|
return filled;
|
}
|
|
/** 通用台账页（班线包车/公交/出租）回填：按“列 A 市州名 + 列 B 指标文案”定位行，不写死行号 */
|
private int fillCityRows(XSSFWorkbook wb, String sheetName,
|
Map<Integer, Map<String, double[]>> monthly,
|
int year, int keepMonths, List<String> problems, boolean wyc) {
|
Sheet sh = wb.getSheet(sheetName);
|
if (sh == null) {
|
problems.add(sheetName + "页不存在（母版缺失该页），跳过自动回填");
|
return 0;
|
}
|
int cap = monthColumnCapacity(sh);
|
int mMax = Math.min(keepMonths, cap);
|
if (mMax <= 0) {
|
problems.add(sheetName + "页未识别到 2026 年累计列，跳过自动回填");
|
return 0;
|
}
|
// 1) 市州起始行（列 A 为规范市州名）
|
Map<String, Integer> cityStart = new LinkedHashMap<>();
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String city = cityOf(row.getCell(0));
|
if (city != null && !cityStart.containsKey(city)) cityStart.put(city, r);
|
}
|
if (cityStart.isEmpty()) {
|
problems.add(sheetName + "页未识别到市州行，跳过自动回填");
|
return 0;
|
}
|
List<Integer> starts = new ArrayList<>(cityStart.values());
|
int filled = 0;
|
List<String> missingMonths = new ArrayList<>();
|
for (int m = 1; m <= mMax; m++) {
|
if (!monthly.containsKey(m)) missingMonths.add(m + "月");
|
}
|
if (!missingMonths.isEmpty()) {
|
problems.add(sheetName + "缺 " + year + " 年 " + String.join("、", missingMonths) + " 源数据（未回填）");
|
}
|
for (int i = 0; i < starts.size(); i++) {
|
String city = findCityByStart(sh, starts.get(i));
|
if (city == null) continue;
|
int end = (i + 1 < starts.size()) ? starts.get(i + 1) - 1 : sh.getLastRowNum();
|
// 2) 行内各指标所在行（B 列文案 → 指标位 0..3）
|
int[] metricRow = new int[]{-1, -1, -1, -1};
|
for (int r = starts.get(i); r <= end; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
int idx = metricIndexOf(text(row.getCell(1)));
|
if (idx >= 0 && metricRow[idx] < 0) metricRow[idx] = r;
|
}
|
if (metricRow[0] < 0 && metricRow[1] < 0) continue;
|
for (int m = 1; m <= mMax; m++) {
|
Map<String, double[]> mm = monthly.get(m);
|
if (mm == null) continue;
|
double[] arr = mm.get(city);
|
if (arr == null || (arr[0] == 0.0 && arr[1] == 0.0)) continue;
|
int col = FIRST_MONTH_COL + 2 * (m - 1);
|
for (int idx = 0; idx < 4; idx++) {
|
if (metricRow[idx] < 0) continue;
|
double val = arr[idx];
|
if (val == 0.0) continue;
|
filled += setNumeric(sh, metricRow[idx], col, val);
|
}
|
}
|
}
|
return filled;
|
}
|
|
/** 货运页回填：全省块 + 17 市州块的 规上/规下/合计 × 货运量、周转量（逐月现算；当月值列=2+2*(m-1)） */
|
private int fillFreightSheet(XSSFWorkbook wb, String period, int keepMonths, List<String> problems) {
|
Sheet sh = wb.getSheet(" 货运");
|
if (sh == null) sh = wb.getSheet("货运");
|
if (sh == null) {
|
problems.add("货运页不存在（母版缺失该页），跳过自动回填");
|
return 0;
|
}
|
int cap = monthColumnCapacity(sh);
|
int mMax = Math.min(keepMonths, cap);
|
if (mMax <= 0) {
|
problems.add("货运页未识别到 2026 年累计列，跳过自动回填");
|
return 0;
|
}
|
int year = periodYear(period);
|
// 块（全省/市州）：指标键 -> 行号
|
Map<String, Map<String, Integer>> blockRows = new LinkedHashMap<>();
|
String cur = null;
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String a = text(row.getCell(0));
|
if (a != null && !a.trim().isEmpty()) {
|
String norm = RegionUtil.normalizeCityName(a.trim());
|
if (FreightCalc.PROVINCE.equals(norm) || RegionUtil.cityList().contains(norm)) {
|
cur = norm;
|
blockRows.computeIfAbsent(cur, k -> new LinkedHashMap<>());
|
} else {
|
cur = null; // 标题行 / 非全省非市州 -> 脱离块
|
}
|
}
|
if (cur == null) continue;
|
String key = freightMetricKey(text(row.getCell(1)));
|
if (key == null) continue;
|
blockRows.get(cur).putIfAbsent(key, r);
|
}
|
if (blockRows.isEmpty()) {
|
problems.add("货运页未识别到全省/市州行，跳过自动回填");
|
return 0;
|
}
|
int filled = 0;
|
// 只回填目标期当月：1..目标月-1 的值以母版（含同期备份母版还原）为准，避免改写已确认口径
|
int targetM = Math.min(periodMonth(period), mMax);
|
for (int m = targetM; m <= targetM; m++) {
|
String mp = String.format("%04d-%02d", year, m);
|
FreightMatrix mx;
|
try {
|
mx = freightCalc.calc(mp);
|
} catch (Exception e) {
|
problems.add("货运页 " + mp + " 取数失败：" + e.getMessage());
|
continue;
|
}
|
int col = FIRST_MONTH_COL + 2 * (m - 1);
|
for (Map.Entry<String, Map<String, Integer>> e : blockRows.entrySet()) {
|
FreightMetrics fm = mx.getMonth().get(e.getKey());
|
if (fm == null) continue;
|
Map<String, Integer> rows = e.getValue();
|
// 1 月列是公式的“派生行”（全省合计 货运量/周转量、市州 规上+规下周转量）不写值，
|
// 由 ensureMonthFormulas 把公式按月右移，保持与母版一致
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("totalFreight")), col, fm.getTotalFreight());
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("aboveFreight")), col, fm.getAboveFreight());
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("belowFreight")), col, fm.getBelowFreight());
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("totalTurnover")), col, fm.getTotalTurnover());
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("aboveTurnover")), col, fm.getAboveTurnover());
|
filled += setFreightCell(sh, skipIfDerived(sh, rows.get("belowTurnover")), col, fm.getBelowTurnover());
|
}
|
}
|
return filled;
|
}
|
|
/** 该行 1 月列（C）是公式 -> 派生行，返回 null 表示不回填数值 */
|
private Integer skipIfDerived(Sheet sh, Integer r) {
|
if (r == null) return null;
|
Row row = sh.getRow(r);
|
if (row == null) return r;
|
Cell c = row.getCell(FIRST_MONTH_COL);
|
if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) return null;
|
return r;
|
}
|
|
private int setFreightCell(Sheet sh, Integer r, int col, Double v) {
|
if (r == null || v == null) return 0;
|
return setNumeric(sh, r, col, v);
|
}
|
|
/** 货运页 B 列指标文案 -> 指标键（规上+规下 / 规上 / 规下 × 货运量 / 周转量） */
|
private String freightMetricKey(String b) {
|
if (b == null) return null;
|
String s = b.replaceAll("\\s+", "");
|
if (s.isEmpty()) return null;
|
boolean turnover = s.contains("周转量");
|
if (s.contains("规上+规下")) return turnover ? "totalTurnover" : "totalFreight";
|
if (s.contains("规上")) return turnover ? "aboveTurnover" : "aboveFreight";
|
if (s.contains("规下")) return turnover ? "belowTurnover" : "belowFreight";
|
return turnover ? "totalTurnover" : "totalFreight"; // 全省块的“货运量/货物周转量”= 合计
|
}
|
|
/** 网约车页回填：17 市州 的 客运量/周转量/其中城市内客运量/其中城市内周转量（逐月现算）
|
* 全省行（=17 市州求和公式）由 ensureMonthFormulas 按月扩列，不在此处写值。 */
|
private int fillWycSheet(XSSFWorkbook wb, String period, int keepMonths, List<String> problems) {
|
Sheet sh = wb.getSheet("网约车");
|
if (sh == null) {
|
problems.add("网约车页不存在（母版缺失该页），跳过自动回填");
|
return 0;
|
}
|
int cap = monthColumnCapacity(sh);
|
int mMax = Math.min(keepMonths, cap);
|
if (mMax <= 0) {
|
problems.add("网约车页未识别到 2026 年累计列，跳过自动回填");
|
return 0;
|
}
|
int year = periodYear(period);
|
Map<String, Map<String, Integer>> cityMetricRows = new LinkedHashMap<>();
|
String cur = null;
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String a = text(row.getCell(0));
|
if (a != null && !a.trim().isEmpty()) {
|
String norm = RegionUtil.normalizeCityName(a.trim());
|
cur = RegionUtil.cityList().contains(norm) ? norm : null;
|
if (cur != null) cityMetricRows.computeIfAbsent(cur, k -> new LinkedHashMap<>());
|
}
|
if (cur == null) continue;
|
String key = wycMetricKey(text(row.getCell(1)));
|
if (key == null) continue;
|
cityMetricRows.get(cur).putIfAbsent(key, r);
|
}
|
if (cityMetricRows.isEmpty()) {
|
problems.add("网约车页未识别到市州行，跳过自动回填");
|
return 0;
|
}
|
int filled = 0;
|
// 只回填目标期当月（历史月以母版/历史生成件为准）
|
int targetM = Math.min(periodMonth(period), mMax);
|
for (int m = targetM; m <= targetM; m++) {
|
String mp = String.format("%04d-%02d", year, m);
|
WycSplitCalc.WycResult wr;
|
try {
|
wr = wycSplitCalc.calc(mp);
|
} catch (Exception e) {
|
if (m == mMax) {
|
problems.add("网约车页 " + mp + " 源数据缺失，当月未回填：" + e.getMessage());
|
}
|
continue;
|
}
|
int col = FIRST_MONTH_COL + 2 * (m - 1);
|
for (Map.Entry<String, Map<String, Integer>> e : cityMetricRows.entrySet()) {
|
WycSplitCalc.WycMetrics wm = wr.getByCity().get(e.getKey());
|
if (wm == null) continue;
|
Map<String, Integer> rows = e.getValue();
|
filled += setFreightCell(sh, rows.get("pax"), col, wm.getTotalPax());
|
filled += setFreightCell(sh, rows.get("turnover"), col, wm.getTotalTurnover());
|
filled += setFreightCell(sh, rows.get("cityPax"), col, wm.getCityPax());
|
filled += setFreightCell(sh, rows.get("cityTurnover"), col, wm.getCityTurnover());
|
}
|
}
|
return filled;
|
}
|
|
/** 网约车页 B 列指标文案 -> 指标键 */
|
private String wycMetricKey(String b) {
|
if (b == null) return null;
|
String s = b.replaceAll("\\s+", "");
|
if (s.isEmpty()) return null;
|
boolean city = s.contains("城市内");
|
boolean turnover = s.contains("周转量");
|
if (city) return turnover ? "cityTurnover" : "cityPax";
|
return turnover ? "turnover" : "pax";
|
}
|
|
/** 轨道轮渡页（轨道=武汉/黄石；轮渡仅武汉）专用回填 */
|
private int fillTrackFerry(XSSFWorkbook wb, Map<Integer, Map<String, double[]>> monthly,
|
int year, int keepMonths, List<String> problems) {
|
String sheetName = "轨道、轮渡";
|
Sheet sh = wb.getSheet(sheetName);
|
if (sh == null) return 0;
|
int cap = monthColumnCapacity(sh);
|
int mMax = Math.min(keepMonths, cap);
|
if (mMax <= 0) return 0;
|
// 轨道块与轮渡块标题行（列 A 文案）
|
int trackTitle = -1, ferryTitle = -1;
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
String a = text(sh.getRow(r) == null ? null : sh.getRow(r).getCell(0));
|
if (a == null) continue;
|
if (trackTitle < 0 && a.startsWith("轨道客运量")) trackTitle = r;
|
else if (ferryTitle < 0 && a.startsWith("轮渡客运量")) ferryTitle = r;
|
}
|
if (trackTitle < 0 || ferryTitle < 0) {
|
problems.add(sheetName + "页未识别到轨道/轮渡块标题，跳过自动回填");
|
return 0;
|
}
|
int filled = 0;
|
filled += fillFerryBlock(sh, monthly, trackTitle + 3, ferryTitle - 1, 0, mMax, problems, sheetName);
|
filled += fillFerryBlock(sh, monthly, ferryTitle + 3, sh.getLastRowNum(), 2, mMax, problems, sheetName);
|
return filled;
|
}
|
|
/** 填充一段“标题行后数据区”：base 为该块指标位基（轨道 0 / 轮渡 2）；block 内
|
* 每市州 2 行：客运量行（列 A=市州名）、旅客周转量行（A 空，B 含“周转量”） */
|
private int fillFerryBlock(Sheet sh, Map<Integer, Map<String, double[]>> monthly,
|
int start, int end, int base, int mMax,
|
List<String> problems, String sheetName) {
|
int filled = 0;
|
// 收集市州客运量行
|
Map<String, Integer> paxRow = new LinkedHashMap<>();
|
for (int r = start; r <= end; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String city = cityOf(row.getCell(0));
|
if (city != null) paxRow.put(city, r);
|
}
|
for (Map.Entry<String, Integer> e : paxRow.entrySet()) {
|
String city = e.getKey();
|
int rp = e.getValue();
|
int rt = findTurnoverRow(sh, rp + 1, Math.min(end, rp + 6));
|
if (rt < 0) continue;
|
for (int m = 1; m <= mMax; m++) {
|
Map<String, double[]> mm = monthly.get(m);
|
if (mm == null) continue;
|
double[] arr = mm.get(city);
|
if (arr == null) continue;
|
int col = FIRST_MONTH_COL + 2 * (m - 1);
|
double pax = arr[base];
|
double turn = arr[base + 1];
|
if (pax != 0.0) filled += setNumeric(sh, rp, col, pax);
|
if (turn != 0.0) filled += setNumeric(sh, rt, col, turn);
|
}
|
}
|
return filled;
|
}
|
|
private int findTurnoverRow(Sheet sh, int from, int to) {
|
for (int r = from; r <= to; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String a = text(row.getCell(0));
|
if (a != null && !a.trim().isEmpty()) return -1; // 进入下一市州块
|
String b = text(row.getCell(1));
|
if (b != null && b.contains("周转量")) return r;
|
}
|
return -1;
|
}
|
|
/** 写数值：格不存在则建格；格带日期样式（如公交/出租 8 月列继承了表头“yyyy年m月”）时改回同行 1 月列的数值样式，避免数值显示成日期 */
|
private int setNumeric(Sheet sh, int r, int col, double val) {
|
Row row = sh.getRow(r);
|
if (row == null) row = sh.createRow(r);
|
Cell c = row.getCell(col);
|
if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
|
return 0; // 母版本来就靠公式算的行（合计/同页求和）保持公式，交 Excel 重算
|
}
|
Cell ref = row.getCell(FIRST_MONTH_COL);
|
boolean needStyle = false;
|
if (c == null) {
|
c = row.createCell(col);
|
needStyle = true;
|
} else if (c.getCellType() == org.apache.poi.ss.usermodel.CellType.BLANK
|
|| (c.getCellType() == org.apache.poi.ss.usermodel.CellType.NUMERIC
|
&& org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(c))) {
|
// 母版把 8 月列的表头日期格式带到了数据格（或有格式无值），写值前改回同行 1 月列的数字格式
|
needStyle = true;
|
}
|
if (needStyle && ref != null && ref.getCellStyle() != null) {
|
c.setCellStyle(ref.getCellStyle());
|
}
|
c.setCellValue(val);
|
return 1;
|
}
|
|
/** 行 B 列文案 → 指标位：0 客运量 / 1 旅客周转量 / 2 其中城市内客运量或其中个体客运量 / 3 对应周转量 */
|
private int metricIndexOf(String b) {
|
if (b == null) return -1;
|
if (b.contains("其中个体旅客周转量") || b.contains("其中城市内旅客周转量")) return 3;
|
if (b.contains("其中个体客运量") || b.contains("其中城市内客运量")) return 2;
|
if (b.contains("旅客周转量")) return 1;
|
if (b.contains("客运量")) return 0;
|
return -1;
|
}
|
|
/** 列 A 文案 → 规范市州名（湖北省/全省、非市州行返回 null） */
|
private String cityOf(Cell a) {
|
String t = text(a);
|
if (t == null || t.trim().isEmpty()) return null;
|
String norm = RegionUtil.normalizeCityName(t.trim());
|
return RegionUtil.cityList().contains(norm) ? norm : null;
|
}
|
|
private String findCityByStart(Sheet sh, int startRow) {
|
Row row = sh.getRow(startRow);
|
return row == null ? null : cityOf(row.getCell(0));
|
}
|
|
/**
|
* 为第 month 月补“当月值”列公式：取同一行最近一个月仍为公式的值列，整列右移过来
|
* （如公交 7 月列 O 的 =O9+O13+... -> 8 月列 Q 的 =Q9+Q13+...）。
|
* 只补空单元格；已有数值（货运全省块/市州块由数据回填）或已有公式的格不动。
|
*/
|
public int ensureMonthFormulas(XSSFWorkbook wb, int year, int month) {
|
if (wb == null || month <= 1) return 0;
|
int targetCol = FIRST_MONTH_COL + 2 * (month - 1);
|
int written = 0;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sh = wb.getSheetAt(i);
|
if (sh == null || !isFilledLedgerSheet(sh.getSheetName())) continue;
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
if (hasContent(row.getCell(targetCol))) continue;
|
Cell srcCell = null;
|
int srcMonth = -1;
|
for (int s = month - 1; s >= 1; s--) {
|
Cell c = row.getCell(FIRST_MONTH_COL + 2 * (s - 1));
|
if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
|
srcCell = c;
|
srcMonth = s;
|
break;
|
}
|
}
|
if (srcCell == null) continue;
|
String f = srcCell.getCellFormula();
|
if (f == null || f.isEmpty()) continue;
|
Cell target = row.getCell(targetCol);
|
if (target == null) target = row.createCell(targetCol);
|
if (srcCell.getCellStyle() != null) target.setCellStyle(srcCell.getCellStyle());
|
target.setCellFormula(shiftFormulaColumns(f, 2 * (month - srcMonth)));
|
written++;
|
}
|
}
|
return written;
|
}
|
|
/** 该格是否已有内容（数值/文本/公式）；仅带样式的空壳（如母版里继承表头日期格式的格子）视为空 */
|
private boolean hasContent(Cell c) {
|
if (c == null) return false;
|
switch (c.getCellType()) {
|
case BLANK: return false;
|
case STRING: return c.getStringCellValue() != null && !c.getStringCellValue().trim().isEmpty();
|
default: return true;
|
}
|
}
|
|
/** 参与逐月数据回填的台账页（货运/班线包车/公交/出租/网约车/轨道轮渡） */
|
private boolean isFilledLedgerSheet(String name) {
|
if (name == null) return false;
|
String n = name.trim();
|
return "货运".equals(n) || "班线包车".equals(n) || "公交".equals(n)
|
|| "出租".equals(n) || "网约车".equals(n) || "轨道、轮渡".equals(n);
|
}
|
|
/** 0 基列号 + 1 基行号 -> "Q5" */
|
private String cellRef(int col0, int row1) {
|
StringBuilder sb = new StringBuilder();
|
int n = col0 + 1;
|
while (n > 0) {
|
int rem = (n - 1) % 26;
|
sb.insert(0, (char) ('A' + rem));
|
n = (n - 1) / 26;
|
}
|
return sb.toString() + row1;
|
}
|
|
/** 从 "2026-08" 取月份（解析失败返回 1） */
|
private int periodMonth(String period) {
|
if (period != null && period.length() >= 7) {
|
try { return Integer.parseInt(period.substring(5, 7)); } catch (Exception ignore) { }
|
}
|
return 1;
|
}
|
|
/** 从 "2026-08" 取年份 */
|
private int periodYear(String period) {
|
if (period != null && period.length() >= 4) {
|
try { return Integer.parseInt(period.substring(0, 4)); } catch (Exception ignore) { }
|
}
|
return 0;
|
}
|
|
/**
|
* 为第 m 月补“与去年同比”公式：把第 m-1 月同比列的公式整体右移 2 列
|
* （如公交 7 月 P5 公式 =O5/AH5-1 -> 8 月 R5 =Q5/AJ5-1），
|
* 因此同比分母取的就是汇总表 2025 年同月列（用户口径）。
|
*/
|
public int ensureMonthYoyFormulas(XSSFWorkbook wb, int year, int month) {
|
if (wb == null || month <= 1) return 0;
|
int valueCol = FIRST_MONTH_COL + 2 * (month - 1);
|
int yoyCol = valueCol + 1;
|
int prevYoyCol = yoyCol - 2;
|
int written = 0;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sh = wb.getSheetAt(i);
|
if (sh == null) continue;
|
if (!isMonthLedgerSheet(sh.getSheetName())) continue;
|
for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
Cell prev = row.getCell(prevYoyCol);
|
if (prev == null || prev.getCellType() != org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
|
Cell target = row.getCell(yoyCol);
|
if (target != null && target.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
|
String f = prev.getCellFormula();
|
if (f == null || f.isEmpty()) continue;
|
String shifted = shiftFormulaColumns(f, 2);
|
if (target == null) {
|
target = row.createCell(yoyCol);
|
if (prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
|
}
|
try {
|
target.setCellFormula(shifted);
|
written++;
|
} catch (Exception e) {
|
throw new IllegalStateException(String.format(
|
"同比公式补写失败：sheet=%s cell=%s 原式=%s 目标式=%s",
|
sh.getSheetName(), cellRef(yoyCol, r + 1), f, shifted), e);
|
}
|
}
|
}
|
return written;
|
}
|
|
/** 随月扩列的长表页（表头在 1..3 行、月份组为“当月值+同比”交替） */
|
private boolean isMonthLedgerSheet(String name) {
|
if (name == null) return false;
|
String n = name.trim();
|
return "货运".equals(n) || "公路总客运".equals(n) || "班线包车".equals(n) || "城市客运".equals(n)
|
|| "公交".equals(n) || "出租".equals(n) || "网约车".equals(n)
|
|| "轨道、轮渡".equals(n) || "轨道轮渡".equals(n)
|
|| "中口径明细".equals(n) || "中口径客运量".equals(n);
|
}
|
|
/** 公式内所有 A1 形式的列引用右移 delta 列（SUM 等函数名后无数字，不会被匹配） */
|
private String shiftFormulaColumns(String formula, int delta) {
|
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 = idx + delta;
|
StringBuilder nc = new StringBuilder();
|
while (nidx > 0) {
|
int rem = (nidx - 1) % 26;
|
nc.insert(0, (char) ('A' + rem));
|
nidx = (nidx - 1) / 26;
|
}
|
m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(m.group(1) + nc + m.group(3) + m.group(4)));
|
}
|
m.appendTail(sb);
|
return sb.toString();
|
}
|
|
/** 月份容量：优先取表头（前 5 行）“2026…累计”列反推；货运页无该文案，
|
* 再用表头里最后一个“2026年M月”/日期格式的月列兜底（如货运 Q3=2026-08-01 -> 8） */
|
private int monthColumnCapacity(Sheet sh) {
|
int byCumulative = 0;
|
int byMonthHeader = 0;
|
for (int r = 0; r <= 4 && r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (Cell c : row) {
|
if (c == null) continue;
|
int idx = c.getColumnIndex();
|
if (idx <= FIRST_MONTH_COL || (idx - FIRST_MONTH_COL) % 2 != 0) continue; // 只看“当月值”列
|
String t = text(c);
|
if (byCumulative == 0 && t != null && t.contains("2026") && t.contains("累计")) {
|
byCumulative = (idx - FIRST_MONTH_COL) / 2;
|
}
|
if (t != null && t.contains("2026") && t.contains("月") && !t.contains("累计")
|
&& !t.contains("同比")) {
|
byMonthHeader = Math.max(byMonthHeader, (idx - FIRST_MONTH_COL) / 2 + 1);
|
} else if (c.getCellType() == org.apache.poi.ss.usermodel.CellType.NUMERIC
|
&& org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(c)) {
|
byMonthHeader = Math.max(byMonthHeader, (idx - FIRST_MONTH_COL) / 2 + 1);
|
}
|
}
|
}
|
int cap = Math.max(byCumulative, byMonthHeader);
|
return cap > 0 ? cap : 7;
|
}
|
|
private String text(Cell c) {
|
if (c == null) return null;
|
switch (c.getCellType()) {
|
case STRING: return c.getStringCellValue();
|
case NUMERIC: return Double.toString(c.getNumericCellValue());
|
default: return null;
|
}
|
}
|
}
|
