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