Read/Write data from Excel

We can read and write data from an excel sheet in Selenium webdriver in Python. An excel workbook consists of multiple sheets and each sheet consists of cells and columns.

To work with Excel in Python (with extensions .xlsx, .xlsm, and so on) we have to utilise the OpenPyXL library. To install this package, we have to run the following command −

pip install openpyxl.

Also, we have to add the statement import openpyxl in our code.

Import Openpyxl

To open an excel workbook, the method is load_workbook and pass the path of the excel file as a parameter to this method. To identify the active sheet, we have to use the active method on the workbook object.

To read a cell, the method cell is applied on the active sheet and the row and column numbers are passed as parameters to this method. Then, the value method is applied on a particular cell to read values within it.

Let us read the value at the third row and second column having the value D as shown below in an excel workbook of name Data.xlsx −

Data xlsx

Code Implementation

The code implementation read/write data from Excel to Selenium Webdriver in Python is as follows −

import openpyxl
#configure workbook path
b = openpyxl.load_workbook("C:\\Data.xlsx")
#get active sheet
sht = b.active
#get cell address within active sheet
cl = sht.cell (row = 3, column = 2)
#read value with cell
print("Reading value from row-3, col-2: ")
print (cl.value)

Output

Output

To write a cell, the method cell is applied on the active sheet and the row and column numbers are passed as parameters to this method. Then, the value method is applied on a particular cell to write on it. Finally, the workbook is to be saved with the method save, the path of the file to be saved is passed as a parameter to this method.

We shall take an Excel name testdata.xlsx and save it within the data folder within our project. We shall write the value - Selenium Python in the third row and seventh column.

Text Data Xlsx

Code Implementation

The code implementation for working on workbook in Selenium Webdriver is as follows −

from selenium import webdriver
import openpyxl
#load workbook
b= openpyxl.load_workbook('../data/testdata.xlsx')
#get active worksheet
sh = b.active
# write value in third row, 8th column
sh.cell(row=3, column=8).value = "Selenium Python"
#save workbook
b.save("../data/testdata.xlsx")
#identify cell
cl = sh.cell(row=3, column=8)
#read cell value
print("Reading value from row-3, col-8: ")
print(cl.value)

Output

Workbook in Selenium Webdriver

The output shows the message - Process with exit code 0 meaning that the above Python code executed successfully. Also, the value - Selenium Python is successfully written on the cell with address - row-3 and column - 8.

Advertisements