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

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

public void openExcelWorkBook(String fileName) throws Exception
{
    //Create an input stream for the given excel file name.
    InputStream is = new FileInputStream(new File(fileName));
    //XSSFWorkbook takes input stream to read xlsx file. 
    wb = new XSSFWorkbook(is);
    // api to get number of sheets present in this excel file.
    sheetCount = wb.getNumberOfSheets();
    System.out.println("Number of sheets:" + sheetCount);
}

Mehtod #2: openSheet

public void openSheet(String sheetName) throws Exception
{
    //assign the given sheet name to the common sheet object
    // this sheet object is used in other methods. 
    sheet = wb.getSheet(sheetName);
    //As column names will be different for each sheet, we need to load them.
    loadColumnsNames();
}

Mehtod #3: loadColumnsNames

public void loadColumnsNames() throws Exception
{
    // below hash map stores column name as key and column number as value
    cols = new HashMap<String,Integer>();
    //below hash map stores column number as index and column name as key
    colIndexes = new HashMap<Integer,String>();

    // get the first from the earlier instantiated sheet.
    row = sheet.getRow(0);
    // get the number of columns in the sheet to iterate and get the list of all column 
    //names
    colCount = row.getLastCellNum();
    for(int i=0;i<colCount;i++)
    {
        cell = row.getCell(i);
        CellType temp = cell.getCellType();
        String colName = "";
        if(temp == CellType.STRING)
        {
            cols.put(cell.getStringCellValue(), i);
            colIndexes.put(i, cell.getStringCellValue());
            colName = cell.getStringCellValue();
        }
        else if(temp == CellType.NUMERIC)
        {
            cols.put(""+cell.getNumericCellValue(),i);
            colIndexes.put(i, ""+cell.getNumericCellValue());
        }
        else
        {
            System.out.println("Encountered non string based column name.");
        }
        System.out.println("Column ["+(i+1)+"]:" + colName);
    }
}

Mehtod #4: getRowCount

public int getRowCount() throws Exception
{
    rowCount = sheet.getLastRowNum();
    return rowCount;
}

Mehtod #5: getTestDataRow

public void getTestDataRow(int rowNum) throws Exception
{
    //get the row from which you want to retrieve the values.
    row = sheet.getRow(rowNum);
    
    //below hash map holds column names as keys and and cell values for that row
    // as values
    data = new HashMap<String,String> ();
    // dataa for each row in a sheet is different , so we call it whenever
    // a new row is accessed.
    loadCellValues(); 
}

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:

public void loadCellValues() throws Exception
{
    for(int i=0;i<colCount;i++)
    {
        cell = row.getCell(i);
        CellType temp = cell.getCellType();
        String colName = "";
        if(temp == CellType.STRING)
        {
            data.put(colIndexes.get(i),cell.getStringCellValue());
            colName = cell.getStringCellValue();
        }
        else if(temp == CellType.NUMERIC)
        {
            data.put(colIndexes.get(i),""+cell.getNumericCellValue());
        }
        else
        {
            System.out.println("Encountered non string based column name.");
        }
    } 
}

Mehtod #6: getValue

public String getValue(String colName) throws Exception
{
    return data.get(colName);
}

Mehtod #7: closeExcelFile

public void closeExcelFile() throws Exception
{
    wb.close();
}

Complete Script code:

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;

import oracle.oats.scripting.modules.basic.api.IteratingVUserScript;
import oracle.oats.scripting.modules.basic.api.ScriptService;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class script extends IteratingVUserScript {
@ScriptService oracle.oats.scripting.modules.utilities.api.UtilitiesService utilities;
@ScriptService oracle.oats.scripting.modules.browser.api.BrowserService browser;
@ScriptService oracle.oats.scripting.modules.functionalTest.api.FunctionalTestService ft;
@ScriptService oracle.oats.scripting.modules.webdom.api.WebDomService web;

XSSFWorkbook wb;
XSSFSheet sheet;
XSSFRow row;
XSSFCell cell;
int colCount = 0, rowCount = 0;

int sheetCount = 0;
HashMap<String,Integer> cols;
HashMap<Integer,String> colIndexes;
HashMap<String,String> data;
String [] colNames;
public void initialize() throws Exception {
}

/**
* Add code to be executed each iteration for this virtual user.
*/
public void run() throws Exception {

}

public void openExcelWorkBook(String fileName) throws Exception
{
InputStream is = new FileInputStream(new File(fileName));
wb = new XSSFWorkbook(is);
sheetCount = wb.getNumberOfSheets();
System.out.println("Number of sheets:" + sheetCount);
}

public void closeExcelFile() throws Exception
{
wb.close();
}

public void openSheet(String sheetName) throws Exception
{
sheet = wb.getSheet(sheetName);
loadColumnsNames();
}

public int getRowCount() throws Exception
{
rowCount = sheet.getLastRowNum();
return rowCount;
}
public void getTestDataRow(int rowNum) throws Exception
{
row = sheet.getRow(rowNum);
data = new HashMap<String,String> ();
loadCellValues(); 
}

public void loadCellValues() throws Exception
{
for(int i=0;i<colCount;i++)
{
cell = row.getCell(i);
CellType temp = cell.getCellType();
String colName = "";
if(temp == CellType.STRING)
{
data.put(colIndexes.get(i),cell.getStringCellValue());
colName = cell.getStringCellValue();
}
else if(temp == CellType.NUMERIC)
{
data.put(colIndexes.get(i),""+cell.getNumericCellValue());
}
else
{
System.out.println("Encountered non string based column name.");
}
} 
}
public void loadColumnsNames() throws Exception
{
cols = new HashMap<String,Integer>();
colIndexes = new HashMap<Integer,String>();

row = sheet.getRow(0);
colCount = row.getLastCellNum();
for(int i=0;i<colCount;i++)
{
cell = row.getCell(i);
CellType temp = cell.getCellType();
String colName = "";
if(temp == CellType.STRING)
{
cols.put(cell.getStringCellValue(), i);
colIndexes.put(i, cell.getStringCellValue());
colName = cell.getStringCellValue();
}
else if(temp == CellType.NUMERIC)
{
cols.put(""+cell.getNumericCellValue(),i);
colIndexes.put(i, ""+cell.getNumericCellValue());
}
else
{
System.out.println("Encountered non string based column name.");
}
System.out.println("Column ["+(i+1)+"]:" + colName);
}
}

public String getValue(String colName) throws Exception
{
return data.get(colName);
}

public void finish() throws Exception {
}
}

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.

// excel in below Method is an alias name that was given when we added function library Excel to "ExcelTestDataScript" script
public void run() throws Exception {
    excel.openExcelWorkBook("<Give path to the excel file which you want to iterate>");
    excel.openSheet("Sheet1"); // assuming you have test data filled in sheet 'Sheet1'
    //iterate through all rows and print the values of each column for the given row
    for(int i=1;i<=excel.getRowCount();i++)
    {
        excel.getTestDataRow(i);
        System.out.println("Username value in row ["+(i)+"]:" + excel.getValue("Username"));
        System.out.println("Password value in row ["+(i)+"]:" + excel.getValue("Password"));
        System.out.println("Age value in row ["+(i)+"]:" + excel.getValue("Age"));
        System.out.println("Address value in row ["+(i)+"]:" + excel.getValue("Address"));
    }
    excel.closeExcelFile();
}

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