嘲弄Excel工作表时如何避免使用动态？ [英] How do I avoid using dynamic when mocking an Excel.worksheet?

问题描述

我试图使用NSubstitute或其他嘲笑框架和MSTest（Visual Studio 2010）来模拟Excel电子表格。我不知道有没有比这更好的方法 - 这不适用于测试：

I'm trying to mock an Excel spreadsheet using NSubstitute or other mocking framework and MSTest (Visual Studio 2010). I'm not sure if there's a better way than this--and this doesn't quite work for testing:

这里有一个例子（这是现在的所有原型代码，而不是很干净）：

Here's an example (this is all prototype code right now, and not very clean):

int[] lowerBounds = { 1, 1 };
int[] lengths = { 2, 2 };

//Initialize a 1-based array like Excel does:
object[,] values = (object[,])Array.CreateInstance(typeof(object), lengths, lowerBounds);
values[1,1] = "hello";
values[2,1] = "world";      

//Mock the UsedRange.Value2 property
sheet.UsedRange.Value2.Returns(values); 

//Test:   
GetSetting(sheet, "hello").Should().Be("world");  //FluentAssertions

到目前为止，这很好：如果GetSetting方法是相同的项目作为我的考试。但是当GetSetting在我的VSTO Excel-Addin项目中时，GetSetting函数的第一行出现以下错误：

So far, so good: this passes if the GetSetting method is in the same project as my test. However when GetSetting is in my VSTO Excel-Addin project, it fails with the following error on the first line of the GetSetting function:

System.MissingMethodException: Error: Missing method 'instance object [MyExcel.AddIn] Microsoft.Office.Interop.Excel.Range::get_Value2()' from class 'Castle.Proxies.RangeProxy'.

为了参考，GetSetting从工作表中的columnA中获取一个值，并返回columnB中的值。

For reference, the GetSetting grabs a value from columnA in the sheet, and returns the value in columnB.

public static string GetSetting(Excel.Worksheet sheet, string settingName) {
  object[,] value = sheet.UsedRange.Value2 as object[,];
  for (int row = 1; row <= value.GetLength(1); row++) {
    if (value[1, row].ToString() == settingName)
      return value[2, row].ToString();
  }
  return "";
}

最后一个有趣的是如果我重新定义我的方法的签名如下：

public static string GetSetting（动态工作表，字符串settingName）

它可以在VSTO项目中使用。

The final interesting piece is if I redefine the signature of my method as follows:
public static string GetSetting(dynamic sheet, string settingName)
it works in the VSTO project.

所以发生了什么，最好的办法是做这样的事情？

So what is going on, and what's the best way to do something like this?

谢谢！

推荐答案

VS2012更新： Moq&互操作类型：在VS2012中工作，在VS2010中失败？

首先：有些更改： 如何在嘲弄Excel工作表时避免使用动态？

我遇到同样的问题使用NSubstitute Mocking Excel对象。动态解决了问题，就像你所提到的那样。但是我想找到根本原因。

I encountered the same problem Mocking Excel objects using NSubstitute. The dynamic resolved the problem just as you mention. However I wanted to find the root cause.

当您的项目引用了 Microsoft。 Office.Interop.Excel.Extensions.dll 需要检查Embed Interop Types属性是否可见。如果这意味着您的目标是.Net 4.0（我可以从动态关键字中猜出）。

When your Project has a reference to Microsoft.Office.Interop.Excel.Extensions.dll you need to check if the Embed Interop Types property is visible. If it is that means your targeting .Net 4.0 (which I could guess from the dynamic keyword).

您可以将测试项目定位.Net 4.0，但您需要
将VSTO Project .Net框架更改为3.5。那么你将要
可能需要做一些明确的转换，并且完全符合条件
才能摆脱这些错误：

You can leave the Test Project targeting .Net 4.0 but you need to you change the VSTO Project .Net framework back to 3.5. Then you'll probably have to do some explicit casting and fully qualify things to get rid of these errors:

C＃Office Excel Interop对象不包含错误的定义，以下是几个示例：

C# Office Excel Interop "object does not contain definition for" errors, here are a couple of examples:

。 Net 4.0：

if (tmpsheetName == xlApp.ActiveSheet.Name)

.Net 3.5等效

Worksheet activeSheet = (Worksheet)xlApp.ActiveSheet;
if (tmpsheetName == activeSheet.Name)

另一个例子：

rn.Select();

.Net 4.0

xlApp.Selection.HorizontalAlignment = Constants.xlCenter; 
xlApp.Selection.Font.Bold = true;
xlApp.Selection.Merge();

.Net 3.5等效

rn.HorizontalAlignment = Constants.xlCenter;
rn.Font.Bold = true;
rn.Merge();

---

继续修复所有的.Net 3.5 vs 4.0语法错误。不要忘记删除动态参数类型，并将其替换为原始的工作表。最后再次启动测试，它将通过!!!

---

Proceed to fix all the .Net 3.5 vs 4.0 syntax errors as per the above examples. Dont forget to remove the dynamic parameter type and replace it the original Worksheet. Finally fire up the Test again and it will pass!!!

鉴于我在Microsoft.CSharp.DLL中遇到的所有悲伤，这个 thread 我是对VSTO进行测试的。具有Mocking Framework的Net 4.0项目不起作用。

Given all the grief I experienced with Microsoft.CSharp.DLL in this thread I am of the opinion testing VSTO .Net 4.0 projects with Mocking Frameworks doesn't work.

这篇关于嘲弄Excel工作表时如何避免使用动态？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！