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