C# Excel 互操作：_Worksheet 还是 Worksheet？

Excel interop: _Worksheet or Worksheet?

提问by Jon Skeet

I'm currently writing about dynamic typing, and I'm giving an example of Excel interop. I've hardly done any Office interop before, and it shows. The MSDN Office Interop tutorialfor C# 4 uses the _Worksheetinterface, but there's also a Worksheetinterface. I've no idea what the difference is.

我目前正在撰写有关动态类型的文章，并给出了 Excel 互操作的示例。我以前几乎没有做过任何 Office 互操作，它显示了。C# 4的MSDN Office Interop 教程使用该_Worksheet接口，但也有一个Worksheet接口。我不知道有什么区别。

In my absurdly simple demo app (shown below) either works fine - but if best practice dictates one or the other, I'd rather use it appropriately.

在我荒谬的简单演示应用程序（如下所示）中，要么工作正常 - 但如果最佳实践决定了其中之一，我宁愿适当地使用它。

using System;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

class DynamicExcel
{
    static void Main()
    {
        var app = new Excel.Application { Visible = true };
        app.Workbooks.Add();

        // Can use Excel._Worksheet instead here. Which is better?
        Excel.Worksheet workSheet = app.ActiveSheet;

        Excel.Range start = workSheet.Cells[1, 1];
        Excel.Range end = workSheet.Cells[1, 20];
        workSheet.get_Range(start, end).Value2 = Enumerable.Range(1, 20)
                                                           .ToArray();
    }
}

I'm trying to avoid doing a full deep-dive into COM or Office interoperability, just highlighting the new features of C# 4 - but I don't want to do anything really, really dumb.

我试图避免对 COM 或 Office 互操作性进行全面深入的研究，只是强调 C# 4 的新功能 - 但我不想做任何非常非常愚蠢的事情。

(There may be something really, really dumb in the code above as well, in which case please let me know. Using separate start/end cells instead of just "A1:T1" is deliberate - it's easier to see that it's genuinely a range of 20 cells. Anything else is probably accidental.)

（上面的代码中也可能有一些非常非常愚蠢的东西，在这种情况下，请告诉我。使用单独的开始/结束单元格而不是“A1:T1”是有意的 - 更容易看出它确实是一个范围20 个单元格。其他任何事情都可能是偶然的。）

So, should I use _Worksheetor Worksheet, and why?

那么，我应该使用_Worksheetor Worksheet，为什么？

采纳答案by Eric Lippert

If I recall correctly -- and my memory on this is a bit fuzzy, it has been a long time since I took the Excel PIA apart -- it's like this.

如果我没记错的话——我的记忆有点模糊，自从我把 Excel PIA 拆开以来已经很长时间了——就像这样。

An event is essentially a method that an object calls when something happens. In .NET, events are delegates, plain and simple. But in COM, it is very common to organize a whole bunch of event callbacks into interfaces. You therefore have two interfaces on a given object -- the "incoming" interface, the methods you expect other people to call on you, and the "outgoing" interface, the methods you expect to call on other people when events happen.

事件本质上是对象在发生某些事情时调用的方法。在 .NET 中，事件是简单明了的委托。但是在 COM 中，将一大堆事件回调组织到接口中是很常见的。因此，您在给定对象上有两个接口——“传入”接口，您希望其他人调用您的方法，以及“传出”接口，您希望在事件发生时调用其他人的方法。

In the unmanaged metadata -- the type library -- for a creatable object there are definitions for three things: the incoming interface, the outgoing interface, and the coclass, which says "I'm a creatable object that implements this incoming interface and this outgoing interface".

在非托管元数据（类型库）中，对于可创建对象，定义了三件事：传入接口、传出接口和 coclass，它表示“我是一个可创建对象，它实现了这个传入接口和这个输出接口”。

Now when the type library is automatically translated into metadata, those relationships are, sadly, preserved. It would have been nicer to have a hand-generated PIA that made the classes and interfaces conform more to what we'd expect in the managed world, but sadly, that didn't happen. Therefore the Office PIA is full of these seemingly odd duplications, where every creatable object seems to have two interfaces associated with it, with the same stuff on them. One of the interfaces represents the interface to the coclass, and one of them represents the incoming interface to that coclass.

现在，当类型库自动转换为元数据时，遗憾的是，这些关系被保留了下来。拥有一个手工生成的 PIA 会更好，使类和接口更符合我们在托管世界中的期望，但遗憾的是，这并没有发生。因此，Office PIA 充满了这些看似奇怪的重复，其中每个可创建的对象似乎都有两个与之关联的接口，上面有相同的东西。其中一个接口代表 coclass 的接口，其中之一代表该 coclass 的传入接口。

The _Workbook interface is the incoming interface on the workbook coclass. The Workbook interface is the interface which represents the coclass itself, and therefore inherits from _Workbook.

_Workbook 接口是工作簿 coclass 上的传入接口。Workbook 接口是代表 coclass 本身的接口，因此继承自 _Workbook。

Long story short, I would use Workbook if you can do so conveniently; _Workbook is a bit of an implementation detail.

长话短说，如果方便的话，我会使用 Workbook；_Workbook 是一个实现细节。

回答by Joe Erickson

I have seen and written quite a bit of C# / Excel COM Interop code over the last few years and I've seen Worksheet used in almost every case. I have never seen anything definitive from Microsoft on the subject.

在过去的几年里，我已经看到并编写了相当多的 C#/Excel COM Interop 代码，并且我已经看到几乎在所有情况下都使用了 Worksheet。我从来没有看到微软在这个问题上有任何明确的说法。

回答by Noldorin

MSDN shows that the Worksheetinterface simply inherits from the _Worksheetand DocEvents_Eventinterfaces. It would seem that one simply provides the events that a worksheet object might raise in additional to everything else. As far as I can see, Worksheetdoesn't provide any other members of its own. So yeah, you might as well just go with using the Worksheetinterface in all cases, since you don't lose anything by it, and potentially might need the events it exposes.

MSDN 显示该Worksheet接口简单地继承自_Worksheet和DocEvents_Event接口。似乎只是提供了工作表对象可能引发的事件，而不是其他所有事件。据我所知，Worksheet不提供任何其他成员。所以是的，您最好Worksheet在所有情况下都使用该接口，因为您不会因此而丢失任何东西，并且可能需要它公开的事件。

回答by JP Alioto

If you look at the PIA assembly (Microsoft.Office.Interop.Excel) in Reflector, the Workbookinterface has this definition ...

如果你看一下在PIA组件（的Microsoft.Office.Interop.Excel） Reflector，该Workbook接口具有此定义...

public interface Workbook : _Workbook, WorkbookEvents_Event

Workbookis _Workbookbut adds events. Same for Worksheet(sorry, just noticed you were not talking about Workbooks) ...

Workbook是_Workbook但添加事件。同样的Worksheet（对不起，只是注意到你不是在谈论Workbooks）......

public interface Worksheet : _Worksheet, DocEvents_Event

DocEvents_Event...

DocEvents_Event...

[ComVisible(false), TypeLibType((short) 0x10), ComEventInterface(typeof(DocEvents),
                     typeof(DocEvents_EventProvider))]
public interface DocEvents_Event
{
    // Events
    event DocEvents_ActivateEventHandler Activate;
    event DocEvents_BeforeDoubleClickEventHandler BeforeDoubleClick;
    event DocEvents_BeforeRightClickEventHandler BeforeRightClick;
    event DocEvents_CalculateEventHandler Calculate;
    event DocEvents_ChangeEventHandler Change;
    event DocEvents_DeactivateEventHandler Deactivate;
    event DocEvents_FollowHyperlinkEventHandler FollowHyperlink;
    event DocEvents_PivotTableUpdateEventHandler PivotTableUpdate;
    event DocEvents_SelectionChangeEventHandler SelectionChange;
}

I would say it's best bet to use Worksheet, but that's the difference.

我会说最好使用Worksheet，但这就是区别。

回答by barrowc

Classes and Interfaces for Internal Use Only

Avoid directly using any of the following classes and interfaces, which are used internally and are typically not used directly.

Class/Interface : Examples

classidClass : ApplicationClass (Word or Excel), WorksheetClass (Excel)

classidEvents x _SinkHelper : ApplicationEvents4_SinkHelper (Word), WorkbookEvents_SinkHelper (Excel)

_classid: _Application (Word or Excel), _Worksheet (Excel)

classidEvents x : ApplicationEvents4 (Word), AppEvents (Excel)

I classidEvents x : IApplicationEvents4 (Word), IAppEvents (Excel)

仅供内部使用的类和接口

避免直接使用以下任何类和接口，它们在内部使用，通常不会直接使用。

类/接口：示例

classid类：ApplicationClass（Word 或 Excel）、WorksheetClass（Excel）

classid事件 x _SinkHelper : ApplicationEvents4_SinkHelper (Word), WorkbookEvents_SinkHelper (Excel)

_classid: _Application (Word 或 Excel), _Worksheet (Excel)

classid事件 x : ApplicationEvents4 (Word)、AppEvents (Excel)

I classidEvents x : IApplicationEvents4 (Word), IAppEvents (Excel)

http://msdn.microsoft.com/en-gb/library/ms247299(office.11).aspx

http://msdn.microsoft.com/en-gb/library/ms247299(office.11​​).aspx

edit: (re: formatting of this answer) cannot correctly format an escaped underscore followed immediately by italic text. Shows correctly in preview but broken when posted

编辑：（重新：此答案的格式）无法正确格式化转义的下划线，后跟斜体文本。在预览中正确显示但在发布时损坏

edit2: works if you make the underscore itself italic which is conceptually horrible but looks the same I suppose

编辑2：如果您将下划线本身设为斜体，这在概念上很可怕，但我想看起来是一样的