package com.trafficaudit.reportexport.calc;
|
|
import com.trafficaudit.common.util.RegionUtil;
|
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;
|
|
/** 2026 年 1 月当月值列（0 基，即 C 列） */
|
private static final int FIRST_MONTH_COL = 2;
|
|
/** 回填 1..keepMonths 月台账页，返回写入格数；缺数提示写入 problems */
|
public int fillMonthlyLedger(XSSFWorkbook wb, 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);
|
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;
|
}
|
|
/** 轨道轮渡页（轨道=武汉/黄石；轮渡仅武汉）专用回填 */
|
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;
|
}
|
|
/** 写数值：格不存在则建格并复制同行 1 月列样式；返回 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 = row.createCell(col);
|
Cell ref = row.getCell(FIRST_MONTH_COL);
|
if (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));
|
}
|
|
/** 月份容量：在表头（前 5 行）找“2026…累计”列，容量=(累计列-1 月列)/2 */
|
private int monthColumnCapacity(Sheet sh) {
|
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;
|
String t = text(c);
|
if (t != null && t.contains("2026") && t.contains("累计") && c.getColumnIndex() > FIRST_MONTH_COL) {
|
return (c.getColumnIndex() - FIRST_MONTH_COL) / 2;
|
}
|
}
|
}
|
return 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;
|
}
|
}
|
}
|
