cancel
Showing results for 
Search instead for 
Did you mean: 

Soapui, Excel, groovy, iteration

Highlighted
Occasional Contributor

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 builder .. etc. But I still can not solve this issue. It might be very simple, but I have problem finding a good, not so complicated algorithm to solve it. Please see the attached excel sheet, which I aslo show below. As you can see, we have 4 rows with A, 4 rows with B and 4 rows with C. I want to iterate through each row group A, B and C, but in a special way. Let us say I have a counter that counts to 10. I want iterate though rows with A, 10 times. I have only 4 rows with A, this means that when I reach the A in the 4th row, then I countinue from the 1st A again, and so on, until the counter reaches 10. In this case it means that I have to iterate/loop 2.5 times through the rows with A to reach 10. Then I jump to rows with B and start in the same way as I did with rows with A. I do not care if you use Sopaui's datasource loop step or just pure groovy in the solution. Please let me know if you have a suggestion.

image.png

 

 

4 REPLIES 4
Moderator

Re: Soapui, Excel, groovy, iteration

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. 

 

Natalie
Customer Care Team

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
Occasional Contributor

Re: Soapui, Excel, groovy, iteration

Thank you Natalie

It is close, but not the solution I want. Sorry, probably I am not so clear in describing the case. Here is the result from the code you send, when I change the iteration to 5 instead of 10. 

  

image.png

But what I expect/want is  

Wed Jun 19 13:51:35 CEST 2019: INFO: A0
Wed Jun 19 13:51:35 CEST 2019: INFO: A1
Wed Jun 19 13:51:35 CEST 2019: INFO: A2
Wed Jun 19 13:51:35 CEST 2019: INFO: A0
Wed Jun 19 13:51:35 CEST 2019: INFO: A1
Wed Jun 19 13:51:35 CEST 2019: INFO: B0
Wed Jun 19 13:51:35 CEST 2019: INFO: B1
Wed Jun 19 13:51:35 CEST 2019: INFO: B2
Wed Jun 19 13:51:35 CEST 2019: INFO: B0
Wed Jun 19 13:51:35 CEST 2019: INFO: B1
Wed Jun 19 13:51:35 CEST 2019: INFO: C0
Wed Jun 19 13:51:35 CEST 2019: INFO: C1
Wed Jun 19 13:51:35 CEST 2019: INFO: C2
Wed Jun 19 13:51:35 CEST 2019: INFO: C0
Wed Jun 19 13:51:35 CEST 2019: INFO: C1

 

As you can se, I want to iterate all the A group first, but in the same time change the row. 

 

and here is how my excel sheet looks 

image.png

Moderator

Re: Soapui, Excel, groovy, iteration

Hi all,

@NBorovykh thanks for looking into this. 

 

Community, maybe can anyone suggest a way to proceed? 

Thanks in advance,

 


Olga Terentieva
SmartBear Assistant Community Manager

Community Manager

Re: Soapui, Excel, groovy, iteration

Hi @tech321,

 

I think you need to play with this link of the code Natalie provided:

cellValues.add( ws.getRow(currRow).getCell(columnIndex).toString())

If you want to use a different Index, I suppose that you will need to increase the columnIndex in the loop.

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
New Here?
Join us and watch the welcome video:
Top Kudoed Authors