Forum Discussion

Azeddin_Margani's avatar
Azeddin_Margani
Contributor
6 years ago
Solved

Iterate/loop through soapui response data/items then write to excel using Groovy.

Hi, 

 

Can you please assist me - I have a soapui response and i'm trying to read the nodes <e> then write them into excel output. Currently, I'm only getting the first item written into the excel output sheet. I need to able to read and write all of the <e> items into excel in one column but separate rows. 

 

Thanks in Advanced. 

 

 

Soapui Response data:

 

<Response>
   <e>1760cd26-1e00641a-b632e328-7fdc13be-9b6f8934</e>
   <e>28821b78-e7cda408-356c1e37-91ee8a7b-a34464df</e>
   <e>6f123c53-93dc4fd8-25e0cd93-1a6deabd-7e94ba2f</e>
   <e>69d116f5-b1ac13a0-ce2da202-7ae2cc2f-5d9575db</e>
   <e>e6dc3e9b-e8c82ae0-ee620328-c5a7e9a7-6f6aa6fd</e>
   <e>5a2e4216-65973e77-62e6ac02-24c6c714-4542be49</e>
   <e>c70de875-60a29986-b399e446-4fc29400-77e9f016</e>
</Response>

 

 

My intended Groovy code is as below: 

 

import jxl.*;
....
...
//Declare variables
log.info("Service testing satrted")
def reqOperationName="MyGetRequest";
def inputDataFileName="C:/Users/Desktop/responseData.xls"
def inputDataSheetName="Sheet1";

Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
     WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
        WritableSheet sheet1 = copy.getSheet(inputDataSheetName);
try
{
rowcount=sheet1.getRows();
       colcount=sheet1.getColumns();
 for(Row in 1..rowcount-1){
    for (Col in 1..colcount-1){
    }
//Read response Xml
def groovyUtils=new com.eviware.soapui.support.GroovyUtils(context)
def resholder = groovyUtils.getXmlHolder(reqOperationName+"#ResponseAsXml")

// Iterate through item nodes in the response message
for( item in resholder.getNodeValues("//*:e"))
  {
    //log.info "Item : [$item]" 

     resTagValue1=resholder.getNodeValue("//*:e")

    //Write response value in xls
     Label resValue1=new Label (0,Row,resTagValue1);
           sheet1.addCell(resValue1);
   }
    }//Row loop end here
}
catch(Exception e){
 log.info(e)
}
finally{
copy.write();
copy.close();
workbook.close();
}
log.info("  service testing is finshed")

 

 

 

  • Hi, this can be done.

     

    Firstly, I copied your sample data and placed it in a file on the disk. In order to read it and loop through each element I created this code:

     

     

    // Parse response
    def xml = new XmlSlurper().parse("D:\\test.txt")
    
    // Start looping for each item
    def i = 0
    def currentElement = ''
    while (( currentElement = xml.e[i]) != '') {
    	i++
    	log.info currentElement
    }

     

     

    Now what was left is to find a way to write this to an excel sheet.

     

     

    // Create the excel file
    File exlFile = new File("D:/target_file.xls");
    WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );
    
    // Create a sheet in the excel file
    WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
    	
    // Create cell
    Label label = new Label(0, 0, "Test string");
    //Add the created Cells to the sheet
    writableSheet.addCell(label);
    	
    //Write and close the workbook
    writableWorkbook.write();
    writableWorkbook.close();

     

    Finally, combining those 2 above, the full code would look like this:

     

    import java.io.File;
    import java.io.IOException;
    import java.util.Date;
     
    import jxl.*;
    import jxl.write.*;
    import jxl.write.Boolean;
    import jxl.write.Number;
    import jxl.write.biff.RowsExceededException;
    
    // Parse response
    def xml = new XmlSlurper().parse("D:\\test.txt")
    
    try {
    	// Create the excel file
    	File exlFile = new File("D:/target_file.xls");
    	final WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );	
    	// Create a sheet in the excel file
    	final WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
    	
    	// Prepare to loop through the file
    	Label label = new Label(0, 0, "");
    	def i = 0
    	def currentElement = ''
    	// Start looping for each item
    	while (( currentElement = xml.e[i]) != '') {
    	
    		// Create cell with the column index i
    		label = new Label(0, i, currentElement.toString());
    		// Add the created Cells to the sheet
    		writableSheet.addCell(label);
    		
    		i++ // Increment index
    	}
    
    	//Write and close the workbook
    	writableWorkbook.write();
    	writableWorkbook.close();
    
    } catch (IOException e) {
    	e.printStackTrace();
    } catch (RowsExceededException e) {
    	e.printStackTrace();
    } catch (WriteException e) {
    	e.printStackTrace();
    }

     

    LATER EDIT: The free version of SoapUI lacks the jxl library. So in order to use this you have to add the library manually. You can just download the attached .jar file and place it in C:\Program Files (x86)\SmartBear\SoapUI-x\bin\ext and you should be good to go.

  • Lucian's avatar
    Lucian
    6 years ago

    Hi, I didn't quite get if the last post was a question or a statement :smileylol: Anyways.. so if I have a project like:

     

    .. where the xml is taken from a response then the code can stay the same except for the first part:

     

    import java.io.File;
    import java.io.IOException;
    import java.util.Date;
     
    import jxl.*;
    import jxl.write.*;
    import jxl.write.Boolean;
    import jxl.write.Number;
    import jxl.write.biff.RowsExceededException;
    
    //Read xml response
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
    // Parse response
    def xml = new XmlSlurper().parseText( groovyUtils.getXmlHolder( "GetResponse#ResponseAsXml" ).getPrettyXml() )
    
    try {
    	// Create the excel file
    	File exlFile = new File("D:/target_file.xls");
    	final WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );	
    	// Create a sheet in the excel file
    	final WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
    	
    	// Prepare to loop through the file
    	Label label = new Label(0, 0, "");
    	def i = 0
    	def currentElement = ''
    	// Start looping for each item
    	while (( currentElement = xml.e[i]) != '') {
    	
    		// Create cell with the column index i
    		label = new Label(0, i, currentElement.toString());
    		// Add the created Cells to the sheet
    		writableSheet.addCell(label);
    		
    		i++ // Increment index
    	}
    
    	//Write and close the workbook
    	writableWorkbook.write();
    	writableWorkbook.close();
    
    } catch (IOException e) {
    	e.printStackTrace();
    } catch (RowsExceededException e) {
    	e.printStackTrace();
    } catch (WriteException e) {
    	e.printStackTrace();
    }

    Don't forget about that library I attached to you. Cheers!

4 Replies

  • Lucian's avatar
    Lucian
    Community Hero

    Hi, this can be done.

     

    Firstly, I copied your sample data and placed it in a file on the disk. In order to read it and loop through each element I created this code:

     

     

    // Parse response
    def xml = new XmlSlurper().parse("D:\\test.txt")
    
    // Start looping for each item
    def i = 0
    def currentElement = ''
    while (( currentElement = xml.e[i]) != '') {
    	i++
    	log.info currentElement
    }

     

     

    Now what was left is to find a way to write this to an excel sheet.

     

     

    // Create the excel file
    File exlFile = new File("D:/target_file.xls");
    WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );
    
    // Create a sheet in the excel file
    WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
    	
    // Create cell
    Label label = new Label(0, 0, "Test string");
    //Add the created Cells to the sheet
    writableSheet.addCell(label);
    	
    //Write and close the workbook
    writableWorkbook.write();
    writableWorkbook.close();

     

    Finally, combining those 2 above, the full code would look like this:

     

    import java.io.File;
    import java.io.IOException;
    import java.util.Date;
     
    import jxl.*;
    import jxl.write.*;
    import jxl.write.Boolean;
    import jxl.write.Number;
    import jxl.write.biff.RowsExceededException;
    
    // Parse response
    def xml = new XmlSlurper().parse("D:\\test.txt")
    
    try {
    	// Create the excel file
    	File exlFile = new File("D:/target_file.xls");
    	final WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );	
    	// Create a sheet in the excel file
    	final WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
    	
    	// Prepare to loop through the file
    	Label label = new Label(0, 0, "");
    	def i = 0
    	def currentElement = ''
    	// Start looping for each item
    	while (( currentElement = xml.e[i]) != '') {
    	
    		// Create cell with the column index i
    		label = new Label(0, i, currentElement.toString());
    		// Add the created Cells to the sheet
    		writableSheet.addCell(label);
    		
    		i++ // Increment index
    	}
    
    	//Write and close the workbook
    	writableWorkbook.write();
    	writableWorkbook.close();
    
    } catch (IOException e) {
    	e.printStackTrace();
    } catch (RowsExceededException e) {
    	e.printStackTrace();
    } catch (WriteException e) {
    	e.printStackTrace();
    }

     

    LATER EDIT: The free version of SoapUI lacks the jxl library. So in order to use this you have to add the library manually. You can just download the attached .jar file and place it in C:\Program Files (x86)\SmartBear\SoapUI-x\bin\ext and you should be good to go.

    • Azeddin_Margani's avatar
      Azeddin_Margani
      Contributor

      Thanks Lucian for the reply.

       

      The sample data is generated by executing request. Instead of reading it from an external file is it possible to replace:

       // Parse response def xml = new XmlSlurper().parse("D:\\test.txt") 

       

      by:

       

      //Read response Xml

      def reqOperationName="GetMyRequest";
      def groovyUtils=new com.eviware.soapui.support.GroovyUtils(context)
      def resholder = groovyUtils.getXmlHolder(reqOperationName+"#ResponseAsXml")

       

      Thanks.

       

       

      • Lucian's avatar
        Lucian
        Community Hero

        Hi, I didn't quite get if the last post was a question or a statement :smileylol: Anyways.. so if I have a project like:

         

        .. where the xml is taken from a response then the code can stay the same except for the first part:

         

        import java.io.File;
        import java.io.IOException;
        import java.util.Date;
         
        import jxl.*;
        import jxl.write.*;
        import jxl.write.Boolean;
        import jxl.write.Number;
        import jxl.write.biff.RowsExceededException;
        
        //Read xml response
        def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
        // Parse response
        def xml = new XmlSlurper().parseText( groovyUtils.getXmlHolder( "GetResponse#ResponseAsXml" ).getPrettyXml() )
        
        try {
        	// Create the excel file
        	File exlFile = new File("D:/target_file.xls");
        	final WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );	
        	// Create a sheet in the excel file
        	final WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
        	
        	// Prepare to loop through the file
        	Label label = new Label(0, 0, "");
        	def i = 0
        	def currentElement = ''
        	// Start looping for each item
        	while (( currentElement = xml.e[i]) != '') {
        	
        		// Create cell with the column index i
        		label = new Label(0, i, currentElement.toString());
        		// Add the created Cells to the sheet
        		writableSheet.addCell(label);
        		
        		i++ // Increment index
        	}
        
        	//Write and close the workbook
        	writableWorkbook.write();
        	writableWorkbook.close();
        
        } catch (IOException e) {
        	e.printStackTrace();
        } catch (RowsExceededException e) {
        	e.printStackTrace();
        } catch (WriteException e) {
        	e.printStackTrace();
        }

        Don't forget about that library I attached to you. Cheers!