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