I know that there are a ton of resources (blogs, example code, etc.) out there that show how you can implement data driven testing into your test projects.  A lot of the implementation requirements are based on which testing framework that you use.  In this post I am going to show how you can use external data files (specifically, Excel spreadsheets) to power your tests within an MSTest framework environment.  Now, you can certainly use XML and CSV files to hold your data and each has their benefits especially if you are more familiar or have more experience with them.  But, they definitely have their drawbacks.  CSV files can be difficult to maintain and control when there is a lot of rows and fields.  XML can be hard to edit with all of the tags required for its structure.  Both typically mean that you could end up with a lot of files to deploy to the tests.  I have used all of the formats but when it gets down to it, I always end up using Excel as my main data store for my tests.

The benefits of Excel files include:

• The editor is naturally part of the environment – it’s easy to enter, copy and paste data into a spreadsheet
• Adding or deleting fields (columns) is trivial
• Auto-generating some of the data can also be trivial (incremented dates, numerics, etc. can sometimes be created by simple click and drag)
• Importing test data from other sources can be done simply – I have used the Data capabilities of Excel to pull in test data from several sources (DB, CSV, XML)
• A single file can hold just about as many spreadsheets as you want so creating a central store for many, many tests is easy
• You can utilize “conditional formatting” within Excel that gives you visual clues to data patterns within the test data (more below)
• One of the biggest benefits is that you can build rules (calculations) into the columns that can be used to generate additional test data and more importantly, create expected results that can be used as direct input into your tests

Building rules into the spreadsheet:

The reason why the ability to build rules into the spreadsheet columns is so important is that it makes the job of generating the test data more straightforward.  You can know how your tests should run ahead of time.  You can also adapt to changes in method requirements and may not have to change your tests since the expected results will change when the rules in the spreadsheet are changed.  So, if the requirements of the methods that you are testing change, there is a good chance that by modifying the spreadsheet rules your tests will continue to run.  Obviously, if the method signature changes (adding/deleting parameters, changing parameter types, etc.) then this is not the case but the end result still could be that you won’t have to change as much code in the test. (This is where I must say: individual results may differ, prices subject to change without notification, always consult with your physician and discontinue use if a rash forms).

How to implement Excel spreadsheets into your MSTest environment:

There are a couple steps to perform to get your tests to be able to utilize external data files in your tests.  The first is to Deploy the file and the second is to set up the file as a “DataSource” to your test method.  This holds true for all data file types but I will just describe how to do it with .xls – Excel 2003 or .xlsx – Excel 2007+ spreadsheet/workbook files. 

Let’s start with an example.  Below is a sample project that I put together.  It is the same project that I will be using for another blog post comparing the difference between mocks and stubs but for now we will just be looking at data driven testing with Excel.

In the TestProject, I have my tests (“CustomerBusLogicTests.cs”) and my test data in the form of .xls and a .xlsx workbook files.  The data contained in each Excel file is the same.  The only difference is how you access them.

Step 1 – Deploy:

The DeployItem attribute is the most straightforward way to deploy.  You can put the DeployItem attribute right above the test method or right above the test class.  Personally, I like placing the attribute right above the class.  This way all the deployment item files are in a single easy to find list.  For the data file(s) to be deployed properly, you should indicate the path and the filename.  Here, I am using the relative path (from the solution) into the “TestProject” folder.  Note, “TestProject” is the name of the folder and not necessarily the name of the project – see “Gotchas” below.  Since I have the data files stored in a subfolder of the project, I also include that folder name.  This is all that is necessary to deploy your test data file to the test folder.

One thing to note is that you do not have to copy the file or use it within a build action to make this work.

Step 2 – Set up the DataSource:

The DataSource attribute is how you tell the individual test method which file and which spreadsheet to use in the test.  It is basically a connection string to the data being read as input to the test plus some additional parameters that point to the specifics of how to read the data.  Since there are two spreadsheet file formats there coincidentally are two ways to access that data.  The following DataSource example is for reading Excel 2003 spreadsheet files:

[DataSource("System.Data.OleDb",
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='|DataDirectory|\\CustomerBusLogicTestData.xls';Persist Security Info=False;Extended Properties='Excel 8.0'",
         "CustomerTestData$", DataAccessMethod.Sequential)]

- DataSource is the attribute
- System.Data.OleDb indicates the type of provider that will be used to read the data
- The next line starting with “Provider” is the connection string.  In this case the “Jet” data driver is going to be used.  This is the case for .xls or Excel 2003 files.  Excel 2007/.xlsx files use the “Ace” drivers (shown below).
- Data Source identifies where to find the file.  |DataDirectory| equates to the “Out” folder that is created every time the unit tests are run.  DeploymentItem places the data file there and |DataDirectory| says go there to get the file.
- CustomerTestData$ is the name of the spreadsheet within the workbook.  Actually, the name of the spreadsheet is “CustomerTestData”.  The added dollar sign “$” is appended to the name as a requirement.  The dollar sign is not included in the actual spreadsheet name.
- DataAccessMethod.Sequential indicates how the data is to be read.  Since the data access is all managed by the MSTest framework “Sequential” is the only one that makes sense.

Here is the DataSource attribute to do the same thing but uses an Excel 2007 (.xlsx) file in the testing:

[DataSource("System.Data.OleDb",
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='|DataDirectory|\\CustomerBusLogicTestData.xlsx';Extended Properties="Excel 12.0 Xml;HDR=YES"",
            "CustomerTestData$", DataAccessMethod.Sequential)]

Step 3 – Use the data:

Once all of the attributes are in place, the data file is included and MSTest initiates the unit tests, the test method will be called once for every row of data you have in the spreadsheet.  You do not have to worry about iterating through the data yourself because MSTest manages everything, the total number of tests will coincide with the number of rows so you get a better feel for how many tests you actually run within the suite of tests.

int monthlyPurchases = int.Parse(TestContext.DataRow["MonthlyPurchases"].ToString());
CustomerStatusEnum statusEnum = (CustomerStatusEnum)int.Parse(TestContext.DataRow["Status"].ToString());

As you can see here, TestContext.DataRow is the syntax used to get at the current row.  To access the data in a particular column enter the title of the column in quotes as an index to DataRow.  Once the data field is accessed it can then be cast and/or parsed into whatever type needed for the test.

In addition to the data used as input for the test, the expected result can also be extracted from the spreadsheet…

CustomerStatusEnum expectedStatusEnum = (CustomerStatusEnum)int.Parse(TestContext.DataRow["ExpectedStatus"].ToString());

And then used to assert the success of the method under test:

Assert.AreEqual<CustomerStatusEnum>(expectedStatusEnum, custStatusReturned.Status);

Ok, you should now be able to use Excel spreadsheets to supply data to your tests.  The following is the full test method:

[DataSource("System.Data.OleDb",

    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='|DataDirectory|\\CustomerBusLogicTestData.xlsx';Extended Properties="Excel 12.0 Xml;HDR=YES"",

    "CustomerTestData$",

    DataAccessMethod.Sequential)]

[Owner("Mark Nichols")]

[TestMethod]

public void CustBusLogic_WhenPurchasesPerMonthChangeStatusShouldIncrementToAppropriateLevelXlsData()

{

    // Arrange

    var custDataProv = new Mock<ICustomerDataProvider>();

    int custNo = 123;

    string name = "Mark Tester";

    string address = "1313 Mockingbird Lane, Anytown, USA, 00000";

    int monthlyPurchases = int.Parse(TestContext.DataRow["MonthlyPurchases"].ToString());

    CustomerStatusEnum statusEnum = (CustomerStatusEnum)int.Parse(TestContext.DataRow["Status"].ToString());

    CustomerStatusEnum expectedStatusEnum = (CustomerStatusEnum)int.Parse(TestContext.DataRow["ExpectedStatus"].ToString());

    var custStatus = new CustomerStatus { Name = name, Address = address, Status = statusEnum };

    var expectedCustStatus = new CustomerStatus { Name = name, Address = address, Status = expectedStatusEnum };

    // Set behavior expectations

    custDataProv.Setup(c => c.GetCustomerStatus(custNo))

        .Returns(custStatus);

    custDataProv

        .Setup(c => c.GetPurchaseCountForMonth(custNo))

        .Returns(monthlyPurchases);

    // This method is only called if the status actually changes

    if (statusEnum != expectedStatusEnum)

    {

        custDataProv

            .Setup(c => c.SetCustomerStatus(custStatus))

            .Returns(true);

    }

    // Act

    var custBusLogic = new CustomerBusLogic(custDataProv.Object, new MessageWrapper());

    var custStatusReturned = custBusLogic.CertifyCustomerPriorityStatus(custNo);

    // Assert

    Assert.IsNotNull(custStatusReturned, "CertifyCustomerPriorityStatus failed to return a customer status object");

    Assert.AreEqual<CustomerStatusEnum>(expectedStatusEnum, custStatusReturned.Status);

    if (statusEnum == expectedStatusEnum)

    {

        custDataProv.Verify(c => c.SetCustomerStatus(It.IsAny<CustomerStatus>()), Times.Never());

    }

    custDataProv.VerifyAll();

}

Building rules into the test data spreadsheet:

As I mentioned before one of the benefits of using a spreadsheet is the fact that you can build rules into the spreadsheet that calculate expected results.  This way your test can be as simple as possible and only needs to compare (assert) values to validate the test.  In addition, you can utilize conditional formatting to help you visualize the data and the rules instead of just looking at the data values themselves.

Here is an example of a spreadsheet full of test data from an actual project.  The data has been scrubbed but still shows the intent.  In this case I entered rules and calculations in multiple locations.  Sometimes the rules can be so extensive that it makes sense to break them up into multiple steps or “gates” which are not actually used in the test method.  Below there are 3 separate gate calculations used to ultimately generate the ExpectedResult.  I also used conditional formatting in Excel to show color in addition to the values so I could visualize data patterns. 

This is the rule embedded in each cell under ExpectedResult:

=IF(J36,IF(A36="SCH",N36,IF(OR(A36="SPT",A36="OWD",A36="CTG"),FALSE,TRUE)),FALSE)

Trying to build these rules inside of a test method would make it far more complicated, more difficult to change if the business rules and requirements changed and you would spend more time on the test method trying to make sure that it worked properly given all of the possible permutations.  By the way, this sheet contained almost 600 rows of data so in the end, a single test method counts for that many individual tests.  Clearly, you can spend a significant amount of time creating the data in a spreadsheet especially when there is a lot of possible permutations but once you get the hang of it, I believe you will save time using simple copy/paste, conditional formatting, formulas and just the benefit of accessing all of your data in a centralized repository.

Gotcha’s when doing data driven testing with MSTest and Excel Spreadsheets:

• Deploying the data file: if your project name differs from the actual folder name holding the project files, you must use the actual folder name to indicate where the data files are coming from.  I have run into a couple situations where (unbeknownst to me) the project name was changed after the project was created or a different folder name was used compared to the project name when it was created.  So, after a near concussion from pounding my head into the desk while telling myself that there was nothing wrong with my code, I realized that I was pointing to a folder that didn’t actually exist.  A quick change and couple Advil later, all was working.
• Deleting rows of data from the spreadsheet:  unless you delete the actual row within a spreadsheet (not just the data in the cells) your tests may fail because of null values.  The data access mechanism that grabs data from a spreadsheet is reliant upon the metadata in the spreadsheet to know how many rows exist.  If you add data to a row and then delete that data from the cells, the spreadsheet won’t know that you do not want that row included.  So, highlight the rows, right-click and select ‘delete’ instead of just hitting the delete key.
• Multiple data types in a single Excel column: sometimes as you enter data in a spreadsheet, the values seem to disappear as they are read into the test.  This happens when (for example) you enter numeric data and then string data in the same column.  Or, for whatever reason, Excel just interprets what you enter as two different types.  If this happens, highlight the column, right-click and set the data format manually so that all value types are the same.
• Excel 2007 drivers:  You may or may not have the drivers loaded on your client or build machine to access Excel 2007 files.  Here is the link to the driver install and yes, it is called AccessDatabaseEngine.exe.  Once you install that driver your machine will be able to open and use .xlsx workbooks for data driven tests.

Here is a zipped version of the test project.  Because this app is also used to demonstrate the use of mocks and stubs it has a dependency on the Moq mocking framework so if you want to try to run the tests you will need to download version 3.1 and add the reference to the test project.