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

10 Replies to “Robot Framework- Working with Excel Library(Part-2)”

  1. Hi ,

    Its really looking cool. can you please share me the code please with excel.
    "Download Link for the Code" link is not working.

    Thanks,
    Pradeep

  2. Hello,

    If possible, please upload the sample excel file used with this example.
    I am getting a couple of errors while executing above in robot framework with RIDE.

    Thanks…

  3. Hello,

    The dowload link for the code on googledrive is broken.
    Kindly fix or mention current link.

    Thanks…

  4. Getting error as "Variable '${colNum}' not found." and "colNum" is coming in pink always. – Noman Ahmed

  5. Installed and setup the script as instructed, but the only issue i too am running into is the ${colNum} doesnt exist.

    If i'm reading the script correctly:
    Run Keyword If "${header}"=="${sColumnName}" Set Test Variable ${colNum} ${y}

    meaning ${colNum} = ${y}?

  6. ${EXECDIR} is default variable in robot framework, which refers to current working directory of robot files.

Leave a Reply

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