Preconditions (4)

Step 1: Open the Excel file 'testdata.xlsx'

Step 2: Read each row from the sheet 'LoginData'

Step 3: For each row, open the login page URL

Step 4: Enter the 'username' value into the username input field with id 'username'

Step 5: Enter the 'password' value into the password input field with id 'password'

Step 6: Click the login button with id 'loginBtn'

Step 7: Verify that the URL changes to 'https://example.com/dashboard' after login

Step 8: Log out or reset the state before next iteration

✅ Expected Result: For each username and password pair from Excel, the login succeeds and the dashboard page loads

Automation Requirements - Selenium with Python

Assertions Needed:

Verify URL after login is 'https://example.com/dashboard' for each data set

Best Practices:

Use openpyxl to read Excel data

Use explicit waits to wait for page load or elements

Use Page Object Model to separate page actions

Close browser after tests

Handle exceptions gracefully

Automated Solution

Selenium Python

import unittest
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from openpyxl import load_workbook

class LoginPage:
    def __init__(self, driver):
        self.driver = driver
        self.username_input = (By.ID, 'username')
        self.password_input = (By.ID, 'password')
        self.login_button = (By.ID, 'loginBtn')

    def load(self):
        self.driver.get('https://example.com/login')

    def login(self, username, password):
        WebDriverWait(self.driver, 10).until(
            EC.visibility_of_element_located(self.username_input))
        self.driver.find_element(*self.username_input).clear()
        self.driver.find_element(*self.username_input).send_keys(username)
        self.driver.find_element(*self.password_input).clear()
        self.driver.find_element(*self.password_input).send_keys(password)
        self.driver.find_element(*self.login_button).click()

class TestLoginWithExcel(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.driver = webdriver.Chrome()
        cls.driver.maximize_window()
        cls.wait = WebDriverWait(cls.driver, 10)

        # Load Excel workbook and sheet
        cls.workbook = load_workbook('testdata.xlsx')
        cls.sheet = cls.workbook['LoginData']

    def test_login_data_driven(self):
        login_page = LoginPage(self.driver)

        # Iterate over rows starting from row 2 (assuming row 1 is header)
        for row in self.sheet.iter_rows(min_row=2, values_only=True):
            username, password = row
            login_page.load()
            login_page.login(username, password)

            # Wait for URL to change to dashboard
            self.wait.until(EC.url_to_be('https://example.com/dashboard'))
            current_url = self.driver.current_url
            self.assertEqual(current_url, 'https://example.com/dashboard',
                             f'Login failed for user {username}')

            # For simplicity, navigate back to login page to reset state
            self.driver.get('https://example.com/login')

    @classmethod
    def tearDownClass(cls):
        cls.driver.quit()

if __name__ == '__main__':
    unittest.main()

This script uses unittest as the test framework and Selenium WebDriver to automate the browser.

The LoginPage class is a simple Page Object Model that encapsulates the login page elements and actions.

We use openpyxl to read the Excel file testdata.xlsx and get username and password pairs from the sheet LoginData.

In the test method, we loop through each row of data, open the login page, enter credentials, click login, and then wait explicitly for the URL to become the dashboard URL.

Assertions check that the URL is correct after login, indicating success.

After each login, we navigate back to the login page to reset the state for the next test data.

Finally, the browser is closed after all tests run.

This approach follows best practices: explicit waits, page objects, and clean test structure.

Common Mistakes - 5 Pitfalls

Hardcoding Excel file path without checking existence

Using time.sleep() instead of explicit waits

Not clearing input fields before sending keys

Not resetting state between test data iterations

Using absolute XPath for locating elements

Bonus Challenge

Now add data-driven testing with 3 different username and password pairs in the Excel file and verify all logins.

Show Hint