How to read every row in a column in Excel for a data driven unit test

Question

I've setup data driven unit tests in my solution. It's wired up, everything is working fine, which is what most of the articles and questions online seem to be focused around.

What I cannot find is how I read subsequent rows after the first row in any given column.

For example if my excel document is setup like so:

  A    |   B
------ | ------
  A1   |   B1
  A2   |   B2
  A3   |
  A4   |

Then back in my unit test I do something like:

var getValue = TestContext.DataRow["A"];
var getOtherValue = TestContext.DataRow["B"];

The output is that getValue = "A1" and getOtherValue = B1. If I try to do something like this instead:

var getValue = TestContext.DataRow[0];
var getOtherValue = TestContext.DataRow[1];

The outputs are still exactly the same, which makes sense.

What I am confused with is how to get the value in A2, A3, B2, and so on. That way I can test all of the different scenarios by just plugging them into excel.

For another example, if I have this table:

 Cost  |  QtyOrdered 
------ | ------------ 
  10   |   10       
  5    |   null
 null  |   2
 null  |   null

In the above scenario I would like to run through all of the different rows and make sure that the values plugged in are what I expect. The first row I would expect 100, and in every extra row I would expect an error message in my unit test.

I don't really see the point of data driven unit tests in excel if I can't just plug in a bunch of data in for multiple rows for each column.

See if this helps you: http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp — , Jan 17 '17 at 14:04
You should give a try to EPPlus, this library give you acces to your worksheet actual dimensions for rows and columns (for example here 4 and 2) — yan yankelevich, Jan 17 '17 at 14:31
@JohnG `DataRow` is simply the index of the column, or the value in the first row (in our case `A`, `B`, etc). I am not dependent on any external libraries for this, it's all built in functionality for .NET's Unit Tests. I did actually answer my question (see below) — maxshuty, Jan 17 '17 at 20:51

score 0 · Answer 1 · answered Jan 17 '17 at 19:04

You can import the data from your Excel sheet and run your tests over the resultant data set using loops or any mechanisms that suit your needs. I personally prefer ClosedXml because of it's usability and speed. Alternatively, you can use the native Microsoft OLEDB library to get it done. Good luck!

score 0 · Accepted Answer · answered Jan 17 '17 at 19:58

Doing data driven unit tests like this will call your method one time for every row in your spreadsheet. If you have everything wired up properly then there is no need for anything else.

I'll leave this question up since I feel like it could be useful to someone else in the future since it wasn't immediately apparent how this worked.

How to read every row in a column in Excel for a data driven unit test

2 Answers2