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.