Data-Driven Testing with XLS Example 2: Euro 2012 Web Service

This post covers a very basic sample project (really a single data-driven test case) to illustrate a data-driven testing technique using an Excel file as your test data source.  It's a simplified run-through of the same material covered here; this post is meant to provide an alternative working example.  There is some information in the original post not covered here (in particular, information about working with mixed data types); once you're familiar with the basics of working with the POI API covered in this post, I still recommend checking out the original post, particularly if you have to work with data types in the source sheet other than text.

You can download the sample project and its XLS file here in a single zipped file.  Unzip the files and import the project into soapUI.  You can place the XLS file wherever you'd like, although by default the script expects to find it in a directory called C:\Temp; you'll have to make a corresponding modification to the script if you'd like to put it somewhere else.

To use Excel sheets, you'll also have to extend soapUI's functionality using an external API; we'll be using the Apache POI API, which make it possible to access Microsoft documents (including Excel spreadsheets) from within Java.  First, download the Apache POI solution (the binary distribution) from here and unzip the downloaded file.  After unzipping, copy the main POI jar file (it should look something like "poi-versionnumber-releasedate.jar"; versionnumber and releasedate are placeholders for the actual version number and release date) into soapUI's special directory for external JARs: [SOAPUIProgramDirectory]/bin/ext-- of course, you should replace [SOAPUIProgramDirectory] with the path to the soapUI directory on your computer.  Once the POI JAR is in this directory, you should be able to access its members via import statements in soapUI scripts (note: soapUI may require a re-start before it recognizes the JAR file).

The web service contains information about the Euro 2012 soccer tournament.  We'll be using a data-driven testing approach to test the FullTeamInfo operation, which takes a country name as an input and returns details about that country's team, including players, coaches, etc.  Our source XLS file consists of a short list of country names in one column (which we'll use as a request parameter) and their corresponding coaches in a second column (which should be returned in the response).

If you've read my post on data-driven testing using a csv file, the general approach is the same; we're just using a different file type as our data source.  Our setup script contains code to open our source file and read in the first line; the country name and coach are assigned to properties we defined for the test case.  A test request is sent using the country name and an assertion verifies that the name of the coach returned in the response matches the name we read from the file.  A Groovy test step reads in the next line of the file (as long as the end hasn't been reached) and sends execution back to the test request step, where a new request is sent using the next row of data, etc.

Note that if you want to implement your own data-driven test case, you'll probably want to disable the Abort on Error option as I've done in this example project.  Otherwise, execution of the test case stops as soon as a failure occurs-- for a data-driven test case like this, we want to continue iterating over the loop to try all of our inputs even if one or more inputs fails.  The setting seems to be enabled by default for new test cases; to disable it, right-click on the test case, select Options from the context menu, and uncheck the corresponding checkbox:

Let's look at the various scripts in more detail, starting with the test case setup script:

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

context.rowCounter = 0
def srcBook = new HSSFWorkbook(new FileInputStream("C:\\Temp\\Euro2012.xls"))
def srcSheet = srcBook.getSheetAt(0)
def sourceRow = srcSheet.getRow(context.rowCounter)
testCase.setPropertyValue("Country",sourceRow.getCell(0).getStringCellValue())
testCase.setPropertyValue("Coach",sourceRow.getCell(1).getStringCellValue())
testCase.getTestStepAt(0).setName("FullTeamInfo - " + testCase.getPropertyValue("Country"))
context.srcWkSheet = srcSheet

Let's go through the script line by line; here's line 1:

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

This is the aforementioned import statement-- as I noted before, in order to read from an XLS file we have to extend soapUI's functionality; the import statement makes resources in the POI JAR file (placed in the bin/ext directory earlier) available in our script.

Next we set up a variable to keep track of our place (in terms of rows) in our source spreadsheet-- we'll set this as a property of the context variable so we can maintain its value across test steps and iterations:

context.rowCounter = 0

The next few lines create a workbook object representing our Excel source document and navigate down its structure using get methods, getting objects corresponding to the first worksheet of the workbook (using the workbook object's getSheetAt() method), then the current row in the sheet (using the sheet's getRow() method).  If you put the source xls file in a location other than C:\Temp, you'll want to modify the path in the first line below to point to the file's location on your computer (be sure to use double slashes for your path separators).

def srcBook = new HSSFWorkbook(new FileInputStream("C:\\Temp\\Euro2012.xls"))
def srcSheet = srcBook.getSheetAt(0)
def sourceRow = srcSheet.getRow(context.rowCounter)

Now that we have the first row in the worksheet (in variable sourceRow), we can read in the values of its cells using the getStringCellValue() method and assign them to the Country and Coach user-defined test case properties; we'll use these later in our test request and assertion.

testCase.setPropertyValue("Country",sourceRow.getCell(0).getStringCellValue())
testCase.setPropertyValue("Coach",sourceRow.getCell(1).getStringCellValue())

For readability, the next line re-names the test request step so we can see in the log which country is being used in each iteration.

testCase.getTestStepAt(0).setName("FullTeamInfo - " + testCase.getPropertyValue("Country"))

Finally, the source worksheet object is put into a context property.  This isn't absolutely necessary, but having it handy as a context property saves us a little bit of work in later steps getting another reference to the worksheet.

context.srcWkSheet = srcSheet

We're all set up now for our first test request, having read in the first line of data from our source spreadsheet.  Here's what the test request looks like, using property expansion to use the Country test case property:

FullTeamInfo test request using property expansion with the request parameter

And here's what our XPath assertion looks like to verify that the correct coach is returned in the response:

XPath assertion using property expansion to define the Expected Result

The remaining step in our test case (named ReadNextLine) is a Groovy Script step to prepare for the next iteration and control looping:

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

context.rowCounter++
if(context.rowCounter <= context.srcWkSheet.getLastRowNum()){
def sourceRow = context.srcWkSheet.getRow(context.rowCounter)
testRunner.testCase.setPropertyValue("Country",sourceRow.getCell(0).getStringCellValue())
testRunner.testCase.setPropertyValue("Coach",sourceRow.getCell(1).getStringCellValue())
testRunner.testCase.getTestStepAt(0).setName("FullTeamInfo - " + testRunner.testCase.getPropertyValue("Country"))
testRunner.gotoStep(0)
}


As with the setup script, we need resources in the POI JAR file, so the script starts with the same import statement.  Next we increment the context property we're using to keep track of our row count:

context.rowCounter++

Before we proceed, we have to check if the row value is valid for our source file-- i.e., we have to make sure we're not trying to read beyond the last row of the file.  The remaining lines of the script are wrapped inside an if block; the if statement compares the rowCounter value with the worksheet's last row value, returned by POI's getLastRowNum() method.

if(context.rowCounter <= context.srcWkSheet.getLastRowNum())

Consequently, the lines within the if block are executed only when the value of rowCounter is less than or equal to the total row count of our source worksheet.  The first few lines inside the block should look familiar: they're basically a repeat of the lines in the setup script responsible for getting a row in the worksheet, reading cell values from the row into test case properties, and renaming the request test step for readability:

def sourceRow = context.srcWkSheet.getRow(context.rowCounter)
testRunner.testCase.setPropertyValue("Country",sourceRow.getCell(0).getStringCellValue())
testRunner.testCase.setPropertyValue("Coach",sourceRow.getCell(1).getStringCellValue())
testRunner.testCase.getTestStepAt(0).setName("FullTeamInfo - " + testRunner.testCase.getPropertyValue("Country"))

Now that the next row's values have been read into their corresponding test case properties, the last line passes execution back to the test request step, the first test step in the test case (denoted by 0 using zero-based indexing).

testRunner.gotoStep(0)

The request is sent again and verified using the new values, after which the Groovy Script step runs again, getting the next row of values and sending execution back to the request step, etc.-- our loop is established.  Once we reach the last line of the source file, the if statement in the Groovy Script step will fail, skipping over the gotoStep method and allowing execution to pass on to the test case's teardown script, which consists of a single line to rename the test request step back to its "default" name.  Again, this isn't necessary, but it helps with keeping request inputs clear.

testCase.getTestStepAt(0).setName("FullTeamInfo")

Run the test suite and you should see it send the test request repeatedly, using the different countries specified in the source file as input parameters and verifying that the correct coach (also specified in the input file) is returned for each country.  I've "planted" a deliberate error in the source file, so you should see a failure occur for the test request for Germany:

Test Suite Log output

Data-Driven Testing Example 2: Holidays Web Service

In this post I'll walk through a sample soapUI project illustrating a data-driven testing technique that leverages test case properties and some light scripting.  This covers the same ground as my previous post on a simple data-driven testing technique, with a few minor differences; for example, this project uses XPath Match assertions where the original post used simple Contains assertions.

You can download the sample project and its accompanying CSV file (used as the source of our inputs and expected outputs) here in a single zipped file.  Download and unzip the files and import the project into soapUI.  You can place the CSV file wherever you'd like, although by default the script expects to find it in a directory called C:\Temp; you'll have to make a corresponding modification to the script if you'd like to put it somewhere else.

The service provides operations that return data related to holidays for the US and the UK; I've created test cases in the project for four of the operations, but it's the GetHolidayDate test case that we'll focus on in terms of data-driven testing.  GetHolidayDate takes a country, year, and holiday code (two of the other services show available country and holiday codes) and returns the date of the given holiday.

The holidays.csv file is our source file; each row of the file consists of a set of input parameters (country, holiday code, and year, in that order) along with our expected result-- the date corresponding to the input parameters.  Here are the contents of the file (just a sampling of all the available countries and holidays):

US,NEW_YEARS,2014,2014-01-01T00:00:00
GBEAW,BURNS_NIGHT,2014,2014-01-25T00:00:00
GBSCT,EMMELINE_PANKHURST,2010,2010-04-17T00:00:00
GBNIR,HOLOCAUST,2000,2000-01-27T00:00:00
US,EASTER,1980,1980-04-06T00:00:00
GBEAW,GUY_FAWKES,2008,2008-11-05T00:00:00
GBSCT,PALM_SUN,2013,2013-03-24T00:00:00
GBNIR,ST_PATRICKS_DAY,2005,2005-03-17T00:00:00
US,FLAG,2004,2004-06-14T00:00:00

We also have several test case properties defined:

Initially, these properties have no assigned values-- we'll fill those in with values from our source file as we iterate over the test case, doing the following:

1) Read a single line of the file, assigning the values in the line to corresponding test case properties.
2) Use property expansion to generate a request using the country, holiday code, and holiday year values we assigned to test case properties in step 1.
3) Use property expansion with an XPath assertion to confirm the response data returns the expected date, which was also assigned to a test case property in step 1.
4) Repeat steps 1 through 3 until the end of the file is reached.

The code to initialize our test data (opening the file for reading and processing in the first line of data) is placed in the test case setup script:

//Create a new BufferedReader object, using the context variable so it can be used between test components
context.sourceFile = new BufferedReader(new FileReader("C:\\Temp\\holidays.csv"))
//Read in the first line of the data file
def firstLine = context.sourceFile.readLine()
//Split the first line into a string array and assign the array members to various test case properties
def propData = firstLine.split(",")
testCase.setPropertyValue("holCountry",propData[0])
testCase.setPropertyValue("holCode",propData[1])
testCase.setPropertyValue("holYear",propData[2])
testCase.setPropertyValue("holDate",propData[3])
//Rename request test step for readability in the log; append code and year
testCase.getTestStepAt(0).setName("GetHolidayDate-" + propData[1] + "-" + propData[2])

The first (non-comment) line opens our file for reading via a BufferedReader object (if you put the csv file into a location other than C:\Temp, you need to modify this line accordingly).  The object is assigned to a property of the built-in context variable.  This allows us to share the object (and consequently the source file) and keep track of where we are in the file across multiple test steps and iterations.  Remember that the setup script is run prior to any test steps; it's not included in the steps that get looped over, which is what we want-- in particular, we don't want this first line to run more than once.

In the remaining lines of the setup script we read in the first line of our file, assigning it to a local variable firstLine (line 4).  In line 6, the line is split (using the comma to define our split points) and the split data items are assigned to an array called propData.  In lines 7 - 10 the members of the array are assigned to test case properties.  In the final line we're actually renaming the request test step by appending the holiday code and year; this is optional but makes the script log more readable.

Once the test case properties have been read into the script, property expansion is used to generate the test request:

The GetHolidayDate test request using test case properties
After reading in the first line of our data file, for example, the test request is sent using "US" as the countryCode, "NEW_YEARS" as the holidayName, and "2014" as the year.

A single XPath Match assertion is set up to validate our test request.  You can use property expansion with an XPath Match to specify the expected result; in this case, the holDate test case property is used:

Using property expansion with the XPath Match assertion for the GetHolidayDate test request
The next step in the test case is the ReadNextLine Groovy test step, responsible for managing iterations.  Here's the script contained in this test step:

/*Read in the next line of the file
  We can use the same object created in the Setup script because it
  was assigned to the context variable.*/
def nextLine = context.sourceFile.readLine()
/*If the end of the file hasn't been reached (nextLine does NOT equal null)
  split the line and assign new property values, rename test request steps,
  and go back to the first test request step*/
if(nextLine != null){
 def propData = nextLine.split(",")
 def curTestCase = testRunner.testCase
 curTestCase.setPropertyValue("holCountry",propData[0])
 curTestCase.setPropertyValue("holCode",propData[1])
 curTestCase.setPropertyValue("holYear",propData[2])
 curTestCase.setPropertyValue("holDate",propData[3])
 //Rename request test step for readability
 curTestCase.getTestStepAt(0).setName("GetHolidayDate-" + propData[1] + "-" + propData[2])
 testRunner.gotoStep(0)
}

The first non-comment line reads in the next line of our file, assigning it to the nextLine variable.  If we're not yet at the end of the file (if we were, nextLine would equal null), we basically go through the same code we saw before in the setup script: the data in the nextLine variable is split into an array, the values in the array are assigned to our test case properties, and the test request step is renamed to identify the current holiday code and year.  One difference here is that there's no built-in testCase variable as there is in the setup script, so we have to get a reference to the test case object via the built-in testRunner variable and assign it the variable curTestCase.  The final line uses the gotoStep() method of the testRunner variable to send execution back to the first test step in the test case-- the test request step.  After the new iteration of the test request step (with the newly assigned property values), the ReadNextLine step runs again, etc., until we finally do reach the end of the file.  Once this happens, the if statement fails and the gotoStep() method is bypassed.  Execution passes on to the short test case teardown script:

//Cleanup: rename test step to its original name and close the file reader
testCase.getTestStepAt(0).setName("GetHolidayDate")
context.sourceFile.close()

In this script we're just renaming the request step back to its default name (no identifying values appended) and the source file is closed.

Try running the test suite-- the resulting test suite log for the GetHolidayDate test case should look something like this (if necessary, click the TestSuite Log button at the bottom of the test case dialog to display the log):

We can see right away that something failed for the GetHolidayDate test request-- and because we were modifying the test step names as we went, we can see it occurred for Emmeline Pankhurst Day, the third row in the source file.  If you want to see more information about the failure, you can simply click on the error to launch the Message Viewer.  Here you can see the request and response XML (including the values resulting from property expansions) to get a better idea of where the problem may be.  In this case, our source file indicated that the expected date for the holiday was 2010-04-17, but the service returned 2010-07-14 (this was in fact an intentional error in the source file).
That illustrates one way to perform data looping with the soapUI free version, but by no means the only way; keep in mind that soapUI Pro includes additional tools to facilitate data driven testing.  If data driven testing is something you intend to use heavily or your particular case requires a complex implementation, the additional cost of the paid version of soapUI may be worth the savings in time and effort invested in maintaining scripts.