NUnit test data from Excel

Tags: nunit, tdd, excel

If you have lots of test case data there are two options:

* Use the [TestCase] attribute and provide the data inline as an attribute to the test method

* Use the [TestCaseSource] source and create one source method and/or class per data source.

In my case I am creating a large scale regression test based on already processed data. The applications does a large number of caluclations based on the data and outputs them in a format similar to Excel. In fact it's exportable to Excel.

To detect the introduction of bugs I'll use calculations from and older version of the software to test newer versions. To do this I created a NUnit ad-hoc plugin (plugin contained in the test assembly itself)

Step 1: Create a new Class Project

Step 2: Add NUnit and NUnit.Runners

Install-Package NUnit
Install-Package NUnit.Runners

Step 3: Add references to Nunit.Core.Interfaces.dll

You can find these in the packages\NUnit.Runners.2.6.3\tools\lib folder

Step 4: Create a class for the addin

This is the class that registers the test case provider

public class ExcelTestCaseAddin : IAddin { public bool Install(IExtensionHost host) { var testCaseProviders = host.GetExtensionPoint("TestCaseProviders"); testCaseProviders.Install(new ExcelTestCaseProvider()); return true;
} }

Step 5: Create the Excel test case provider

public class ExcelTestCaseProvider : ITestCaseProvider2
        public bool HasTestCasesFor(MethodInfo method)
            return method.GetCustomAttributes(typeof(ExcelTestCaseSourceAttribute)).Any();

        public IEnumerable GetTestCasesFor(MethodInfo method)
            var attributes = method.GetCustomAttributes(typeof (ExcelTestCaseSourceAttribute));

            foreach (var attribute in attributes)
                var a = attribute as ExcelTestCaseSourceAttribute;

                if (a == null)

                using (var stream = File.Open(a.Filename, FileMode.Open, FileAccess.Read))
                using (var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
                    excelReader.IsFirstRowAsColumnNames = true;

                    /* Skip first row */

                    while (excelReader.Read())
                        var first = excelReader.GetDecimal(0);
                        var second = excelReader.GetDecimal(1);
                        var expectedResult = excelReader.GetDecimal(2);

                        var data = new TestCaseData(first, second, expectedResult);

                        yield return data;

        public bool HasTestCasesFor(MethodInfo method, Test suite)
            return HasTestCasesFor(method);

        public IEnumerable GetTestCasesFor(MethodInfo method, Test suite)
            return GetTestCasesFor(method);

Step 6: Create the ExcelTestCaseSourceAttribute

public class ExcelTestCaseSourceAttribute : Attribute
        public string Filename { get; private set; }

        public ExcelTestCaseSourceAttribute(string filename)
            Filename = filename;

Step 7: Create the Book1.xlsx with data as below and add it to the project. Set the Copy to Output Directory property to Copy Always

First Second Result
1 2 3
2 3 5

Step 8: Create the Test fixture

    public class Tests
        [Test, ExcelTestCaseSource("Book1.xlsx")]
        public void Add_Two_Numbers_Should_Return(decimal first, decimal second, decimal expectedResult)

            var classUnderTest = new AddTwoNumbers();

            var r = classUnderTest.Add(first, second);

            Assert.AreEqual(expectedResult, r);

    public class AddTwoNumbers

        public decimal Add(decimal first, decimal second)
            return first + second;

Step 9: Run tests and watch them pass with test data provided by Excel

No Comments

Add a Comment