如何用Python（Selenium/xlrd/Pandas）逐行提取Excel列数据并解决重复输入问题

Hey there, let's break down why your code is causing cumulative input and how to fix it.

The Root Cause #

Right now, you're collecting all rows of data into names and codes lists first, then trying to send the entire lists to the input fields at once. Selenium's send_keys() treats lists as a sequence of strings to append, which is why you're seeing ZachChris and 11112222—it's concatenating all items in the list instead of sending just one row at a time. Plus, your input/submit logic is outside the loop, so it only runs once (but with the full list) instead of per row.

The Fixed Code #

Here's the adjusted version of your function, with key changes highlighted:

from selenium import webdriver
from selenium.webdriver.common.keys import Keys
import xlrd
import time

driver = webdriver.Firefox()

def addCodes():
    global driver
    path = 'test.xlsx'
    workbook = xlrd.open_workbook(path)
    sheet = workbook.sheet_by_index(0)
    
    # Iterate over each row directly (no need to store all data first unless you need it later)
    for row_idx in range(sheet.nrows):
        # Grab ONLY the current row's data
        current_name = str(sheet.cell_value(row_idx, 0))
        current_code = str(sheet.cell_value(row_idx, 1))
        
        # Re-locate input fields each loop (critical if the page refreshes after submit)
        code_input = driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/div[5]/p/input')
        name_input = driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/div[6]/p/input')
        
        # Clear fields to eliminate any leftover text
        code_input.clear()
        name_input.clear()
        
        # Send the single row's data (not the entire list!)
        code_input.send_keys(current_code)
        name_input.send_keys(current_name)
        
        # Submit the form
        driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/input').send_keys(Keys.SHIFT, Keys.ENTER)
        
        # Optional: Add a short wait to let the page process the submission
        time.sleep(1)

Key Changes Explained #

1. Per-Row Processing: We moved the input/submit logic inside the row loop, so each iteration handles exactly one row of data.
2. Target Current Row Data: Instead of sending the full names/codes lists, we send current_name and current_code—the values from just the current row.
3. Re-Locate Elements: After submitting the form, the page might refresh or the DOM might change. Re-finding the input fields each loop prevents StaleElementReferenceException errors.
4. Consistent Clearing: Clearing the fields before each input ensures no leftover text from previous submissions creeps in.

Alternative: If You Need to Keep the Data Lists #

If you need to retain the names and codes lists for other purposes, you can still use them with zip() to iterate over pairs:

def addCodes():
    global driver
    path = 'test.xlsx'
    workbook = xlrd.open_workbook(path)
    sheet = workbook.sheet_by_index(0)
    
    names = []
    codes = []
    for y in range(sheet.nrows):
        names.append(str(sheet.cell_value(y, 0)))
        codes.append(str(sheet.cell_value(y, 1)))
    
    # Iterate over paired name/code values
    for name, code in zip(names, codes):
        code_input = driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/div[5]/p/input')
        name_input = driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/div[6]/p/input')
        
        code_input.clear()
        name_input.clear()
        
        code_input.send_keys(code)
        name_input.send_keys(name)
        
        driver.find_element_by_xpath('/html/body/div/div/div/main/div/form/div[2]/input').send_keys(Keys.SHIFT, Keys.ENTER)
        time.sleep(1)

This achieves the same result but keeps your data stored in lists if you need them elsewhere in your code.

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