Introducing CustomerTestsExcel

A BDD framework to round trip Excel Customer Tests to C# NUnit Tests, specialised to numerical / analytical contexts.

The problem

BDD invovles focusing collaborative work around concrete, real-world examples, that illustrate how a system should behave. The examples are executable as tests, and can be understood by the whole team (programmers, testers, business analysts, subject matter experts and so on). This means that the examples are always up to date and serve as the Single Source of Truth, and as Living Documentation. Cucumber is the most widely used tool for creating these examples / tests, using Gherkin syntax.

This all works extremely well for specifying business centric code, such as Given I am logged in as Greg, When I try to post to 'Expensive Therapy', Then I should see 'Your article was published'. However, the code I work with is mostly calculation centric, and all the existing tools are much less useful when trying to specify complex physics, statistics or maths. In these cases there can be a lot of input and output data for a calculation, and it is also important to see the intermediate steps. For example Given I use the sample ERA5 data, When I run the roughness calculation, Then the result should be 5 doesn't communicate very much.

So for calculation heavy code, we need a different way to communicate. This communication is necessarily calculation centric, so the whole team needs to have some mathematical skills, but in the context of technical software, this is very likely to be the case.

The framework

The CustomerTestsExcel framework aims to solve this problem by using Microsoft Excel to define the examples / calculations, which has a number of advantages:

• Excel is ubiquitious, and familiar to most technical people
• Large calculation inputs and outputs can be specified easily
• Excel supports any complexity of calculation
• All the intermediate steps of a calculation are visisble
• Named ranges can be used to make calculations more expressive, for example: SQRT(POWER(ShearTurbulenceWakeErosionRate,2)+POWER(AmbientTurbulenceWakeErosionRate,2)+POWER(MechanicalWakeErosionRate,2))
• Explanatory comments can be added to Excel

The framework then generates C# Unit tests from Excel, which can be run just like normal and integrate well with build servers and code coverage and similar. The framework has two other features that serve to strengthen the link between Excel and C#.

Round tripping: The C# tests can optionally round trip back to Excel, recreating the Excel files as they are run. This allows you to use automated refactoring tools in C#, to say rename a function, and then to recreate the Excel files with the new name.

Auto Generating Setup Files: The framework can scan assemblies / dlls, and match interfaces with entities found in Excel.

These features mean that only limited mapping code is required, and that there is a strong incentive to keep the names consistent, which aids communication between programmers and non programmers.

This brings the well known benefits of BDD to the previously out of reach world of calculation centric code.

• The examples are executable as tests, and can be understood by the whole team (programmers, testers, business analysts, subject matter experts and so on)
• The examples are always up to date and serve as the Single Source of Truth, and as Living Documentation

Getting Started

The easiest way to get started, is to clone / download the scaffolding project. It has numerous examples, and everything supported by the framework is represented at least once (the examples also function as end to end tests of the framework). The projects in it can be used independently or can be added to your own solutions, as a starting point, and as a reference.

Writing Excel test cases

Each sheet that has "Specification" in A1 becomes a test. Other sheets can be used for supporting data / calculations. Primitives, objects and lists / tables are supported to any level of nesting (except that tables can't be nested within tables, and objects within tables can't round trip).

The example Vermuelen wake length spreadsheet is a good example of a physics calculation based on tabular data, with tabular expected values. The excerpt below shows a a table of data being set up, where one of the columns (TurbineGeometry) is an object (as opposed to a primitive).

The example Anova spreadsheet is a good example of a statistics calculation, using the power of Excel to show all the intermediate steps. The excerpt below shows supporting calculations alongside each row of a table. The framework requires a blank cell after the values you are setting up, but after that you can use the power of Excel to your heart's content.

Generating the C# NUnit tests

The tests themselves depend on the CustomerTestsExcel nuget package, which also contains GenerateCodeFromExcelTest.exe to create the C# from Excel. This is installed to "<nuget folder>\customertestsexcel\2.0.4\tools\GenerateCodeFromExcelTest\", and takes the following parameters:

• /folder The folder where the tests should be generated, for example "..\SampleTests"
• /namespace The namespace that you would like the tests to use, for example "SampleTests"
• /usings Any using statements that the test will require to access the code under test, for example "SampleSystemUnderTest SampleSystemUnderTest.AnovaCalculator ..."
• /assertionClassPrefix A optional prefix added to Excel assertion names. When used, it is usually "I", with the expectation that the names map to interfaces
• /assembliesUnderTest Any assemblies to scan to find interfaces that match entities found in Excel. Sadly these must be entered as absolute paths, due to a limitation in C# Reflection. For example "C:\code\SampleSystemUnderTest\obj\Debug\SampleSystemUnderTest.dll"

You can see an example of this in the scaffolding repo.

When run, the tool will generate the following:

• A Test Class for each sheet / tab in any of the Excel spreadsheets. For example VermeulenNearWakeLength.cs.
• A Setup Class file for each interface that it finds in /assembliesUnderTest that matches an Entity in Excel. For example Group.cs, in the Anova calculation
• A Example Setup Class file for each Entity in Excel where no matching interface is found in /assembliesUnderTest. For example A_Table.cs
• A Root Setup Class for each root class that it finds in Excel (The first entity in an Excel sheet, to the right of "Given A"). For example VermeulenNearWakeLengthCalculator.cs.

Adding Custom C# Code

Test Classes do not require any custom modifications.

Setup Classes may not have matched everything in Excel, in which case you can add a partial class to fill in the gaps. The convention is to name the file "<Filename>Partial.cs", like ClassWithCustomPropertyPartial.cs in the scaffolding repo. This allows you to perform complex custom setup in code without exposing the complexity in Excel. This is obviously a double edged sword, so use with due awareness.

public partial class SpecificationSpecificClassWithCustomProperty
{
    internal SpecificationSpecificClassWithCustomProperty CustomInt_of(int value)
    {
        // The framework requires you to do this
        AddValueProperty(GetCurrentMethod(), value);

        // And then you can do some custom thing with `value` here
        // classWithCustomProperty.Setup(m => m.Name).Returns(value);

        // The framework requires you to do this (the syntax is fluent)
        return this;
    }
}

Example Setup Classes don't set anything up in your system under test, but they do record all the things that have been set up on them, which might be all you need in some contexts.

Root Setup Classes always require custom code, in order to exercise the system under test, and to return the results (The "When" and "Then" sections of BDD tests). VermeulenNearWakeLengthCalculatorPartial.cs in the example repo is a good example of this.

public partial class SpecificationSpecificVermeulenNearWakeLengthCalculator
{
    // For the "Then" section, return the results to assert
    internal IEnumerable<IVermeulenNearWakeLength> VermeulenNearWakeLengths { get; private set; }

    // For the "When" section, exercising the system under test.
    internal void Calculate()
    {
        VermeulenNearWakeLengths = 
            new VermeulenNearWakeLengthCalculator(
                vermeulenNearWakeLengthInputss.Select(i => i.VermeulenNearWakeLengthInput).ToList()
            ).Calculate();
    }
}

You can override any of the generated code by creating a "Override<Filename>.cs" file in the "setup" folder, like OverrideAnything.cs in the scaffolding repo. This allows you to completely replace the generated code (instead of just adding to it). In this case the framework generates a OverrideAnything.cs.txt so that you can see what it would have generated, and potentially copy and paste things from it.

The framework does not generate the .csproj file, so that you can easily add your own dependencies and suchlike. You should create your own one, or use the scaffolding project example. Dotnet Core is recommended.

Round tripping Excel spreadsheets from the C# tests

Round tripping isn't required to use the framework, but is worth doing if you want to use C# refactoring tools. It is especially helpful when renaming functions in Visual Studio and pushing the changes back to Excel.

To do this, set a couple of environment variables. "CUSTOMER_TESTS_EXCEL_WRITE_TO_EXCEL" should be set to "true". "CUSTOMER_TESTS_RELATIVE_PATH_TO_EXCELTESTS" should be set to the relative location of the Excel files from the location where the tests run ("........\SampleTests\ExcelTests" for a standard setup). Then whenever you run the tests (either from the command line or through visual studio), they will recreate their associated Excel file / sheet.

Running on build server

It is probably a good idea to regenerate the C# from the Excel on the build server. That way you can be completely sure that they are synchronised with each other. The framework Auto Generates Setup Files, by scanning the assemblies you specify. This means that the assembly has to exist, and due to a restriction in C# Reflection, you need to provide an absolute path to it.

The upshot of this is that you need to build your code (to create the assemblies to be scanned), then generate the C#, and then build / test again, in case the C# has changed. You also need to pass in the absolute path of these assemblies.

You can see this in action in the appveyor.yml of the scaffolding repo, and below.

 - dotnet build CustomerTestsExcelScaffolding.sln   
 - AppveyorGenerateTests.bat
 - dotnet test

Conclusion

Existing BDD frameworks are not well suited to calculation centric code.

The CustomerTestsExcel framework fills this niche in C#, by using Excel as an ubiquitous technology, which is well known to most technical people. By Round Tripping and Auto Generating Setup Files, it strongly incentivises the same names to be used in code and Excel, strengthening the ubiquitous language of the calculations.