Python实现匹配指定字符串后读取Excel整行数据并用于Selenium

Absolutely, this requirement is completely feasible! Let’s walk through a step-by-step solution using Python, pandas (for Excel handling), and Selenium (for web automation).

Prerequisites #

First, install the necessary packages via pip:

pip install pandas openpyxl selenium webdriver-manager

• pandas + openpyxl: To read and process Excel files
• selenium: For web interaction
• webdriver-manager: To automatically manage browser drivers (no manual downloads needed)

Step-by-Step Implementation #

1. Read Excel & Extract Matching Incident Data #

First, we’ll load your Excel file, find the row matching your target incident ID, and extract the details into variables.

Assume your Excel file (incidents.xlsx) has columns like INC #, Priority, Date, Assignee (adjust these to match your actual column names):

import pandas as pd

# Load the Excel sheet into a DataFrame
df = pd.read_excel("incidents.xlsx")

# Target incident ID to match
target_incident = "INC00000"

# Find the row where INC # matches the target
matching_row = df[df["INC #"] == target_incident]

# Handle case where no matching incident is found
if matching_row.empty:
    print(f"Error: No incident found with ID {target_incident}")
else:
    # Extract details into variables
    inc_number = matching_row["INC #"].values[0]
    priority = matching_row["Priority"].values[0]
    incident_date = matching_row["Date"].values[0]
    assignee = matching_row["Assignee"].values[0]

    # Optional: Print to verify
    print(f"INC #: {inc_number}")
    print(f"Priority: {priority}")
    print(f"Date: {incident_date}")
    print(f"Assignee: {assignee}")

2. Use Extracted Variables in Selenium #

Next, we’ll use these variables to populate web elements. Adjust the element locators (like By.ID, element IDs) to match your webpage’s structure:

from selenium import webdriver
from selenium.webdriver.common.by import By
from webdriver_manager.chrome import ChromeDriverManager

# Initialize Chrome driver (you can use Firefox/Edge too)
driver = webdriver.Chrome(ChromeDriverManager().install())

# Navigate to your target webpage
driver.get("https://your-webpage-url.com")

# Populate the web fields with our variables
# Replace the locators (e.g., "incident-id-input") with your actual element IDs/XPaths/CSS selectors
driver.find_element(By.ID, "incident-id-input").send_keys(inc_number)
driver.find_element(By.ID, "priority-select").send_keys(priority)
# Convert date to string since send_keys expects text
driver.find_element(By.ID, "incident-date-input").send_keys(str(incident_date))
driver.find_element(By.ID, "assignee-input").send_keys(assignee)

# Optional: Submit the form if needed
# driver.find_element(By.ID, "submit-form-button").click()

# Keep the browser open for verification (remove this line in production)
input("Press Enter to close the browser...")

# Clean up: Close the driver
driver.quit()

Key Notes to Avoid Errors #

• Excel Column Names: Ensure the column names in your code exactly match those in your Excel file (case-sensitive!).
• Element Locators: Use the correct locator strategy (ID, XPath, CSS selector) for your webpage elements. You can find these using browser dev tools (F12).
• Date Formatting: If the date from Excel is a datetime object, convert it to a string format that your webpage accepts (e.g., incident_date.strftime("%m/%d/%Y") for MM/DD/YYYY).
• Error Handling: Add try-except blocks if you want to handle cases like missing web elements or Excel file issues.

Full Combined Code #

Here’s the full code putting it all together:

import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.by import By
from webdriver_manager.chrome import ChromeDriverManager

# --- Step 1: Read Excel and extract data ---
df = pd.read_excel("incidents.xlsx")
target_incident = "INC00000"
matching_row = df[df["INC #"] == target_incident]

if matching_row.empty:
    print(f"Error: No incident found with ID {target_incident}")
else:
    inc_number = matching_row["INC #"].values[0]
    priority = matching_row["Priority"].values[0]
    incident_date = matching_row["Date"].values[0].strftime("%m/%d/%Y")  # Format date
    assignee = matching_row["Assignee"].values[0]

    # --- Step 2: Use Selenium to fill web elements ---
    driver = webdriver.Chrome(ChromeDriverManager().install())
    driver.get("https://your-webpage-url.com")

    try:
        driver.find_element(By.ID, "incident-id-input").send_keys(inc_number)
        driver.find_element(By.ID, "priority-select").send_keys(priority)
        driver.find_element(By.ID, "incident-date-input").send_keys(incident_date)
        driver.find_element(By.ID, "assignee-input").send_keys(assignee)
        
        print("Successfully filled all fields!")
        input("Press Enter to close...")
    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        driver.quit()

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