prod_selec/src/excel_handler.py

import sys
import os
import time
from openpyxl import load_workbook

def get_resource_path(relative_path):
    """ 获取资源绝对路径，兼容开发环境和 PyInstaller 打包环境 """
    if hasattr(sys, '_MEIPASS'):
        # PyInstaller 打包后的临时解压路径
        return os.path.join(sys._MEIPASS, relative_path)
    # 开发环境下的路径
    base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    return os.path.join(base_dir, relative_path)

def get_existing_info(file_path):
    """
    读取已有文件中的链接和最后一行编码
    """
    links = set()
    last_code = 0
    if not os.path.exists(file_path):
        return links, last_code
    
    try:
        wb = load_workbook(file_path, data_only=True)
        ws = wb.active
        # 假设 A 列是编码，K 列是链接
        for r in range(3, ws.max_row + 1):
            code_val = ws.cell(row=r, column=1).value
            link_val = ws.cell(row=r, column=11).value
            
            if link_val:
                links.add(str(link_val).strip())
            
            if isinstance(code_val, (int, float)):
                last_code = max(last_code, int(code_val))
    except:
        pass
    return links, last_code

def append_to_template(products, output_path, status_callback=None):
    """
    将产品数据追加写入到指定的 Excel 文件中。
    增加文件占用检测：如果文件被打开，则暂停任务直到关闭。
    并在第二个 Sheet 中记录商品总数。
    """
    template_path = get_resource_path(os.path.join('templates', '【进价】产品信息空表.xlsx'))
    
    if not os.path.exists(template_path):
        template_path = os.path.join('templates', '【进价】产品信息空表.xlsx')

    if not os.path.exists(template_path):
        raise FileNotFoundError(f"未找到核心模板文件: {template_path}")

    if os.path.exists(output_path):
        wb = load_workbook(output_path)
    else:
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        wb = load_workbook(template_path)
    
    # 1. 写入主数据 Sheet
    ws = wb.active
    
    # 寻找起始行 (基于第 11 列“产品链接”进行判定，防止覆盖)
    start_row = 3
    for r in range(3, ws.max_row + 2):
        val_link = ws.cell(row=r, column=11).value
        if val_link is None or str(val_link).strip() == "":
            start_row = r
            break
    else:
        start_row = ws.max_row + 1
    
    # 获取当前已有的链接集合，用于后续统计唯一商品
    existing_links = set()
    for r in range(3, start_row):
        link = ws.cell(row=r, column=11).value
        if link: existing_links.add(str(link).strip())

    for i, product in enumerate(products):
        row = start_row + i
        ws.cell(row=row, column=1, value=row - 2) 
        ws.cell(row=row, column=2, value=product.get('category', ''))
        ws.cell(row=row, column=3, value=product.get('brand', ''))
        ws.cell(row=row, column=4, value=product.get('name', ''))
        ws.cell(row=row, column=5, value=product.get('color', ''))
        ws.cell(row=row, column=6, value=product.get('spec', ''))
        ws.cell(row=row, column=7, value=product.get('material', ''))
        ws.cell(row=row, column=8, value=product.get('price', ''))
        ws.cell(row=row, column=9, value=product.get('moq', ''))
        ws.cell(row=row, column=10, value=product.get('wholesale_price', ''))
        ws.cell(row=row, column=11, value=product.get('link', '')) 
        ws.cell(row=row, column=12, value=product.get('supplier', ''))
        
        link = product.get('link')
        if link: existing_links.add(str(link).strip())

    # 2. 写入/更新计数 Sheet (第二个 Sheet)
    sheet_names = wb.sheetnames
    if len(sheet_names) < 2:
        wb.create_sheet("统计状态")
    
    ws_stat = wb["统计状态"]
    ws_stat.cell(row=1, column=1, value="已解析商品总数")
    ws_stat.cell(row=1, column=2, value=len(existing_links))
    ws_stat.cell(row=2, column=1, value="最后更新时间")
    ws_stat.cell(row=2, column=2, value=time.strftime("%Y-%m-%d %H:%M:%S"))

    # 3. 占用检测保存循环
    while True:
        try:
            wb.save(output_path)
            if status_callback:
                status_callback(False, "写入成功")
            break
        except PermissionError:
            msg = "文件被占用，请关闭 Excel"
            print(f"[!] {msg}: {output_path}")
            if status_callback:
                status_callback(True, msg)
            time.sleep(3)