Spreadsheet data-driven tests have the advantage over database data-driven tests in that the source data is very easy to setup. You can use Excel or OpenOffice (saving the spreadsheet as a .XLS file) to create the source data in a worksheet and then tell VSTS to repeatedly run the test for each row in the sheet.

There are a few caveats when working with spreadsheets. The first is that EVERY CELL MUST BE FORMATTED FOR TEXT. If you fail to do this then the ADO.NET data adapter that is reading the spreadsheet will infer the type from the content of the first data row for the cell. If you have a column that contains the heading "Text" on row 1, the data value 42 on row 2 and the data value Hello on row 3 then you will get an exception when row 3 is read because the number on row 2 has fooled the data adapter into thinking ALL of the cells are numbers. So rule one, absolutely definitely never break it, when you start a new worksheet press Ctrl+A to select all cells and then set the format to TEXT.

Once you're past that it gets a lot easier. In your test class you need to declare the public read/write property TestContext of type TestContext. This is a magic property that is filled by VSTS at the start of the test and it will carry the data from the spreadsheet to your test.

To pull data from a worksheet in a test you add this attribute to the test method, replacing SPREADSHEET.XLS with the name of your spreadsheet and WORKSHEET$ with the name of the worksheet to read, suffixed with a dollar sign:

[DataSource("Data Source='SPREADSHEET.XLS';Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Extended Properties='Excel 8.0'", "WORKSHEET$")]

You then create an instance of ExcelWorksheetData at the start of the test and use the read methods to pull data in, usually by column name. The read methods are all named after the type that they cast the value to, read methods for nullable value types all start with a capital N. There are special methods for reading strings where you need to be able to return either null or an empty string, methods to parse the names of enum values into the correct type and methods to parse a cell's text into a byte array.

Finally you need to get the spreadsheet copied into the test deployment folder by VSTS so that the test can use it. The easiest way to do this is to create a folder on a test project that contains all of your spreadsheets (Virtual Radar Server has a folder called TestFiles under the Test.Framework project for this) and then edit the test configuration file (in VSTS either double-click LocalTestRun.testrunconfig in Solution Items or use the Test | Edit Test Settings menu entry) and add the sub-folder with the spreadsheet(s) in to the Deployment section.

This example tests the standard ASCIIEncoding object's GetString method. Assume that we have a spreadsheet called Tests.xls that contains a worksheet called TestData with the following rows and columns (all formatted as TEXT):

The full code for the test class might be:

[TestClass]
public class ExampleTest
{
    public TestContext TestContext { get; set; }

    [TestMethod]
    [DataSource("Data Source='Tests.xls';Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Extended Properties='Excel 8.0'",
    "TestData$")]
    public void ASCIIEncoding_GetString_Converts_Byte_Arrays_To_Strings()
    {
        var worksheet = new ExcelWorksheetData(TestContext);

        // You could do this in one line, as below, but to make it clearer we will spell it out step-by-step:
        // Assert.AreEqual(worksheet.String("Text"), Encoding.ASCII.GetString(worksheet.ParseBytes("Bytes")));

        byte[] input = worksheet.ParseBytes("Bytes");    // Read string in cell "Bytes" and parse into a byte array
        string expected = worksheet.String("Text");      // Read content of "Text" cell as a string
        string actual = Encoding.ASCII.GetString(input);

        Assert.AreEqual(expected, actual);
    }
}

When you run the test you will get 4 executions (one per row plus one for the overall test). In this case all 4 will pass. If one failed then you would get 3/4 passed, and so on. Double-clicking on the row for the test in TestResults will show a list of results for each row. Add two to the row number to translate from the row number in TestResults to the row number in the spreadsheet (e.g. row number 0 in TestResults corresponds to row 2 in the spreadsheet).

If the intent of a test is not clear from the input and output values listed in the spreadsheet row then you may want to consider adding a 'Comments' column to the spreadsheet and describe in there what it is that you're trying to assert.

Reference