Robot Framework- Working with Excel Library(Part-2)

Life is short..share while you still have knowledge.

We started with installation of Excel Library in Robot Framework , today we would focus on utilizing the keywords present in the Excel Library.
Next is publishing run results into Excel

We can connect on Fb,G+,Twitter to spread more learning.

Scenario:- How we can fetch data from Excel in Robot framework to parameter our test cases.

Solution:- We need to implement ExcelLibrary keywords.

Inbuilt Keywords Used in our Example:-

  • Open Excel
  • Get Column Count
  • Read Cell Data by Coordinates
  • Get Row Count

Download code from GitHub

Code:-

*** Settings ***
Library ExcelLibrary
Library OperatingSystem
Library robot.api.logger

*** Variables ***
${sFileName} ${EXECDIR}\Data\TestSheet.xls # DataSheet

*** Test Cases ***
TC_Fetch Data from Excell
[Documentation] Fetching the data from given Row and Column Name
Open given Excel file ${sFileName}
#Passing SheetName,RowDetails and ColumnName, to fetch data
${sData} Get data from Excel with given column DataSheet TC_02 UserName
Log ${sData}

*** Keywords ***
Open given Excel file
[Arguments] ${sFileName}
#Check that the given Excel Exists
${inputfileStatus} ${msg} Run Keyword And Ignore Error OperatingSystem.File Should Exist ${sFileName}
Run Keyword If "${inputfileStatus}"=="PASS" info ${sFileName} Test data file exist ELSE Fail Cannot locate the given Excel file.
Open Excel ${sFileName}

Get data from Excel with given column
[Arguments] ${sSheetName} ${sTestCaseNo} ${sColumnName}
log ${sColumnName}
${colCount} Get Column Count ${sSheetName}
: FOR ${y} IN RANGE 0 ${colCount}
${header} Read Cell Data By Coordinates ${sSheetName} ${y} 0
#Check if this is the given header
Run Keyword If "${header}"=="${sColumnName}" Set Test Variable ${colNum} ${y}
log ${colNum}
#Get the total rows in the Sheet
${iTotalRows} ExcelLibrary.Get Row Count ${sSheetName}
: FOR ${iRowNo} IN RANGE 1 ${iTotalRows}+1
${TC_Num} Read Cell Data By Coordinates ${sSheetName} 0 ${iRowNo}
#Incase TestCase No is same , fetch the data from same row and given column No
${sSearchedData} Run Keyword If "${sTestCaseNo}"=="${TC_Num}" ExcelLibrary.Read Cell Data By Coordinates ${sSheetName} ${colNum}
... ${iRowNo}
Run Keyword If "${sTestCaseNo}"=="${TC_Num}" Exit For Loop
[Return] ${sSearchedData}


Learn RobotFramework
Selenium Turorials

Robot Framework- Working with Excel Library(Part-1)

In part-1 we would discuss on Installing Excel Library to work with Robot Framework.

True Success is balance. For me, this means Health, Wealth, Happiness.
So I try and get 1% better each week in each of these areas.
Lets learn the robot framework integration with Robot Framework to enhance our skills.

Scenario: – How we can install Excel Library to work with excel files using Robot Framework.
Solution:-
Download:-
Dependencies:-
After extracting all the files we need to install all of them:-
Downloaded files of Excel library

Installation:-
Using command prompt browse to the location where files are located and use the command.

Windows:-
Setup.py install

Installing Excel Library in Robot Framework

Repeat the above process for all the dependencies.

Linux:-

Robot Framework with Excel on UNIX


Verify:-
We can check that we have successfully installed all the individual components by typing python, followed by import “folder Name” in the command window after the installation.

For example:-

ExcelLibrary

Verify Excel library is installed

natsort

Verify Excel library dependencies are installed


Note: – We cannot use .xlsx extensions, due to limited support thus we need to stick to .xls extension(Excel 97-2003).

In our next post we are going to explore more on Excel integration with Robot Framework.