Working with excel in Selenium Python
In the previous articles on Selenium Python Tutorial, we have covered “Handling Cookies in Selenium Python“. In this tutorial, we will learn Working with excel in Selenium Python.
We can work with the excel workbook in Selenium WebDriver. Excel, also called a spreadsheet can have extensions like .xlsx, .xlsm, and so on. Excel consists of multiple worksheets.
Each worksheet is divided into rows and columns both having an address. The address of rows starts from 1 and the column address begins from A. A cell inside a worksheet is the intersection point of a row and a column.
Each cell in a worksheet has a unique address defined by the combination of row and column address. Out of the multiple worksheets in a workbook, the worksheet on which we are currently working is known as the active worksheet.
While working with the Selenium tool along with language Python, we use the OpenPyXL library to access Excel from version 2010. Python does not provide this library automatically.
We need to run the command pip install openpyxl to get the OpenPyXL library. Also we have to add an import openpyxl statement in our code to get all the methods under that library.
To fetch the active worksheet in a workbook, we have to use the load_workbook () method which takes the path of the Excel as its parameter then uses the active method.
Code Implementation to identify active worksheet.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active
To read the value of a particular cell, we need to follow all the above steps. Then we have to apply the cell () method on the active worksheet object. This method contains the row and column number as parameters. Finally we shall use the value method to actually read the cell data.
Code Implementation to read a cell value.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active # to identify the cell c = s.cell (row = 3, column = 1) # to retrieve the cell value and print print ( c.value)
To write the value on a particular cell, we need to identify the active worksheet. Then we have to apply the cell () method on the active worksheet object. This method contains the row and column number as parameters.
Next, to set a value, we have to use the value method. Finally to save the workbook, we have to apply the save () method on the workbook object. The save () method takes the path of the file to be saved as a parameter.
Code Implementation to write a cell value.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active # to identify the cell c = s.cell (row = 2, column = 8) # to write a value in that cell s.cell (row = 2, column = 8).value = "Python" # to save the workbook in location bk.save ("C:\\STM\\Python.xlsx")
To get the maximum number of rows and columns in the worksheet, we need to identify the active worksheet. Then we have to apply the max_row method on the active worksheet object to get the total occupied rows count. Also, we have to apply the max_column method on the active worksheet object to get the total occupied columns count.
Code Implementation to identify maximum occupied row and column count.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active # to identify maximum rows count print ( s.max_row) # to identify maximum columns count print ( s.max_column)
To get all the cell data in the worksheet, we need to identify the active worksheet. Then we have to apply the max_row method on the active worksheet object to get the total occupied rows count. Also, we have to apply the max_column method on the active worksheet object to get the total occupied columns count.
To obtain the cell data, we shall then iterate through the maximum number of rows and columns that are occupied in the worksheet and then retrieve the cell value.
Code Implementation to get all the cell values in the worksheet.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active # to identify maximum rows count print ( s.max_row) # iterate till the count of occupied rows for m in range ( 1, s.max_row + 1): # iterate till the count of occupied columns for n in range ( 1, s.max_column + 1): # to get the cell data and print print (s.cell (row=m, column=n). value)
To get all the cell data of a particular row in the worksheet, we need to identify the active worksheet. Then we have to apply the max_row method on the active worksheet object to get the total occupied rows count. Also, we have to apply the max_column method on the active worksheet object to get the total occupied columns count.
To obtain the cell data, we shall then iterate through the maximum number of rows and columns that are occupied in the worksheet and then retrieve the cell value. Once we get the cell value we shall execute a conditional statement, and get all the values from a specific row which matches our requirement.
Let us consider a workbook having a worksheet with the above set of data. Suppose we want to get the Student details for the topic Python.
Code Implementation for the above scenario.
import openpyxl # to load the workbook with its path bk = openpyxl.load_workbook(“C:\\STM\\Python.xlsx”) # to identify active worksheet s = bk.active # to identify maximum rows count print ( s.max_row) # iterate till the count of occupied rows for m in range ( 1, s.max_row + 1): # to get all the cell values from column 1 if s.cell (row=m, column=1).value == "Python" : # iterate till the count of occupied columns for n in range ( 1, s.max_column + 1): # to get all the cell data from a row and print print (s.cell (row=m, column=n). value)
In the next article, we will learn 100+ Selenium Interview Questions.
Related posts:
- Selenium Python Tutorial
- Selenium Java Tutorial
- Selenium Interview Questions
- API Testing Tutorial
- Postman Tutorial