如何基于用户输入读取Excel指定行数据（替代硬编码方式）

Got it, let's ditch that messy hardcoded index approach and build a clean, dynamic solution that pulls the right row based on user input. Here's how to make it work smoothly:

Step 1: Use Pandas for Easy Excel Handling #

Pandas is perfect for querying Excel data without manual index hunting. First, install the required packages if you haven't already:

pip install pandas openpyxl

(Note: Use xlrd instead of openpyxl if you're working with .xls files)

Step 2: Build a Function to Fetch the Target Row #

Create a reusable function that takes your Excel path, sheet name, and the user-provided number value, then returns the matching row data.

import pandas as pd

def get_row_by_number(excel_path, sheet_name, target_number):
    # Load the Excel sheet into a DataFrame
    df = pd.read_excel(excel_path, sheet_name=sheet_name)
    
    # Filter rows where the 'number' column matches the target
    matching_row = df[df['number'] == target_number]
    
    # Check if we found a match
    if matching_row.empty:
        raise ValueError(f"No row found with number: {target_number}")
    
    # Convert the row to a dictionary for easy access to columns
    return matching_row.iloc[0].to_dict()

Step 3: Integrate with Your Selenium Workflow #

Now, get the user input, fetch the row, and pass each column value to the corresponding Selenium elements:

from selenium import webdriver
from selenium.webdriver.common.by import By

# Example user input (replace with actual input method, like input() or UI input)
user_input_number = input("Enter the PRB number: ")

# Fetch the matching row
try:
    row_data = get_row_by_number("your_excel_file.xlsx", "Sheet1", user_input_number)
except ValueError as e:
    print(e)
    exit()

# Initialize Selenium driver
driver = webdriver.Chrome()
driver.get("your_target_url")

# Pass each column value to the corresponding input fields
driver.find_element(By.ID, "number_input").send_keys(row_data['number'])
driver.find_element(By.ID, "priority_input").send_keys(row_data['priority'])
driver.find_element(By.ID, "assignee_input").send_keys(row_data['assignee'])

# Add more fields as needed...

Step 4: Handle Edge Cases #

Don't forget to account for scenarios where the user enters a number that doesn't exist in the sheet—we added a ValueError in the function to catch that, but you can adjust the error handling to fit your workflow (like showing a user-friendly message instead of exiting).

This approach eliminates hardcoded indexes entirely, making your code easier to maintain and adapt if the Excel sheet's row order changes.

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