Testing Spreadsheet Logic

Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.

IronPython Wiki

IronPython in Action

Testing Spreadsheet Logic

Contents

Introduction
Unit Testing
TestableSheet

Introduction

Spreadsheet errors cost money, in some cases a lot of money. There is another industry that suffers from computer errors, and that is the software industry where they are called bugs.

In order to try and solve the problem of bugs in software, a system called unit testing has evolved. This is a way of testing the logic of parts of a program to ensure that the right data in is going to end up with the the right result at the end.

Resolver treats spreadsheets as programs. It turns your data and formulae into code and then executes this in order to create the final values. By exporting a spreadsheet as code, we can use the same systems for testing software on the logic of a spreadsheet.

In spreadsheets there are two different kinds of errors, there are errors in formulae and errors in data. Unit testing is very easy to apply to formulae. If a formulae is supposed to add a set of values then we can test it by providing known data, and checking that the answer is what we expect.

The second kind of error is where the wrong data is input. Whilst it is harder to test this directly, you could catch common errors (like putting the decimal point in the right place) by checking that all the values fall within expected bounds.

This article shows an example of unit testing a spreadsheet. The first one tests a simple spreadsheet with formulae, based on the Cash Balance Example. It tests the formulae, but also tests some aspects of the spreadsheet presentation and some user code.

To follow these examples you should download this zipfile, which has the original spreadsheets and the test code:

Download the Spreadsheet Testing Example (zipfile)

Unit Testing

Unit testing is based on testing the individual parts of a 'system'. Logically, if you know that each of the individual steps in a spreadsheet or a program are correct then you can be much more confident in the results. It is from testing individual steps that this pattern gets its name 'unit' testing. In our case, the units will be individual formulae.

With Python code, the standard way of unit testing is to use a framework called unittest. This is included with Resolver, and once you know how to use it it very simple.

Note

At Resolver we have used unittest to create a large framework for testing Resolver itself. At the last count there were over wighty thousand lines of code and around three and a half thousand tests! Writing tests is an important part of how we write Resolver, which you can read about in the article Developing Resolver.

Resolver spreadsheets hasve a useful feature that makes them particularly easy to test. When you export them as a class you get an opportunity to supply your own set of data (constants) for the formulae to be calculated with. By default they use the constants set in the spreadsheet, but you can override these and supply your own.

In this article we will be setting known data in the spreadsheets and testing that the formulae produce the correct answers.

TestableSheet

A testable spreadsheet, two tables with formulae.

The first of our two examples uses a spreadsheet called TestableSheet.rsl. It is based on the Cash Balance spreadsheet. It has a table of income and a table of outgoings. It then calculates three values based on this data:

Total income (cell C22): all the values in column C
Total outgoings (cell C23): all the values in column G
Balance: total income minus total outgoings

In addition, if the balance is negative then this cell is shown in Red.

We are going to test all of these things.

The Files

The example zipfile contains a batchfile (TestableSheetUnitTests.bat) and a directory (TestableSheet). Drop both of these into the Resolver install directory. This will normally be somewhere like C:\Program Files\Resolver. The batch file runs the unit tests, which we will come to in a moment.

The directory contains:

The spreadsheet: TestableSheet.rsl
The spreadsheet exported as a class: TestableSheet.py
The unit tests: TestableSheetTest.py
An empty 'package file' so that we can import from inside this directory __init__.py

The batch file runs the tests using the bin\RSIronPython.exe utility, which is not much more than a jumped up IronPython interpreter.

The directory needs to be inside the Resolver directory so that it can import the Library modules that exported spreadsheets rely on. Assuming a standard Resolver install, you can move your spreadsheet tests anywhere so long as you add the following code to the start of the tests:

import sys
sys.path.append(r'c:\Program Files\Resolver')
sys.path.append(r'c:\Program Files\Resolver\python24')
sys.path.append(r'c:\Program Files\Resolver\ply')

Testing Spreadsheets

Testing spreadsheets in this way relies on us being able to import the spreadsheet from a Python file. We can then provide a specific set of data and then check that the results of formulae are what we expect them to be.

When a Resolver spreadsheet is exported as a Python class, you get access to the spreadsheet by importing the Spreadsheet class.

This class has a recalculate method on it. Calling this without arguments calculates the spreadsheet with all the values (the constants) that were set in the worksheets. We can override this by providing our own constants instead - as a dictionary.

from SpreadsheetExportedAsClass import Spreadsheet

Constants = {
    'Sheet1': {
        (1, 1): 'Cell A1 in Sheet1',
        (2, 2): 20,
        (4, 4): 99.5
    }

    'Sheet2': {
        (1, 1): 'Cell A1 in Sheet2',
        (1, 2): 1,
        (1, 3): 2,
        (1, 4): 3
    }

    'Sheet3': {}
}

workbook = Spreadsheet()
workbook.recalculate(Constants=Constants)

print workbook['Sheet1'].A1

The first thing that recalculate does is to reset all the cells, so if we want we can call it multiple times on the same spreadsheet with different data.

Unit Testing

Unit testing with the unittest is pretty straightforward, and I'm not going to explain it in detail as you can always look at the example files.

The basic principle is that you create a test class (that inherits from unittest.TestCase). You then pass this to a test runner, which executes every method whose name begins with test. If any of these tests raise an exception then they are marked as either FAIL or ERROR depending on the type of error [1].

Inside the test methods we can setup a spreadsheet with some test data and then make assertions about what the state of the spreadsheet should be. If the assert fails, then an AssertionError is raised and that test is marked with a fail.

Once all the tests have run, the results are summarised and presented to you. In the test file here I have set a higher than normal verbosity level so that you can see each method as it is run:

The code that runs the tests is shown below.

Our test class, suite.addTests(unittest.makeSuite(TestableSheetTest)) is added to a test suite and then passed to the TextTestRunner. It isn't really necessary to understand this, just to realise that we could create a test suite from multiple testcases if we want.

Lets look at the individual tests now, to see how the magic is done.

Test Worksheets and Configuration

The tests are located in the file TestableSheetTest.py. You can view or edit this file with any text editor or Python IDE.

Our tests are unit tests, this means that we should test the smallest units that make sense in any one test. That way if a test fails it points us to the exact problem. They also allow us to build the tests up incrementally, starting with the simplest behaviour and adding tests for the more sophisticated formulae (etc) as the spreadsheet grows.

The first two tests test that our spreadsheet has the right number of worksheets and that the important sheet, "Sheet1", is configured correctly:

def testWorksheets(self):
    workbook = Spreadsheet()
    workbook.recalculate()

    # Get a list of all the worksheet names
    worksheets = [sheet.Name for sheet in workbook]

    # Check that the list is correct!
    self.assertEquals(worksheets, ['Sheet1', 'Sheet2', 'Sheet3'],
                      "Incorrect worksheets in spreadsheet")

def testBasicSpreadsheetConfiguration(self):
    workbook = Spreadsheet()
    workbook.recalculate()

    # Sheet1 should have ShowBounds set to True
    self.assertTrue(workbook['Sheet1'].ShowBounds, "Sheet1.ShowBounds is not set to True")

    # And ShowGrid to False
    self.assertFalse(workbook['Sheet1'].ShowGrid, "Sheet1.ShowGrid is not set to False")

You can see that they both start by creating an instance of our Spreadsheet class and recalculating it. Our other tests will follow the same pattern.

Income & Outgoings

The data in the spreadsheet is contained in two tables - income and outgoings. All the items of income have their value in column C, the outgoings in column E.

Below the two tables are two formulae. Cell C22 should be the sum of all the income and C23 should be the sum of all the outgoings.

To test this, we will put some known data into column C, and then test that after recalculating the value in C22 is what we expect. Then we repeat for column E.

def testTotalIncome(self):
    Constants = {
        'Sheet1': {
            (3, 4): 1,
            (3, 5): 2,
            (3, 6): 3,
            (3, 7): 4,
        }
    }
    workbook = Spreadsheet()
    workbook.recalculate(Constants=Constants)
    self.assertEquals(workbook['Sheet1'].C22, 10, "Total Income calculated incorrectly")

The sum of 1, 2, 3 and 4 is 10 - so this test will only pass if the value in C22 is 10. The next test, testTotalOutgoings, is identical except that it puts the data into column 7 and tests the value in cell C23.

Balance

Cell C24 contains a formula which calculates a balance for the spreadsheet. Unsurprisingly it subtracts the total outgoings from the total income.

Unit testing this formula is also easy. We put a single value into columns C and E and test that after a recalculate the value in cell C24 is the difference.

def testBalance(self):
    Constants = {
        'Sheet1': {
            (3, 4): 200,
            (7, 4): 100
        }
    }
    workbook = Spreadsheet()
    workbook.recalculate(Constants=Constants)
    self.assertEquals(workbook['Sheet1'].C24, 100, "Balance calculated incorrectly")

Balance BackColor from User Code

In this spreadsheet there is also a small piece of user code that sets a red BackColor on cell C24 if the balance is negative. There are two things to test here:

If the balance is positive C24 should not have a red BackColor
If the balance is negative C24 should have a red BackColor

def testBalanceBackColor(self):
    PositiveBalance = {
        'Sheet1': {
            (3, 4): 100,
            (7, 4): 99
        }
    }
    workbook = Spreadsheet()
    workbook.recalculate(Constants=PositiveBalance)
    self.assertNotEquals(workbook['Sheet1'].Cells.C24.BackColor, Color.Red, "Incorrect BackColor for positive balance")

    NegativeBalance = {
        'Sheet1': {
            (3, 4): 99,
            (7, 4): 100
        }
    }
    workbook.recalculate(Constants=NegativeBalance)
    self.assertEquals(workbook['Sheet1'].Cells.C24.BackColor, Color.Red, "Incorrect BackColor for negative balance")

[1]	A test is marked as a FAIL if an `AssertionError` is raised, any other kind of exception is an ERROR.

Return to Top

Last edited Mon Dec 10 21:12:13 2007.