excel.py 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191
  1. """结果输出:写 xlsx(流式)、逐表 CSV 镜像,并把每张表物化进 ``stat.sqlite``。
  2. 为什么要物化 SQLite:
  3. ``data_read`` 工具需要对“统计结果”做过滤、排序、分页和聚合。把每张表落到
  4. SQLite 后,Agent 的查询是确定性的 SQL,而不是把整表读进上下文。
  5. 精度策略:
  6. * 每列都保存 ``TEXT`` 原文(Decimal 不做任何舍入),这是对外可核对的真值;
  7. * 同时为每个数值列建 ``_num_<列>`` 的 REAL 影子列,只用于排序/区间过滤,
  8. 返回值永远是 TEXT 原文。
  9. """
  10. from __future__ import annotations
  11. import csv
  12. import hashlib
  13. import json
  14. import re
  15. import sqlite3
  16. from decimal import Decimal
  17. from pathlib import Path
  18. from ...query_indexes import create_query_indexes
  19. from openpyxl import Workbook
  20. from openpyxl.cell import WriteOnlyCell
  21. from openpyxl.styles import Alignment, Font, PatternFill
  22. from openpyxl.utils import get_column_letter
  23. EXCEL_MAX_ROWS = 1_048_576
  24. #: 复用样式对象。openpyxl 每 new 一个 Font/Alignment 都要走一遍 hash/eq,
  25. #: 几十万单元格时开销远大于写值本身,因此这里全部缓存成模块级常量。
  26. _HEADER_FONT = Font(name="等线", bold=True, color="FFFFFF", size=11)
  27. _HEADER_FILL = PatternFill("solid", fgColor="203864")
  28. _HEADER_ALIGN = Alignment(wrap_text=True, vertical="center")
  29. _BODY_FONT = Font(name="等线", size=11)
  30. _BODY_ALIGN = Alignment(vertical="center", wrap_text=True)
  31. def table_name(sheet: str) -> str:
  32. """表名 → SQLite 合法标识符(保留可读性)。"""
  33. cleaned = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff]+", "_", sheet).strip("_")
  34. return f"s_{cleaned}" if not cleaned[:1].isdigit() else f"s_{cleaned}"
  35. def _unique_headers(headers: list[str]) -> list[str]:
  36. seen: dict[str, int] = {}
  37. result = []
  38. for header in headers:
  39. name = str(header or "").strip() or "列"
  40. if name in seen:
  41. seen[name] += 1
  42. name = f"{name}_{seen[name]}"
  43. else:
  44. seen[name] = 0
  45. result.append(name)
  46. return result
  47. class Output:
  48. """一次运行的结果写入器。"""
  49. def __init__(self, out: Path) -> None:
  50. self.out = Path(out)
  51. self.out.mkdir(parents=True, exist_ok=True)
  52. self.wb = Workbook(write_only=True)
  53. self.sheets: dict[str, dict] = {}
  54. (self.out / "精确结果CSV").mkdir(exist_ok=True)
  55. self.db = sqlite3.connect(str(self.out / "stat.sqlite"))
  56. self.db.execute("CREATE TABLE sheet_meta(sheet TEXT PRIMARY KEY, table_name TEXT, headers_json TEXT, row_count INTEGER)")
  57. # ------------------------------------------------------------------ schema
  58. def sheet(self, name: str, headers: list[str]) -> None:
  59. headers = _unique_headers(headers)
  60. entry: dict = {"name": name, "headers": headers, "count": 0, "part": 1}
  61. csv_path = self.out / "精确结果CSV" / f"{name.replace('/', '_')}.csv"
  62. handle = csv_path.open("w", encoding="utf-8-sig", newline="")
  63. entry["file"] = handle
  64. entry["csv"] = csv.writer(handle)
  65. entry["csv"].writerow(headers)
  66. self.sheets[name] = entry
  67. self._new_part(entry)
  68. table = table_name(name)
  69. entry["table"] = table
  70. numeric = [header for header in headers if self._looks_numeric(header)]
  71. entry["numeric"] = set(numeric)
  72. columns = ['"_row_index" INTEGER PRIMARY KEY', '"_row_hash" TEXT']
  73. for header in headers:
  74. columns.append(f'"{header}" TEXT')
  75. for header in numeric:
  76. columns.append(f'"_num_{header}" REAL')
  77. self.db.execute(f'CREATE TABLE "{table}" ({",".join(columns)})')
  78. entry["insert"] = (
  79. f'INSERT INTO "{table}" VALUES({",".join("?" for _ in range(len(headers) + 2 + len(numeric)))})'
  80. )
  81. self.db.execute(
  82. "INSERT INTO sheet_meta VALUES(?,?,?,?)",
  83. (name, table, json.dumps(headers, ensure_ascii=False), 0),
  84. )
  85. @staticmethod
  86. def _looks_numeric(header: str) -> bool:
  87. if header in {"年度", "月度", "ISO年", "ISO周", "归属年", "归属月"}:
  88. return True
  89. return any(
  90. key in header
  91. for key in ["金额", "数量", "指数", "单价", "价格", "占比", "环比", "权重", "记录数", "排名"]
  92. )
  93. def _new_part(self, entry: dict) -> None:
  94. suffix = "" if entry["part"] == 1 else f"_{entry['part']}"
  95. worksheet = self.wb.create_sheet(entry["name"][: 31 - len(suffix)] + suffix)
  96. worksheet.freeze_panes = "E2"
  97. worksheet.sheet_view.showGridLines = False
  98. worksheet.row_dimensions[1].height = 46
  99. headers = entry["headers"]
  100. for index, header in enumerate(headers, 1):
  101. worksheet.column_dimensions[get_column_letter(index)].width = (
  102. 26 if any(key in header for key in ["原因", "范围", "说明", "路径", "原文"]) else max(13, min(26, len(header) * 2 + 3))
  103. )
  104. cells = []
  105. for header in headers:
  106. cell = WriteOnlyCell(worksheet, value=header)
  107. cell.font = _HEADER_FONT
  108. cell.fill = _HEADER_FILL
  109. cell.alignment = _HEADER_ALIGN
  110. cells.append(cell)
  111. worksheet.append(cells)
  112. entry["ws"] = worksheet
  113. entry["part_rows"] = 1
  114. # ------------------------------------------------------------------ writing
  115. def row(self, name: str, values: list) -> None:
  116. entry = self.sheets[name]
  117. if len(values) != len(entry["headers"]):
  118. raise ValueError(f"{name} 列数错误 {len(values)} != {len(entry['headers'])}")
  119. if entry["part_rows"] >= EXCEL_MAX_ROWS:
  120. entry["ws"].auto_filter.ref = f"A1:{get_column_letter(len(values))}{entry['part_rows']}"
  121. entry["part"] += 1
  122. self._new_part(entry)
  123. entry["csv"].writerow(["" if value is None else str(value) for value in values])
  124. cells = []
  125. for header, value in zip(entry["headers"], values):
  126. cell = WriteOnlyCell(entry["ws"], value=float(value) if isinstance(value, Decimal) else value)
  127. if isinstance(value, str):
  128. cell.data_type = "s" # 原始字符串永远不变成公式
  129. elif isinstance(value, Decimal) and ("占比" in header or "环比" in header):
  130. cell.number_format = "0.00%"
  131. cells.append(cell)
  132. entry["ws"].append(cells)
  133. # SQLite:TEXT 原文 + REAL 影子列 + 行哈希(行级溯源)
  134. texts = ["" if value is None else (str(value) if not isinstance(value, Decimal) else format(value, "f")) for value in values]
  135. row_hash = "sha256:" + hashlib.sha256(json.dumps(texts, ensure_ascii=False).encode("utf-8")).hexdigest()
  136. numeric_values = []
  137. for header, value in zip(entry["headers"], values):
  138. if header in entry["numeric"]:
  139. try:
  140. numeric_values.append(float(value) if value is not None else None)
  141. except (TypeError, ValueError):
  142. numeric_values.append(None)
  143. self.db.execute(entry["insert"], [entry["count"], row_hash] + texts + numeric_values)
  144. entry["count"] += 1
  145. entry["part_rows"] += 1
  146. # ------------------------------------------------------------------ closing
  147. def save(self) -> Path:
  148. for entry in self.sheets.values():
  149. entry["ws"].auto_filter.ref = f"A1:{get_column_letter(len(entry['headers']))}{entry['part_rows']}"
  150. entry["file"].close()
  151. self.db.execute("UPDATE sheet_meta SET row_count=? WHERE sheet=?", (entry["count"], entry["name"]))
  152. for entry in self.sheets.values():
  153. create_query_indexes(self.db, entry["table"], entry["headers"] + ['_num_' + h for h in entry['numeric']])
  154. self.db.execute('ANALYZE')
  155. self.db.commit()
  156. path = self.out / "统计结果.xlsx"
  157. self.wb.save(path)
  158. return path
  159. def close(self) -> None:
  160. try:
  161. self.db.commit()
  162. finally:
  163. self.db.close()
  164. def table_of(self, name: str) -> str:
  165. return self.sheets[name]["table"]