Introduction

In real-world automation projects, test data is rarely hard-coded inside the test scripts. Instead, it is usually stored in external files such as Excel, CSV, JSON, or databases. Among these, Microsoft Excel is one of the most commonly used formats because it is easy to create, update, and manage large amounts of test data.

Selenium itself cannot read Excel files directly. To work with Excel spreadsheets in Python, we use the openpyxl library. It allows us to create Excel files, read data from worksheets, update cell values, and save changes.

By reading test data from Excel files, the same Selenium test can execute multiple times using different input values, making the automation framework more reusable and easier to maintain.

In this tutorial, you’ll learn what reading Excel files means, why it is important, how to read Excel data using openpyxl, and how to use that data in Selenium automation.

What is Reading Excel Files?

Reading Excel Files is the process of retrieving test data stored in a Microsoft Excel spreadsheet and using it during automation test execution.

Instead of writing input values directly inside the test script, the framework reads them from an Excel file.

For example:

         Excel File (.xlsx)
               │
               ▼
        Read Data using openpyxl
               │
               ▼
        Selenium Test Script
               │
               ▼
         Execute Test
               │
               ▼
        Validate Results

This approach separates the test data from the test logic, making the framework more flexible.

Why Read Test Data from Excel?

Reading data from Excel offers several benefits:

Keeps test data separate from test scripts.
Reduces hard-coded values.
Makes test cases reusable.
Allows non-programmers to update test data.
Supports Data-Driven Testing.
Simplifies maintenance.
Handles multiple test scenarios efficiently.
Improves framework scalability.

Installing `openpyxl`

Before reading Excel files, install the openpyxl library using:

pip install openpyxl

After installation, it can be imported into Python scripts.

Example

from openpyxl import Workbook, load_workbook
from selenium import webdriver
from selenium.webdriver.common.by import By


def test_reading_excel_files(tmp_path):
    excel_path = tmp_path / "messages.xlsx"

    workbook = Workbook()
    sheet = workbook.active
    sheet.append(["message"])
    sheet.append(["Excel Message"])
    workbook.save(excel_path)

    workbook = load_workbook(excel_path)
    sheet = workbook.active
    message = sheet.cell(row=2, column=1).value

    driver = webdriver.Chrome()

    try:
        driver.get("https://www.testmuai.com/selenium-playground/simple-form-demo")
        driver.find_element(By.ID, "user-message").send_keys(message)
        driver.find_element(By.ID, "showInput").click()

        assert driver.find_element(By.ID, "message").text == message
    finally:
        driver.quit()

Understanding the Code

Import Required Modules

from openpyxl import Workbook, load_workbook
from selenium import webdriver
from selenium.webdriver.common.by import By

The required modules are imported.

Workbook creates a new Excel workbook.
load_workbook opens an existing Excel file.
webdriver launches the browser.
By is used to locate web elements.

Create an Excel File

excel_path = tmp_path / "messages.xlsx"

workbook = Workbook()
sheet = workbook.active

A temporary Excel file named messages.xlsx is created.

A workbook object is initialized, and its active worksheet is selected.

Note: In real automation frameworks, the Excel file already exists and contains test data. It is usually stored inside a folder such as testdata or resources.

Add Data to the Excel File

sheet.append(["message"])
sheet.append(["Excel Message"])
workbook.save(excel_path)

The first row contains the column header.

The second row contains the actual test data.

Finally, the workbook is saved.

The Excel sheet looks like this:

Message
Excel Message

Open the Excel File

workbook = load_workbook(excel_path)
sheet = workbook.active

The saved Excel workbook is opened again using load_workbook().

The active worksheet is selected for reading data.

Read Data from a Cell

message = sheet.cell(row=2, column=1).value

The value stored in Row 2, Column 1 is retrieved.

The value read from the Excel file is:

Excel Message

This value is stored in the variable message.

Launch the Browser

driver = webdriver.Chrome()

A new Chrome browser instance is launched.

Open the Application

driver.get("https://www.testmuai.com/selenium-playground/simple-form-demo")

The browser opens the Selenium Playground Simple Form Demo page.

Enter the Excel Data

driver.find_element(By.ID, "user-message").send_keys(message)

The value read from the Excel file is entered into the input textbox.

Since the value comes from Excel, no data is hard-coded inside the Selenium commands.

Click the Button

driver.find_element(By.ID, "showInput").click()

The Show Message button is clicked.

The application displays the entered message.

Verify the Result

assert driver.find_element(By.ID, "message").text == message

The displayed message is compared with the value read from the Excel file.

If both values match, the test passes successfully.

Close the Browser

finally:
    driver.quit()

The browser is closed after the test execution, ensuring proper cleanup.

Practical Example

Suppose an e-commerce website has a login page.

Instead of hard-coding usernames and passwords inside the Selenium script, all login credentials are stored in an Excel file. During execution, the automation framework reads each row from the spreadsheet and uses the values to perform multiple login tests.

Automation Testing Example

Consider an online banking application where hundreds of customer accounts must be validated.

The automation framework stores account numbers, usernames, passwords, and expected results in an Excel spreadsheet. Selenium reads each row one by one and executes the same test using different customer data, enabling efficient Data-Driven Testing.

Real-World Example

Reading Excel files is commonly used in automation frameworks for:

Banking Applications
E-commerce Websites
CRM Systems
Healthcare Applications
ERP Systems
Insurance Portals
HR Management Systems
Enterprise Web Applications

Typical Excel data includes login credentials, customer information, product details, search keywords, payment data, transaction records, and expected test results.

Advantages of Reading Excel Files

Separates test data from test scripts.
Supports Data-Driven Testing.
Reduces hard-coded values.
Makes test cases reusable.
Allows easy updates to test data.
Handles large datasets efficiently.
Improves framework maintainability.
Supports non-technical users who manage test data.

Common Mistakes Beginners Make

Hard-Coding Test Data

Avoid writing input values directly inside Selenium scripts.

Store reusable data in Excel files whenever possible.

Reading the Wrong Cell

Always verify the correct row and column numbers before retrieving data.

Forgetting to Save the Workbook

If changes are made to an Excel file, remember to save the workbook before reading it again.

Not Installing `openpyxl`

Selenium cannot read Excel files directly.

Install and import the openpyxl library before working with Excel files.

Best Practices

Store reusable test data in Excel files.
Keep test logic separate from test data.
Use meaningful column headers.
Organize related data into separate worksheets when needed.
Validate that the required cells contain data before using them.
Close resources properly after execution.
Use Excel files when business users frequently update test data.

Conclusion

Reading Excel Files is an essential skill for implementing Data-Driven Testing in Selenium with Python. By using the openpyxl library, automation scripts can retrieve test data from Excel spreadsheets instead of relying on hard-coded values. This approach improves reusability, simplifies maintenance, and enables automation frameworks to execute the same test with multiple datasets efficiently.

Frequently Asked Questions (FAQs)

Why is Excel commonly used in Selenium automation?

Excel is easy to create, edit, and manage, making it a convenient way to store large amounts of test data for Data-Driven Testing.

Can Selenium read Excel files directly?

No.

Selenium cannot read Excel files directly. Libraries such as openpyxl are used to read and write Excel files in Python.

What is `openpyxl`?

openpyxl is a Python library used to create, read, update, and save Microsoft Excel (.xlsx) files.

Can one Excel file contain multiple test cases?

Yes.

An Excel workbook can contain multiple worksheets, and each worksheet can store many rows of test data for different test scenarios.

Is reading Excel files commonly used in automation frameworks?