Python中如何用Openpyxl将Selenium抓取数据逐行写入Excel？

Got it, let's fix this issue for you. The core problem here is that you're not dynamically incrementing the row number when writing to the Excel file, plus there are a couple of small typos and redundant steps in your code causing unexpected behavior. Let's break this down and fix it step by step.

First, let's spot the key issues in your code: #

• Fixed row number: You’re hardcoding row=1 in ws.cell, so every td value overwrites the same cell. You need a variable that increments each time you write a new entry.
• Typo: value=ta should be value=td (you’re referencing a variable that doesn’t exist).
• Redundant workbook reload: You load formulaFile.xlsx twice—this is unnecessary and can erase unsaved changes if you reload before saving.
• ws.append not working: When you tried ws.append([td]), it likely failed because you reloaded the workbook after scraping, resetting any unsaved changes.

Solution 1: Use ws.append (simplest way to add rows) #

append automatically adds each entry to the next empty row in your sheet. This is the most straightforward approach for your use case:

import openpyxl
from selenium import webdriver  # Don't forget to import your driver

# Initialize Selenium driver (adjust to your browser)
driver = webdriver.Chrome()
driver.get("your_target_webpage_url")

# Load the workbook ONCE at the start
wb = openpyxl.load_workbook('formulaFile.xlsx')
ws = wb.active

# Scrape the table data
datapoint1 = driver.find_elements_by_tag_name("td")
allrows1 = datapoint1  # Fixing the variable mismatch from your original code

# Loop through each scraped td element and append to Excel
for td in allrows1:
    td_text = td.text
    print(td_text)
    ws.append([td_text])  # Append as a list to add a new row with this value

# Save changes once after all writes
wb.save('formulaTest.xlsx')

# Clean up Selenium resources
driver.quit()

Solution 2: Use dynamic row numbers (for precise control) #

If you need to start writing from a specific row (e.g., after headers), use a counter variable to increment the row number each iteration:

import openpyxl
from selenium import webdriver

driver = webdriver.Chrome()
driver.get("your_target_webpage_url")

wb = openpyxl.load_workbook('formulaFile.xlsx')
ws = wb.active

datapoint1 = driver.find_elements_by_tag_name("td")
allrows1 = datapoint1

# Set your starting row (adjust based on your Excel structure)
current_row = 1  # Start at row 1, or 2 if you have headers

for td in allrows1:
    td_text = td.text
    print(td_text)
    # Write to the current row, column 1 (adjust column number if needed)
    ws.cell(row=current_row, column=1, value=td_text)
    # Increment row for the next entry
    current_row += 1

wb.save('formulaTest.xlsx')
driver.quit()

Quick additional tips: #

• Always load your workbook once at the start—reloading it mid-process will discard any unsaved changes.
• If you use append, it will add rows to the first empty row in the sheet. If you have blank rows in between, the counter method gives you more control over where data goes.
• Don’t forget to call driver.quit() to close the browser and free up resources after scraping.

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