Write excel report using groovy step in SOAPUI for an automated Test Case which iterates 3 times

Question

I am trying to automate Webservice execution using soapui (groovy scripting). I have the following TestSteps:

1. Data Source (Reads rows from and excel datasource)
2. Properties
3. SOAP Step
4. Capture SOAP REQ-RES as xml files
5. Generate Excel Report with xml tag value parsed from response
6. Looper (Iterate the Test Case for Total Rows in datasource excel file)

I have been successful in creating the framework but stuck in 5th step.

Step 5 executes successfully but creates report excel file having response data of only the last test iteration. A new workbook is created with each iteration which is overwritten by next iteration.

Please suggest changes so as to implement the feature which apppends response data for every iteration in excel sheet

I am using jxl api for writing parsed xml data using below code:

import jxl.*;

import com.eviware.soapui.support.XmlHolder

import jxl.write.*;


// create an excel workbook
WritableWorkbook workbook1=Workbook.createWorkbook(newFile("/Users/anant/Downloads/GroovyTest/report.xls"));

// create a sheet in the workbook
WritableSheet sheet1 = workbook1.createSheet("Report Worksheet", 0);

// Get the data to be added to the report
def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
def holder = groovyUtils.getXmlHolder("divide#response")

def fieldFromResponse = holder.getNodeValue("//n:divideResponse/n:Result")

// create a label 
Label label = new Label(0, 0, fieldFromResponse);
log.info fieldFromResponse

// Add the label into the sheet
sheet1.addCell(label); 
workbook1.write();
workbook1.close();

Answer 1

Try this one using apache poi.. Don't know much about jxl. Pls ignore the extra imports.. Modified the a code to suit your requirement :) - Here no creation of file, sheet etc. everytime, coz that was the problem in your code. And also there wasn't any row incrementer also.

   import java.io.*;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.ss.usermodel.*;
    import java.util.Iterator;

                // Pre requisite : create a property at testcase level as "rowIncrementer" with initial value 0 (0th row). This property will be used as row index
                //similar could be used for column also (cell). But i haven't used it now :)
                     def rowInc = testRunner.testCase.getPropertyValue("rowIncrementer")
                     rowInc = Integer.parseInt(rowInc)
                // accessing existing file
                    FileInputStream fIpStream= new FileInputStream(new File("C:\\excl.xls")); 

                    HSSFWorkbook wb = new HSSFWorkbook(fIpStream);
                      // accessing your existing sheet
                    HSSFSheet worksheet = wb.getSheetAt(0);

                    Row row = worksheet.createRow(rowInc);
                 //Create a new cell in current row
                 Cell cell = row.createCell(0);

                    cell.setCellValue("NewValue"+rowInc.toString());
                      //cell.setCellValue(fieldFromResponse)
                    fIpStream.close(); //Close the InputStream

                    FileOutputStream output_file =new FileOutputStream(new File("C:\\excl.xls"));  
                    wb.write(output_file);

                    output_file.close(); 

                // to increment the row for every loop -> will be incremented for every run of this teststep
                rowInc = rowInc + 1
                     testRunner.testCase.setPropertyValue("rowIncrementer", rowInc.toString())