how to generate a Excel sheet based report through soapui when executing a test suite

how to generate an excel sheet based Report from soapui when we execute a testsuite or testcase?(in testing a ws开发者_如何学编程dl based )

As far as I know there is no way to create an Excel report after having run a test case or test suite. One way may be to create a DataSink test step, set the data sink type to Excel and then write several properties to it.

To export the test results to an excel file you need to create a groovy step within the test case.

soapUI is using the free Java Excel API to create or manipulate data in the Excel files.

Here below you can find the basic sample code.

import jxl.*;
import jxl.write.*;

// create an excel workbook
WritableWorkbook workbook1 = Workbook.createWorkbook(new File("c:\\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 fieldFromResponse = context.expand( '${Test Request#Response#declare namespace soap=\'http://www.w3.org/2003/05/soap-envelope\'; //soap:Text[1]}' );

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

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

The following code works to create excel file, to write sheet using Java Excel API:

import jxl.*;
import jxl.write.*;
import java.io.*;

public class CreateExcel_JxlApi {
    public static void main(String[] args) {

        //create WorkbookSettings object
        WorkbookSettings ws = new WorkbookSettings();

       try{
             //create work book
             //WritableWorkbook workbook = Workbook.createWorkbook(new File("F:/Tips/JExcelTip/TestReport.xls"), ws);
           WritableWorkbook workbook = Workbook.createWorkbook(new File("F:\\TestReport.xls"), ws);
           System.out.println("Did excel file create?");

             //create work sheet
             WritableSheet workSheet = null;
             workSheet = workbook.createSheet("Test Report" ,0);
             SheetSettings sh = workSheet.getSettings();

             //Creating Writable font to be used in the report  
             WritableFont normalFont = new WritableFont(WritableFont.createFont("MS Sans Serif"),
             WritableFont.DEFAULT_POINT_SIZE,
             WritableFont.NO_BOLD,  false);

             //creating plain format to write data in excel sheet
             WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
             normalFormat.setWrap(true);
             normalFormat.setAlignment(jxl.format.Alignment.CENTRE);
             normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
             normalFormat.setWrap(true);
             normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,
             jxl.format.Colour.BLACK);
             //write to datasheet 
             workSheet.addCell(new jxl.write.Label(0,0,"User Name",normalFormat));
             workSheet.addCell(new jxl.write.Label(1,0,"Password",normalFormat));   
             //write to the excel sheet
             workbook.write();
           //close the workbook
             workbook.close();
   }catch(Exception e){
        e.printStackTrace();
  }
}
}

import java.util.*; 
import java.lang.*;
import jxl.*
import jxl.write.*

testCase = testRunner.testCase.testSuite.project.getTestSuiteByName('TS_CurrencyConverter').getTestCaseByName('TC_CurrencyConverter')
def properties = new com.eviware.soapui.support.types.StringToObjectMap ()
def async = false
def runner=testCase.run (properties, async)

for(r in runner.results)
{
    log.info(testCase.name+ ":Executed Successfully with Status " + r.status ) 
    //testCase.name+ ":Executed Successfully with Status " + r.status
    WritableWorkbook workbook1 = Workbook.createWorkbook(new File("c:/AQR/TestResult.xls"))
    WritableSheet sheet1 = workbook1.createSheet("RunReport", 0)
    Label TCNamelabel = new Label(0, 0, "Test Case Name");
    Label TCStatus= new Label(1,0,"Test Case Status");
    //Label TCComment= new Label(0,2,"Comment");
    sheet1.addCell(TCNamelabel); 
    sheet1.addCell(TCStatus); 

    Label TCANamelabel = new Label(0, 1, testCase.name);
    Label TCAStatus= new Label(1, 1, ""+ r.status);
    sheet1.addCell(TCANamelabel); 
    sheet1.addCell(TCAStatus); 
    workbook1.write()
    workbook1.close()
}