Unit Testing With Excel Data Source with Nunit

Question

I need to test some classes with Nunit by importing data from excel data source in C#,I couldn't find any helpful resource to guide me ,Is there any solution? Consider this test in Nunit

[Test]
[TestCase(new[] { -4, -3, -3, -2, -1, 0, 1, 2, 2, 3, 4 }, 
          new []{1, 0, 1, 0,1, 0, 1, 1, 0, 1, 0})]
public void YourTest(int[] given, int[] expected)  
{ 
///some code
}

I would read data from excel, If I have one excel files I put the given Values in first column and expected values in second column:

column1(in Excel)  column2(in Excel)
-4                      1
-3                      0
-3                      1
-2                      0
-1                      1
0                       0
1                       1
2                       1 
2                       0
3                       1
4                       0

The reason I want to test with excel because I need to apply some formula on column1 and my formula results appers on column2 and I don't like to copy from excel to my test class.

In MsTest we have :

[TestMethod]
  [Owner("Name")]
  [TestProperty("TestCategory", "Developer"),
      DataSource("Microsoft.ACE.OLEDB.12.0",
     "Data Source=C:/Sheets/DataSheet.xlsx;Extended Properties=Excel 12.0;",
     "[Sheet1$]",
     DataAccessMethod.Sequential)]

Do we have the same thing in Nunit ,Or we need to simulate ? How we can simpuate it ? In [Setup] section or in [TestMethod]

How do you *know* that the excel formula is correct? Have you tested that as well? — Mike Zboray, Nov 19 '12 at 21:22
http://stackoverflow.com/questions/4220943/how-to-pass-dynamic-objects-into-an-nunit-testcase-function seems to address your needs — Tim Williams, Nov 19 '12 at 21:47

score 0 · Answer 1 · answered Nov 19 '12 at 21:24

0

If your question is "how do I read data from Excel" and you don't care about formatting (i.e. you only want the data) then your best bet is probably OleDb. It's about the quickest and easiest way to code data retrieval from Excel.

Have a look here for a quick tutorial. It should give you everything you need.

Once you have an idea of how it works, if you run into any trouble then there are plenty of answers on this site to help you out.

answered Nov 19 '12 at 21:24

Sid Holland

2,871
3
27
43

I'm not familiar with MsTest or NUnit, I'm afraid, but that's definitely an OldDb connection string. – Sid Holland Nov 19 '12 at 22:44
please review my question, I added something for clarification purpose – Houshang.Karami Nov 19 '12 at 22:54

score 0 · Answer 2 · answered Nov 19 '12 at 21:29

0

Use EPPlus and you can easily read excel files, something like this :

var ep = new ExcelPackage(new FileInfo("c:\\path-to-excel\\data.xslx"));
ExcelWorksheet ws = ep.Workbook.Worksheets["Sheet1"];

// first row is column name, array indexer goes from 1 
for (int i = 2; i < ws.Dimension.End.Row; i++)
{
  int given = (int)ws.Cells[i, 1].Value;
  int expected = (int)ws.Cells[i, 2].Value;
  YourTest(given, expected);
}

answered Nov 19 '12 at 21:29

Antonio Bakula

20,445
6
75
102

If you are working with a team? How would you fix the issue of replacing the file location string every time you pull? – Matthew S Jan 22 '18 at 16:27
that was just an example, we store excel files with parameters and results (to test some complex calculations) in unit test dll resource – Antonio Bakula Jan 22 '18 at 18:35

Unit Testing With Excel Data Source with Nunit

2 Answers2