My SoapUI Thoughts on Effective Testing in SoapUI

Processing Spreadsheets

We’ve looked at processing comma-separated value (CSV) data. Now that we have the beginnings of a class-based implementation, I’d like to use this article to show how to extend it to support Excel spreadsheets.



  • An understanding of how to process data stored in spreadsheets.
  • An awareness of alternative request formats.

Consider the comments resource. We can store the data to test this resource via data in an Excel spreadsheet as follows. Note that you should save the file in Excel 97-2003 format in order to be compatible with the jxl library used in this example. Spreadsheet01 The way to handle this test data is by extending our existing Datasource class with an XLSDatasource class. I’ve implemented this class via the following script.

import jxl.*

class XLSDatasource extends Datasource
	boolean initialise()
		try {
			def book  = Workbook.getWorkbook( new File( fileName ) ),
			    sheet = book?.getSheet( 0 )
			( 0 ..< sheet.getRows() ).each { rowNo ->
				def cells = sheet.getRow( rowNo ),
				    line  = []
				if( cells.size() > 0 ) {
					cells.each { line += it?.getContents() ?: "" }
				testData.add( line )
		catch( Exception e ) { testRunner.cancel( "$e" ) }

To use this class, create a copy of the CSVDatasourceFull.groovy file in the SoapUI project directory and call it XLSDatasourceFull.groovy. Replace the CSVDatasource class with the XLSDatasource class (including the import jxl.* line), keep the Datasource base class, and save the file.

(We now have two duplicated copies of the Datasource base class. This violates the last of the requirements for our datasource framework and I will address this shortcoming in the article relating to compiling Groovy code into JAR files.)

In addition to the imports required by the base class, XLSDatasource requires an import of the jxl library. To access this library, do the following.

  1. Close SoapUI if it’s running.
  2. Download the jxl library file.
  3. Copy the jxl.*.jar file into SoapUI’s lib folder and re-start SoapUI.

You’ll see that this code is quite similar to the CSV implementation. This implementation uses the same approach of populating a list with the values of each row in the spreadsheet, then adding the list to the testData member of the class.

To create a test case for the comments resource, clone the Todo test case. Make the following changes to the new test case:

  1. Edit the Datasource test step so that the script refers to the XLSDatasourceFull.groovy file instead of the CSVDatasourceFull.groovy file.
  2. Update the test case properties so that PropertiesFile = comments.xls and comments = resourceName.
  3. Change the assertions on the test steps to refer to the properties relating to comments.

Request Formats

For completeness, I decided to explore the fact that the JsonPlaceholder API accepts requests in alternative formats. In discussing the service definitions, I alluded to the use of multiple requests on a single method in the context of different media types. Thus far, we have been using application/json as the media type for all the POST and PUT requests. In the test for the comments resource, I’ve configured the POST test step to use the application/x-www-form-urlencoded media type instead. The request in this form looks like this. POSTurlEncoded01 Note that the request body is on one line, resembling the format used in query filter strings. Conceptually this is identical to the JSON format we’ve been using so far, but from a testing perspective it’s wise to include this media type in at least some of your tests if your API supports it.


With our data-driven framework now supporting two data formats, I’d like to look at how to implement a datasink.