Crawl/json_to_excel.py

#!/usr/bin/env python3
"""将 data.json 中的 models 模块导出为 Excel 文件。"""

import json
import sys
from pathlib import Path

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill

DATA_FILE = Path(__file__).parent / "data.json"
OUTPUT_FILE = Path(__file__).parent / "models_output.xlsx"


def parse_prices(prices: dict) -> list[str]:
    """解析价格字典，返回可读的价格行列表。"""
    rows = []
    for tier_label, tier_data in prices.items():
        if not isinstance(tier_data, dict):
            continue
        # 判断是否本身就有 price（简单计价）
        if "price" in tier_data:
            # 简单计价：tier_data = {"raw": "...", "price": 2.0, ...}
            price = tier_data.get("price", "")
            unit = tier_data.get("unit", "")
            price_original = tier_data.get("price_original")
            note = tier_data.get("note", "")
            row = f"{tier_label}: {price} {unit}"
            if price_original:
                row += f" (原价{price_original})"
            if note:
                row += f" [{note}]"
            rows.append(row)
        else:
            # 阶梯计价：tier_data = {"输入": {"price": ...}, "输出": {"price": ...}}
            for label, info in tier_data.items():
                if isinstance(info, dict) and "price" in info:
                    price = info.get("price", "")
                    unit = info.get("unit", "")
                    price_original = info.get("price_original")
                    note = info.get("note", "")
                    row = f"{tier_label} | {label}: {price} {unit}"
                    if price_original:
                        row += f" (原价{price_original})"
                    if note:
                        row += f" [{note}]"
                    rows.append(row)
    return rows


def main():
    with open(DATA_FILE, "r", encoding="utf-8") as f:
        data = json.load(f)

    models = data.get("models", [])
    if not models:
        print("没有找到 models 数据")
        sys.exit(1)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Models"

    # 表头
    headers = ["模型名称", "价格", "价格单位", "介绍", "分组", "标签"]
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF")

    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")

    # 设置列宽
    ws.column_dimensions["A"].width = 35
    ws.column_dimensions["B"].width = 80
    ws.column_dimensions["C"].width = 15
    ws.column_dimensions["D"].width = 80
    ws.column_dimensions["E"].width = 20
    ws.column_dimensions["F"].width = 25

    for row_idx, model in enumerate(models, 2):
        model_name = model.get("model_name", "")
        group_name = model.get("group_name", "")

        # 价格信息
        prices = model.get("prices", {})
        price_lines = parse_prices(prices)
        price_str = "\n".join(price_lines)

        # 介绍
        model_info = model.get("model_info", {})
        description = model_info.get("description", "")

        # 标签
        display_tags = ", ".join(model_info.get("display_tags", []))

        ws.cell(row=row_idx, column=1, value=model_name)
        ws.cell(row=row_idx, column=2, value=price_str)
        ws.cell(row=row_idx, column=3, value="")
        ws.cell(row=row_idx, column=4, value=description)
        ws.cell(row=row_idx, column=5, value=group_name)
        ws.cell(row=row_idx, column=6, value=display_tags)

        # 自动换行
        for col in (2, 4):
            ws.cell(row=row_idx, column=col).alignment = Alignment(wrap_text=True, vertical="top")

    wb.save(OUTPUT_FILE)
    print(f"导出完成，共 {len(models)} 条模型数据 -> {OUTPUT_FILE}")


if __name__ == "__main__":
    main()