Selenium网页自动化：如何遍历SpreadSheet中的多组数据

Hey there! I see you're stuck on iterating through multiple rows in your Excel sheet for your web automation test—let's get that sorted out. The key here is to loop through each row of your spreadsheet (skipping the header row) and run your existing automation flow for every record.

Step-by-Step Solution #

• Get total rows: Use the nrows property of your sheet object to know how many records you need to process.
• Iterate through data rows: Start looping from row 1 (since row 0 is your header) up to the total number of rows.
• Dynamic row references: Replace all fixed cell(1, column) calls with cell(row, column) so each iteration uses the current row's data.
• Add error handling (optional): Wrap the automation flow in a try-except block to prevent one failed record from breaking the entire script.

Modified Code #

Here's your updated script with looping logic integrated:

import time
from selenium import webdriver
import xlrd

driver = webdriver.Chrome('path')
driver.fullscreen_window()
driver.get('url')

# Load workbook and target sheet
Workbook = xlrd.open_workbook("excelpath")
Customerdetails = Workbook.sheet_by_index(0)

# Get total number of data rows (skip header row 0)
total_rows = Customerdetails.nrows

# Loop through each record starting from row 1
for row in range(1, total_rows):
    try:
        # Wait for initial page load (note: explicit waits are better than sleep!)
        time.sleep(5)
        
        # Fill zipcode from current row
        Storagezipcode = Customerdetails.cell(row, 0)
        text_area = driver.find_element_by_xpath('(//*[@id="QuoteDetails/PostalCode"])[2]')
        text_area.send_keys(Storagezipcode.value)
        
        # Click Next button
        Nextbutton = driver.find_element_by_xpath('//span[contains(text(),"Next")]')
        Nextbutton.click()
        time.sleep(10)
        
        # Navigate to car link page
        CarLink = driver.find_element_by_xpath('//span[@class="link"]')
        CarLink.click()
        time.sleep(30)
        
        # Select model year from current row
        yeardetail = Customerdetails.cell(row, 14)
        Yearlink = driver.find_element_by_xpath("//span[normalize-space(.)='{}']".format(yeardetail.value))
        Yearlink.click()
        time.sleep(10)
        
        # Select company name from current row
        SubModel = Customerdetails.cell(row, 15)
        SubModellink = driver.find_element_by_xpath("//span[normalize-space(.)='{}']".format(SubModel.value))
        SubModellink.click()
        time.sleep(10)
        
        # Select company model from current row
        Bodymodel = Customerdetails.cell(row, 16)
        Bodylink = driver.find_element_by_xpath("//span[normalize-space(.)='{}']".format(Bodymodel.value))
        Bodylink.click()
        time.sleep(10)
        
        # Reset to initial page for next record (adjust based on your workflow)
        driver.get('url')
        
    except Exception as e:
        print(f"Error processing row {row}: {str(e)}")
        # Optional: Save screenshot for debugging
        driver.save_screenshot(f"error_row_{row}.png")
        # Continue to next record instead of stopping
        continue

# Clean up after all records are processed
driver.quit()

Key Tips #

• Replace time.sleep() with explicit waits: Hardcoded sleeps are unreliable—use WebDriverWait with conditions like element_to_be_clickable to make your script more stable.
• Validate your data: Ensure every row in your Excel sheet has all required values (zipcode, year, submodel, bodymodel) to avoid missing element errors.
• Adjust post-record navigation: The example uses driver.get('url') to reset, but you might need to use driver.back() multiple times or navigate via page links depending on the site's structure.

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