project/tbsg/excel_log.py

"""
采集数据 Excel 备份 — 按天一个文件，一个药品（搜索词）一个工作表
列: 标题 | 价格 | 店铺名(列表页OCR) | 店铺名(AI详情页) | 快照URL | 商品链接 | 来源截图
用途: 对比列表页OCR店铺名和详情页AI提取店铺名的差异；来源截图供 audit_ocr.py 事后核对OCR准确率
文件: 采集数据/YYYYMMDD.xlsx，sheet = 药品名
"""
import re
import time
from pathlib import Path

EXCEL_DIR = Path(__file__).parent / "采集数据"

HEADERS = ["标题", "价格", "店铺名(列表页OCR)", "店铺名(AI详情页)", "快照URL", "商品链接", "来源截图"]


def _safe_sheet_name(name: str) -> str:
    """Excel sheet名限制：≤31字符，不允许 []:*?/\\ """
    name = re.sub(r'[\[\]:*?/\\]', '', str(name or "")).strip()
    return (name or "采集")[:31]


def append_row(title: str, price: str, shop_list: str, shop_ai: str,
               snapshot_url: str, link: str, sheet_name: str = "",
               source: str = "") -> None:
    """追加一行到当天 Excel 的对应药品 sheet，失败只打印不抛异常（不影响采集主流程）
    source: 来源截图标识（如 OV7T8PV8GEHYSCNB/step3_b5），供事后OCR准确率核对追溯"""
    try:
        from openpyxl import Workbook, load_workbook
    except ImportError:
        print("[excel] 未安装 openpyxl，跳过Excel备份（pip install openpyxl）")
        return
    try:
        EXCEL_DIR.mkdir(exist_ok=True)
        path = EXCEL_DIR / f"{time.strftime('%Y%m%d')}.xlsx"
        sheet = _safe_sheet_name(sheet_name)
        if path.exists():
            wb = load_workbook(path)
            if sheet in wb.sheetnames:
                ws = wb[sheet]
            else:
                ws = wb.create_sheet(sheet)
                ws.append(HEADERS)
        else:
            wb = Workbook()
            ws = wb.active
            ws.title = sheet
            ws.append(HEADERS)
        ws.append([
            str(title or ""), str(price or ""),
            str(shop_list or ""), str(shop_ai or ""),
            str(snapshot_url or ""), str(link or ""),
            str(source or ""),
        ])
        wb.save(path)
        print(f"[excel] 已保存: {path.name} [{sheet}]")
    except Exception as e:
        print(f"[excel] 保存失败: {e}")