Forum Discussion

tech321's avatar
tech321
Contributor
5 years ago
Solved

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. 

     

6 Replies

  • 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's avatar
      tech321
      Contributor

      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's avatar
        Olga_T
        SmartBear Alumni (Retired)

        Hi all,

        NBorovykh thanks for looking into this. 

         

        Community, maybe can anyone suggest a way to proceed? 

        Thanks in advance,

         

    • TanyaYatskovska's avatar
      TanyaYatskovska
      SmartBear Alumni (Retired)

      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's avatar
        tech321
        Contributor

        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.