Forum Discussion

ebudihar's avatar
ebudihar
Occasional Contributor
7 years ago

excel datasource

dear experts,

I am very new in soapUI. I downloaded 5.4.0-EB and 5.3.0 and I read this documentation (https://www.soapui.org/docs/data-driven-tests/functional-tests.html) and tried the steps how to import an excel file through datasource function.

When I try, I do not see any datasources in my "DataSource TestStep" menu to add it. Can you tell me how to do it?

But when I check in the ReadyAPI, it has.

My question is:

1. is it really there is no datasource function in the menu for soapUI? and it is just in the ReadyAPI only?

2. if there is that funcion, do you mind if you can tell me how? because I am curious, why in the docs has, but in my SoapUI does not have it.

  • nmrao's avatar
    nmrao
    7 years ago
    That step is not part of Open Source edition. And only available via Pro edition.

    However, it is possible if you write a groovy script and you should be able to find some example code snippet if you search on the net.
  • nmrao's avatar
    nmrao
    Champion Level 3
    7 years ago
    That step is not part of Open Source edition. And only available via Pro edition.

    However, it is possible if you write a groovy script and you should be able to find some example code snippet if you search on the net.
  • juampaSoapUi's avatar
    juampaSoapUi
    Regular Visitor
    7 years ago

    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)
	}