How to read data from Excel sheet in Robot Framework

We all know reading and writing in an excel is a very frequent requirement of Robotic process automation. In this post, we will discuss how you can read data from an excel sheet using the Robot framework. We are using PyCharm as an IDE.

To read data from we will include the following Library.

Library Selenium
Library ExcelLibrary

To add Excel Libray in PyCharm you need to follow the following steps:

File -> Settings -> Project (ProjectName) ->Python Interpreter

Add robotframework-excellibrary-xwfintech (Lastest) instead of robotframework-excellibrary as it is not supported in certain cases. Do Include Selenium and Robot Framework.

Here is the source code:

*** Test Cases ***
Open and Close Browser  
#provide path of Excel file to Open Excel 
Open Excel	 /home/sandeep/PycharmProjects/projectName/DataFile/Excel-file-name.xls
# Reading No of Columns in Excel Sheet
${strColCount} =  Get Column Count  Sheet1
#Printing number of Columns on Console Log
Log To Console  \nCols are => ${strColCount}
# Reading number of Rows in Excel Sheet
Set Test Variable   ${Col_ID}   ${strColCount}
${strRowCount}  Get Row Count   Sheet1
Log To Console  \nCols are => ${strRowCount}

#Retrive Data
FOR    ${rowIndex}     IN RANGE    1    ${strRowCount}
#calling another loop named Inner loop to read the column of a row passing row number as parameter
 	Inner loop     ${rowIndex}
END

*** Keywords ***
Inner loop
    [Arguments]     @{row}    
    FOR     ${colIndex}    IN RANGE   0  ${Col_ID}
	#reading cell data from Sheet 1
        ${data}     Read Cell Data By Coordinates   Sheet1  ${colIndex}     @{row}
        Log to console  ${data}
    END

Read Cell Data By Coordinate is the function which will read the data from the cell. It takes three parameters Sheet Name, Column Index and Row Index.

Hope you like the post, if you have any other queries you can write to us.