Road to data driven testing in SoapUI using groovy script with excel file

SoapUI Pro has a feature to read data from external files like: excel, csv etc. But SoapUI does not provide such feature to read data from excel file. So for reading data from excel file in SoapUI, we need to write some code in groovy script.
I this post I will show you, how to read data from excel file.I am using poi jar files to read data from excel file in groovy, download following jar files and put into SoapUI lib folder.

• poi-3.8-beta5-20111217.jar
• poi-examples-3.8-beta5-20111217.jar
• poi-excelant-3.8-beta5-20111217.jar
• poi-ooxml-3.8-beta5-20111217.jar
• poi-ooxml-schemas-3.8-beta5-20111217.jar
• poi-scratchpad-3.8-beta5-20111217.jar 
• dom4j-1.6.1.jar

In this post, I have created a SoapUI project for “ConversionRate“ API, and created a test case and teststep with name “ConversionRate”. So here I need to run this test for the set of data, where data is in external excel file.

I have created a “ReadXLSFile” groovy step and write below code to read data from “Book1.xlsx” file for the “ConversionRate” API method. Below is excel file data:

To	From
USD	ALL
AFA	DZD
AWG	BSD
BSD	BDT

GroovyScript Code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.*;
import java.io.*;

class ExcelReader {
 
  def readData() {
        def path = "E:\\Automation-WorkArea\\APITest\\Book1.xlsx";
        InputStream inputStream = new FileInputStream(path);
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
                     
        Iterator rowIterator = sheet.rowIterator();
        rowIterator.next()
        Row row;                       
        def rowsData = []
        while(rowIterator.hasNext()) {
             row = rowIterator.next()
             def rowIndex = row.getRowNum()
             def colIndex;
             def rowData = []
             for (Cell cell : row) {
                 colIndex = cell.getColumnIndex()
                  rowData[colIndex] = cell.getRichStringCellValue().getString();
             }                    
             rowsData << rowData
         }
         rowsData
  }
 }

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def myTestCase = context.testCase

ExcelReader excelReader = new ExcelReader();
List rows = excelReader.readData();
def d = []
Iterator i = rows.iterator();
while( i.hasNext()){
         d = i.next();
         myTestCase.setPropertyValue("From", d[0])
         myTestCase.setPropertyValue("To", d[1])       
         testRunner.runTestStepByName( "ConversionRate")
}
         

Description:

• ExcelReader class containing a function “readData” to read data from “Book1.xlsx" file.
• myTestCase.setPropertyValue("From", d[0]) and myTestCase.setPropertyValue("To", d[1])  are  used to set testcase “from” and “To” properties  value.
• testRunner.runTestStepByName( "ConversionRate") this steps is used to run test step “ConversionRate”

So in this way when I run test “ReadXLSFile” read data from xls file and will execute test “ConversionRate” for each setoff data