When testing a calculation one must generate expected results from comprehensive and interesting input data. Another important consideration is that those who specified the calculation need to understand the input data and the expected results; all of the corner cases need to be highlighted and thoroughly discussed, for example.
An excellent way to address this problem is to specify input data in Excel, use Excel's tools to generate expected results, then use the same data and results to drive ordinary unit tests. The key is to be able to read the data and expected results maintained in the Excel workbook from within the test cases themselves. Once the data and tests are set up appropriately, one can work effortlessly and collaboratively with colleagues to explore how the caluculation will work in any situation, and thus develop a deep level of confidence that the calculation behaves as expected. (All of the other advantages of unit tests also apply, of course!)
There are several options available for communicating with Excel from Java. In the following example, we'll use the estimable JExcel API. All of the code described here is available for download, along with the example workbook.
A few preliminaries. The goal of this exercise is to develop a rudimentary API to facilitate using Excel to maintain input data and expected results for standard JUnit test cases. I chose 2003 American League salaries for input data. Each record has the player's position and team in addition to his salary. My example calculations are salaries averaged over position and team. To compute expected results that will be used to check my calculations, I just created a simple pivot table in Excel from the list of players.
To begin, we need to open and close the workbook containing test data. Reading the data depends of course on the specific application, and is thus relegated to subclasses. First consider a snippet from WorkbookReader. (Note that we are not terribly concerned with error handling. Any exception thrown will be easily diagnosed. The stakes are low.)
public void readWorkbook() throws Exception {
Workbook workbook = null;
try {
File file = new File(getWorkbookName());
workbook = Workbook.getWorkbook(file);
read(workbook);
workbook.close();
} finally {
if (workbook != null) workbook.close();
}
}
public abstract String getWorkbookName();
public abstract void read(Workbook workbook);
In order to extract the data once the workbook is open, we'll iterate over either rows or columns; thus every RangeParser must provide the Dimension of interest. In addition, each RangeParser must specify a set of parsing parameters — the integer indices, for example, that mark the boundaries of the range relative to the sheet.
public abstract class RangeParser
{
private Sheet sheet;
public void parse(Workbook workbook, String rangeName) {
// find range in workbook, determine the sheet,
// then delegate to abstract parse method
}
public abstract void setParsingParameters(Range range);
public abstract Dimension getDimension();
public abstract void parse(Range range);
protected Cell[] getCells(int index) {
switch (getDimension()) {
case ROW:
return this.sheet.getRow(index);
case COLUMN:
return this.sheet.getColumn(index);
default:
throw new IllegalStateException();
}
}
}
In our example we need to parse a set of contiguous cells for each row of player data, and we need to extract key-value pairs from the pivot table of expected results. This leads us to two subclasses of RangeParser: CellRangeParser and KeyValueRangeParser. Each implements the abstract parse method, factoring out the common activities. Subclasses must implement the more specific methods parseCells and setKeyValuePair, respectively. Other attributes are defined to hold range parameters. (Yes, I know that there are redundancies, like the start and end indices. Sometimes specificity and clarity trump principle.)
public abstract class CellRangeParser extends RangeParser
{
protected int startIndex;
protected int endIndex;
public void parse(Range range) {
setParsingParameters(range);
for (int i = this.startIndex; i <= this.endIndex; i++) {
Cell[] cells = getCells(i);
parseCells(cells);
}
}
public abstract void parseCells(Cell[] cells);
}
public abstract class KeyValueRangeParser extends RangeParser
{
protected int startIndex;
protected int endIndex;
protected int keyIndex;
protected int valueIndex;
public void parse(Range range) {
setParsingParameters(range);
for (int i = this.startIndex; i <= this.endIndex; i++) {
Cell[] cells = getCells(i);
String key = cells[this.keyIndex].getContents();
String value = cells[this.valueIndex].getContents();
setKeyValuePair(key, value);
}
}
public abstract void setKeyValuePair(String key, String value);
}
Finally we can put it all together. Here's a test class that makes use of all of the infrastructure.
package quince.excel;
import quince.excel.util.*;
import junit.framework.*;
import java.util.*;
import java.math.*;
import jxl.*;
public class SalaryCalculatorTest extends TestCase
{
SalaryDataReader salaryReader = new SalaryDataReader();
// get both input data and expected results from spreadsheet
public SalaryCalculatorTest(String name) throws Exception {
super(name);
this.salaryReader = new SalaryDataReader();
this.salaryReader.readWorkbook();
}
// compare expected and computed average salaries for each team
public void testAverageSalaryByTeam() throws Exception {
Map computedAverages =
SalaryCalculator.averageSalaryByTeam(this.salaryReader.players);
for (Map.Entry entry : computedAverages.entrySet()) {
BigDecimal e = this.salaryReader.expectedAverageSalaryByTeam.get(entry.getKey());
BigDecimal c = entry.getValue().getAverage();
assertEquals(0, e.compareTo(c));
}
}
// compare expected and computed average salaries for each position
public void testAverageSalaryByPosition() throws Exception {
Map computedAverages =
SalaryCalculator.averageSalaryByPosition(this.salaryReader.players);
for (Map.Entry entry : computedAverages.entrySet()) {
BigDecimal e =
this.salaryReader.expectedAverageSalaryByPosition.get(entry.getKey());
BigDecimal c = entry.getValue().getAverage();
assertEquals(0, e.compareTo(c));
}
}
}
class SalaryDataReader extends WorkbookReader
{
// input to algorithm
List players = new ArrayList();
// expected output
Map expectedAverageSalaryByTeam;
Map expectedAverageSalaryByPosition;
public String getWorkbookName() { return "config/salaryData.xls"; }
public void read(Workbook workbook) {
this.players = new PlayerParser().getPlayers(workbook, "SourceData");
this.expectedAverageSalaryByTeam =
new TeamRangeParser().getSalaries(workbook, "Results");
this.expectedAverageSalaryByPosition =
new PositionRangeParser().getSalaries(workbook, "Results");
}
}
class PlayerParser extends CellRangeParser
{
List players = new ArrayList();
public Dimension getDimension() { return Dimension.ROW; }
public void setParsingParameters(Range range) {
this.startIndex = range.getTopLeft().getRow();
this.endIndex = range.getBottomRight().getRow();
}
public void parseCells(Cell[] cells) {
assert (cells != null && cells.length >= 4);
Player p = new Player();
p.setTeam (cells[0].getContents());
p.setName (cells[1].getContents());
p.setSalary (cells[2].getContents());
p.setPosition(cells[3].getContents());
this.players.add(p);
}
public List getPlayers(Workbook workbook, String rangeName) {
parse(workbook, rangeName);
return this.players;
}
}
class TeamRangeParser extends KeyValueRangeParser
{
Map salaries = new HashMap();
public Dimension getDimension() { return Dimension.ROW; }
public void setParsingParameters(Range range) {
this.startIndex = range.getTopLeft().getRow() + 1;
this.endIndex = range.getBottomRight().getRow() - 1;
this.keyIndex = range.getTopLeft().getColumn();
this.valueIndex = range.getBottomRight().getColumn();
}
public void setKeyValuePair(String key, String value) {
this.salaries.put(key, new BigDecimal(value));
}
Map getSalaries(Workbook workbook, String rangeName) {
parse(workbook, rangeName);
return this.salaries;
}
}
class PositionRangeParser extends KeyValueRangeParser
{
Map salaries = new HashMap();
public Dimension getDimension() { return Dimension.COLUMN; }
public void setParsingParameters(Range range) {
this.startIndex = range.getTopLeft().getColumn() + 1;
this.endIndex = range.getBottomRight().getColumn() - 1;
this.keyIndex = range.getTopLeft().getRow();
this.valueIndex = range.getBottomRight().getRow();
}
public void setKeyValuePair(String key, String value) {
this.salaries.put(key, new BigDecimal(value));
}
Map getSalaries(Workbook workbook, String rangeName) {
parse(workbook, rangeName);
return this.salaries;
}
}