Excel Library in Robot Framework

Excel Library is a test library that includes keywords for opening, reading, writing and saving Excel files from the Robot Framework. If we have a bunch of test cases and all these test cases need some data, and it makes no sense for us to write the data inside each test case, hence we can generalize the test data into a test case by using Excel Library.

The robot framework automation tool will read the data from the excel file, and it will feed the data to the test case.

For example, we have a Login commonkeyword, The address of the excel file can be stored by generating a common user keyword containing the login details (username and password) of the ngendigital / practice domain. Whenever we want to sign on to the ngendigital website, we can use this keyword in any test case instead of writing it again and again.

The pre-requisite is that we must have to import the Excel Library into the Test Suite

Before this, we have to install the Excel Library into the robot framework by using the pip command as pip install robotframework-excellibrary.

Open the command prompt and enter the above command and it will install the excel library

successfully-installed-excel-library

Now, go to your test suite and click Library under import

select-library-from-ride-editor

Enter the name of the library ExcelLibrary and click ok.

importing-excel-library

If the excel library is imported successfully, it will show like below

successfully-imported-excel-library

Create a new test suite Excel Demo

excel-demo-suite

And create a new test case as shown below ExcelTC01

creating-testc-case-tc01

Now, import the excel library into the test suite, and you can see several keywords that are associated with the excel library inside the test case.

excel-library-keywords



Data Driven Test case

Read Operations Through Excel Library in Robot Framework

Some of the read operations which we are doing through an excel library :

Check Cell Type

The Check Cell Type keyword checks the type of the cell, whether it is an Integer or String type. To check cell type, it requires a sheet name, column number, and the row number. Create an excel file with the following details, the excel filename is exceldemo.xls and the sheet name is Sheet1.

excel-file-with-sheet-name-and-file-name

Now go back to the ride editor and create a new test case as ReadExcelDemo

read-excel-test-case

And write the test case as shown below. The first keyword is Open Excel which requires an argument, Filename(which is an excel sheet address) and the second keyword is Check Cell Type which requires three arguments respectively, sheet name, column count and the row count. The complete ReadExcelDemo test case is as shown below:

readexcel-demo-test-case

Now, select the test case and run, once the robot framework starts executing the test case, it will verify the cell type and if you check the log report, you can see that the cell type is a string type because We had given the string value as Ashu

Get Column Count

The Get Column Count keyword Returns the specific number of columns of the sheet name specified. Open the ReadExcelDemo test case and add the keyword Get Column Count that requires only one parameter that is a sheet name and hence, by creating a new scalar variable, the robot framework will give the count of the cell value.

To create a new scalar variable, Right-click on the ExcelDemo tets suite and select the new scalar variable and create a scalar variable $(Count1) as shown below:

selecting-new-scalar-variable

new-scalar-variable

And the updated ReadExcelDemo tets case is as shown below:

cell-count-read-demo-excel

Now, select the test case and Run, you can see the cell value count as shown

log-report-of-cell-count-1

Get Column Values

The Cell Column Values keyword gives the value of the specific column. It requires a sheet name and column number. Go to the test case ReadExcelDemo And add the keyword Get Column Value and its parameters. Before running the test case, create a new list variable as @{Count}

list-variable-to-get-column-value

Now, run the below test case

test-case-for-get-column-value

Once you run the test case, the robot framework will display the values of the given column, you can see the log report as follow:

log-report-ofcell-value-count-1

By creating another one new scalar variable you can write the for loop so that we will get each value individually as follow:

for-loop-in-read-excel-demo

And the log report is:

each-cell-value-as-individually

Get Sheet Names

The Get Sheet Names keyword Returns the names of all the worksheets in the current workbook. It requires only a sheet name as a parameter.

testcase-to-get-sheet-name-1

And the Log report is:

log-report-of-get-sheet-name-1

Get Row Count

The Get Row Count keyword Returns the specific number of rows of the sheet name specified.

test-case-for-get-row-count-1

And the log report is:

log-report-of-get-row-count-1

Read Cell Data By Coordinates

Read Cell Data By Coordinates keyword uses the column and row to return the data from that cell. For this, we require the Sheet name, column number, and the row number as a parameter.

test-case-for-read-cll-data-by-co-ordinates-1

And the log report is:

log-report-of-read-cell-value-1

Read Cell Data by Name

Read Cell Data by Name keyword uses the cell name to return the data from that cell. The parameters are the sheet name and the cell name.

To find the Cell Name, Go to your excel file on the top of the excel file you will find the column names start from A, B, C, D, etc... and on the very left you will find the row number start from 1, 2, 3,4, etc.. and if you click on any cell in the excel sheet, then it will show you the cell name on the top.

finding-the-cell-name

read-cell-data-by-cell-name-1

And the Log report is:

log-report-of-read-cell-data-by-cell-name-1

Miscellaneous Keywords in Selenium Library

Excel Library Write Operation Keywords in Robot Framework

All the keywords which are present in the write operation are updated keywords, when we are trying to save to excel file, the robot framework excel library doesn't save the excel file, it creates a copy of new excel file.

Let us discuss some of the write keywords as follow :

Open Excel

The Open Excel keyword Opens the Excel file from the path provided in the file name parameter. If the boolean useTempDir is set to true, depending on the operating system of the computer running the test, the file will be opened in the Temp directory if the operating system is Windows or tmp directory if it is not.

Usually, we will never set useTempDir to True. By default, it will always be set to false only. And the parameters are Filename, which provides the full length of the address path of the excel file. The Robot framework excel library only works with the extension of xls and not with the xlsx.

Now, open Ride editor and the ExcelTC01 test case and write the test case as below

open-excel-file

As it will not open the excel file in the log report, but it will be open behind seen.

Create Excel Workbook

The Create Excel Workbook keyword creates a new excel workbook.

The test case is:

test-case-for-creating-excel-workbook

If you open the location where your test case has been located, the robot framework will create a new workbook with the name Demo1.xls

demo1-xls-workbook

If you open that workbook, you can see the excel sheet created by the name sample123.

demo-xls-workbook-with-sheet-name-as-sample123

Put a String / Number/Date to Cell

Put a String/Number/Date to cell keyword add these values to the cell, the cell must initially have the benefits. Otherwise, it will show an out of index error.

Go back to the Ride editor and try to put a string to the sample123 workbook from the test case ExcelTC01.

test-case-for-put-string-to-cell-sample123

We have created the new excel sheet. Demo2.xls and try to put the string NGen into the cell, the robotframework has thrown an error as follows

log-report-of-put-string-to-cell-1

The reason for the error is that the cell is not having any initial value to update it. So, enter some values to the cell and then try to update it.

Open Demo1.xls file and enter some values into the excel and save it

entering-random-values-to-the-excel

Now try to update ram as NGen got o ride editor and enter the keyword as follow in the ExcelTC01 save this into new excel workbook Demo2.xls and run the test case.

test-case-for-put-string-to-cell

If you open the Demo2.xls the file you can see that, the cell value Rama has been updated to NGen

updated-cell-value-dmeo2-xls

In the same way, we can update the number to the cell also and the date in the format dd.mm.yy. With the correct row and the column number.

The Robotframwork excel library won't support for Python 3.x versions(tested); it will help for 2.x versions only.

Problem Faced :

When I try to add an excel sheet to the existing excel file and when I run the tets case am getting permission denied error. even I have saved the xlsx file in the same folder where I have saved the robot framework project only.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions
  • Gab
    Hello, i tried to save a excel, but always i had the same error, AttributeError: 'NoneType' object has no attribute 'save'	
    Reply
  • Hima
    Hi,
    Im unable to use put string to cell, it is not replacing my string to the new string, i have tried several times and multiple techniques.
    This is my ExcelLibrary.py file for put string to cell:
        def put_string_to_cell(self, sheetname, column, row, value):
            """
            Using the sheet name the value of the indicated cell is set to be the string given in the parameter.
    
            Arguments:
                    |  Sheet Name (string) | The selected sheet that the cell will be modified from.                                           |
                    |  Column (int)        | The column integer value that will be used to modify the cell.                                    |
                    |  Row (int)           | The row integer value that will be used to modify the cell.                                       |
                    |  Value (string)      | The string value that will be added to the specified sheetname at the specified column and row.   |
            Example:
    
            | *Keywords*           |  *Parameters*                                                           |
            | Open Excel           |  C:\Python27\ExcelRobotTest\ExcelRobotTest.xls  |     |     |        |
            | Put String To Cell   |  TestSheet1                                        |  0  |  0  |  Hello |
    
            """
            if self.wb:
                my_sheet_index = self.sheetNames.index(sheetname)
                if not self.tb:
                    self.wb.sheets()
                    self.tb = copy(self.wb)
            if self.tb:
                plain = easyxf('')
                self.tb.get_sheet(my_sheet_index).write(int(row), int(column), value, plain)
    Reply
  • Subscribe to See Videos

    Subscribe to my Youtube channel for new videos : Subscribe Now