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

[Azeddin_Margani]

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

[Lucian · Accepted solution]

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]

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 · Accepted solution]

Hi, I didn't quite get if the last post was a question or a statement 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!

[Azeddin_Margani]

Thanks Lucian - it works the way I wanted it.