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.

Selenium Python Tutorial 11 | Selenium python Logging | Log File

Moving on to 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 Python Selenium logging with import logging library. You can log multiple messages using the Selenium Python logging at multiple levels (debug, info, warning, error and critical). First, view the Selenium Python Logging Tutorial. Then read on.

 After import logging in Python 3, you can use logging.basicConfig to configure logging e.g. to log only certain levels, specify a logging format or specify the log file. Here is the first Selenium Python logging example, which is in Logs.py file in the Selenium Python tutorial 11. The explanations are in the comments within the code (lines or phrases starting with #).

# Selenium WebDriver Python coding
# import Python logging library with the alias, L
import logging as L
# Configure logging to start with severity level, DEBUG.
L.basicConfig(level=L.DEBUG)
L.debug('Debug message')
L.info('Info message')
L.warning('Warning message')
L.error('Error message')
L.critical('Critical message')

This is my Python user defined function to log messages automatically. I put it in the Utilities.py file that I showed in the Selenium Python tutorial 10.

# Selenium WebDriver Python coding

import logging as L

def log(level, message, file):
    # Configure logging to start with the INFO level, the log file and the filemode as append to log file.

    L.basicConfig(level=L.INFO, filename=file, filemode="a",
                  format="%(asctime)-12s %(levelname)s %(message)s", # log entry format
                  datefmt="%d-%m-%Y %H:%M:%S") # date time format
    if level == "INFO": L.info(message)
    if level == "WARNING": L.warning(message)
    if level == "ERROR": L.error(message)
    if level == "CRITICAL": L.critical(message)

Here is my log function called in the Checkbox.py Selenium Python example that I showed in the Selenium Python tutorial 3. The Checkbox.py Selenium Python script answers a quiz automatically until each answer is "Correct." or it runs out of checkbox combinations.

# 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
import CheckboxFunctions as C
# Import Utilities in order to call the log function.
import Utilities as U

exec_path = r"E:\Training\SeleniumPython\Downloads\geckodriver-v0.26.0-win32\geckodriver.exe"
URL = "https://inderpsingh.blogspot.com/2013/01/HTMLCSSQuiz1.html"
wait_time_out = 15
check_name_locator = "option"
# Specify the log file file path and file name with your computer's folder. Escape the backslashes.
# The following is an example only.
log_file = "E:\\Training\\SeleniumPython\\Logs\\LogCheckBoxQuiz.txt"
# Specify the log message for pass and fail.
pass_message = "Answered correctly - Question Number"
fail_message = "Answered incorrectly - Question Number"

driver = webdriver.Firefox(executable_path=exec_path)
wait = W(driver, wait_time_out)
driver.get(URL)
i = 0
while i<10:
    i += 1
    driver.execute_script("window.scrollBy(0,120)","")
    check_element_1 = wait.until(E.presence_of_element_located((By.NAME, check_name_locator + str(i) + "1")))
    check_element_2 = wait.until(E.presence_of_element_located((By.NAME, check_name_locator + str(i) + "2")))
    check_element_3 = wait.until(E.presence_of_element_located((By.NAME, check_name_locator + str(i) + "3")))
    check_element_1.click()
    check_element_2.click()
    check_element_3.click() # checkboxes 1, 2 & 3 are selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_1.click() # checkboxes 2 & 3 are selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_1.click()
    check_element_2.click() # checkboxes 1 & 3 are selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_2.click()
    check_element_3.click() # checkboxes 1 & 2 are selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_2.click() # only checkbox 1 is selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_1.click()
    check_element_2.click() # only checkbox 2 is selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    check_element_2.click()
    check_element_3.click()# only checkbox 3 is selected
    if C.answered(driver, i):
        # Python Selenium logging if the question is answered successfully
        U.log("INFO", pass_message + str(i), log_file)
        continue
    # Selenium Python logging if the question is still unanswered.
    U.log("ERROR", fail_message + str(i), log_file)

The above Selenium Python script calls the answered user defined function that I wrote in CheckboxFunctions.py file below.

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
wait_time_out = 15
def answered(d, question_number):
    wait_variable = W(d, wait_time_out)
    answer_element = wait_variable.until(E.presence_of_element_located((By.NAME, "answer" + str(question_number))))
    if "Correct." in answer_element.get_attribute("value"):
        return True
    else:
        return False

Want to see the above Selenium Python test automation working? Please view my Selenium Python Logging Tutorial. Thank you.

Selenium Python Tutorial 10 | Selenium python Screenshot | selenium python screenshot_as_png

This is the next tutorial in the Selenium Python Tutorials for Beginners. This Selenium Python beginner tutorial explains Selenium Python screenshot or Selenium Python snapshot, meaning the image of the screen in a specific state. We may need to take a Selenium Python screenshot on failure of test case for bug reporting or on success for proof of testing. First, view the Selenium Python Screenshot Tutorial. Then read on.

Here is my Selenium Python example with a Python user defined function to take a WebDriver screenshot Python automatically. Then, I will use Python Imaging Library to modify the screenshot. The explanations are in the comments within the code (lines starting with #).

# Selenium WebDriver Python coding
# This Python file, Utilities.py, contains the WebDriver screenshot Python functions, screenshot and modify_screenshot.
import time
from PIL import Image as I

def screenshot(d):
    """Take the Selenium Python screenshot with the current date and time"""
    # Put the folder path with your computer's folder. The following folder path is an example only.
    # Escape each backslash with another backslash.
    folder = "E:\\Training\\SeleniumPython\\Screenshots\\"
    time_string = time.asctime().replace(":", " ")
    # Take Selenium Python screenshot_as_png
    file_name = folder + time_string + ".png"
    # Save Python Selenium screenshot with Selenium WebDriver save_screenshot method.
    d.save_screenshot(file_name)
   # Another Save Python Selenium screenshot with Selenium WebDriver method is get_screenshot_as_file (commented out).
    #d.get_screenshot_as_file(file_name)
    modify_screenshot(file_name)

def modify_screenshot(f):
    picture = I.open(f)
    # Modify the screenshot. Resize screenshot to another screen resolution.
    picture = picture.resize((1280, 654))
    # Modify the screenshot. Rotate screenshot by 90 degrees.
    picture = picture.transpose(I.ROTATE_90)
    # Modify the screenshot to save Selenium Python screenshot_as_jpg.
    picture = picture.convert("RGB")
    # Python strings are immutable. Create a new string for the filename.
    f_new = f.replace(".png", ".jpg")
    picture.save(f_new)

Here is my Selenium Python script, Screenshots.py that calls the Python Selenium screenshot function. Note that the screenshot user defined function calls the modify_screenshot function on it's last line.

# Selenium WebDriver Python coding
from selenium import webdriver
import Utilities as U
exec_path = r"E:\Training\SeleniumPython\Downloads\geckodriver-v0.26.0-win32\geckodriver.exe"
URL = "https://www.youtube.com/playlist?list=PLc3SzDYhhiGUPPWt_rIVszepL1nMTbDaW"

driver = webdriver.Firefox(executable_path=exec_path)
driver.get(URL)
U.screenshot(driver)
# Close the browser with the Selenium WebDriver quit method.
driver.quit()

Want to learn how the above user defined functions work in action? Or how to implement the screenshot function in an existing Selenium Python test automation script? Then, please view my Selenium Python Screenshot Tutorial. Thank you.