soapUI: Data-Driven Testing with Excel (xlsx)

Posts on data-driven testing seem to be fairly popular, so I'm visiting that well one last time.  So far I've covered data-driven testing with csv files and xls files; in this post, we'll look at adapting the DDT script to use an xlsx file as our data source.  If you've read the post on using an xls data source, most of this post will look very familiar-- in fact, the only differences are a few changes in the Apache POI classes used for our objects, so you may want to skip to looking at the script itself, paying particular attention to import statements and classes used.  For readers new to scripting concepts or otherwise having difficulty following along, you may want to check out some of the beginning scripting posts or the post on data-driven testing with a csv file (see the soapUI posts launch page for the relevant links).

To review the basic approach used in this script (and as you learn more about scripting in soapUI and look at other examples, you'll see that this is only one of many possible approaches):

1) Open the xlsx source file and read in a single row, which consists of inputs with some expected response data, and copy the data to test case properties.
2) Execute our test requests, plugging the inputs into test requests and expected results into content assertions via property expansion.
3) The last test step in the test case is a Groovy script step; if the end of the source file hasn't been reached, this step processes the next line in the source file and sends execution back to step 2.
4) The data-driven test case ends when the end of the file is reached, and the second test case (which only executes once) is run.

SoapUI doesn't have a native capability to read Excel files, so we have to use third-party components; in this case we're using the Apache POI API, which you can download from its site here-- you'll probably want the binary distribution (note: as I'm writing this, there seems to be an issue with the download links on the download page-- if you encounter issues, try following the link for "Binary Artifacts" in the "Release Archives" section at the bottom of the download page and get a version from there).  Unzip the downloaded file and drop the main jar file (something like poi-3.9-20121203.jar) into the /bin/ext directory of your soapUI program directory (I'm assuming that you already have soapUI installed).  This is a special directory for external jar files that allows soapUI to easily import their contents (we'll get to the import in a little bit).

The suite and xlsx source file can be downloaded here.  Unzip the contents and import the project xml file into soapUI (File - Import Project).  By default, the script looks for the xlsx file in a directory called C:\PerTableData; if you want to put it anywhere else you'll have to modify a single line in the script.  Also note that the source file intentionally contains some incorrect data to demonstrate how failures would appear (you should see three).

There are a couple of wrinkles in our source file.  First of all, there's a mix of data types: we have integers for the most part, but we have some floats (the boiling points for helium and oxygen, for example) and one number represented as a string (the boiling point of hydrogen).  Additionally, the atomic number for oxygen is represented with a SUM formula.  Generally speaking, your best bet is to work with strings-- property-related "set" and "get" methods in soapUI work with strings-- but this post should illustrate that it's possible to work with numeric data, too.

Let's start with the setup script for the ElementSpecificRequests test case (the data-driven test case).  The script begins with two import statements:

import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.usermodel.DataFormatter

These lines are where the components we need are imported from the POI jar file for use in our script.  Those of you familiar with my previous post on using xls files should make note of the use of xssf instead of hssf in our first imported package.

The next few lines set up some of the objects we'll need, including some based on POI classes.  The DataFormatter object (dFormatter) is used to handle the formatting of numeric data when it's converted to text for use with our property setter methods.  The XSSFFormulaEvaluator object (fEval) is used to handle cells with formula data (in this case, the atomic number of oxygen is the only such cell).  The XSSFWorkbook object (srcBook) represents our source xlsx file (you'll have to modify the source file path in the line where this object is created if you put the source file anywhere other than the default location).  Finally, the first sheet is extracted from the workbook object using its getSheetAt() method:

//Create data formatter
dFormatter = new DataFormatter()

//Create a new workbook using POI API
srcBook = new XSSFWorkbook(new FileInputStream(new File("C:\\PerTableData\\TestData-Mix.xlsx")))

//Create formula evaluator to handle formula cells
fEval = new XSSFFormulaEvaluator(srcBook)

//Get first sheet of the workbook (assumes data is on first sheet)
sourceSheet = srcBook.getSheetAt(0)

On to the mechanics of reading in data from the source file, starting with establishing our counter variable-- note that it's set as a context property with the built-in context variable so we can share its value across test components:

//Sets row counter to 0 (first row)-- if your sheet has headers, you can set this to 1
context.rowCounter = 0

Next we get the first row of data in the spreadsheet using the getRow() method, then step through the cells in that row, copying their contents to the corresponding test case properties using the setPropertyValue() method. Note the use of our DataFormatter object (dFormatter) with its formatCellValue() method-- this handles some potential problems with formatting numeric values when they're converted to strings.  Additionally, the XSSFFormulaEvaluator object (fEval) is used as an argument with the formatCellValue() method to handle the case where a cell has formula data.  Some of you may recognize this as overkill given our data source-- we know some of the columns (like element name) contain text data exclusively, but using these objects and methods allows for some flexibility moving forward.

//Read in the contents of the first row
sourceRow = sourceSheet.getRow(0)

//Step through cells in the row and populate property values-- note the extra work for numbers
elNameCell = sourceRow.getCell(0)
testCase.setPropertyValue("ElName",dFormatter.formatCellValue(elNameCell,fEval))

atNumCell = sourceRow.getCell(1)
testCase.setPropertyValue("AtNum",dFormatter.formatCellValue(atNumCell,fEval))

symbolCell = sourceRow.getCell(2)
testCase.setPropertyValue("Symbol",dFormatter.formatCellValue(symbolCell,fEval))

atWtCell = sourceRow.getCell(3)
testCase.setPropertyValue("AtWeight",dFormatter.formatCellValue(atWtCell,fEval))

boilCell = sourceRow.getCell(4)
testCase.setPropertyValue("BoilPoint",dFormatter.formatCellValue(boilCell,fEval))

The next few lines are optional-- these rename test steps for readability (so you can more easily identify which property values are being used in the test request):

//Rename request test steps for readability in the log; append the element name to the test step names
testCase.getTestStepAt(0).setName("GetAtomicNumber-" + testCase.getPropertyValue("AtNum"))
testCase.getTestStepAt(1).setName("GetAtomicWeight-" + testCase.getPropertyValue("AtWeight"))
testCase.getTestStepAt(2).setName("GetElementySymbol-" + testCase.getPropertyValue("Symbol"))

Finally, we add a reference to the sheet as a context property:

context.srcWkSheet = sourceSheet

With the data from our spreadsheet plugged into test case properties, the test requests are run, after which we get to the ReadNextLine Groovy script step.  As you might expect, this script is responsible for processing the next line of data and controlling execution accordingly.

As in the setup script above, the first few lines import the necessary contents of the POI package and create required objects.  Note the use of the srcWkSheet context property (referencing a worksheet object) we created at the end of our setup script and the call to its getWorkbook() method.

import org.apache.poi.xssf.usermodel.*

import org.apache.poi.ss.usermodel.DataFormatter

cellDataFormatter = new XSSFDataFormatter()

//Create formula evaluator
fEval = new XSSFFormulaEvaluator(context.srcWkSheet.getWorkbook())

The next line increments our counter variable, stored in test case context:

//Increment the rowcounter then read in the next row of items
context.rowCounter++;

The code to actually read in row data should look familiar-- it's nearly identical to code from the setup script, except it's wrapped in an if statement that checks for the end of the file (using the getLastRowNum() method of the POI XSSFSheet class). The code in the if block is only executed when the end of the file hasn't been reached; the last line sends execution back to the first test request step and repeats the loop, this time using the newly copied property values.

if(context.rowCounter<=context.srcWkSheet.getLastRowNum()){
 curTC = testRunner.testCase
 sourceRow = context.srcWkSheet.getRow(context.rowCounter)//Get a spreadsheet row
 
 //Step through cells in the row and populate property data 
 elNameCell = sourceRow.getCell(0)
 curTC.setPropertyValue("ElName",cellDataFormatter.formatCellValue(elNameCell,fEval))

 atNumCell = sourceRow.getCell(1)
 curTC.setPropertyValue("AtNum",cellDataFormatter.formatCellValue(atNumCell,fEval))

 symbolCell = sourceRow.getCell(2)
 curTC.setPropertyValue("Symbol",cellDataFormatter.formatCellValue(symbolCell,fEval))

 atWtCell = sourceRow.getCell(3)
 curTC.setPropertyValue("AtWeight",cellDataFormatter.formatCellValue(atWtCell,fEval))

 boilCell = sourceRow.getCell(4)
 curTC.setPropertyValue("BoilPoint",cellDataFormatter.formatCellValue(boilCell,fEval))

 //Rename test cases for readability in the TestSuite log
 curTC.getTestStepAt(0).setName("GetAtomicNumber-" + curTC.getPropertyValue("AtNum"))
 curTC.getTestStepAt(1).setName("GetAtomicWeight-" + curTC.getPropertyValue("AtWeight"))
 curTC.getTestStepAt(2).setName("GetElementSymbol-" + curTC.getPropertyValue("Symbol"))

 //Go back to first test request with newly copied properties
 testRunner.gotoStep(0)
}

This general technique should work for most xlsx data sources, but you may need to modify some of the code depending on your particular test data and its formatting standards. If you want to explore the Apache POI API more thoroughly, its documentation can be found here.

soapUI: A New "Launch Page" for soapUI Posts

I've written quite a few posts about soapUI so far (and intend to write quite a few more), so I've added a new "launch page" to the side navigation bar to make it easier to find and access material.  It's sort of like a virtual table of contents, grouping posts by general topic and briefly summarizing each one.  I'll try to keep the page up to date as I add more soapUI-related information.

Beginning soapUI Scripting 4: Lists, for, and if

Many of the methods you'll encounter in soapUI return lists, objects that can contain multiple data items.  In this post we'll cover some of the techniques and methods for working with lists, the for statement-- which is frequently used with lists, and the if statement, used for basic control flow.  For this post we'll actually use these concepts (and some of the others previously covered) with HTTP test requests, illustrating their practical application in soapUI.

Working with Lists in Groovy

In Groovy, you can create a list using the following syntax:

def myList = ["One", "Two", "Three", "Four"]

This creates a list of four strings.  Note the use of brackets ([ and ]) to enclose the items in the list; each item is also separated by a comma.  You can retrieve individual items in the list like this:

myList[0]

This would return the first item in the list-- "One" in this case.  The format is listName[x], where x is the zero-based position, or index, of the item in the list.  By zero-based, I mean we start counting at zero when determining an item's index: the first item in the list is at index 0, the second is at index 1, the third is at index 2, etc.

Since lists are objects, they have their own methods; here's some code illustrating a few of them:

def myList = ["One", "Two", "Three", "Four"]
log.info("List contents: $myList")
//size()-- returns number of items in the list
log.info("Size is " + myList.size())
log.info("Element one is " + myList[0])
//indexOf()-- returns the index of a given item in the list
log.info("Index of 'Four' is " + myList.indexOf("Four"))
//add()-- adds an item to the list
myList.add("Five")
log.info("After adding 'Five', size is " + myList.size())
log.info("Element five is " + myList[4])
//remove()-- removes an item from the list
myList.remove("Two")
log.info("After removing 'Two', size is " + myList.size())
log.info("List contents now: $myList")

The output from this script:

A SoapUI Project Using Lists

Now let's take a look at our example project; you can download a zipped version of it here. Download it, unzip it, and import it into soapUI.  The test suite consists of a single test case with four HTTP test requests; the service we're testing takes a zip code and returns the U.S. state where the zip code is located.

The SetZipList Groovy Script step establishes a list of zip codes to use with the HTTP requests and sets it as a context property (note that using a list here is done for illustrative purposes; in truth it would probably be more practical to just hard code the zip codes in each test step):

context.zipList = ["19106","20500","10118","57751"]

The members of the list are plugged into each HTTP request using a special form of property expansion.  Here's a screenshot of the first HTTP request step:


Note the value for the ZipCode parameter of our request-- this is a property expansion expression incorporating Groovy Script.  The "$" and enclosing braces are standard property expansion syntax; however, the "=" signifies that what follows within the braces is Groovy Script.  context.zipList[0] returns the first item in the list we created in the first test step ("19106"), using it as our ZipCode parameter in the request.  Each HTTP request step is set up the same way, with each retrieving a different item in the list (the second request gets the second item, the third the third item, etc.).

The following script is in the test case tear down:

def resultsList = testRunner.getResults()

for(res in resultsList){
    if(res.getTestStep().getClass() == com.eviware.soapui.impl.wsdl.teststeps.HttpTestRequestStep){
        def tStep = res.getTestStep()
        log.info(" Step Name = " + tStep.getName())
        log.info("    URL = " + res.getProperty("URL"))
        log.info("    Status = $res.status")
        log.info("    Time Taken = $res.timeTaken ms")
    }
}

This script retrieves select information from our test results and writes them to the log.  The first line uses the getResults() method of the test case's built-in testRunner object to retrieve the results of the test case's test steps, returned in a list.

Using for and if

Frequently when dealing with a list, you'll want to iterate through it and perform some action or set of actions on each individual item.  A for statement is one way to do this.  The basic syntax:

for (varName in list) {
     Set of actions to perform...
}

This takes each item in list list and assigns it to the variable varName, then performs the actions contained in the braces ({ and }).  The code is repeated for every item in the list.  So in our script above, the first result in resultsList is assigned to variable res and the code in the brackets is executed.  If there's another item in the list, that item then gets assigned to res and the code in the brackets is repeated, and so on, until all the items in the list have been processed.

You should recognize most of the code within the brackets as method calls, but the line starting with if may be unfamiliar.  There's a problem in our for loop-- it iterates over every item in our list of results, including results from the first test step, the Groovy Script test step.  Consequently, not all of the method calls we attempt to make are valid-- the getProperty() call, for example, would fail when we tried to call it on the results from the first test step.

The if statement allows us to run or not run code based on certain criteria.  The basic syntax for the if statement:

if (test expression that evaluates to true or false){
     Code to execute if test expression is true
}[else{
     Code to execute if test expression is false
}]

The else clause is optional, and in fact there's no else clause used in our example.  The if statement there checks to see if our results were generated by an HTTP test request step-- if they were (the expression evaluates to true), then the code in the brackets is safe to execute; if they weren't, the code in the brackets is skipped.  Note the operator used to check for equality-- == and not = as you might expect.  Other basic comparison operators include > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and != (not equal to).

Also note the indentation used in the script; with each block an extra level of indentation is used.  While not strictly required, this is considered good practice for readability-- you can easily see where the for block and if block begin and end.

Finally, here's the output in the script log produced by the tear down script: