Selenium中Excel数据读取代码全局化及多场景适配指导

Got it, let's break down how to make your Excel data reading logic reusable across different Selenium test scenarios. Here's a practical, modular approach you can follow:

We'll abstract all Excel handling logic into a standalone class — this way, any test scenario can call it with just the right parameters instead of duplicating code. I'll use openpyxl (a widely used Excel library for Python) here, since it's great for reading test data.

from openpyxl import load_workbook
from typing import List, Dict

class ExcelReader:
    def __init__(self, excel_file_path: str):
        self.excel_file = excel_file_path
        try:
            # Load workbook with data_only=True to get cell values (not formulas)
            self.workbook = load_workbook(filename=self.excel_file, data_only=True)
        except FileNotFoundError:
            raise Exception(f"Oops, Excel file not found at path: {self.excel_file}")

    def get_sheet_data(self, sheet_name: str, include_header: bool = True) -> List[Dict]:
        """
        Fetch all data from a specified worksheet, return as list of dictionaries
        :param sheet_name: Name of the sheet you want to read
        :param include_header: Use first row as keys for the output dictionaries
        :return: List of test data entries (each entry is a dict)
        """
        if sheet_name not in self.workbook.sheetnames:
            raise Exception(f"Worksheet '{sheet_name}' doesn't exist in your Excel file")
        
        sheet = self.workbook[sheet_name]
        data = []
        headers = []

        # Extract headers if needed
        if include_header:
            for cell in sheet[1]:
                headers.append(cell.value.strip() if cell.value else "")
        
        # Iterate through data rows (skip header row if include_header is True)
        start_row = 2 if include_header else 1
        for row in sheet.iter_rows(min_row=start_row, values_only=True):
            if all(cell is None for cell in row):
                continue  # Skip empty rows to avoid junk data
            
            if include_header:
                # Map row values to their corresponding headers
                row_dict = {}
                for idx, header in enumerate(headers):
                    row_dict[header] = row[idx] if row[idx] is not None else ""
                data.append(row_dict)
            else:
                data.append(list(row))
        
        return data

Now you can instantiate this class once (or per test suite) and fetch data from any sheet with a single method call. Here's how it works for your existing login scenario, plus a new checkout scenario as an example:

Example 1: Login Scenario #

from selenium import webdriver

# Initialize the Excel reader once (point to your test data file)
excel_reader = ExcelReader("test_data.xlsx")

def test_login():
    # Fetch login-specific data from the "Login" sheet
    login_test_cases = excel_reader.get_sheet_data("Login")
    
    for test_case in login_test_cases:
        driver = webdriver.Chrome()
        driver.get("https://your-app-login-url.com")
        
        # Use data directly from the Excel row
        driver.find_element("id", "username").send_keys(test_case["Username"])
        driver.find_element("id", "password").send_keys(test_case["Password"])
        driver.find_element("id", "login-btn").click()
        
        # Add your login success/failure assertions here
        assert "Dashboard" in driver.title
        
        driver.quit()

Example 2: Checkout Scenario #

def test_checkout():
    # Fetch checkout-specific data from the "Checkout" sheet
    checkout_test_cases = excel_reader.get_sheet_data("Checkout")
    
    for test_case in checkout_test_cases:
        driver = webdriver.Chrome()
        driver.get("https://your-app-checkout-url.com")
        
        # Use checkout-specific Excel data
        driver.find_element("id", "product-code").send_keys(test_case["ProductCode"])
        driver.find_element("id", "shipping-address").send_keys(test_case["ShippingAddress"])
        # ... rest of your checkout test steps
        
        driver.quit()

You can extend the ExcelReader class with more methods to fit your needs:

• get_single_row(sheet_name, row_num): Fetch data from a specific row
• get_column_values(sheet_name, column_header): Get all values from a single column
• filter_test_cases(sheet_name, filter_key, filter_value): Only return rows where a header matches a value (e.g., only get "Valid" login test cases)

• No code duplication: You won't rewrite Excel reading logic for every new scenario
• Easy maintenance: If your Excel structure changes, you only update the utility class once
• Scalable: Add new test sheets anytime without touching existing test code

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