Excel Library in Robot Framework

The test library, which provides keywords to allow opening, reading, writing, and saving Excel files from Robot Framework, is called an Excel library. When we have a bunch of test cases, and all these test cases required some data, and it does not make any sense that for every test case, we have to write the data inside the test case, we can generalize the test data into an excel file.

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

For example, we have a login functionality; here we can save the username and password Inside an excel file, and whenever we want to log in, we can use the login functionality keyword. And the pre-requisite is that we must have to import the Excel Library into the Test Suite. We can install the Excel Library 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 and enter the name of the library ExcelLibrary and click ok.

importing-excel-library

If the excel library is successfully installed, 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.

Data-Driven Test case

Read Operations Through Excel Library

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

1. Check Cell Type : These keyword checks the value of the cell, whether it is an Integer or String type. To check cell type, it requires a sheet name column and the row. Go back to the ride editor and create a new test case as and I have created one excel sheet with the following details. The column count will starts from 0,1,2...etc..

excel-sheet

Now enter the data inside the Test case ReaExcelDemoThe first keyword is open Excel which requires an excel sheet address path and the complete ReadExcelDemo the test case is as shown below:

readexcel-demo-test-case

Now, select the test case andrun, once the robot framework stats executing the test case, and if you check the log report, you can see that the cell value is a string. Because we had given the value as Ashu

the-cell-value-is-string

2. Get Column Count: This keyword provides specific value in the sheet.

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.

Creating a new scalar variable : Right-click on the ExcelDemo tets suite and select the New Scalar variable and create, as shown below:

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

3. Get Column Values : This 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, you can see the log report as follow

log-report-ofcell-value-count

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

4. Get sheet names : This keyword gives the sheet names, like in any situations that we need to verify the Sheet name first. It requires only a sheet name as a parameter(get sheet names).

testcase-to-get-sheet-name

And the Log report is:

log-report-of-get-sheet-name

5. Get Row count : This keyword gives the Row count.

test-case-for-get-row-count

And the log report is:

log-report-of-get-row-count

6. Read Cell Data By Coordinates : By using the row number and the column number, we can read the cell value. 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

And the log report is:

log-report-of-read-cell-value

7. Read the Cell Data by Name : By using the cell name, we can read the cell data as follow

read-cell-data-by-cell-name

And the Log report is:

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

Miscellaneous Keywords in Selenium Library

Excel Library Write Operation Keywords

All the keywords which are present in the write operation keywords are updated keywords, all of these 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 :

1.Open Excel: 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.

2. Create a new workbook : New Sheet Name (string) The name of the original sheet added to the new 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

3. Put a String Value/a Number value, and also we can put a date to the cell: To 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 value to the sample123 workbook from the test case ExcelTC01.

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

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

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 and save this into new excel workbook Demo2.xls and 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.











Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions