Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources] نسخه متنی

The Office Primary Interop Assemblies (PIAs)

Before learning any more about how to build Office solutions, you need to understand in more detail the managed assemblies that you use to talk to the Office object model in .NET. The managed assemblies used to talk to Office are called the Office primary interop assemblies (PIAs).

As mentioned previously, when you are talking to an Office object model in .NET, you talk to it through a .NET technology called COM interop. The Office object models are all written in unmanaged code (C and C++) that exposes COM interfaces. To talk to these COM interfaces from managed code (C# or Visual Basic), you talk via a wrapper that allows managed code to talk to interoperate with the unmanaged COM interfaces of Office. This wrapper is a set of .NET classes compiled into an assembly called a Primary Interop Assembly, or PIA.

The word

primary is used when describing these assemblies because they are the Office-approved wrappers for talking to the Office object models. This designation is needed because you could create your own wrapper for the Office COM object models by using a tool provided with .NET called TLBIMP. A wrapper you create on your own is called an interop assembly (IA) rather than a primary interop assembly. Even though you might be tempted to go play with TLBIMP and build your own interop assemblies, you should never use anything other than the Office-provided interop assemblies to do Office development. If every developer created his or her own sets of wrappers for Office development, then no Office solution could interoperate with anyone else's solution; each interop wrapper class of, say, Worksheet created by each developer would be considered a distinct type. Even though the interop assembly I created has a Worksheet object and the interop assembly you created has a Worksheet object, I cannot pass you my Worksheet object and you cannot pass me your Worksheet object. We need to both be using the same interop assembly: the primary interop assembly.

A second reason to not build your own interop assemblies is that Office has made special fixes to the PIAs to make them work better when doing Office development. If you generate your own then you are likely to run into issues that are fixed in the PIAs.

Installing the PIAs

The Office 2003 PIAs are available through the Office 2003 installer. The Office 2003 PIAs are also available as a Microsoft Windows Installer package that you can redistribute with your application. To install the Office 2003 PIAs through the Office 2003 Installer, when you do a setup, check the Choose advanced customization of applications check box in the first step of the Office 2003 Setup Wizard. Then in the tree control that appears in the next screen of the wizard, you will see a .NET Programmability Support node under each application for which PIAs are available, as shown in Figure 1-4. Click each of these .NET programmability support nodes and make sure that you set Run from my computer. Also, under the Office Tools node in the tree, you might want to turn on Microsoft Forms 2.0 .NET Programmability Support and Smart Tag .NET Programmability support. A second method to getting the Office 2003 PIAs is to do a Complete install of Office 2003all the .NET programmability support will be turned on for you automatically.

The Office PIAs get installed to the Global Assembly Cache (GAC). The GAC is usually in the Assembly subdirectory of the Windows directory.

A number of Office PIAs are available. Table 1-4 lists some of the most common ones. One PIA listed here that is of note is the Office.dll PIA, which is where common types that are shared between the Office applications such as CommandBar are found.

Referencing the PIAs

Adding a reference to a PIA is not necessary for most VSTO projects because the reference is automatically added for you. The console application examples in this book, such as the ones that automate Excel, can be typed into a Visual Studio console project and compiled, but you must first add a reference to the necessary PIA. To add a reference, right-click the References folder under your project in the Visual Studio Solution Explorer, as shown in Figure 1-5. Choose Add Reference from the menu that pops up when you right-click the References folder.

Choose the COM tab of the Add Reference dialog that appears, as shown in Figure 1-6. The COM references are listed by component name, which matches the description column in Table 1-4. So, to add a reference to the Excel PIA, you choose the Microsoft Excel 11.0 Object Library and click the OK button to add the Excel 2003 PIA reference to your project, as shown in Figure 1-6.

Note in Figure 1-6 that the Path column in the COM tab of the Add References dialog displays the path to the COM library that the PIA wraps. For example, the Microsoft Excel 11.0 Object Library points to the location on your machine of the Excel.EXE executable. When you select these references and close the dialog, you can examine the properties of the actual references that were added by expanding the References folder in the project, right-clicking the references that you added, and choosing Properties. You will see that Visual Studio figures out the PIA managed object in the GAC that corresponds to the COM object you selected. In this case, you will not get a reference to the Excel.EXE executable but instead to the Microsoft.Office.Interop.Excel.dll in the GAC.

Finally, note that even though you did not explicitly add a reference to the Microsoft Office 11.0 Object Library (office.dll), a reference is added for you. This is because the Excel 11.0 Object Library uses types from the Microsoft Office 11.0 Object Library. Visual Studio detects this and adds the required Office PIA to your project references automatically.

Browsing the PIAs

When you look at the PIA you have referenced in the object browser in Visual Studio, you might find yourself very confused. The object browser shows many helper objects that are created as part of the interop wrapper. For example, consider what .NET Interop does to the seemingly simple Excel Application object. It turns it into a multiple-headed (8 heads to be exact, 36 if you count each delegate individually) monster. All of the following are public types that you see in the browser related to the Excel Application object:

Interfaces

• _Application

• AppEvents

• AppEvents_Event

• Application

• IAppEvents

Delegates

• AppEvents_*EventHandler (29 of them)

Classes

• AppEvents_SinkHelper (AppEvents)

• ApplicationClass (_Application, Application, AppEvents_Event)

This pattern repeats for Chart, OLEObject, QueryTable, Worksheet, and Workbook.

Let's try to untangle this mess by working our way backward from the original COM definition of the Excel Application object. The COM coclass for the Application object looks like thisit has two interfaces, a primary interface called _Application and an event interface called AppEvents. You can think of a coclass as something that defines the interfaces that a COM class implements.

coclass Application {
[default] interface _Application;
[default, source] dispinterface AppEvents;
};

TLBIMP (which is used to process the COM type library for Excel and make the PIA) directly imports the _Application and AppEvents interfaces, so this explains where two of the eight types come from. But the AppEvents interface is not very usefulit seems like an artifact of the TLBIMP conversion in some ways. It has to be processed further to create another interface described later called AppEvents_Event to be of any use.

When TLBIMP processes the COM coclass, it creates a .NET class called ApplicationClass, which is named by taking the coclass name and appending Class. It also creates a .NET interface with the same name as the coclass called Application for our example. If you look at Application in the browser, it has no properties and methods of its own, but it derives from the other two interfaces associated with the coclass: _Application and AppEvents_Event.

We have not yet explained where the AppEvents_Event interface comes from. When TLBIMP processes the AppEvents event interface on the coclass, it creates several helper types. First it creates AppEvents_Event, which looks like AppEvents but with events and delegate types replacing the methods in AppEvents. It also creates delegates named AppEvents_

* EventHandler, where

* is the method name for each method on the original AppEvents interface. Finally, it creates an AppEvents_SinkHelper, which can be ignored.

That leaves only the IAppEvents interface unexplained. TLBIMP imports this interface directly because it is a public type in the Excel type library. You can ignore this also. This is effectively a duplicate of AppEvents, except AppEvents is declared as a dispinterface in the type library, and IAppEvents is declared as a dual interface type.

So which of these do you really use? Basically, you should only use in your code the Application interface (which derives from _Application and AppEvents_Events) and the delegates. You can usually pretend the others do not exist. The one exception to this rule is when a method and event name collide, as described earlier in this chapter. To disambiguate between a method and an event you must cast to the _Application interface when you want to call the method or the AppEvents_Event interface when you want to connect to the event. Table 1-5 presents a summary.

The Application interface that is created by TLBIMP for the coclass behaves in an interesting way. You can write code in C# that makes it look like you are creating an instance of the Application interface, which we all know is impossible:

Excel.Application myApp = new Excel.Application();

Really, this is syntactical sugar that is using the ApplicationClass behind the scenes (the Application interface is attributed to associate it with the ApplicationClass) to create an Excel Application object and return the appropriate interface.

Finally, we mentioned earlier that this pattern repeats for Chart, OLEObject, QueryTable, Worksheet, and Workbook. The mapping to Chart is straightforwardreplace Application with Chart and AppEvents with ChartEvents and you'll get the general idea. Worksheet is a bit different. Its coclass looks like this:

coclass Worksheet {
[default] interface _Worksheet;
[default, source] dispinterface DocEvents;
};

So for Worksheet, replace Application with Worksheet, but replace AppEvents with DocEventsyielding DocEvents_*EventHandler as the delegates for WorkSheet events.

QueryTable is even weirder. Its coclass looks like this:

coclass QueryTable {
[default] dispinterface _QueryTable;
[default, source] dispinterface RefreshEvents;
};

So for QueryTable, replace Application with QueryTable and replace AppEvents with RefreshEventsyielding RefreshEvents_*EventHandler as the delegates for QueryTable events.

Dummy Methods

When you look at the Excel PIA in the object browser in Visual Studio, you might notice a slew of methods with the text

Dummy in them. There's even an interface called IDummy.

No, this is not Excel's way of insulting your intelligence. Everything with Dummy in it is a test method that actually has a legitimate purpose and more descriptive names in Microsoft's internal "debug" version of Excel. For example, Application.Dummy6 is called Application.DebugMemory in the debug version of Excel. Each method was renamed to Dummy in the retail version of Excel. All 508 of these Dummy methods actually do something in debug Excel, but in the retail version of Excel, they do nothing except raise an error when called.

Excel has marked these as "hidden," but the C# object browser shows hidden methods by default. When you view the PIA in the C# object browser, you will see these Dummy methods. If you create a Visual Basic project, the Visual Basic object browser will hide methods and properties with this attribute.