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.
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
Now, go to your test suite and click
Library under import
Enter the name of the library
ExcelLibrary and click ok.
If the excel library is imported successfully, it will show like below
Create a new test suite
And create a new test case as shown below
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.
Some of the read operations which we are doing through an excel library :
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.
Now go back to the ride editor and create a new test case as ReadExcelDemo
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:
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
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:
And the updated ReadExcelDemo tets case is as shown below:
Now, select the test case and Run, you can see the
cell value count as shown
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
Now, run the below test case
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:
By creating another one
new scalar variable you can write the for loop so that we will get each value individually as follow:
And the log report is:
Get Sheet Names keyword Returns the names of all the worksheets in the current workbook. It requires only a sheet name as a parameter.
And the Log report is:
Get Row Count keyword Returns the specific number of rows of the sheet name specified.
And the log report is:
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.
And the log report is:
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.
And the Log report is:
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 :
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
As it will not open the excel file in the log report, but it will be open behind seen.
Create Excel Workbook keyword creates a new excel workbook.
The test case is:
If you open the location where your test case has been located, the robot framework will create a new workbook with the name
If you open that workbook, you can see the excel sheet created by the name
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
Go back to the Ride editor and try to put a string to the
sample123 workbook from the test case ExcelTC01.
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
Demo1.xls file and enter some values into the excel and save it
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.
If you open the
Demo2.xls the file you can see that, the cell value Rama has been updated to NGen
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.
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.