excel datasource

You can put this code in the TearDown script of the TestSuite. it will execute every TestCase that has a property called Execute and the value is Yes. Then it will execute each test case with a tes of data that is contained in an excel file. The excel file will have a sheet per testcase, sheet name and test case name should be the same (to be able to identify it) 

import jxl.*
import jxl.write.*

def excelfile = testRunner.testCase.testSuite.project.getPropertyValue( "InputDataExcel" )
def workbook = Workbook.getWorkbook(new File(excelfile.toString()))


def properties = new com.eviware.soapui.support.types.StringToObjectMap()
def async = false
def my_TestSuite = runner.testSuite
log.info(my_TestSuite.name)
log.info(my_TestSuite.getProperties())
int rows=0; 
int cols=0; 
def End;
def sheet;




for (my_TestCase in my_TestSuite.getTestCaseList())
 {
    
    if (my_TestCase.getPropertyValue("Execute") == "Yes"){
    	     log.info(my_TestCase.getName())
    		sheet = workbook.getSheet(my_TestCase.getName())
    		rows=sheet.getRows(); 
    		cols=sheet.getColumns()-1; 
    		my_TestSuite.setPropertyValue( "End", rows.toString())
    		my_TestSuite.setPropertyValue( "RowSelector","1")   
    		my_TestSuite.setPropertyValue( "Cols",cols.toString())
	 
    		End=rows
    		log.info ("TotalRuns:" + End)
    
 		   def RowSelector = my_TestSuite.getPropertyValue( "RowSelector" ).toInteger()
    
    		while (RowSelector<End){
			my_TestCase.run(  properties, async )
			RowSelector=RowSelector+1
			my_TestSuite.setPropertyValue( "RowSelector",RowSelector.toString())		
			log.info ("TestRun: " + RowSelector)
    		}	
     	
    }

    my_TestSuite.setPropertyValue( "RowSelector","1")	
    my_TestSuite.setPropertyValue( "End","0")		
 }

Following code could be inserted in the same script or in the setup script of each test case.

import jxl.*
import jxl.write.*

 
//pull value from test suite properties 
def RowSelector = testRunner.testCase.testSuite.getPropertyValue( "RowSelector" ).toInteger()
def Cols = testRunner.testCase.testSuite.getPropertyValue( "Cols" ).toInteger()
log.info ("column "+ Cols + "row" + RowSelector)
log.info ("Columna: " + Cols.toString())

//Read Excel
def excelfile = testRunner.testCase.testSuite.project.getPropertyValue( "InputDataExcel" )
Workbook wb = Workbook.getWorkbook(new File(excelfile.toString()))

//creating a copy to save results in
WritableWorkbook copy = Workbook.createWorkbook(new File("C:\\Apps\\Results\\Output.xls"),wb)
File datasheet = new File("C:\\Program Files\\SmartBear\\SoapUI-5.2.1\\bin\\ext\\dataSheets\\ResultTemp.xls")

def key
def value

for (i=0; i<Cols; i++){
	log.info("i:" + i)
 	key=wb.getSheet(testRunner.testCase.getName()).getCell(i, 0).getContents()
 	value=wb.getSheet(testRunner.testCase.getName()).getCell(i, RowSelector).getContents()
 	testRunner.testCase.setPropertyValue(key.toString(), value.toString())
	log.info("Key Is: " + key + "value is:" + value);
	log.info("column "+ Cols + "row" + RowSelector)
	}