import json import time from copy import copy from pathlib import Path from openpyxl import Workbook, load_workbook from selenium import webdriver from selenium.webdriver.chrome.options import Options from selenium.webdriver.common.by import By from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC DEBUG_ADDR = "127.0.0.1:9222" # 与启动 Chrome 时的端口一致 WAIT = 15 # 填入excel表格的路径 EXCEL_PATH = Path( r"C:\Users\Meng\PycharmProjects\PythonProject\【进价】产品信息空表.xlsx" ) COLUMNS = [ "编码", "品类", "品牌", "商品名称", "颜色", "规格尺码", "材质", "单品进价（元）", "moq（起订量）", "批发进价（元）", "产品链接", "供应商信息", ] def build_driver(): opts = Options() opts.add_experimental_option("debuggerAddress", DEBUG_ADDR) opts.add_argument("--start-maximized") # 若需要规避自动化特征可按需添加： opts.add_argument("--disable-blink-features=AutomationControlled") return webdriver.Chrome(options=opts) def human_wait(sec=1.2): time.sleep(sec) def scrape_item(driver, url): driver.get(url) WebDriverWait(driver, WAIT).until( EC.presence_of_element_located((By.TAG_NAME, "body")) ) human_wait() def safe_text(by, sel): try: return driver.find_element(by, sel).text.strip() except Exception: return "" # 1688 页面里 window.context.result.global.globalData.model 含完整商品数据 model = driver.execute_script( "return (window.context && window.context.result && " "window.context.result.global && window.context.result.global.globalData " "&& window.context.result.global.globalData.model) || null;" ) def get_attr(name): """从 featureAttributes 里取指定属性值""" try: attrs = model["offerDetail"]["featureAttributes"] for item in attrs: if item.get("name") == name: return item.get("value", "") except Exception: return "" return "" # 价格与 MOQ trade = model.get("tradeModel", {}) if model else {} price_min = trade.get("minPrice", "") or "" price_max = trade.get("maxPrice", "") or "" begin_amount = trade.get("beginAmount", "") # 批发价区间拼接 ranges = trade.get("disPriceRanges") or trade.get("currentPrices") or trade.get("offerPriceModel", {}).get("currentPrices", []) range_text = " / ".join( [f"{r.get('beginAmount')}起 ¥{r.get('price') or r.get('discountPrice')}" for r in ranges] ) if ranges else "" data = { "品类": (model.get("offerDetail", {}).get("leafCategoryName", "") if model else "") or safe_text(By.CSS_SELECTOR, "div[class*=breadcrumb] a:last-child"), "品牌": get_attr("品牌"), "商品名称": (model.get("offerDetail", {}).get("subject", "") if model else "") or safe_text(By.CSS_SELECTOR, "h1.d-title") or safe_text(By.CSS_SELECTOR, "h1[class*=title]"), "颜色": get_attr("颜色") or safe_text(By.XPATH, "//div[@id='productAttributes']//th[span='颜色']/following-sibling::td[1]//span[@class='field-value']"), "规格尺码": get_attr("尺码") or safe_text(By.XPATH, "//div[@id='productAttributes']//th[span='尺码']/following-sibling::td[1]//span[@class='field-value']"), "材质": get_attr("材质") or safe_text(By.XPATH, "//div[@id='productAttributes']//th[span='材质']/following-sibling::td[1]//span[@class='field-value']"), "单品进价（元）": f"{price_min}-{price_max}" if price_min and price_max and price_min != price_max else f"{price_min}" if price_min else "", "moq（起订量）": begin_amount or safe_text(By.XPATH, "//div[@id='productAttributes']//th[span='起订量']/following-sibling::td[1]//span[@class='field-value']"), "批发进价（元）": range_text, "产品链接": url, "供应商信息": (model.get("sellerModel", {}).get("companyName", "") if model else "") or safe_text(By.CSS_SELECTOR, "a.company-name") or safe_text(By.CSS_SELECTOR, "div.company-name"), } return data def save_to_excel(path: Path, rows: list[dict]): """ 将抓取结果追加写入已有格式的表格。 - 默认使用首个工作表，并基于模板行复制样式。 - 模板行：若存在第3行则用第3行样式，否则用第2行。 """ if path.exists(): wb = load_workbook(path) ws = wb.active else: wb = Workbook() ws = wb.active ws.append(COLUMNS) # 选择模板行（通常是设计好的第一行数据样式） template_row_idx = 3 if ws.max_row >= 3 else 2 if ws.max_row >= 2 else 1 template_row = ws[template_row_idx] # 找到首个“数据区”空行（除编码列外其余列为空），否则追加到末尾 data_cols = range(2, len(COLUMNS) + 1) # 跳过编码列 first_empty_row = None for r in range(template_row_idx + 1, ws.max_row + 1): if all((ws.cell(row=r, column=c).value in (None, "")) for c in data_cols): first_empty_row = r break insert_row = first_empty_row or (ws.max_row + 1) # 计算编码起始值（取首列已有最大数字） last_code = 0 for r in range(1, ws.max_row + 1): try: val = ws.cell(row=r, column=1).value if isinstance(val, (int, float)) and val > last_code: last_code = int(val) except Exception: continue next_code = last_code + 1 for row_data in rows: # 若目标行不存在，扩展表行数；若已存在空行，直接写入避免上移模板序号 if insert_row > ws.max_row: ws.append([None] * len(COLUMNS)) for col_idx, col_name in enumerate(COLUMNS, start=1): if col_name == "编码": value = row_data.get("编码", next_code) else: value = row_data.get(col_name, "") cell = ws.cell(row=insert_row, column=col_idx, value=value) # 复制模板样式 if col_idx <= len(template_row): tmpl = template_row[col_idx - 1] cell._style = copy(tmpl._style) next_code += 1 insert_row += 1 try: wb.save(path) except PermissionError: alt = path.with_name(f"{path.stem}_out_{int(time.time())}{path.suffix}") wb.save(alt) print(f"原文件被占用，已写入副本：{alt}") if __name__ == "__main__": ITEM_URLS = [ "https://detail.1688.com/offer/860913286492.html?src=zhanwai&pid=301011_0000&ptid=017700000007986632e2076d03b97563&exp=enquiry%3AB%3BqueryMobilePhone%3AC%3Bxlyx%3AB&_force_exp_buckets_=11803%2C2024061703%2C2024011602&spm=a312h.2018_new_sem.dh_002.1.f2803d1evDvWwN&cosite=baidujj_pz&tracelog=p4p&_p_isad=1&clickid=73a657ca198445129a0f9657e5b848a5&sessionid=a22bd65f97342308cefe01ce93b2fb30&a=1128&e=Do0-iFADT1PETFOTY8XkCkiE39RAE-Osyk6HP6xWf0u9yqFIKWxE-tv1Y3207LPZ5B-yn5Il1MSwxqPfQ8JvdyS98dA7jdxprtnbuiV4OUqnLO30gnz1.Xd-cgEt.XmRfbc0snn-075TsNtnLsryNlOtFDjT98D.4kjEePgSJhSsTCWyLsXVy0.5ucS4u.tGix-9aJf-M.TJFqBjAJ84c-ZtUAhSFMPxrFV7CUJXRsAfWkEYWS5X9RD2lAIIXvm1vGzlY7ihi3tvyEKFOnqcIOZfewv0q.6g&sk=sem&style=1", 'https://detail.1688.com/offer/711070382704.html?src=zhanwai&pid=301011_0000&ptid=017700000007986632e2076d03b97563&exp=enquiry%3AB%3BqueryMobilePhone%3AA%3Bxlyx%3AB&_force_exp_buckets_=11803%2C2024061701%2C2024011602&spm=a312h.2018_new_sem.dh_002.3.f2803d1eBPrwvm&cosite=baidujj_pz&tracelog=p4p&_p_isad=1&clickid=8f28c35e2dab4a02ac6ab7bde4d85d7e&sessionid=a22bd65f97342308cefe01ce93b2fb30&a=1143&e=zES-dp9sJJ3zrrk0AjWQ.80TNDrQDIxAfEp4OeWz-t73PxVXx2khSRkm4WW-Gc2mWvz9cqAP-EtjxMGkm81MROlxv-X4ECd0aflzSA.u7Sw3XqQVUjl1FuCMFIzXZNqVuhIhieuUBNzHeKI5kOSG2IZTlYUgrDcjviyzqR9QbTS1DK6Qg8fQSsEm2SWtGdfb.otTyBxd30qq6gPLLvqhYiW2FW.t4rGnK1eNs8ThmT6RNZF-ol9mriMaxAlPq2t2MvQtMFuvgdZWZh-v9vZHQsr2UDsUSUJ44V7XlLMFYIk_&sk=sem&style=1', # 替换为你的商品链接 ] driver = build_driver() try: results = [] for url in ITEM_URLS: info = scrape_item(driver, url) results.append(info) print(json.dumps(info, ensure_ascii=False, indent=2)) human_wait(1.5) # 控制节奏，避免频繁触发风控 save_to_excel(EXCEL_PATH, results) finally: driver.quit()