#!/usr/bin/env python3 """Build a full HTML data browser from every sheet in the tender entity workbook.""" from __future__ import annotations import json from collections import Counter, defaultdict from datetime import datetime from pathlib import Path from typing import Any from openpyxl import load_workbook ROOT = Path(__file__).resolve().parents[1] SOURCE_XLSX = ROOT / "docs" / "deliverables" / "20260617-raw-excel-entity-tables" / "原始Excel招投标实体抽取表.xlsx" OUT_DIR = ROOT / "docs" / "deliverables" / "20260617-full-excel-data-browser" OUT_HTML = OUT_DIR / "招投标实体全量数据浏览.html" def clean(value: Any) -> str | int | float: if value is None: return "" if isinstance(value, datetime): return value.strftime("%Y-%m-%d") if isinstance(value, (int, float)): return value text = str(value).strip() return "" if text in {"--", "None", "nan"} else text def read_workbook() -> dict[str, Any]: wb = load_workbook(SOURCE_XLSX, read_only=False, data_only=True) sheets: dict[str, Any] = {} for ws in wb.worksheets: headers = [str(clean(cell.value)) for cell in ws[2]] headers = [header for header in headers if header] rows: list[dict[str, Any]] = [] hyperlinks: dict[str, str] = {} for excel_row in range(3, ws.max_row + 1): item: dict[str, Any] = {} for col_index, header in enumerate(headers, 1): cell = ws.cell(excel_row, col_index) value = clean(cell.value) item[header] = value if cell.hyperlink and cell.hyperlink.target: hyperlinks[f"{len(rows)}::{header}"] = cell.hyperlink.target if any(str(value).strip() for value in item.values()): rows.append(item) sheets[ws.title] = { "headers": headers, "rows": rows, "rowCount": len(rows), "hyperlinks": hyperlinks, } wb.close() return sheets def as_float(value: Any) -> float: if isinstance(value, (int, float)): return float(value) try: return float(str(value).replace(",", "")) except ValueError: return 0.0 def first(row: dict[str, Any], *keys: str) -> str: for key in keys: value = row.get(key, "") if value != "": return str(value) return "" def build_indexes(sheets: dict[str, Any]) -> dict[str, Any]: projects = sheets.get("招投标项目表", {}).get("rows", []) buyers = sheets.get("招标公司表", {}).get("rows", []) bidders = sheets.get("投标公司表", {}).get("rows", []) products = sheets.get("投标产品表", {}).get("rows", []) qualifications = sheets.get("投标公司资质表", {}).get("rows", []) relations = sheets.get("招标-投标关系表", {}).get("rows", []) contacts = sheets.get("联系人表", {}).get("rows", []) sources = sheets.get("来源链接表", {}).get("rows", []) regions = sheets.get("地区统计表", {}).get("rows", []) projects_by_id = {first(row, "项目ID"): row for row in projects if first(row, "项目ID")} buyers_by_id = {first(row, "招标公司ID"): row for row in buyers if first(row, "招标公司ID")} bidders_by_id = {first(row, "投标公司ID"): row for row in bidders if first(row, "投标公司ID")} def group_by(rows: list[dict[str, Any]], key: str) -> dict[str, list[dict[str, Any]]]: groups: dict[str, list[dict[str, Any]]] = defaultdict(list) for row in rows: value = first(row, key) if value: groups[value].append(row) return groups province_counter = Counter(first(row, "发布省份") or "未识别" for row in projects) demand_counter = Counter(first(row, "需求类型") or "未识别" for row in projects) client_counter = Counter(first(row, "客户类型") or "未识别" for row in projects) amount_total = sum(as_float(row.get("中标金额_万元")) for row in projects) source_url_count = sum(1 for row in sources if first(row, "来源链接")) todo_qualification_count = sum(1 for row in qualifications if first(row, "状态") == "待补抓") top_projects = sorted( [ { "projectId": first(row, "项目ID"), "name": first(row, "项目名称"), "amountWan": as_float(row.get("中标金额_万元")), "buyer": first(row, "招标公司"), "bidder": first(row, "投标公司/中标单位"), } for row in projects ], key=lambda row: row["amountWan"], reverse=True, )[:30] return { "projectsById": projects_by_id, "buyersById": buyers_by_id, "biddersById": bidders_by_id, "productsByProject": group_by(products, "项目ID"), "relationsByProject": group_by(relations, "项目ID"), "sourcesByProject": group_by(sources, "项目ID"), "contactsByProject": group_by(contacts, "来源项目ID"), "qualificationsByBidder": group_by(qualifications, "投标公司ID"), "relationsByBuyer": group_by(relations, "招标公司ID"), "relationsByBidder": group_by(relations, "投标公司ID"), "projectsByBuyer": group_by(projects, "招标公司ID"), "projectsByBidder": group_by(projects, "投标公司ID"), "regions": regions, "summary": { "sheetCount": len(sheets), "rowCount": sum(sheet["rowCount"] for sheet in sheets.values()), "projectCount": len(projects), "buyerCount": len(buyers), "bidderCount": len(bidders), "productCount": len(products), "relationCount": len(relations), "contactCount": len(contacts), "sourceCount": len(sources), "sourceUrlCount": source_url_count, "todoQualificationCount": todo_qualification_count, "totalAmountWan": round(amount_total, 4), }, "charts": { "province": province_counter.most_common(), "demand": demand_counter.most_common(), "client": client_counter.most_common(), "topProjects": top_projects, }, } def build_payload() -> dict[str, Any]: sheets = read_workbook() return { "meta": { "source": str(SOURCE_XLSX.relative_to(ROOT)), "generatedAt": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), }, "sheets": sheets, "indexes": build_indexes(sheets), } def html_escape_json(payload: dict[str, Any]) -> str: return json.dumps(payload, ensure_ascii=False).replace(" 招投标实体全量数据浏览

招投标实体全量数据浏览

这个页面把实体 Excel 的所有 sheet 全部转成可搜索、可点击、可互跳的 HTML。项目、招标公司、投标公司、产品、资质、关系、联系人、来源链接和地区统计都在同一个页面里。

关键分布

高金额项目索引

点击项目可查看完整关联

Excel 全量 Sheet

""" def build_html(payload: dict[str, Any]) -> str: return HTML_TEMPLATE.replace("__DATA__", html_escape_json(payload)) def main() -> None: if not SOURCE_XLSX.exists(): raise FileNotFoundError(SOURCE_XLSX) payload = build_payload() OUT_DIR.mkdir(parents=True, exist_ok=True) OUT_HTML.write_text(build_html(payload), encoding="utf-8") print(OUT_HTML) print(json.dumps(payload["indexes"]["summary"], ensure_ascii=False, indent=2)) if __name__ == "__main__": main()