cancel
Showing results for 
Search instead for 
Did you mean: 

Soapui, Excel, groovy, iteration

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

 

 

6 REPLIES 6
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.↓↓↓↓↓
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.↓↓↓↓↓
Contributor

Re: Soapui, Excel, groovy, iteration

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. 

Community Manager

Re: Soapui, Excel, groovy, iteration

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

 

---------
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:
Watch the New Interview
APITestingMistake#2
Top Kudoed Authors