Powerful Excel Data Driven Tests with NUnit

Creating a good suite of automated data driven tests calls for an easier and more maintainable way for inputing data and asserting output results - it should be easy for everyone (team members and even customers) to modify and create new scenarios.

Often the complexity involved on scenario creation is not technical at all, the business rules normally makes it much more difficult. That´s why we need better tools/GUI for creating and maintaining these scenarios.

Turns out spreadsheet softwares like Excel happens to be the right tool for the job - well known GUI, capable of performing complex calculations and very good for organizing and structuring data. Then, why not take advantage of such powerful tools integrating them with an XUnit framework? Well that´s what this post is about.

Integrating NUnit Parameterized Tests and Excel Data Reader

Recently I had the same need and since I´m on a .NET project, I came up with a helper class for creating NUnit´s data driven testcases from Excel spreadsheets: ExcelTestCaseDataReader (this could probably be achieved by using any XUnit framework and Excel library in any language or plataform).

ExcelTestCaseDataReader basically provides a fluent interface (this was my first time applying the builder pattern for creating such an interface so don´t be too demanding) which encapsulates the complexity for reading excel spreadsheets - either from an embedded resource or from the file system, using the (very good) Excel Data Reader library - and turns them into NUnit´s testcases.

Fortunately NUnit has some very good and flexible built-in features which help us creating this kind of parameterized tests. Combining NUnit´s TestCaseSource attribute and the ExcelTestCaseDataReader does the magic.

Setting things up

The example below shows how to create and configure an ExcelTestCaseDataReader instance which will load the tests.xls workbook from the file system and include the content of Sheet1 and Sheet2.

var testCasesReader = new ExcelTestCaseDataReader()
.FromFileSystem(@"e:\testcases\tests.xls")
.AddSheet("Sheet1")
.AddSheet("Sheet2");

Then , to get the TestCaseData list, you should invoke the GetTestCases method passing it a delegate which should know how to extract the details from each row and transform them into a TestCaseData.

var testCases = testCasesReader.GetTestCases(delegate(string sheetName, DataRow row, int rowNum)
 {
      var testName = sheet + rowNum;
      IDictionary testDataArgs = new Hashtable();
      var testData = new TestCaseData(testDataArgs);
      return testData;
  }
);

Adding to NUnit

Now you just need a test method which uses the TestCaseSource attribute. There are numerous options for yielding the data, and one of them is to define an IEnumerable as a public method of your test class. In the example below the MyTest method takes its TestCases from a static method called "SampleTestCaseData".

[Test]
[TestCaseSource("SampleTestCaseData")]
public void MyTest(IDictionary testData)
{
  //do the assertions here
}     

And finally the SampleTestCaseData snippet. It just iterates on the testCases list we created earlier (with ExcelTestCaseDataReader) and yields each of them.

public static IEnumerable<TestCaseData> SampleTestCaseData
{
    get
    {
        foreach (TestCaseData testCaseData in testCases)
        {
            yield return testCaseData;
        }
    }
}

Conclusion

NUnit is a great testing tool and has great data driven tests capabilities. Sometimes you need to provide non tech people the ability to maintain test scenarios or maybe just need a better tool - with a good and user friendly GUI - for scenario creation.

The ExcelTestCaseDataReader is just a basic example of what can be achieved by integrating excel (front end) and nunit (back end). And how it could help alleviating the burden of such boring tasks.

This small framework has been very useful for me and my team and although is´s very limited it can be easily extended to achieve your own needs.

Looking forward to hear about different approaches on data driven tests automation!