Get xls data in loop in groovy script

try following:

Download jxl jar from http://www.java2s.com/Code/Jar/j/Downloadjxljar.htm

Place copy of this jar file in following folders of your soapui installation:

• SoapUI-5.2.1\lib
• SoapUI-5.2.1\Bin\ext

Create a sample excel sheet at any location (in this case I am placing my excel sheet in C drive folder name Excel i.e C:\\Excel) with any name (in this case I am using Temp.xls)

Let’s say your excel sheet has following values

@ A1 value1 is “SOAPUI1”

@B1 value2 is “Smartbear1”

@C1 value3 is “20161”

@ A2 value1 is “SOAPUI2”

@B2 value2 is “Smartbear2”

@C2 value3 is “20162”

And you would like to load these values in your soapui testsuite properties.

Create three properties in testsuite properties

• “RowSelector”
• “Start” //enter the start row value of your excel sheet in this case it will be 0
• “End” //enter the end row value of your excel sheet in this case it will be 2

Create following groovy test step (test step name GroovySetup)at the beginning of all test steps

import jxl.*

import jxl.write.*

def value1

def value2

def value3

//pull value from test suite properties

def RowSelector = testRunner.testCase.testSuite.getPropertyValue( "RowSelector" )

//Read Excel

Workbook workbook = Workbook.getWorkbook(new File("C:\\Excel\\Temp.xls"))

//creating a copy to save results in

WritableWorkbook copy = Workbook.createWorkbook(new File("C:\\Excel \\Results\\Output.xls"),workbook)

File datasheet = new File("C:\\Program Files\\SmartBear\\SoapUI-5.2.1\\bin\\ext\\dataSheets\\ResultTemp.xls")

if (datasheet.exists()){

   log.info("Exist")

}

else {

log.info("Blank")

}

// loading Values from Excel (A1) to TestSuite properties

                                        value1 = wb.getSheet(0).getCell(0, RowSelector).getContents() //cell A1

                                        testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue1", value1 )

                                        log.info("Value1 Is: " + value1)

// loading Values from Excel (B1) to TestSuite properties

                                        Value2 = wb.getSheet(0).getCell(1, RowSelector).getContents() //Cell B1

                                        testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue2", value2 )

                                        log.info("Value2 Is: " + value2)

// loading Values from Excel (C1) to TestSuite properties

                                        Value3 = wb.getSheet(0).getCell(2, RowSelector).getContents() //Cell C1

                                        testRunner.testCase.testSuite.setPropertyValue( "TestSuitevalue3", value3 )

                                        log.info("Value3 Is: " + value3)

                                        wb.close()

//End Groovy step

---------------------------------------------------------------------------------------------------------------------

This groovy will load value from excel to your testsuite property.

Now you can use these values as they are now available in your testsuite properties. If you would like to apply loop then at the end of the testcase add another groove script apply a loop counter code

It should be something like

 -----------------------------------------------------------------------------------------------------------------------

//Start Groovy step (say name GroovyLoop)

def RowSelector = testRunner.testCase.testSuite.getPropertyValue( " RowSelector " )

def Start = testRunner.testCase.testSuite.getPropertyValue( " Start " )

def End = testRunner.testCase.testSuite.getPropertyValue( "End" )

def start = ‘0’ //Loop start value u can use it for defining row or coloumn

def end = ‘0’ //Loop end value u can use it for defining row or coloumn

//setting up loop

For (RowSelector = Start; RowSelector<=End; RowSelector++){

testRunner.testCase.testSuite.setPropertyValue( " RowSelector ", RowSelector)

//Reference https://www.soapui.org/Functional-Testing/controlling-flow.html

testRunner.runTestStepByName( "GroovySetup") //calling first groovy step where we loaded excel and etc

//if you have other test steps and you would like them to be part of this loop then place them too like sample below.

//testRunner.runTestStepByName( "TestStepName")

}