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.
