soapUI: Data-Driven Testing with Excel (xls)

Note: If you experience any issues with webservicex.net or the periodic table service used in this post, or would just like to see another example, I've provided an alternative post covering some of the same material with a different service here. There is some material in this post, however, that's not covered in the alternative post (in particular, information about the DataFormatter class and using different data types)-- based on your own needs/situation, you may still want to come back here once you've seen a working example.

In response to a comment on a previous post about data-driven testing in soapUI, I'm going to try to illustrate a solution that works (with some limitations) for Excel data sources.

Generally speaking, it's easiest to work with data as text.  Property-related "set" and "get" methods take and return strings, so you end up having to convert numbers to text anyway-- and that's where things can get a little messy.  While the numbers 24.0 and 24 are equal, the strings "24.0" and "24" are not.  Of course, formatting all your numbers as text may be defeating the purpose of using Excel in the first place.

The basic idea behind the Excel test suite is the same as with the CSV-driven suite: each row of the source file consists of inputs with some expected response data; these are copied to test case properties and property expansion is used to plug the inputs into test requests and the response data into assertions.  A Groovy script step controls reading in the next row of data and repeating the test case until the end of the file is reached.  You can see the post covering a CSV-driven suite here for a more detailed explanation of the basic structure.  This post will only look at the script modified to use Excel as a data source (everything else is basically the same).

The suite and xls source file can be downloaded here in a zip file-- unzip the contents and import the project xml file into soapUI (File - Import Project).  By default, the script expects the XLS file to be located 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.

To use Excel sheets, you'll also have to extend soapUI's functionality using external APIs; in this case, 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.  Once the POI JAR is in this directory, you should be able to access its members via import statements in soapUI scripts.

Before we get to the script itself, lets take a look at our Excel source file to see a couple of the potential problems it presents.  Note that one cell in the second column (the atomic number for oxygen) is actually represented with a SUM formula; otherwise, the numbers are integers.  The last column (representing boiling points) could be even more problematic.  For one thing, the first row contains a string while the rest of the rows are numeric.  Even among the numeric rows, the types of numbers vary: the boiling points for carbon and gold are integers, for example, while the boiling points for helium and oxygen are floats.

Let's break down the Setup script for the ElementSpecificRequests TestCase (containing the steps to be repeated based on our source data).

The first line is an import statement importing the contents of the POI package containing the classes we need to work with Excel files-- again, placing the POI jar containing this package into the soapUI bin/ext directory as described above makes it possible to use this import statement.

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

The next few lines set up some POI objects we'll need for our script: an HSSFDataFormatter to handle the formatting of our numbers when they're converted to text (unnecessary if you're only using text data), an HSSFWorkbook object to open and read our source XLS file-- this is the line you'd need to alter if you changed the location of the source file from the default, an HSSFFormulaEvaluator to handle cells containing formulas (this object is not required if your source spreadsheet does not contain any formulas), and an HSSFSheet corresponding to the first sheet of our source workbook.  The script assumes the source data is contained on the first sheet-- if that's not the case for your own workbook, change the zero-based index of the sheet passed to the getSheetAt() function (first sheet is index 0, second sheet is index 1, etc.); alternatively, there's a getSheet() function where you can get a sheet by name-- e.g., sourceSheet = srcBook.getSheet("mydatasheet").

//Create data formatter
dFormatter = new HSSFDataFormatter()

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

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

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

Next, we create a rowCounter variable in our test case's context, setting it to 0-- we put it in test case context to keep the variable between steps and iterations of the test case.

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

We use the POI HSSFSheet class's getRow() function to read in the first (zero-based index 0) row of our spreadsheet. If your source spreadsheet contains one or more header rows, you may need to adjust this number accordingly.

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

The heart of reading in the source data is in the next few lines, where we take the row object we created in the line above and read in the data from each cell, assigning it to one of our test case properties via the setPropertyValue() method.  Note the use of the HSSFDataFormatter object (dFormatter) we created and its formatCellValue() method.  In the POI API, if you attempt to simply read in a numeric cell's value and then convert it to a string via the toString() method, it'll read in the number as a float before converting it to a string-- so if your cell contains the number 25, for example, it ends up getting formatted as "25.0" as a string.  The formatCellValue() method of the HSSFDataFormatter class handles this particular scenario, formatting the number 25 as "25".

//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))

One more note about the formatCellValue() function: the second argument (fEval, representing our HSSFFormulaEvaluator object) is only necessary if you're working with a source spreadsheet containing formulas-- otherwise, the function only needs one argument: for example, dFormatter.formatCellValue(elNameCell).  If you're working exclusively with text cells, even the dFormatter object and formatCellValue() method are unnecessary-- you can use the cell class's toString() method directly on each cell-- e.g., testCase.setPropertyValue("BoilPoint",boilCell.toString()).

The next few lines are actually optional-- they rename test steps for readability in the test suite log, appending the names of elements so you can keep track of which row is being processed.

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 copy our sourceSheet to a variable in test case context to save ourselves the trouble of having to re-create it later on.

context.srcWkSheet = sourceSheet

Now let's look at the ReadNextLine Groovy script step, run after our test requests are sent.

The first few lines are nearly identical to the Setup script described above, importing the contents of our POI package and creating a few needed objects.  Since we put the worksheet object created in the Setup script into context, we don't have to explicitly create another workbook object-- we can use the worksheet object's getWorkbook() method to get an object corresponding to our workbook.

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

cellDataFormatter = new HSSFDataFormatter()

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

The next line increments our counter variable.  This is the same variable that was created in our Setup script; again, we can re-use it and keep track of its value between test steps because it's in test case context.

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

The code to read in row data is wrapped in an if statement that checks to see if the end of the file has been reached (using the getLastRowNum() method of the POI HSSFSheet class). The last line of the if block goes back to the first test request step, running through the test case's test requests again with the newly copied property values.  Otherwise, this code is nearly identical to the corresponding code in the Setup script.

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)
}

The suite structure and scripts illustrated here should work for most typical scenarios with an Excel input file; however, you may need to modify some of the code depending on your particular test data and its formatting standards. The usual disclaimers also apply: I'm not a programmer by profession, so I'm sure there's plenty of room for improvement; I encourage you to explore other techniques and the POI API more thoroughly on your own (documentation for the POI API can be found here).

JMeter: A Data-Driven Test Script - Part 2

This is the second post looking at a sample JMeter script; in the first we covered the basic structure of the script.  In this post we'll take a look at the request samplers and associated config and assertion elements.  Here's a look at our script tree again:


The methods of our service are divided into two groups.  The three element specific requests are grouped together beneath a Loop Controller (labelled "ElementRequests Loop") with a CSV Data config element to facilitate testing the requests using different inputs.  Each of the three element specific requests are further nested under Simple Controllers-- these are purely structural, giving each sampler its own "branch" in the tree so we can group them with their own assertions and config elements.

For the sake of comparison, the first service method (GetElementSymbol) uses a WebService (SOAP) request sampler:


In the WSDL helper section you can enter the web service's WSDL URL and click the Load WSDL button to let JMeter try to identify available services; selecting a service and clicking the Configure button automatically populates key fields like Protocol, SOAPAction, etc. (to see this behavior, try temporarily adding a new sampler in the Workbench section, entering "http://www.webservicex.net/PeriodicTable.asmx?WSDL" for the URL).  Notice that the Server Name and Path fields are blank here-- these were already specified in the HTTP Request Defaults config element near the top of our script tree; since the sampler is at a lower level, these defaults are applied to it.  This sampler also illustrates that we can use variables in component name fields ("GetElementSymbol - SOAP Request - ${ElName}")-- useful for making our results more readable.  The "Read SOAP Response" checkbox must be enabled if you intend to use response data with a listener (which we are doing here).

We still have to provide the actual body of the request-- here's the GetElementSymbol request XML:


As the documentation for the WebService (SOAP) request sampler notes (and the dialog title screams in capital letters), it's been deprecated (you can read a discussion re: deprecation of the sampler here).  The recommended technique is to make SOAP requests via the HTTP sampler.  The remaining three requests are performed with the HTTP Request sampler; here's the GetAtomicNumber sampler:


By default, an HTTP Request sampler is configured to pass a series of parameters; to use the HTTP sampler for a SOAP request, you have to switch to use the Post Body tab in the HTTP Request section and put your request there.  Additionally, the HTTP sampler is missing a few of the fields available in the SOAP request sampler (the SOAPAction field, for example) so we have to add these to our HTTP Requests using a Request Header config element with each HTTP Request.  Here's the Request Header associated with the GetAtomicNumber sampler:


Each sampler is also grouped with a Response Assertion element to check response data for expected results:


As you can see, variables can be used in assertions, which makes our data-driven technique possible: each row of our CSV data file contains an input variable (an element name passed in as a request parameter) along with variables for expected response content.

The Pattern Matching Rules section of the Response Assertion dialog warrants some clarification.  The Contains and Matches options are used when your patterns to test are regular expressions (the Matches option means the entire response needs to match your regular expression).  The Equals and Substring options are used when your patterns to test should be evaluated as literal (case-sensitive) strings (the Equals option means the entire response needs to match the provided string).  The Not checkbox essentially reverses the test-- i.e., the assertion passes if the pattern is not found or matched.

JMeter: A Data-Driven Test Script - Part 1

In this post and the next I'm going to break down a JMeter script that illustrates a simple data-driven test technique and some of JMeter's components.  The first part will focus on the structure of the script, covering some key logic and configuration controllers; since they warrant a little more attention, the second part will look at the script's request samplers and closely associated components. The script (JMeter script files have a JMX extension) and csv data file used by the script are available for download here in a single zip file.  After unzipping the files, open the JMX file in JMeter (File - Open in the file menu).  By default, the csv file is expected to be in a folder called C:\Temp\PerTableData, but you can put it anywhere you'd like as long as you modify the CSV Data Controller accordingly.

Here's what the script tree should look like once you've opened it:


To see the script in action, click on the node for the View Results Tree listener (where it's easiest to see the script's progress), then click on the Play button at the top of JMeter's main window (the button with the large green arrow).  The script runs against a web service that provides data about the periodic table of elements.  There are four methods provided by the service, which are split into two groups in the script-- three of the methods are element specific (they return data based on an individual element provided as a parameter) and the last simply returns a list of all the elements in the periodic table (if you've read my post on data driven testing in soapUI, this should all look very familiar-- this script runs against the same service and is similarly structured).

Since this is strictly a functional test script, we only need a single user configured in the Test User element at the top of the script:


The HTTP Request Defaults config element at the top of the script specifies a default server, path, etc., to use for request samplers.  Since it's placed at the top of the tree, it's applied to all requests; however, you can override these default values by specifying different values locally (within an individual request's own configuration dialog).  One potential "gotcha" to note: the server name field in this element does not take a full URL-- the "http://" prefix should be omitted when specifying the server.


The "Duration LT 5 Sec" Duration Assertion asserts that all requests should complete within 5000 milliseconds (5 seconds).  As with the HTTP defaults element, its placement at the topmost level of the script tree means it's applied to all requests.


The "ElementRequests Loop" Loop Controller establishes a "branch" to the tree that loops indefinitely (an If Controller placed within the loop will terminate it based on our source data):


Within our loop, the first element is a CSV Data Set Config element:


The element specifies the location of our source CSV file to use to read in variable values-- if you put this file somewhere other than the default location, you'll have to change the Filename entry here.  It also specifies the variable names we're going to use in our script-- each variable name corresponds to a column in our CSV file-- so the first item in each row is assigned to the ElName variable, the second item is assigned to the ElNum variable, etc.  Note that we don't have to do any additional work to manage reading this file-- no maintaining a separate counter to keep our place, for example-- each time it's encountered in our loop, JMeter reads in the next line.  Although we're not using it in this case (we only have one user configured), note that you can "share" data from the CSV file between multiple threads/virtual users via the "Sharing mode" options-- meaning each time any thread encounters the CSV file in its execution, it reads the next line.  So you could have one thread read in data from row 1, the next thread read in data from row 2, etc.

To prevent the loop from running indefinitely, our test requests are nested beneath an If Controller that checks to see if the end of our CSV file has been reached.


When JMeter attempts to read in data from our CSV file after it's already read in the last row, "<EOF>" is returned.  That serves as a flag that it's time to exit the loop-- or more specifically, when "<EOF>" is not returned as the value of the ElName variable, that serves as a flag that it's okay to continue to execute our requests.  The condition statement illustrates how to reference variables: enclose the name in braces and precede it with "$" (remember that our variable names were configured in the CSV Config element).  Note that in our condition statement ("${ElName}"!="<EOF>") the name of our string variable is enclosed in quotes-- this syntax may seem unusual to some; see JMeter's component reference for some more condition statement examples.

Skipping the request samplers and related config elements for now (again, we'll look at those in the next post), the script contains two listener components which collect and report run data.  The Results Tree listener shows request and response data in detail-- even if you don't intend to use this listener in your finalized script, it's good for troubleshooting when you're building a script, allowing you to see exactly what's sent and received and create your assertions accordingly.


In the next post we'll look specifically at the script's request samplers and compare the deprecated SOAP Request sampler with the HTTP Request sampler.