Forum Discussion

Azeddin_Margani's avatar
Azeddin_Margani
Contributor
7 years ago

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 ...
  • Lucian's avatar
    7 years ago

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