Reading test data from Excel in Selenium Python

Introduction

Reading test data from Excel helps you run tests with different inputs easily. It saves time and avoids mistakes from typing data again and again.

You want to test a login form with many usernames and passwords.

You need to check a calculator app with many number pairs.

You want to run the same test steps but with different data sets.

You want to keep test data separate from test code for easy updates.

Syntax

Selenium Python

import openpyxl

# Load Excel file
workbook = openpyxl.load_workbook('data.xlsx')

# Select sheet by name
sheet = workbook['Sheet1']

# Read cell value
value = sheet.cell(row=1, column=1).value

Use openpyxl library to read Excel files in Python.

Rows and columns start at 1, not 0.

Examples

This reads the value from row 2, column 3 of the active sheet.

Selenium Python

import openpyxl
workbook = openpyxl.load_workbook('testdata.xlsx')
sheet = workbook.active
print(sheet.cell(row=2, column=3).value)

This loops through all rows in the 'Users' sheet and prints usernames and passwords.

Selenium Python

import openpyxl
workbook = openpyxl.load_workbook('testdata.xlsx')
sheet = workbook['Users']
for row in range(2, sheet.max_row + 1):
    username = sheet.cell(row=row, column=1).value
    password = sheet.cell(row=row, column=2).value
    print(f"User: {username}, Pass: {password}")

Sample Program

This script reads login data from an Excel file and checks that the data is not empty. It simulates using this data in a test.

Selenium Python

import openpyxl

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

# Read username and password from second row
username = sheet.cell(row=2, column=1).value
password = sheet.cell(row=2, column=2).value

# Simple test simulation
print(f"Testing login with username: {username} and password: {password}")

# Assert username and password are not empty
assert username is not None and username != '', 'Username is empty'
assert password is not None and password != '', 'Password is empty'

print('Test data read successfully and assertions passed.')

Important Notes

Make sure the Excel file is in the same folder or provide the full path.

Use meaningful sheet names to avoid confusion.

Always check for empty or missing data to avoid test errors.

Summary

Reading test data from Excel helps run tests with many inputs easily.

Use openpyxl to load and read Excel files in Python.

Always validate the data read before using it in tests.