Read Excel using Python in Robot Framework | Devstringx Technologies

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

Task:
Read Employee Name from the excel file whose screenshot is shared as below:

Create a function to read data from Excel File using Python

First Install Openpyxl library in your system as per the steps mentioned below:
1. Open command prompt.

2. Write “pip install robotframework-openpyxllib” command to install openpyxl library.

Now create a python file. You can give any name to your file and save it with .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 are stated below:
a) sheetname — You can pass the sheet name to read the data from particular sheet by specifying the sheet name.
b) column_value — Give column value whose data you want to get.
c) file — Give name of excel file which you have created.

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

All above mentioned parameters we will pass at a time of function call through robot 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 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, ‘1’ is the first column whose data I want to get.
# ‘Test.xlsx’ is an excel file 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

.

Originally published at https://www.devstringx.com on Aug 17, 2021

--

--

--

Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Auto-configuration in SpringBoot

4 Python Modules to make a GUI

Clearing the Dead Letter Queue on an Azure Service Bus Queue

Comprehensive Notes For Java 8 Features Every Developer Must Have

API: What It Is and How It Works

Final Submission 5/4/21

Seven Ways Your CTO Thinks About Software Development

4 types of scissors of different sizes.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Devstringx Technologies

Devstringx Technologies

Devstringx Technologies is highly recommended IT company for custom software development, mobile app development and automation testing services

More from Medium

Simply Basic Python — part 1

Formatting Python — Why and How !

Web Automation with Selenium Using Python

Introduction to SQL with Python’s sqlite3 Module