Forum Discussion

tech321's avatar
tech321
Contributor
6 years ago

Soapui, Excel, groovy, iteration

Hi, I have this question about iteration through Excel sheet using groovy in Soapui. I have seen many descriptions, like "write Excel Data with poi api in Groovy script" and using groovy excel builde...
  • NBorovykh's avatar
    6 years ago

    Hi tech321 ,

     

    The Excel DataSource test step does not support the described scenario, so you'll need to use a custom Groovy Script. 

     

    For the example that you provided the following code should work:

    import org.apache.poi.ss.usermodel.*
    import org.apache.poi.hssf.usermodel.*
    import org.apache.poi.xssf.usermodel.*
    import org.apache.poi.ss.util.*
    
    def fs = new FileInputStream ("F:/Temp/Mappe1.xlsx")
    
    Workbook wb = WorkbookFactory.create(fs);
    def ws = wb.getSheet("Ark1")
    
    def cellValues = []
    def columnIndex = 0; // read values from the first column
    for (def currRow=0; currRow < ws.getPhysicalNumberOfRows(); currRow++){ 
    	cellValues.add( ws.getRow(currRow).getCell(columnIndex).toString())
    }
    
    def numOfIterationsForUniqueValue = 10;
    cellValues.unique().each(){ 
    	for (def currIterationForUniqueValue=0; currIterationForUniqueValue < numOfIterationsForUniqueValue; currIterationForUniqueValue++){
    	 	log.info("${it}");
    	}	
    }

    The code uses the Apache POI library that is already a part of the ReadyAPI 2.7.0 installation. The only library you will need to add is ooxml-schemas-1.1.jar. You need to place it to the <ReadyAPI-2.7.0>\lib folder and restart ReadyAPI.