Forum Discussion

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

Mappe1.xlsx8 KB

NBorovykh

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.

NBorovykh

Moderator

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

tech321
Contributor
6 years ago
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
  SmartBear Alumni (Retired)
  6 years ago
  Hi all,
  
  NBorovykh thanks for looking into this.
  
  Community, maybe can anyone suggest a way to proceed?
  
  Thanks in advance,
TanyaYatskovska
SmartBear Alumni (Retired)
6 years ago
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
  Contributor
  6 years ago
  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
    SmartBear Alumni (Retired)
    6 years ago
    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).