Selenium Python Tutorial 12 | Selenium python read Excel | Python selenium write to Excel | OpenPyXL

This is the next tutorial in the Selenium Python Tutorials for Beginners. It deals with Python Automation Testing with Selenium WebDriver Python. This Selenium Python beginner tutorial explains Selenium Python read Excel test data and Python Selenium write to Excel the test results. First, view the Selenium Python Excel Tutorial. Then read on.

In order to work with Selenium Python Excel, we can use the OpenPyXL library for commands to open Excel file, open a single worksheet, access a specific cell in the worksheet and save the Excel file. In the command prompt, we can use pip to install OpenPyXL as shown below:

Next, in our PyCharm project, we need to install the OpenPyXL package. We can click File > Settings > Project > Project interpreter. Then click + button, which is the Install button. It shows all the available packages. In the search text box, type openpyxl. Select openpyxl and click Install Package button. It gives the message, Package 'openpyxl' installed successfully. Click Close button.

Here is my first Selenium Python example for selenium python read Excel. This is the ExcelRead.py Python script that I showed in the Selenium Python tutorial 11. I have put explanations in the comments within the code (lines or phrases starting with #).

# Selenium WebDriver Python coding
# Import OpenPyXL to work with Python Selenium Excel.
import openpyxl as O
# Specify the Selenium Python Excel file path and file name with your computer's folder.
# Escape the backslashes. The following is an example only.
Excel_file = "E:\\Training\\SeleniumPython\\TestData\\JourneyPlanner.xlsx"
# My Excel worksheet has the columns Distance, Speed, Travel hours/ day, Expected Result - Travel days and Test result.
# You can see the Excel worksheet format in the Selenium Python tutorial 12.
Excel_worksheet = "Data1"
# Python Selenium open Excel file
wb = O.load_workbook(Excel_file)
# Python Selenium open Excel worksheet in the Excel file
ws = wb[Excel_worksheet]
row_num = ws.max_row
col_num = ws.max_column
print ("The number of rows is ", row_num, "and the number of columns is ", col_num)
row = 2 # In the Excel worksheet, the first row has headers. The test data is in the second row.
print ("distance = ", ws.cell(row, 1).value)
print ("speed = ", ws.cell(row, 2).value)
print ("Travel hours/day = ", ws.cell(row, 3).value)
print ("Expected Result = ", ws.cell(row, 4).value)

Here is my second Selenium Python example for Python Selenium write to Excel. This is the DropDownTestData.py Python script that I showed to test the Journey Planner application in the Selenium Python tutorial 4. I modified the DropDownTestData.py Selenium Python script to read test data values and use Python Selenium write to Excel.

# Selenium WebDriver Python coding
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait as W
from selenium.webdriver.support import expected_conditions as E
from selenium.webdriver.support.ui import Select
import time
# Import OpenPyXL to work with Selenium Python Excel.
import openpyxl as O
exec_path = r"E:\Training\SeleniumPython\Downloads\geckodriver-v0.26.0-win32\geckodriver.exe"
URL = "https://inderpsingh.blogspot.com/2014/08/demowebapp_24.html"
Excel_file = "E:\\Training\\SeleniumPython\\TestData\\JourneyPlanner.xlsx"
# My Excel worksheet has the columns Distance, Speed, Travel hours/ day, Expected Result - Travel days and Test result. 
# You can see the Excel worksheet format in the Selenium Python tutorial 12. 
Excel_worksheet = "Data1"
distance_id_locator = "distance"
speed_id_locator = "speed"
time_id_locator = "hours"
calculate_css_locator = ".post-body > div:nth-child(1) > div:nth-child(1) > form:nth-child(1) > button:nth-child(20)"
result_id_locator = "result"
message_id_locator = "message"
wait_time_out = 5
driver = webdriver.Firefox(executable_path=exec_path)
driver.get(URL)
wait_variable = W(driver, wait_time_out)
driver.execute_script("window.scrollBy(0,240)", "")
distance_element=wait_variable.until(E.presence_of_element_located((By.ID, distance_id_locator)))
speed_element=wait_variable.until(E.presence_of_element_located((By.ID, speed_id_locator)))
time_element=Select(wait_variable.until(E.presence_of_element_located((By.ID, time_id_locator))))
calculate_element=wait_variable.until(E.presence_of_element_located((By.CSS_SELECTOR, calculate_css_locator)))
# The result_element gives the result if the test data is valid.
result_element = wait_variable.until(E.presence_of_element_located((By.ID, result_id_locator)))
# The message_element gives the error message if the test data is invalid.
message_element=wait_variable.until(E.presence_of_element_located((By.ID, message_id_locator)))
# Python Selenium open Excel file
wb = O.load_workbook(Excel_file)
ws = wb[Excel_worksheet]
# Run the for loop from 2 (because row 1 has headers, not the test data).
for r in range(2, ws.max_row + 1):
    d = str(ws.cell(r, 1).value)
    distance_element.clear()
    distance_element.send_keys(d)
    s = str(ws.cell(r, 2).value)
    speed_element.clear()
    speed_element.send_keys(s)
    t = str(ws.cell(r, 3).value)
    time_element.select_by_visible_text(t)
    calculate_element.click()
    time.sleep(1)
    e = str(ws.cell(r, 4).value)
    # Check if the result or error message is the same as test data in the Excel file.
    if str(e) in result_element.text or str(e) in message_element.text:
        ws.cell(r, 5).value = "Pass"
    else:
        ws.cell(r, 5).value = "Fail"
    # Python Selenium write to Excel on disk
   # The Excel file must be closed when the save and close methods run.
    wb.save(Excel_file)
    wb.close()

Want to understand how the above Selenium Python script works? Please view my Selenium Python Read Excel Write Excel Tutorial. Thank you.