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.

Selenium Python Tutorial 9 | python selenium copy paste | Keyboard Actions in selenium python | Keys and PyAutoGUI

Moving on to the next tutorial in the Selenium Python Tutorials for Beginners. This Selenium Python beginner tutorial explains keyboard actions in Selenium Python using the Keys and PyAutoGUI libraries. The keyboard actions are send keys, edit text, copy and paste text and set focus using the keyboard. First, view the Selenium Python Copy Paste Tutorial. Then read on. 

 

Here is my first Selenium Python example with explicit wait. I have put explanations in the comments within the code (lines starting with #).

# Selenium WebDriver Python coding
# Type something in the Distance textbox and copy it to the speed textbox.
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 import ActionChains as A
from selenium.webdriver.common.keys import Keys as K

exec_path = r"E:\Training\SeleniumPython\Downloads\geckodriver-v0.26.0-win32\geckodriver.exe"
URL = "https://inderpsingh.blogspot.com/2014/08/demowebapp_24.html"
distance_id_locator = "distance"
speed_id_locator = "speed"
wait_time_out = 15
driver = webdriver.Firefox(executable_path=exec_path)
# Define variable for WebDriverWait Python.
wait_variable = W(driver, wait_time_out)
# Navigate to the URL given above.
driver.get(URL)
# Define the ActionChains object named a (to do keyboard actions).
a = A(driver)
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)))
# Type some text in the Distance text box using send keys.
distance_element.send_keys("123456")
# Python Selenium select all text using Ctrl+A
a.key_down(K.CONTROL).send_keys("a").perform()
# Python Selenium copy text to clipboard using Ctrl+C
a.key_down(K.CONTROL).send_keys("c").perform()
# Set focus to the Speed text box.
a.click_and_hold(speed_element).perform()
# Python Selenium paste text using Ctrl+V i.e. Python Selenium copy paste
a.key_down(K.CONTROL).send_keys("v").perform()

Here is my second Selenium Python example using PyAutoGUI, which I find having a number of features for keyboard actions and easy to use. You install the PyAutoGUI package with the pip command in the Command Prompt. Then install the PyAutoGUI package in the project under File menu>Settings>Project Interpreter. I explained such tasks here.

# 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 pyautogui as P

exec_path = r"E:\Training\SeleniumPython\Downloads\geckodriver-v0.26.0-win32\geckodriver.exe"
URL = "https://inderpsingh.blogspot.com/2014/08/demowebapp_24.html"
distance_id_locator = "distance"
wait_time_out = 15
driver = webdriver.Firefox(executable_path=exec_path)
wait_variable = W(driver, wait_time_out)
driver.get(URL)
distance_element = wait_variable.until(E.presence_of_element_located((By.ID, distance_id_locator)))
# Call send keys method to send an empty string to set focus to the Distance text box.
distance_element.send_keys("")
# Call PyAutoGUI write method to type text in the Distance text box.
P.write("123456.78")
P.sleep(1)
# Call PyAutoGUI press method to press backspace key 3 times i.e. remove ".78".
P.press("backspace", 3)
# Call PyAutoGUI hotkey method to press keyboard shortcut Ctrl+A i.e. Python Selenium select all.
P.hotkey("ctrl", "a")
# Call PyAutoGUI hotkey method to press keyboard shortcut Ctrl+C i.e. Python Selenium copy text from web page.
P.hotkey("ctrl", "c")
P.sleep(1)
# Call PyAutoGUI press method to set focus to Speed text box.
P.press("tab")
# Call PyAutoGUI hotkey method to press keyboard shortcut Ctrl+V i.e. Python Selenium paste text from clipboard.
P.hotkey("ctrl", "v")

Want to learn more? Want to see the above Python scripts in action? Then, please view my Selenium Python Keyboard Actions Tutorial. Thank you.