网页爬取表格存入Excel时重叠同工作表，如何修复？

我编写了一段网页爬取代码，用于获取以下两个链接中的表格：

• https://racing.hkjc.com/racing/information/English/Jockey/JockeyRanking.aspx
• https://racing.hkjc.com/racing/information/English/Trainers/TrainerRanking.aspx

代码运行无报错，但爬取的两张表格会重叠在Excel的同一张工作表中，而非分别存入不同工作表。请问该如何修复此问题？

代码如下：

import pandas as pd
from bs4 import BeautifulSoup
from playwright.sync_api import sync_playwright

def scrape_ranking(url, sheet_name):
    with sync_playwright() as pw:
        browser = pw.chromium.launch()
        page = browser.new_page()
        page.goto(url, wait_until="networkidle")
        soup = BeautifulSoup(page.content(), "html.parser")
        table = soup.select_one(".table_bd")

        if table is None:
            print("Table not found.")
        else:
            df = pd.read_html(str(table))[0]
            df.to_excel("hkjc.xlsx", sheet_name=sheet_name, index=True)

# Scrape TrainerRanking page
url_trainer = "https://racing.hkjc.com/racing/information/English/Trainers/TrainerRanking.aspx"
scrape_ranking(url_trainer, "TrainerRanking")

# Scrape JockeyRanking page
url_jockey = "https://racing.hkjc.com/racing/information/English/Jockey/JockeyRanking.aspx"
scrape_ranking(url_jockey, "JockeyRanking")

print("done")

问题核心是：每次调用df.to_excel()时，都会覆盖整个Excel文件，第二次写入会完全替换第一次的内容，最终只保留最后一次爬取的表格（你感知到的"重叠"实际是文件被覆盖导致的异常显示）。要实现多工作表写入，需要用pandas.ExcelWriter统一管理文件写入流程，修改后的代码如下：

import pandas as pd
from bs4 import BeautifulSoup
from playwright.sync_api import sync_playwright

def scrape_ranking(url):
    with sync_playwright() as pw:
        browser = pw.chromium.launch()
        page = browser.new_page()
        page.goto(url, wait_until="networkidle")
        soup = BeautifulSoup(page.content(), "html.parser")
        table = soup.select_one(".table_bd")

        if table is None:
            print("Table not found.")
            return None
        else:
            df = pd.read_html(str(table))[0]
            return df

# 用ExcelWriter统一写入多个工作表
with pd.ExcelWriter("hkjc.xlsx") as writer:
    # 爬取练马师排名并写入工作表
    trainer_df = scrape_ranking("https://racing.hkjc.com/racing/information/English/Trainers/TrainerRanking.aspx")
    if trainer_df is not None:
        trainer_df.to_excel(writer, sheet_name="TrainerRanking", index=True)
    
    # 爬取骑师排名并写入工作表
    jockey_df = scrape_ranking("https://racing.hkjc.com/racing/information/English/Jockey/JockeyRanking.aspx")
    if jockey_df is not None:
        jockey_df.to_excel(writer, sheet_name="JockeyRanking", index=True)

print("done")

关键修改说明 #

1. 重构爬取函数：让scrape_ranking只负责爬取并返回DataFrame（或空值），把文件写入逻辑抽离出来，避免重复打开/覆盖文件。
2. 使用ExcelWriter：通过with语句创建写入器对象，所有工作表都通过这个对象写入，确保文件仅被打开一次，多个工作表被正确添加到同一个Excel文件中。
3. 增加有效性判断：写入前检查爬取结果是否为空，避免无效写入操作。

修改后，两张表格会分别存入Excel文件的独立工作表，不会再出现覆盖或重叠问题。

内容的提问来源于stack exchange，提问作者NNBananas