Soapui, Excel, groovy, iteration

[tech321]

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 4^th 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.

[NBorovykh · Accepted solution]

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. 

[tech321]

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. 

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 

[Olga_T]

Hi all,

@NBorovykh thanks for looking into this. 

Community, maybe can anyone suggest a way to proceed? 

Thanks in advance,

[TanyaYatskovska]

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.

[tech321]

Thanks Tanya, 

No, I do not need more colums. My problem is how to tell the code to do the exact thing I want it to, which is this

A0
A1

A2

A0

A1

B0

B1

B2

B0

B1

...... in case the loop = 5, so it keeps looping the A's, one row at a time, and when the loop finish, then it jumps to the B's and do the same, etc. 

[TanyaYatskovska]

Hi @tech321,

I suggest that you debug the code Natalie has posted. It will help you better understand what should be modifying in this sample. I think the idea should be to keep the cell value in the current loop (say, A) in a variable. In each iteration, you will need to check if the current cell is equal to the value of the current loop. If it is, continue the iteration the need number of times. Once you exceed the needed number of iteration, you will need to move to another group (B).