Mark Nichols

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.

Recently I had to create a demo for a presentation where I could show how we created automated functional testing for a multi-language WPF application.  I have lead testing teams that developed automated tests for a WPF app but unfortunately, I didn’t have much experience in the actual creation of a multi-language WPF GUI so I worked to figure it out.  I did some research using Bing and discovered that there isn’t a definitive mechanism for providing multi-language capability in a WPF application.  I decided that I would use static WPF binding to the text resource files for the non-dynamic text.  It is easy to do and was very effective for my presentation.

So, I created my app as well as the language-based resource files (English as the default, Spanish and even Japanese).  I left the resource files empty.  I didn’t fill them in because I wanted to manage the text in a single place and use Excel to create my “single source of the truth”.  With Excel I can use the database-like table structure to manage all of the translations.  It would then be the source for all of my automated tests.  As long as the translations are correct, I can assert the Excel values against the application output.

Since I was putting all of the text into Excel, I still needed to be able to copy all of the key/value pairs into the resource files.  Laziness (and the need for accuracy) kept me from thinking that I would do all of that copying manually so Powershell to the rescue.  I created a script that would walk through all of the entries in my Excel spreadsheet and insert the appropriate key/value pairs into the associated language resource file.

Here is the project including the resource file naming conventions.  Resources.resx is the “default” language file and in this app it is English.  Default means that if there is a failure to locate an entry in the specified culture/language then an attempt to find an entry in the default file will be performed.  If it is found then it will be used.

I broke the script down into a couple major steps:

1. Get the data from Excel using standard database connections and place the data in a dataset for later use
2. Loop on each language that I specify in the script
3. Generate the resource file (resx) for each language

Here is the Powershell code to extract the Excel data into a dataset table:

# function: Get-ExcelLanguageData

# Extract the ID and text values from the spreadsheet

# This data will be directly used to create the region

# specific resource files later

function Get-ExcelLanguageData {

    param( [string] $excelFilePath )

    # Use OleDb to open the Excel spreadsheet

    $selectStatement = "Select * from [$sheetName] Where TextId <> `"`""

    $connection = New-Object Data.OleDb.OleDbConnection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$excelFilePath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

    $command = New-Object Data.OleDb.OleDbCommand $selectStatement, $connection

    $connection.Open()

    $dataAdapter = New-Object Data.OleDb.OleDbDataAdapter $command

    $dataSet = New-Object Data.DataSet

    [Void] $dataAdapter.Fill($dataSet)

    $connection.Close()

    return $dataSet

}

It takes, as input, the full path of the Excel spreadsheet, opens it using an OleDB connection string and fills a dataset with the data from the specified spreadsheet.

Next is the Powershell code to generate a resource file.  It deletes any current entries within the resx file before creating all new entries with the data from Excel.  This code assumes that the resx files already exist in the application.

# function Create-ResxFile

# Use the region-specific language data (the dataset created from Excel) 

#  and modify the EXISTING resource files from my C# project.

function Create-ResxFile {

    param ( [string] $resxFilePath, [Data.DataSet] $excelData, [string] $languageColumn )

    $xmlDoc = new-object "System.Xml.XmlDocument"

    $xmlDoc.Load($resxFilePath)

    $dataNodeList = $xmlDoc.selectNodes("/root/data")

    # Remove any existing data nodes since they will all be replaced with the data from excel

    foreach($dataNode in $dataNodeList)

    {

        $dataNode.parentNode.removeChild($dataNode);

    }

    # Create each of the "data" elements in XML - this is where the 

    #  text data resides with the resource key

    foreach ($row in $excelData.Tables[0])

    {

        $dataElem = $xmlDoc.CreateElement("data")

        $nameAttr = $xmlDoc.CreateAttribute("name")

        $preserveAttr = $xmlDoc.CreateAttribute("xml:space")

        $preserveAttr.Value = "preserve"

        $nameAttr.Value = $row['TextId']

        $dataElem.Attributes.Append($nameAttr)

        $dataElem.Attributes.Append($preserveAttr)

        $dataElem.InnerXml = $row[$languageColumn]

        $rootNode = $xmlDoc.selectSingleNode("/root")

        $rootNode.AppendChild($dataElem);

    }

    # Save the resource file

    $xmlDoc.Save($resxFilePath)

}

The only part left is to loop through each of the languages and fill the resource files.  The code below shows how I performed that step.  I provide the source location information for the Excel file, the name of the spreadsheet and the language/culture information.  The “$resxRegions” Powershell variable holds the culture and column name information.  So, for a given language/culture it is paired with where to find the translated data – in other words, what column in the spreadsheet contains that language data.

# Root location of the spreadsheet and resx resource files

$projectPath = "D:\_App Dev\TechReady9_DEV320\TechReady9_DEV320"

# Filename of the Excel Translation spreadsheet

$excelDataFile = "TextResourceTranslation.xlsx"

# The name of the Excel sheet with the translation data in it

$sheetName = "TextTranslation`$"    

# Array of arrays: (region, Excel column name)

# For this script, the region is also used as the resource file (resx) filename

# Note: if the region is left blank ("") then it will become the default resource file

$resxRegions = @(("", "enUS"), ("es-ES", "esES"), ("ja-JP", "jaJP"))

# This is the subfolder where the resx files are to be found/stored

$resxSubfolder = "Properties"

# This creates the full path for the Excel translation data file

[string] $excelFullPath = [System.IO.Path]::Combine($projectPath, $excelDataFile)

# Get the data from Excel and store in a dataset

[Data.DataSet] $excelData = Get-ExcelLanguageData $excelFullPath

# Loop through the regions and build the resx resource files

#  extracting the data from the Excel spreadsheet 

#  and modifying/adding to the xml in the resource file 

foreach($regionData in $resxRegions) {

    [string] $resxFilename = ""

    if ($regionData[0] -eq "") {

        $resxFilename = "Resources.resx"

    } else {

        $resxFilename = "Resources.$($regionData[0]).resx"

    }

    # Create the full path to the resource file

    [string] $resxFullPath = [System.IO.Path]::Combine($projectPath, $resxSubfolder)

    $resxFullPath = [System.IO.Path]::Combine($resxFullPath, $resxFilename)

    # Use the dataset to generate the resource 

    Create-ResxFile $resxFullPath $excelData $regionData[1]

}

Here is the full script:

# ResourceGenerator.ps1

#

# Summary: Script that will extract region-specific text data from an Excel

# spreadsheet and update resource files.  This allows you to create a master

# spreadsheet that contains all of your translated application text data in one

# central location.  The data in the resx files are then slaves to the 

# spreadsheet.  This way you have one source of the "true" text data within

# a multi-language application.

# NOTE: This script will not work in 64bit Powershell because of the lack of a

#  64bit OleDb driver for Excel.  It will work on (in fact was developed on) a 

#  64bit machine - you just have to run 32bit Powershell.

#

# Steps to make this work in your application are toward the bottom of this script

# function: Get-ExcelLanguageData

# Extract the ID and text values from the spreadsheet

# This data will be directly used to create the region

# specific resource files later

function Get-ExcelLanguageData {

    param( [string] $excelFilePath )

    # Use OleDb to open the Excel spreadsheet

    $selectStatement = "Select * from [$sheetName] Where TextId <> `"`""

    $connection = New-Object Data.OleDb.OleDbConnection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$excelFilePath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

    $command = New-Object Data.OleDb.OleDbCommand $selectStatement, $connection

    $connection.Open()

    $dataAdapter = New-Object Data.OleDb.OleDbDataAdapter $command

    $dataSet = New-Object Data.DataSet

    [Void] $dataAdapter.Fill($dataSet)

    $connection.Close()

    return $dataSet

}

# function Create-ResxFile

# Use the region-specific language data (the dataset created from Excel) 

#  and modify the EXISTING resource files from my C# project.

function Create-ResxFile {

    param ( [string] $resxFilePath, [Data.DataSet] $excelData, [string] $languageColumn )

    $xmlDoc = new-object "System.Xml.XmlDocument"

    $xmlDoc.Load($resxFilePath)

    $dataNodeList = $xmlDoc.selectNodes("/root/data")

    # Remove any existing data nodes since they will all be replaced with the data from excel

    foreach($dataNode in $dataNodeList)

    {

        $dataNode.parentNode.removeChild($dataNode);

    }

    # Create each of the "data" elements in XML - this is where the 

    #  text data resides with the resource key

    foreach ($row in $excelData.Tables[0])

    {

        $dataElem = $xmlDoc.CreateElement("data")

        $nameAttr = $xmlDoc.CreateAttribute("name")

        $preserveAttr = $xmlDoc.CreateAttribute("xml:space")

        $preserveAttr.Value = "preserve"

        $nameAttr.Value = $row['TextId']

        $dataElem.Attributes.Append($nameAttr)

        $dataElem.Attributes.Append($preserveAttr)

        $dataElem.InnerXml = $row[$languageColumn]

        $rootNode = $xmlDoc.selectSingleNode("/root")

        $rootNode.AppendChild($dataElem);

    }

    # Save the resource file

    $xmlDoc.Save($resxFilePath)

}

# Steps to make this work for your application"

#

# 1: Review the "TextResourceTranslation.xlsx" spreadsheet in this project to 

#     get a good feeling for how this script works with the column/row values

#     within it.

# 2: Create your own translation spreadsheet or modify the one provided

# 3: Substitute location path values below to match your project location

# 4: Substitute appropriate filenames

# 5: Substitute the name of the spreadsheet ($sheetName - this is the name from

#     the Excel workbook. In other words, the name on the tab at the bottom of

#     Excel.  In a new spreadsheet this defaults to "Sheet1".

# 6: Modify the $resxRegions array values.  The first value is the name 

#     of the resource (resx) file.  The second value is the column name of the 

#     appropriate language text within the spreadsheet.

# Root location of the spreadsheet and resx resource files

$projectPath = "D:\_App Dev\TechReady9_DEV320\TechReady9_DEV320"

# Filename of the Excel Translation spreadsheet

$excelDataFile = "TextResourceTranslation.xlsx"

# The name of the Excel sheet with the translation data in it

$sheetName = "TextTranslation`$"    

# Array of arrays: (region, Excel column name)

# For this script, the region is also used as the resource file (resx) filename

# Note: if the region is left blank ("") then it will become the default resource file

$resxRegions = @(("", "enUS"), ("es-ES", "esES"), ("ja-JP", "jaJP"))

# This is the subfolder where the resx files are to be found/stored

$resxSubfolder = "Properties"

# This creates the full path for the Excel translation data file

[string] $excelFullPath = [System.IO.Path]::Combine($projectPath, $excelDataFile)

# Get the data from Excel and store in a dataset

[Data.DataSet] $excelData = Get-ExcelLanguageData $excelFullPath

# Loop through the regions and build the resx resource files

#  extracting the data from the Excel spreadsheet 

#  and modifying/adding to the xml in the resource file 

foreach($regionData in $resxRegions) {

    [string] $resxFilename = ""

    if ($regionData[0] -eq "") {

        $resxFilename = "Resources.resx"

    } else {

        $resxFilename = "Resources.$($regionData[0]).resx"

    }

    # Create the full path to the resource file

    [string] $resxFullPath = [System.IO.Path]::Combine($projectPath, $resxSubfolder)

    $resxFullPath = [System.IO.Path]::Combine($resxFullPath, $resxFilename)

    # Use the dataset to generate the resource 

    Create-ResxFile $resxFullPath $excelData $regionData[1]

}

There are additional instructions included within the Powershell file showing what variables to modify to point the script towards your spreadsheet data and Visual Studio project.