数据驱动单元测试从Excel电子表格 [英] Data-Driven Unit Test from Excel Spreadsheet

问题描述

如何在VS2012中设置从Excel .xlsx电子表格中读取数据的数据驱动单元测试方法？

我已尝试使用Google搜索解决方案结果是指的是旧版本的Visual Studio或不适用于.xlsx文件。

到目前为止，我有一个测试类，测试方法和数据源文件，TestData.xlsx，它在构建时被复制到输出目录。

解决方案

>

给您的测试方法以下属性：

  [DeploymentItem（TestData xlsx）] 
 [DataSource（System.Data.Odbc，@Dsn = Excel Files; dbq = .\TestData.xlsx; defaultdir =; driverid = 790; maxbuffersize = 2048; pagetimeout = 5 ，Sheet1 $，DataAccessMethod.Sequential）] 
 [TestMethod] 
 public void MyTestMethod（）
 {
 ... 
} 
  

[DeploymentItem]只是确保.xlsx文件最终在t目录中他的测试最终从...执行。

您还需要将.xlsx文件添加到您的项目中，并在其属性中设置复制到输出目录属性为复制（如果较新）或永久复制，否则.xlsx文件将不会被复制到测试目录，并且您会收到一个令人讨厌的不必要的错误，只是说Microsoft Access数据库引擎可以没有找到对象Sheet1 $。

Sheet1 $表示名为Sheet1的Excel文档中的整个工作表将被用作数据表。如果您希望您可以在Excel文档中创建命名范围，则可以将NamedRangeName替换为$号，作为数据表源。

How do I set up a data-driven unit test method in VS2012 that reads data from an Excel .xlsx spreadsheet?

I have tried Googling the solution but the results are either referring to an older version of Visual Studio or are not for .xlsx files.

So far I have a test class, test method, and the data source file, TestData.xlsx which is copied to the output directory at build time.

解决方案

Figured it out on my own.

Give your test method the following attributes:

[DeploymentItem("TestData.xlsx")]
[DataSource("System.Data.Odbc", @"Dsn=Excel Files;dbq=.\TestData.xlsx;defaultdir=.; driverid=790;maxbuffersize=2048;pagetimeout=5", "Sheet1$", DataAccessMethod.Sequential)]
[TestMethod]
public void MyTestMethod()
{
  ...
}

[DeploymentItem] just makes sure that the .xlsx file ends up in the directory where the test ultimately executes from.

You also NEED to add the .xlsx file to your project and in it's Properties set the "Copy to Output Directory" property to "Copy if newer" or "Copy always" or else the .xlsx file won't get copied to the test directory and you will get an annoyingly undescriptive error that just says "The Microsoft Access database engine could not find the object 'Sheet1$'."

"Sheet1$" means that the entire worksheet in the Excel doc named "Sheet1" will be used as the data table. If you want you can make named ranges in the Excel doc then instead you can put "NamedRangeName" with no "$" sign as your data table source.

这篇关于数据驱动单元测试从Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！