Read Excel Sheet

In Previous section, we have discussed about the Executing JavaScript in Python. In this section we will discuss about the Data Driven Framework in Selenium Python and how to read data from Excel Sheet.

Objective:

  • What is Data Driven Framework
  • How to implement Data Driven Framework
  • Example

What is Data Driven Framework?

Data Driven Framework is nothing but an automation framework in which we can take input from external Excel Sheet and manipulate / use in our selenium script. In this way, tester can test the application with multiple input value with a single test.

Read Excel Sheet

From the above diagram, you can notice that in Data Driven Framework, our script takes input from the external Data file as Test Data and run the script in Application Under Test (AUT). Once the script runs complete it will display the output in Test Result and can be saved in external data file of the extended output if any.

Why Data Driven Framework:

From the above section, we have understood what is Data Driven Framework. But why do we need Data Driven Framework if we can use variable in application which is faster. This option is good if we need to manipulate small number of Data set. If we need to handle more number of data set, then handling all data in variable will be complex and time consuming which needs more variable which takes more time. But in Data Driven Framework, We can separate our test case and Test data and takes input data from the external data file what ever needed which is much faster than traditional method.

How to read /write Excel sheet:

To implement Data Driven Framework, there is a package available which we need to install to our application called XLRD package. XLRD means Excel Reader. This is similar to Apache POI in java. This well help to rad the excel file in selenium Python. XLRD is not installed by default. To install this package we need to install with pip command like below.

pip install xlrd

After successfully installation, you can see below terminal screen.

Now Let’s read excel sheet step by step.

Import xlrd package.

To start with the reading of excel sheet, we need ti use xlrd package. For this we  need to import this to our script so that we can use in our script.

import xlrd

Open Excel Sheet:

After importing, we need to locate the excel file and open the corresponding file by using below command. To locate and open the excel sheet we need xlrd.open_workbook() method which takes location of the Excel sheet as parameter. This method returns a workbook object. In below example wb is the workbook object.

wb = xlrd.open_workbook("C://Users//admin//Desktop//LinkedIn.xlsx")

Find the Sheet.

As we know every excel sheet has number of sheet. To work with excel sheet, we need to find the sheet name in which we are going to work. For this we need sheet_by_name() method which takes the name of the sheet in argument. This will return the sheet object. In below example, sh is called sheet object.

sh = wb.sheet_by_name("LinkedIn")

Find the total row number:

Once you are in the sheet, you need to find the total number of row and column so that you can apply the loop to read the data one by one. For this you can use nrows and ncols to count number of rows and columns respectively. These method will return the number of rows and columns.

row = sh.nrows
column = sh.ncols

Read data one by one:

Now we are at final step. After knowing number if rows and cols we can apply loop and print corresponding data and also we can use in our application.

for link in range(1, row):
    first_name = sh.cell_value(link, 0)
    last_name = sh.cell_value(link, 1)
    print(first_name + " " + last_name)

It will take all content and print in console.

import xlrd

wb = xlrd.open_workbook("C://Users//admin//Desktop//LinkedIn.xlsx")
sh = wb.sheet_by_name("LinkedIn")

# Total Number of rows

row = sh.nrows
column = sh.ncols
print("Row count: ", row)
print("Column count: ", column)

for link in range(1, row):
    first_name = sh.cell_value(link, 0)
    last_name = sh.cell_value(link, 1)
    print(first_name + " " + last_name)

Output:

Summary:

  • Data Driven Framework is nothing but an automation framework in which we can take input from external Excel Sheet and manipulate in our selenium script.
  • In Data Driven Framework, We can separate our test case and Test data and takes input data from the external data file what ever needed which is much faster than traditional method.
  • To implement Data Driven Framework, there is a package available which we need to install to our application called XLRD package. XLRD means Excel Reader.

Leave a Reply

Your email address will not be published. Required fields are marked *