Openscript test data parameterization using Excel

valIn one of our previous articles “Openscript test data paramterization with databanks“, we learnt to modify a script, so that it can iterate for a given number of test data records. The databank used was of type CSV Files. Now in this article, we will look at Openscript test data parameterization using Excel files. As you must be aware by now that, Openscript is a plugin, which was built on top of Eclipse IDE. Openscript scripting is done in Java programming language, which brings in quite a good number of possibilities.

Openscript test data parameterization using Excel.

You need to perform the following steps:

1. Identify a list of methods you want to develop and use them to interact with Excel files.
2. Download Apache POI Libraries.
3. Create a Function Library in Openscript to interact with excel files.
4. Attach all the required POI libraries as assets to the function library.
5. Write code for the identified methods.
6. Parameterize your scripts to iterate for different sets of test data.

Step 1: Identify a list of methods

S.No	Method Name	Purpose
1	openExcelWorkBook	A Method to load an excel file(xls / xlsx files) in to run time program.
2	openSheet	A Method to load a specific sheet from a given excel file.
3	loadColumnsNames	A Method to load the column names for the opened sheet. These column names are required as we can use them as a reference and value from a specific row.
4	getRowCount	A Method to get the test data records.
5	getTestDataRow	A Method to set the program at a specific row in a given sheet.
6	getValue	A Method to retrieve the value for a specific row and for a specific column.
7	closeExcelFile	A Method to close the opened Excel Workbook, it helps us to avoid locking of excel file through the program.

Step 2: Download Apache POI Libraries

Open the apache poi v4.0 library link and click on the first link, as highlighted in a brown box in the below image.  When we wrote this article the latest version of Apache POI Library was 4.0. 

Once you have downloaded the zip file, unzip the same and keep in a location where you can refer it back.

Step 3: Create a function library in Openscript

Refer to the article “How to create function library in OATS” and create a function library with the name “Excel”. In this newly created function library script, we will write code for all the identified methods.

Step 4: Attach POI Libraries to interact with Excel files

Now to the newly created function library perform the following steps:

1. When working with these POI libraries of version 4.0, we observed that there was some issue in packaging by Apache, they missed some important “Jars”, download them from the following “Commons Compress” link, click on the link highlighted with brown box in the following image, unzip it at the same location where we unzipped the poi libraries in step 2.
2. Open the Function Library “Excel”.
3. Click on the “Assets” tab and select “Jars” option. ( follow: add jar files as assets in OATS )
4. Click on the “Add” button on the right side and select the following list of libraries from the unzipped folder( refer to Step 2)
   • poi
   • poi-ooxml
   • poi-ooxml-schemas
   • commons-collections
   • xmlbeans
5. In addition to above, add “commons-compress-1.18.jar” Jar to Assets of the function library. The one which was downloaded separately from apache poi jars.

Now we are all set with required Jar files. We need to know start writing the code for the identified methods.

This is how it looks:Note: When adding the jar files as assets, it may not allow special characters like “.” , so please make sure the alias names are updated, only then we can successfully add the Jar assets.

Step 5: Write code for identified methods

In the following code you would see some of the variables used, understand that those are variables declared at class level, the below code only shows you the code specific to each function, I would also include full copy of the script file, and in addition will attach a function library script’s zip file for you to use it wherever you wish to. Also an important note for the readers, the code written hear does not have all the best practices to be followed, this is an attempt to explain more about the interaction with Excel file and rather about the best practices. For any best practices, you can refer to following article: 24 best practices for OATS/OFT

Mehtod #1: openExcelWorkBook

Mehtod #2: openSheet

Mehtod #3: loadColumnsNames

Mehtod #4: getRowCount

Mehtod #5: getTestDataRow

when we try to get values from a specific row, we identified we might need one more Method, namely: “loadCellValues”, please find below the code for the same:

Mehtod #6: getValue

Mehtod #7: closeExcelFile

Complete Script code:

Step 6: Use the function library and iterate through multiple sets of test data records

1. Create any functional automation script in Openscript and give it a name, say “ExcelTestDataScript”
2. Attach the above-created function library as a script asset to “ExcelTestDataScript”, as we can see in below image
3. Create any excel and give some sample test data, say Username, Password, Email, Age, Address. Fill it with data for maybe 2 to 3 records.
4. In the run method of “ExcelTestDataScript” script, use the following code to iterate through the excel file.

You can modify this script to your needs so that the data is actually set into the UI components of the application under test.

As mentioned earlier please find below the files that you can download and use them for your purpose:

1. “Excel” Function Library, unzip and open in your Openscript IDE.
2. Sample “xlsx” file: Registrations“, which was used to store the set of test data records.
3. Actual script ExcelTestDataScript that called the methods developed in Excel Function Library