"""结果输出:写 xlsx(流式)、逐表 CSV 镜像,并把每张表物化进 ``stat.sqlite``。 为什么要物化 SQLite: ``data_read`` 工具需要对“统计结果”做过滤、排序、分页和聚合。把每张表落到 SQLite 后,Agent 的查询是确定性的 SQL,而不是把整表读进上下文。 精度策略: * 每列都保存 ``TEXT`` 原文(Decimal 不做任何舍入),这是对外可核对的真值; * 同时为每个数值列建 ``_num_<列>`` 的 REAL 影子列,只用于排序/区间过滤, 返回值永远是 TEXT 原文。 """ from __future__ import annotations import csv import hashlib import json import re import sqlite3 from decimal import Decimal from pathlib import Path from ...query_indexes import create_query_indexes from openpyxl import Workbook from openpyxl.cell import WriteOnlyCell from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter EXCEL_MAX_ROWS = 1_048_576 #: 复用样式对象。openpyxl 每 new 一个 Font/Alignment 都要走一遍 hash/eq, #: 几十万单元格时开销远大于写值本身,因此这里全部缓存成模块级常量。 _HEADER_FONT = Font(name="等线", bold=True, color="FFFFFF", size=11) _HEADER_FILL = PatternFill("solid", fgColor="203864") _HEADER_ALIGN = Alignment(wrap_text=True, vertical="center") _BODY_FONT = Font(name="等线", size=11) _BODY_ALIGN = Alignment(vertical="center", wrap_text=True) def table_name(sheet: str) -> str: """表名 → SQLite 合法标识符(保留可读性)。""" cleaned = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff]+", "_", sheet).strip("_") return f"s_{cleaned}" if not cleaned[:1].isdigit() else f"s_{cleaned}" def _unique_headers(headers: list[str]) -> list[str]: seen: dict[str, int] = {} result = [] for header in headers: name = str(header or "").strip() or "列" if name in seen: seen[name] += 1 name = f"{name}_{seen[name]}" else: seen[name] = 0 result.append(name) return result class Output: """一次运行的结果写入器。""" def __init__(self, out: Path) -> None: self.out = Path(out) self.out.mkdir(parents=True, exist_ok=True) self.wb = Workbook(write_only=True) self.sheets: dict[str, dict] = {} (self.out / "精确结果CSV").mkdir(exist_ok=True) self.db = sqlite3.connect(str(self.out / "stat.sqlite")) self.db.execute("CREATE TABLE sheet_meta(sheet TEXT PRIMARY KEY, table_name TEXT, headers_json TEXT, row_count INTEGER)") # ------------------------------------------------------------------ schema def sheet(self, name: str, headers: list[str]) -> None: headers = _unique_headers(headers) entry: dict = {"name": name, "headers": headers, "count": 0, "part": 1} csv_path = self.out / "精确结果CSV" / f"{name.replace('/', '_')}.csv" handle = csv_path.open("w", encoding="utf-8-sig", newline="") entry["file"] = handle entry["csv"] = csv.writer(handle) entry["csv"].writerow(headers) self.sheets[name] = entry self._new_part(entry) table = table_name(name) entry["table"] = table numeric = [header for header in headers if self._looks_numeric(header)] entry["numeric"] = set(numeric) columns = ['"_row_index" INTEGER PRIMARY KEY', '"_row_hash" TEXT'] for header in headers: columns.append(f'"{header}" TEXT') for header in numeric: columns.append(f'"_num_{header}" REAL') self.db.execute(f'CREATE TABLE "{table}" ({",".join(columns)})') entry["insert"] = ( f'INSERT INTO "{table}" VALUES({",".join("?" for _ in range(len(headers) + 2 + len(numeric)))})' ) self.db.execute( "INSERT INTO sheet_meta VALUES(?,?,?,?)", (name, table, json.dumps(headers, ensure_ascii=False), 0), ) @staticmethod def _looks_numeric(header: str) -> bool: if header in {"年度", "月度", "ISO年", "ISO周", "归属年", "归属月"}: return True return any( key in header for key in ["金额", "数量", "指数", "单价", "价格", "占比", "环比", "权重", "记录数", "排名"] ) def _new_part(self, entry: dict) -> None: suffix = "" if entry["part"] == 1 else f"_{entry['part']}" worksheet = self.wb.create_sheet(entry["name"][: 31 - len(suffix)] + suffix) worksheet.freeze_panes = "E2" worksheet.sheet_view.showGridLines = False worksheet.row_dimensions[1].height = 46 headers = entry["headers"] for index, header in enumerate(headers, 1): worksheet.column_dimensions[get_column_letter(index)].width = ( 26 if any(key in header for key in ["原因", "范围", "说明", "路径", "原文"]) else max(13, min(26, len(header) * 2 + 3)) ) cells = [] for header in headers: cell = WriteOnlyCell(worksheet, value=header) cell.font = _HEADER_FONT cell.fill = _HEADER_FILL cell.alignment = _HEADER_ALIGN cells.append(cell) worksheet.append(cells) entry["ws"] = worksheet entry["part_rows"] = 1 # ------------------------------------------------------------------ writing def row(self, name: str, values: list) -> None: entry = self.sheets[name] if len(values) != len(entry["headers"]): raise ValueError(f"{name} 列数错误 {len(values)} != {len(entry['headers'])}") if entry["part_rows"] >= EXCEL_MAX_ROWS: entry["ws"].auto_filter.ref = f"A1:{get_column_letter(len(values))}{entry['part_rows']}" entry["part"] += 1 self._new_part(entry) entry["csv"].writerow(["" if value is None else str(value) for value in values]) cells = [] for header, value in zip(entry["headers"], values): cell = WriteOnlyCell(entry["ws"], value=float(value) if isinstance(value, Decimal) else value) if isinstance(value, str): cell.data_type = "s" # 原始字符串永远不变成公式 elif isinstance(value, Decimal) and ("占比" in header or "环比" in header): cell.number_format = "0.00%" cells.append(cell) entry["ws"].append(cells) # SQLite:TEXT 原文 + REAL 影子列 + 行哈希(行级溯源) texts = ["" if value is None else (str(value) if not isinstance(value, Decimal) else format(value, "f")) for value in values] row_hash = "sha256:" + hashlib.sha256(json.dumps(texts, ensure_ascii=False).encode("utf-8")).hexdigest() numeric_values = [] for header, value in zip(entry["headers"], values): if header in entry["numeric"]: try: numeric_values.append(float(value) if value is not None else None) except (TypeError, ValueError): numeric_values.append(None) self.db.execute(entry["insert"], [entry["count"], row_hash] + texts + numeric_values) entry["count"] += 1 entry["part_rows"] += 1 # ------------------------------------------------------------------ closing def save(self) -> Path: for entry in self.sheets.values(): entry["ws"].auto_filter.ref = f"A1:{get_column_letter(len(entry['headers']))}{entry['part_rows']}" entry["file"].close() self.db.execute("UPDATE sheet_meta SET row_count=? WHERE sheet=?", (entry["count"], entry["name"])) for entry in self.sheets.values(): create_query_indexes(self.db, entry["table"], entry["headers"] + ['_num_' + h for h in entry['numeric']]) self.db.execute('ANALYZE') self.db.commit() path = self.out / "统计结果.xlsx" self.wb.save(path) return path def close(self) -> None: try: self.db.commit() finally: self.db.close() def table_of(self, name: str) -> str: return self.sheets[name]["table"]