Introducing CustomerTestsExcel
Tags: bdd, csharp, excel
Published: 2020-11-06 17:58:44

---
title: Introducing CustomerTestsExcel
published: true
description: A [BDD framework to round trip Excel Customer Tests to C# NUnit Tests](https://github.com/resgroup/customer-tests-excel), specialised to numerical / analytical contexts.
tags: bdd, csharp, excel
//cover_image: https://raw.githubusercontent.com/resgroup/customer-tests-excel/master/media/example-excel-given-table.png
---
A [BDD framework to round trip Excel Customer Tests to C# NUnit Tests](https://github.com/resgroup/customer-tests-excel), specialised to numerical / analytical contexts.

## The problem

[BDD](https://cucumber.io/docs/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](https://www.goodreads.com/en/book/show/34927405-living-documentation). [Cucumber](https://cucumber.io/) is the most widely used tool for creating these examples / tests, using [Gherkin syntax](https://cucumber.io/docs/gherkin/reference/).

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](https://github.com/resgroup/customer-tests-excel) 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](https://github.com/resgroup/customer-tests-excel-scaffolding). 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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/ExcelTests/Vermeulen%20Near%20Wake%20Length.xlsx?raw=true) 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).

![Example table property in CustomerTestsExcel](https://raw.githubusercontent.com/resgroup/customer-tests-excel/master/media/example-excel-given-table.png)

The example [Anova](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/ExcelTests/Anova.xlsx?raw=true) 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.

![Example of supporting calculations in CustomerTestsExcel](https://raw.githubusercontent.com/resgroup/customer-tests-excel/master/media/example-excel-supporting-calculations.png)

### Generating the C# NUnit tests

The tests themselves depend on the [CustomerTestsExcel](https://www.nuget.org/packages/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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/GenerateTests.bat).

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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/Vermeulen%20Near%20Wake%20Length/VermeulenNearWakeLength.cs).
- A __Setup Class__ file for each interface that it finds in `/assembliesUnderTest` that matches an Entity in Excel. For example [Group.cs](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/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.

``` csharp
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](https://github.com/ceddlyburge/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/VermeulenNearWakeLengthCalculatorPartial.cs) in the example repo is a good example of this.

```csharp
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](https://github.com/ceddlyburge/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/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](https://github.com/ceddlyburge/customer-tests-excel-scaffolding/blob/master/SampleTests/Setup/Anything.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](https://github.com/resgroup/customer-tests-excel-scaffolding/blob/master/SampleTests/SampleTests.csproj). 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](https://github.com/resgroup/customer-tests-excel/blob/master/appveyor.yml) of the scaffolding repo, and below.

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

## Conclusion

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

The [CustomerTestsExcel framework](https://github.com/resgroup/customer-tests-excel) 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.