In this blog, you will learn how to create a Python function to read and fetch the data from an Excel sheet and then call that function in your robot class.

Task: Read Employee Name from the Excel file whose screenshot shared below

Create a function to read data from Excel Files using Python

First Install the Openpyxl library in your system as per the steps mentioned below:

1. Open a command prompt

2. Write the “pip install robot framework-openpyxllib” command to install openpyxl library

Now create a Python file. You can give any name to your file and save it with the .py extension

I have created a file python file as ExcelHandling.py

import openpyxl
def open_and_read_excel_file(sheetname, column_value, file):
filename = file
wb_obj = openpyxl.load_workbook(filename, data_only = True)
sheet_obj = wb_obj.get_sheet_by_name(sheetname)
column = int(column_value)
m_row = sheet_obj.max_row
my_list = [] #created an empty list
for i in range(2, m_row): # Here I have started the loop from 2 as I want to skip the column heading value in output
cell_obj = sheet_obj.cell(row=i, column=column)
print(cell_obj.value)
my_list.append(cell_obj.value)
return my_list

Here I have created a function as open_and_read_excel_file and passed three parameters in that function. Use all the parameters stated below:

a) Sheet name — You can pass the sheet name to read the data from the particular sheet by specifying the sheet name.
b) Column_value — Give the column value whose data you want to get.
c) File — Give the name of the Excel file which you have created.

Use the ‘Data only’ property if you want to fetch the value of the cell instead of the formula.

All the above-mentioned parameters we will pass at a time of function call through the robot framework.

Read Also:- Robot Framework and Features | Automation Framework

Calling Python Function In Robot Framework to Read Excel Data

# Import OpenPyxlLibrary, ExcelLibrary, and the File in which you have created a function to read data from an excel file.
*** Settings ***
Library OpenPyxlLibrary
Library ExcelLibrary
Library ../Scripts/ExcelHandling.py
*** Test Cases ***

Read Excel File Data

#open excel and read data from excel
${excel_val}= ExcelHandling.Open And Read Excel File Sheet1 1 E:\\Project_Name\\Files\\Test.xlsx
# Here ‘Sheet1’ is the name of the sheet from which I want to read the data, and ‘1’ is the first column whose data I want to get.
# ‘Test.xlsx’ is an excel file for which data I want to read.
FOR ${data} IN ${excel_val} # loop to print the excel sheet value on console
log to console ${data}

END

Let’s save this file with “TestExcel.robot”

Run the above file by using the following command:
> robot TestExcel.robot

Output

FAQs

• How do I open a PDF file in Python?

To open an internally stored PDF file you can use the open() function in Python

• Is a robot framework better than selenium?

We are using selenium with a robot

• Which Python version is best for Robot Framework?

Python 3.6 or newer is best for Robot Framework

• Which language is used in Robot Framework?

Python programming language is used in the Robot Framework

If you are interested in even more articles and information on Robot Framework from us here at Devstringx, then we have a lot to choose from for you.