SoapUI: Writing Results to Excel

SoapUI's testrunner can be used to run test suites from the command line and provides an option to generate jUnit-style test reports.  These can be useful, of course, but they're not always easily readable.  In this post I'll cover how to collect test results and record them in an Excel document using the Apache POI API, downloadable here.  As a bonus, I'll also show some examples of Groovy closures in action to help streamline our script and avoid repetition.  Fair warning: this post's a bit long, but I really couldn't think of a good place to try to break it up.

I'll be building on the data-driven periodic table project using an Excel file as a test source; you can download this extended version of the project here.  Unzip the files (the project and source xls file) and import the project into SoapUI.  Modify the set up script for the ElementSpecificRequests test case to specify the location where you put the source data file (remember to double slashes in the path to escape them); likewise, you can change the location where you want to output your report file by modifying the test suite tear down script.  Run the suite, and it should create an Excel workbook containing test suite step results in the location specified in the suite tear down script.

For the sake of brevity I'll focus primarily on what's changed or new; you can read or review the details of the unchanged bits in the original post.  Setting up a project to use the API is also covered there: remember that to use the POI API library (or any third party library) within SoapUI, you need to drop its jar file into the bin/ext directory in your SoapUI program folder (a re-start of SoapUI may also be necessary).  This makes it possible to import and use the library's resources in your scripts.

Since we'll need to write to our report from both test cases, the code to set up the report is at the suite level.  Here are the first few lines of the set up script for the suite:

import org.apache.poi.hssf.usermodel.*

//Create report output workbook and worksheet
context.reportWorkbook = new HSSFWorkbook()
def reportSheet = context.reportWorkbook.createSheet("PeriodicTable-TestReport")

The import statement makes the members of the org.apache.poi.hssf.usermodel package available in our script-- components related to working with xls files.  A new HSSFWorkbook object is created and a sheet ("PeriodicTable-TestReport") is added to the workbook.  Note that the workbook is assigned to a context property (reportWorkbook) so it can be referenced again in the tear down script.

After the report workbook and worksheet are set up, we come to the first closure.  Closures are blocks of code that can be treated as pieces of data; i.e., they can be assigned to variables and properties, used as arguments to methods, etc.  While not the same as traditional Java functions, closures in practice behave very similarly.  In this case, a closure is used to encapsulate the code to write test results to the report worksheet to avoid re-writing the code in multiple places.  I'll review the closure in two parts:

//In the following closure tsResult represents a TestStepResult object
context.recordResults = {tsResult ->
   //Create new row in output workbook
   def newRow = reportSheet.createRow(reportSheet.getPhysicalNumberOfRows())
   def nameCell = newRow.createCell(0, HSSFCell.CELL_TYPE_STRING)
   def timeStampCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING)
   def resultCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING)
   def timeCell = newRow.createCell(2, HSSFCell.CELL_TYPE_NUMERIC)
   def assertFailCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING)

   //Write test result data to corresponding cells in the newly created row
   def statusString = tsResult.getStatus().toString()
   nameCell.setCellValue(tsResult.testStep.testCase.name + "-" + tsResult.testStep.getName())
   resultCell.setCellValue(statusString)
   timeCell.setCellValue(tsResult.getTimeTaken())
   timeStampCell.setCellValue(new Date(tsResult.getTimeStamp()).toString())

The closure itself is demarcated by braces ({ and }), and the entire thing is assigned to the recordResults context property to facilitate re-use across test cases.  Note the odd-looking bit at the beginning of the closure (just after the opening brace): tsResult ->; this designates tsResult as the variable name used within the closure to represent the argument we'll eventually pass into it.  If this doesn't make sense just yet, it should be a little clearer once you see an example of calling the closure a little bit later.

The next non-comment line create a new row in the worksheet (newRow).  The createRow() method requires a zero-based index value to designate where in the sheet the row should be created (0 is the first row of the sheet, 1 is the second row, etc.).  Since we want to add in each new row after the existing rows, we use the getPhysicalNumberOfRows() method of the worksheet object to get the correct index we should use.  So, for example, if we have 6 rows already added in the worksheet, the physical row count (which is not zero-based-- it uses natural counting starting at 1) is 6.  The zero-based index of the last row is 5, hence the zero-based index we need to use for the next row is also 6.

The next few lines create the cells in the new row corresponding to test result data.  The createCell() method takes an index argument (zero-based again) specifying the column in the row where the cell should be created and a data type argument.

Finally we retrieve the test result data from the tsResult variable (which represents a TestStepResult object) and set cell values accordingly.  We're writing a test step name (created by combining the name of the step's parent test case with the name of the step separated by a dash), status, time taken, and time of execution.  There's a little bit of extra work involved in getting a string representation of the time of execution.  The time stamp is returned in milliseconds by the getTimeStamp() method.  That's used to create a new Date object, and that object's toString() method is called.

The last cell in the row, assertFailCell, should contain a list of any assertions (by name) that failed for a given test step.  The code to get that list of assertions makes up the second part of the closure:

 
   //Code to get the names of any failed assertions
   def failedAsserts = "" 
   if(statusString != "OK"){
      def assertList = tsResult.testStep.getAssertionList()
      assertList.each(){
         if(it.status.toString() != "VALID"){
            if(failedAsserts == ""){
               failedAsserts = it.name
            }else{
               failedAsserts += (", " + it.name)
            }
         }
      }
   }

   //Write the list of failed assertions to the appropriate cell
   assertFailCell.setCellValue(failedAsserts)
}

After defining a string where we can create our list of assertions (failedAsserts), there's an if block that contains code that's only executed if the value of statusString is not "OK"-- in other words, when the test step did not pass.  If the step didn't pass, we drill down into the step's assertions, getting the step result's parent test step, then using the step's getAssertionList() method to retrieve a list of assertions.

Once the list is assigned to the variable assertList, we call its each() method.  This is one of several special methods available to aggregate data structures (i.e., data structures like lists, maps, etc., capable of containing multiple values).  The each() method takes each member of the list and performs some action on it.  The action to perform is specified in a closure defined immediately after the method call-- note the opening brace after each() marking the beginning of the closure. Each member is passed as an argument to the closure and assigned to the variable it within the closure-- it is the variable name used when another variable name is not explicitly designated (as we did above with the tsResult variable).

In this case each assertion's status is checked.  If it's not equal to "VALID" (i.e., the assertion did not pass), the name of the assertion is retrieved and added to our comma-separated list of failed assertion names.  Once we've finished building the list, it's written to the assertFailCell cell in our report worksheet and the recordResults closure is finally ended with a closing brace.

I think it's important to point out that none of the code in the recordResults closure is actually executed here.  The closure is just defined the same way we might define a string or number variable or property for use elsewhere later on.

The last lines of the test suite set up script create a header row for our test report sheet:

//Write out report column headers to the report sheet
def headerRow = reportSheet.createRow(0);
def testNameCell = headerRow.createCell(0, HSSFCell.CELL_TYPE_STRING)
def timeStampCell = headerRow.createCell(1, HSSFCell.CELL_TYPE_STRING)
def testTimeCell = headerRow.createCell(2, HSSFCell.CELL_TYPE_STRING)
def testResultCell = headerRow.createCell(3, HSSFCell.CELL_TYPE_STRING)
def failedAssertions = headerRow.createCell(4, HSSFCell.CELL_TYPE_STRING)

//Populate header row cells with column names
testNameCell.setCellValue("Test Name")
testResultCell.setCellValue("Pass/Fail")
testTimeCell.setCellValue("Time Taken (ms)")
timeStampCell.setCellValue("Test Date/Time")
failedAssertions.setCellValue("Failed Assertions")

The suite tear down script is much shorter and should be relatively self-explanatory-- it writes the workbook data out to disk (modify the path string used to create the FileOutputStream object to change your output location and/or file name) and then closes the output stream used to write the data:

import org.apache.poi.hssf.usermodel.*

//Workbook exists in memory; write it out to a file here
def fout = new BufferedOutputStream(new FileOutputStream("C:\\Temp\\SoapUIRunReport.xls"))
context.reportWorkbook.write(fout)
fout.close()

Finally, we'll look at some code that actually uses the closure we defined above-- and you'll see just how useful closures can be.  Here's the code that's been added to the ReadNextLine Groovy script step in the ElementSpecificRequests test case:

def resultsList = testRunner.getResults()

context.recordResults(resultsList[-3])
context.recordResults(resultsList[-2])
context.recordResults(resultsList[-1])

This code gets the results from the three request test steps in the test case and writes their data (step name, time stamp, etc.) to the report workbook-- and it does all of this in four lines of code, thanks to the recordResults closure we created above.  There's a single line in the tear down script of the GetAtoms test case that does basically the same thing; however, the ElementSpecificRequests test case is a slightly more interesting example.

The first line calls the testRunner object's getResults() method to get a list of TestStepResult objects.  The last three lines take the last three members of that list and pass them as arguments to the recordResults closure.

You may be wondering what's up with the negative index numbers used to get the members of the list.  Because of the way we've implemented our data-driven testing, looping over the test case's test steps repeatedly, test results accumulate.  For example, the first time we reach the ReadNextLine script step, three step results have been recorded for the test case.  After looping through again, when we reach the ReadNextLine step we'll have seven step results for the test case (results for all four steps from the first time through, plus the first three steps for the second time through), and so on.  So each time through we only want to grab the last three available steps in the list of results, corresponding to the three request steps for the current loop.  Negative indexing allows us to do just that: using an index of -1 (resultsList[-1]) gets the last member of the list, an index of -2 gets the second to last member of the list, etc.

So once we have the last three members we can pass them to the closure.  In terms of syntax, you can see it looks just like calling a method-- we put our argument(s) to the closure inside parentheses after the name of the closure.  Scroll back up and take a look again at the recordResults closure to get a better idea of what's happening.  Remember we defined tsResult as the name of the variable holding our argument within the closure; when we pass resultsList[-1] into the closure, the last available TestStepResult object is assigned to that variable and the code in the closure is executed.

If you compare this project against the original data-driven xls project, you'll see that I've also made some changes to the code used to read data from the source file, re-organizing it into a closure so duplication of some code is avoided.  As this post is quite long enough already, I'll leave it to you to identify those changes on your own.