feat(投资): 市州月报统一填报模板 + 县(市、区)列解析 + 表头行判定收紧

zhizhijie

2026-09-20 a08c70db67e49bf6cd125df04ebaf68c26f0d8dd

 traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/SummaryWorkbookFiller.java

@@ -7,6 +7,8 @@
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
@@ -33,6 +35,8 @@
 */
@Service
public class SummaryWorkbookFiller {

    private static final Logger log = LoggerFactory.getLogger(SummaryWorkbookFiller.class);

    @Resource
    private MidCalc midCalc;
@@ -429,9 +433,11 @@
        if (wb == null || month <= 1) return 0;
        int targetCol = FIRST_MONTH_COL + 2 * (month - 1);
        int written = 0;
        int carried = 0;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            if (sh == null || !isFilledLedgerSheet(sh.getSheetName())) continue;
            if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
            boolean derivedSheet = isDerivedFormulaSheet(sh.getSheetName());
            for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
@@ -446,7 +452,21 @@
                        break;
                    }
                }
                if (srcCell == null) continue;
                if (srcCell == null) {
                    if (!derivedSheet) continue;
                    // 派生页（城市客运/中口径明细/公路总客运）里「各月都是同一个常量」的结构性常量行
                    // （如中口径明细 武汉 城际城乡巡游出租=0），按月沿用该常量，避免当月值列空着导致累计缺项
                    double struct = structuralConstant(row, month);
                    if (Double.isNaN(struct)) continue;
                    Cell target = row.getCell(targetCol);
                    if (target == null) target = row.createCell(targetCol);
                    Cell prev = row.getCell(FIRST_MONTH_COL + 2 * (month - 2));
                    if (prev != null && prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
                    target.setCellValue(struct);
                    carried++;
                    written++;
                    continue;
                }
                String f = srcCell.getCellFormula();
                if (f == null || f.isEmpty()) continue;
                Cell target = row.getCell(targetCol);
@@ -456,7 +476,31 @@
                written++;
            }
        }
        if (carried > 0 && log != null) {
            log.info("汇总工作簿：派生页结构性常量行按月沿用 {} 格（第 {} 月）", carried, month);
        }
        return written;
    }

    /** 该行 1..month-1 月的当月值列是否全为同一个数值常量（结构性常量行，如各月恒为 0） */
    private double structuralConstant(Row row, int month) {
        if (row == null || month <= 2) return Double.NaN;
        Double v = null;
        for (int s = 1; s < month; s++) {
            Cell c = row.getCell(FIRST_MONTH_COL + 2 * (s - 1));
            if (c == null || c.getCellType() != org.apache.poi.ss.usermodel.CellType.NUMERIC) return Double.NaN;
            double d = c.getNumericCellValue();
            if (v == null) v = d;
            else if (Math.abs(d - v) > 1e-9) return Double.NaN;
        }
        return v == null ? Double.NaN : v;
    }

    /** 派生页（当月值全部来自其他页/常量，纯公式驱动）：城市客运 / 中口径明细 / 公路总客运 / 中口径客运量 */
    private boolean isDerivedFormulaSheet(String name) {
        if (name == null) return false;
        String n = name.trim();
        return "城市客运".equals(n) || "中口径明细".equals(n) || "公路总客运".equals(n) || "中口径客运量".equals(n);
    }

    /** 该格是否已有内容（数值/文本/公式）；仅带样式的空壳（如母版里继承表头日期格式的格子）视为空 */
@@ -467,14 +511,6 @@
            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" */
@@ -529,11 +565,10 @@
                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());
                }
                String shifted = shiftFormulaToNextMonth(sh, f, year, month);
                if (target == null) target = row.createCell(yoyCol);
                // 母版把新月份的同比列做成了普通数值格式（显示成小数），统一沿用上月同比列的样式（百分数）
                if (prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
                try {
                    target.setCellFormula(shifted);
                    written++;
@@ -580,6 +615,250 @@
        return sb.toString();
    }

    /** 公式内 A1 列引用匹配（SUM 等函数名后无数字，不会被匹配） */
    private static final java.util.regex.Pattern A1_REF =
            java.util.regex.Pattern.compile("(?<![A-Za-z0-9_$])([$]?)([A-Z]{1,3})([$]?)([0-9]+)");
    /** “2025年8月” 这种单月表头 */
    private static final java.util.regex.Pattern YM_HEADER =
            java.util.regex.Pattern.compile("^(\\d{4})年(\\d{1,2})月$");
    /** “2025年1-7月累计” 这种区间累计辅助列（不属于任何单月） */
    private static final java.util.regex.Pattern YM_RANGE_CUM =
            java.util.regex.Pattern.compile("^\\d{4}年\\d{1,2}-\\d{1,2}月累计$");

    /** A1 列字母 -> 0 基列号（与 POI 的 Cell.getColumnIndex() 对齐，A=0、AJ=35） */
    private int colIndexOf(String letters) {
        int idx = 0;
        for (int i = 0; i < letters.length(); i++) idx = idx * 26 + (letters.charAt(i) - 'A' + 1);
        return idx - 1;
    }

    /** 0 基列号 -> "Q" */
    private String colLettersOf(int idx0) {
        StringBuilder sb = new StringBuilder();
        int n = idx0 + 1;
        while (n > 0) {
            int rem = (n - 1) % 26;
            sb.insert(0, (char) ('A' + rem));
            n = (n - 1) / 26;
        }
        return sb.toString();
    }

    /** 表头（前 5 行）建立 (年*100+月) -> 列号（0 基）：识别 “2025年8月” 文本格与 2025-08-01 这类日期格；
     *  同一 (年,月) 取最左一列；“2025年1-7月累计” 这类区间累计辅助列不属于任何单月，不参与映射 */
    private Map<Integer, Integer> yearMonthCols(Sheet sh, int year) {
        Map<Integer, Integer> m = new LinkedHashMap<>();
        if (sh == null) return m;
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Integer ym = yearMonthOf(row.getCell(c), year);
                if (ym != null && !m.containsKey(ym)) m.put(ym, c);
            }
        }
        return m;
    }

    /**
     * 单个月份表头 -> 年*100+月；只认今年/去年，其余一律不认（避免把 2024/2023 对比块、杂格当月份列）。
     * 两种写法：文本 “2026年8月”；数值日期序列（母版城市客运页 3..8 月的表头是 46235 这种裸序列、
     * 数字格式还是 General，只按“当月 1 号”判断，不能依赖 isCellDateFormatted）。
     */
    private Integer yearMonthOf(Cell cell, int year) {
        if (cell == null) return null;
        org.apache.poi.ss.usermodel.CellType t = cell.getCellType();
        if (t == org.apache.poi.ss.usermodel.CellType.STRING) {
            String v = cell.getStringCellValue();
            if (v == null || v.isEmpty()) return null;
            v = v.trim();
            if (v.contains("累计") || v.contains("同比")) return null;
            java.util.regex.Matcher m = YM_HEADER.matcher(v);
            if (!m.matches()) return null;
            int y = Integer.parseInt(m.group(1));
            if (y != year && y != year - 1) return null;
            return y * 100 + Integer.parseInt(m.group(2));
        }
        if (t == org.apache.poi.ss.usermodel.CellType.NUMERIC) {
            double v = cell.getNumericCellValue();
            if (v < 1 || v > 200000) return null;
            try {
                java.util.Calendar cal = java.util.Calendar.getInstance();
                cal.setTime(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v));
                if (cal.get(java.util.Calendar.DAY_OF_MONTH) != 1) return null;
                int y = cal.get(java.util.Calendar.YEAR);
                if (y != year && y != year - 1) return null;
                return y * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
            } catch (Exception ignore) {
                return null;
            }
        }
        return null;
    }

    /** 表头里 “20XX年a-b月累计” 这类区间累计辅助列的 0 基列号集合 */
    private java.util.Set<Integer> cumulativeHelperCols(Sheet sh) {
        java.util.Set<Integer> set = new java.util.HashSet<>();
        if (sh == null) return set;
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String v = cell.getStringCellValue();
                if (v != null && YM_RANGE_CUM.matcher(v.trim()).matches()) set.add(c);
            }
        }
        return set;
    }

    /** 月份映射的可用性守卫：去年与今年 1..month 各月列必须齐全且列号严格递增，
     *  任一不满足（表头没写月份、或抓到杂格）就返回空表，调用方退回「整体右移 2 列」的旧行为 */
    private Map<Integer, Integer> usableMonthCols(Sheet sh, int year, int month) {
        Map<Integer, Integer> usable = new LinkedHashMap<>();
        Map<Integer, Integer> all = yearMonthCols(sh, year);
        int prevLy = -1, prevCy = -1;
        for (int m = 1; m <= month; m++) {
            Integer ly = all.get((year - 1) * 100 + m);
            Integer cy = all.get(year * 100 + m);
            if (ly == null || cy == null || ly <= prevLy || cy <= prevCy) return new LinkedHashMap<>();
            prevLy = ly;
            prevCy = cy;
            usable.put((year - 1) * 100 + m, ly);
            usable.put(year * 100 + m, cy);
        }
        return usable;
    }

    /**
     * 「上月同比」公式 → 「本月同比」公式：公式里引用到月份列的引用按表头 (年,月) 顺延一个月，
     * 其余引用退回「整体右移 2 列」。
     *
     * 为什么不能一律右移 2 列：母版《城市客运》页在 2025 年段插了一列「2025年1-7月累计」辅助列，
     * 2025 各月列从 8 月起不再等距（2025年8月在 AK、不在 AJ），右移 2 列会落到辅助列上，
     * 于是 8 月同比被算成「8月值 ÷ 2025年1-7月累计 − 1」（2026-09-20 用户上报的问题）。
     */
    private String shiftFormulaToNextMonth(Sheet sh, String formula, int year, int month) {
        if (formula == null || formula.isEmpty()) return formula;
        Map<Integer, Integer> cols = usableMonthCols(sh, year, month);
        if (cols.isEmpty()) return shiftFormulaColumns(formula, 2);
        Map<Integer, Integer> colToYm = new LinkedHashMap<>();
        for (Map.Entry<Integer, Integer> e : cols.entrySet()) colToYm.put(e.getValue(), e.getKey());
        java.util.regex.Matcher m = A1_REF.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            int idx = colIndexOf(m.group(2));
            Integer ym = colToYm.get(idx);
            Integer next = null;
            if (ym != null) {
                next = cols.get((ym / 100) * 100 + (ym % 100) + 1);
            }
            m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(
                    m.group(1) + colLettersOf(next != null ? next : idx + 2) + m.group(3) + m.group(4)));
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /**
     * 修正「累计同比」分母。母版《城市客运》页把 2025 年 1..M 月各月列**和**它们的
     * 「2025年1-M月累计」辅助列一起写进分母（1..M-1 月重复计一次、当月漏计），
     * 另有在母版里删掉辅助列后残留 #REF! 的情形；两种都会让累计同比严重失真
     * （2026-09-20 用户上报：城市客运 8 月同比 −86.5%、累计同比 −43.5%）。
     * 统一按表头把分母重写为「去年 1..当月 各月值列之和」。
     *
     * 2026-09-20 已在《2026年8月道路运输量汇总表.xlsx》母版里删掉该辅助列（备份见
     * docs/生成汇总大表/_bak_202608母版删除AJ列前_20260920.xlsx），正常情况本方法命中 0 格；
     * 保留它是为了兜住「同期备份母版 / 历史部署包 / 之后又插了辅助列的母版」这类情况。
     */
    public int repairCumulativeYoyFormulas(XSSFWorkbook wb, int year, int month) {
        if (wb == null || month < 1) return 0;
        int fixed = 0;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
            int[] cum = cumulativeCols(sh, year);
            if (cum == null) continue;
            java.util.Set<Integer> helperCols = cumulativeHelperCols(sh);
            Map<Integer, Integer> all = yearMonthCols(sh, year);
            List<Integer> lastYearValueCols = new ArrayList<>();
            int prev = -1;
            for (int m = 1; m <= month; m++) {
                Integer c = all.get((year - 1) * 100 + m);
                if (c == null || c <= prev) {
                    lastYearValueCols.clear();
                    break;
                }
                prev = c;
                lastYearValueCols.add(c);
            }
                if (lastYearValueCols.isEmpty()) continue;
                for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                Cell cc = row.getCell(cum[1]);
                if (cc == null || cc.getCellType() != org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
                String f = cc.getCellFormula();
                if (f == null || !cumulativeYoyBroken(f, helperCols)) continue;
                // 注意：POI 的 setCellFormula 不接受以 "=" 开头的公式串（会抛 FormulaParseException）
                StringBuilder sb = new StringBuilder();
                sb.append(cellRef(cum[0], r + 1)).append("/(");
                for (int k = 0; k < lastYearValueCols.size(); k++) {
                    if (k > 0) sb.append('+');
                    sb.append(cellRef(lastYearValueCols.get(k), r + 1));
                }
                sb.append(")-1");
                try {
                    cc.setCellFormula(sb.toString());
                    fixed++;
                } catch (Exception ex) {
                    // 个别公式改写失败不阻塞导出（打开时仍按原公式重算）
                    if (log != null) log.warn("累计同比公式改写失败：sheet={} cell={} 原式={} 新式={}（{}）",
                            sh.getSheetName(), cellRef(cum[1], r + 1), f, sb, ex.toString());
                }
            }
        }
        if (fixed > 0 && log != null) {
            log.info("汇总工作簿：累计同比分母修正 {} 格（第 {} 月，分母取去年 1..{} 月各月值列）", fixed, month, month);
        }
        return fixed;
    }

    /** 累计同比公式是否“坏了”：含 #REF!，或分母引用了区间累计辅助列（与各月列重复计入） */
    private boolean cumulativeYoyBroken(String formula, java.util.Set<Integer> helperCols) {
        if (formula.contains("#REF!")) return true;
        java.util.regex.Matcher m = A1_REF.matcher(formula);
        while (m.find()) {
            if (helperCols.contains(colIndexOf(m.group(2)))) return true;
        }
        return false;
    }

    /** 该页「累计 / 累计同比」两列（0 基），按表头前 5 行找 “累计” 或 “{year}年累计”，且其右列含 “同比” */
    private int[] cumulativeCols(Sheet sh, int year) {
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String v = cell.getStringCellValue();
                if (v == null) continue;
                v = v.trim();
                if (!"累计".equals(v) && !(year + "年累计").equals(v)) continue;
                Cell nxt = row.getCell(c + 1);
                if (nxt == null || nxt.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String nt = nxt.getStringCellValue();
                if (nt != null && nt.contains("同比")) return new int[]{c, c + 1};
            }
        }
        return null;
    }

    /** 月份容量：优先取表头（前 5 行）“2026…累计”列反推；货运页无该文案，
     *  再用表头里最后一个“2026年M月”/日期格式的月列兜底（如货运 Q3=2026-08-01 -> 8） */
    private int monthColumnCapacity(Sheet sh) {