SOAP UI - Read Excel data using Groovy Code

How to read the data in excel using Groovy script in SOAP UI Free Version tool

To Read the data from excel sheet we need to download the "jxl-2.6.jar", any latest version can work
and place the jar in the location where SOAP UI in "ext" folder under "bin" (.\\SmartBear\SoapUI-5.5.0\bin\ext)

Groovy code for reading the Excel data - "save the excel file as *.xls file format"

// IMPORT THE LIBRARIES WE NEED

import com.eviware.soapui.support.XmlHolder
import jxl.*
import jxl.write.*

// DECLARE THE VARIABLES

def myTestCase = context.testCase //myTestCase contains the test case

def counter,next,previous,size //Variables used to handle the loop and to move inside the file
def ProjectPath = new com.eviware.soapui.support.GroovyUtils(context).projectPath+"/OnboardingsInput.xls" //(Give the required file name - Sample.xls)

log.info(ProjectPath)
Workbook workbook1 = Workbook.getWorkbook(new File(ProjectPath)) //file containing the data

Sheet sheet1 = workbook1.getSheet("ProfileController") // ( WorkBook -  Sheet name should be specified)

size= sheet1.getRows().toInteger() //get the number of rows, each row is a data set

propTestStep = myTestCase.getTestStepByName("ExcelProperties") // (Give the Name as Created for the Properties file - created in the TestSuite(TS) by Right Click on TS and Select the Properties file)

propTestStep.setPropertyValue("Total", size.toString())

counter = propTestStep.getPropertyValue("Count").toString() //counter variable contains iteration number

counter = counter.toInteger()

log.info(counter)


// OBTAINING THE DATA YOU NEED


// Code to read an single data

Cell m1 = sheet1.getCell(12,1)
CandidateID = m1.getContents()
propTestStep.setPropertyValue("CandidateID", CandidateID) //the value is saved in the property


//To get Complete  column values

for(i=1;i<size;i++)
{
          def G=sheet1.getCell(6,i).getContents()
          propTestStep.setPropertyValue("ResponseCode"+i,G)
}

//Decide if the test has to be run again or not

if (counter == size-1)
{
propTestStep.setPropertyValue("StopVal", "T")

log.info "Setting the StopVal property now..."
}

else if (counter==0)
{
def runner = new com.eviware.soapui.impl.wsdl.testcase.WsdlTestCaseRunner(testRunner.testCase, null)
propTestStep.setPropertyValue("StopVal", "F")
}

else
{
propTestStep.setPropertyValue("StopVal", "F")
}


// To read and access the Values , create an "Excel Property" file in the Test Suite and create the Key and values of 3 variables

1. Count - 1
2. StopVal - F
3. Total - once executed automatically the value will be generated.