| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191 |
- """结果输出:写 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"]
|