Python爬虫数据导入现有Excel指定列报错，求解决方案

我正在做Python网页爬取并导出到Excel的项目，已经成功提取信息并写入新Excel文件。现在需要把爬取到的Service Tag和Expiry Date数据分别导入现有数据集的指定列，而不是生成新工作表。

爬虫代码 #

import selenium
from selenium.webdriver.common.by import By
from selenium import webdriver
import pandas as pd


path = "C:\\Users\\ChloeChew\\Downloads\\chromedriver_win32\\chromedriver.exe"

options = webdriver.ChromeOptions()
options.headless = True
options.add_argument('--headless')
options.add_argument('--start-maximized')
options.add_argument('--window-size=1920,1080')

driver = webdriver.Chrome(options=options, executable_path=path)

website_list = ["https://www.dell.com/support/home/en-sg/product-support/servicetag/0-eDBNTlVQeS9UV2FyMmpDK3ZqUWdOdz090/overview",
                "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-WldhUmxVOU9UZGxhSkVQQWZMMEo1UT090/overview",
                "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-ZUQwbW14d28xZHZBYTZWNDdHVy80Zz090/overview",
                "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-NGUxWVVCWkxFZGFxUTdYTTlZM3dsdz090/overview"]

#data = []
servicetag_list = []
expiry_list = []

for website in website_list:
    driver.get(website)
    # PARSE THE WEBSITE
    driver.implicitly_wait(10)
    #driver.save_screenshot('./save_screenshot_method.png')  # Capture the screen
    frame = driver.find_elements(By.XPATH, '//*[@id="site-wrapper"]/div/div[4]/div[1]/div[2]/div[1]/div[2]/div/div/div')

    for datas in frame:
        serial_number=(datas.find_element(By.XPATH, "//p[@class='service-tag mb-0 d-none d-lg-block']").text[13:])
        expiry_date=(datas.find_element(By.XPATH, "//p[@class='warrantyExpiringLabel mb-0 ml-1 mr-1']").text)
        #data.append({'Service Tag': serial_number,'Expiry Date': expiry_date})
        servicetag_list.append({'Service Tag': serial_number})
        expiry_list.append({'Expiry Date': expiry_date})


#df = pd.DataFrame(data)
#print(df)

现有Excel处理代码 #

oldfile = "C:\\Users\\ChloeChew\\PycharmProjects\\scrapping\\automation.xlsx"
df = pd.read_excel(oldfile)
df.insert(8, 'servicetag',servicetag_list)
df.insert(11, 'date',expiry_list)

df.to_csv("C:\\Users\\ChloeChew\\PycharmProjects\\scrapping\\newfile.xlsx")

遇到的报错 #

• ValueError: Length of values (4) does not match length of index (5)
• 类似raise ValueError("cannot convert {} to excel".format(value))的错误

问题分析与修复方案 #

核心问题 #

1. 数据结构错误：将爬取的单个值包装成字典存入列表，Pandas无法直接将字典列表作为列值导入
2. 长度不匹配：现有Excel有5行数据，但仅爬取了4条记录，导致行列长度不一致
3. 保存格式错误：使用to_csv方法却保存为.xlsx后缀，格式不兼容

修改后的完整代码 #

爬虫部分（调整数据存储结构）

import selenium
from selenium.webdriver.common.by import By
from selenium import webdriver
import pandas as pd

path = "C:\\Users\\ChloeChew\\Downloads\\chromedriver_win32\\chromedriver.exe"

options = webdriver.ChromeOptions()
options.headless = True
options.add_argument('--headless')
options.add_argument('--start-maximized')
options.add_argument('--window-size=1920,1080')

driver = webdriver.Chrome(options=options, executable_path=path)

website_list = [
    "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-eDBNTlVQeS9UV2FyMmpDK3ZqUWdOdz090/overview",
    "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-WldhUmxVOU9UZGxhSkVQQWZMMEo1UT090/overview",
    "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-ZUQwbW14d28xZHZBYTZWNDdHVy80Zz090/overview",
    "https://www.dell.com/support/home/en-sg/product-support/servicetag/0-NGUxWVVCWkxFZGFxUTdYTTlZM3dsdz090/overview"
]

# 改为纯字符串列表，取消字典包装
servicetag_list = []
expiry_list = []

for website in website_list:
    driver.get(website)
    driver.implicitly_wait(10)
    frame = driver.find_elements(By.XPATH, '//*[@id="site-wrapper"]/div/div[4]/div[1]/div[2]/div[1]/div[2]/div/div/div')

    for datas in frame:
        serial_number = datas.find_element(By.XPATH, "//p[@class='service-tag mb-0 d-none d-lg-block']").text[13:]
        expiry_date = datas.find_element(By.XPATH, "//p[@class='warrantyExpiringLabel mb-0 ml-1 mr-1']").text
        servicetag_list.append(serial_number)
        expiry_list.append(expiry_date)

driver.quit()  # 关闭浏览器进程，避免残留

现有Excel处理部分（修复长度匹配与保存格式）

oldfile = "C:\\Users\\ChloeChew\\PycharmProjects\\scrapping\\automation.xlsx"
df = pd.read_excel(oldfile)

# 处理长度不匹配：如果现有行数多于爬取数据，补空值填充
while len(servicetag_list) < len(df):
    servicetag_list.append("")
    expiry_list.append("")

# 插入指定位置的列
df.insert(8, 'Service Tag', servicetag_list)
df.insert(11, 'Expiry Date', expiry_list)

# 使用to_excel保存为Excel格式，index=False避免生成额外索引列
df.to_excel("C:\\Users\\ChloeChew\\PycharmProjects\\scrapping\\newfile.xlsx", index=False)

额外说明 #

• 如果现有Excel行数少于爬取数据量，可根据需求截取前N行（N为现有行数）或扩展空行
• driver.quit()必须添加，防止Chrome进程后台残留占用资源

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