VSTO: Using C # to develop Excel, Word "6"

Office primary Interop Assemblies (PIA)
Before you learn how to build an Office solution, you need to know more about the. NET managed assemblies that communicate with the Office object model. The managed assemblies that are used for calling with Office are called Office Primary Interop Assemblies (PIA).

As mentioned earlier, when you are working with the. NET, you can talk to it by using. NET technology called COM Interop when you talk to the Office object model in. All of the Office object models are written in unmanaged code that exposes COM interfaces (c and C + +). To call these COM interfaces through managed code (C # or Visual Basic), you can call through a wrapper, allowing managed code to interoperate with Office's unmanaged COM interfaces. This wrapper is a set of. NET classes that are compiled into an assembly called the primary interop assembly or PIA.

The term "primary" is used when describing these assemblies because they are wrappers approved by office to communicate with the Office object model. This designation is required because you can use the. NET provides tools, called TlbImp, to create your own wrappers for the Office COM object model. The wrapper you create yourself is called an interop assembly (IA), not a primary interop assembly. Even if you might try to use tlbimp and build your own interop assemblies, you should not use any other action for Office development in addition to the interop assemblies that are provided by office. If every developer creates their own suite for their office developers, no Office solution can interoperate with anyone's solution, and each interop wrapper class, such as worksheet created by each developer, will be treated as a different type. Even if the interop assembly that I created has a Worksheet object, and the interop assembly that you created has a Worksheet object, I cannot pass my worksheet object or pass it to your worksheet object. We need to use the same interop assembly: the primary interop assembly.

The second reason that you do not establish your own interop assemblies is that Office has made special repairs to the PIA to make them work better when you perform office development. If you build your own, you are likely to experience problems fixed in the PIA.

Installing the PIA
Office 2003 PIA is available through the Office 2003 Setup program. The Office 2003 PIA can also be provided as a Microsoft Windows installer package that you can use to redistribute the application. To install the Office 2003 PIA through the Office 2003 Setup program, in the first step of the Office 2003 Setup Wizard, select the Select Advanced customization of Applications check box. Then, in the tree control that appears in the next screen of the wizard, you will see a programmable controller available under each application. NET Programmability Support node, shown in 1-4. Click each of these. NET programmable support node and make sure you run it from my computer settings. Additionally, under the Office Tools node in the tree, you may have to enable Microsoft Forms 2.0. NET Programmability Support and smart tag. NET Programmability Support. The second way to get the Office 2003 PIA is to complete the installation of Office 2003all. NET Programmability Support will open automatically.

The Office PIA installs to the Global assembly cache (GAC). The GAC is typically located in the assembly subdirectory of the Windows directory.

More than one office PIA is available. Table 1-4 lists some of the most common ones. One of the PIAs listed here is the Office.dll Pia, which is a common type that is shared between Office applications such as CommandBar.

Reference PIAs
Adding a reference to the PIA is unnecessary for most VSTO projects, because the reference will be added automatically for you. Examples of console applications in this book, such as the console application example for automating Excel, can type a Visual Studio console project and compile, but you must first add a reference to the necessary PIA. To add a reference, in Visual Studio Solution Explorer, right-click the References folder under Projects, as shown in 1-5. When you right-click the References folder, choose Add Reference from the menu that pops up.

Select the COM tab of the Add Reference dialog box that appears, as shown in 1-6. The COM reference is listed by component name and matches the Description column in table 1-4. Therefore, to add a reference to the Excel PIA, select the Microsoft Excel 11.0 Object Library, and then click the OK button to add the Excel 2003 PIA reference to the project, as shown in 1-6.

Note that the path column in the COM tab of the Add Reference dialog box in Figure 1-6 shows the path to the COM library wrapped by the PIA. For example, the Microsoft Excel 11.0 Object Library points to the location of the Excel.EXE executable file on your computer. When you select these references and close the dialog box, you can check the properties of the actual reference that you added by extending the References folder in your project, right-clicking the reference that you added, and then selecting Properties. You will see that Visual Studio calculates the PIA managed object that corresponds to the COM object that you selected in the GAC. In this case, you will not get a reference to the Excel.EXE executable file, but rather the Microsoft.Office.Interop.Excel.dll in the GAC.

Finally, be aware that even if you do not explicitly add a reference to the Microsoft Office 11.0 Object Library (office.dll), you have added a reference to it. This is because the Excel 11.0 Object library uses the types in Microsoft Office 11.0 Object Library. Visual Studio detects this and automatically adds the required office PIA to your project reference.

Explore PIA
When you see the PIA that is referenced in the Object Browser in Visual Studio, you may find yourself confused. The Object Browser shows many helper objects that were created as part of the interop wrapper. For example, consider. NET interop for a reference to a seemingly simple Excel application object. It becomes a multi-associative header reference (8 header standard Reference, 36 privately referenced). All of the following are common types that are related to Excel application objects in the browser:

Interfaces ( interface )

• _application

• Appevents

• Appevents_event

• Application

• Iappevents

Delegates (commissioned)

• Appevents_*eventhandler (them)

Classes (Class)

• Appevents_sinkhelper (appevents)

• ApplicationClass (_application, Application, appevents_event)

This graphic is for Chart,oleobject,querytable,worksheet and workbook.

We try to solve this confusion by working backwards from the original COM definition of the Excel application object. The COM coclass of the Application object looks like Thisit has two interfaces, a main interface called _application, and an event interface named Appevents. You can treat coclass as something that defines the interface that the COM class implements.

coclass Application {        [defaultinterface  _application;        [default, source] dispinterface appevents;    };

TLBIMP (for working with the COM type library of Excel and using 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 useful and seems like some kind of tlbimp conversion artifact. It must be further processed to create another interface for the Appevents_event described later.

When TlbImp handles COM coclass, it creates a. NET class named ApplicationClass, which is named by the name of the coclass and appended with the class. It also creates a. NET interface with the same name as the coclass named application. If you view the application in a browser, it does not have its own properties and methods, but it comes from the other two interfaces associated with coclass: _application and Appevents_event.

We have not yet explained where the Appevents_event interface comes from. When TlbImp handles the Appevents event interface on coclass, it creates several help types. First, it creates appevents_event, which looks like appevents, but events and delegate types replace the methods in Appevents. It also creates a proxy named Appevents_ * EventHandler, where * is the method name of each method on the original Appevents interface. Finally, it creates a appevents_sinkhelper that can be ignored.

This will only make the Iappevents interface impossible to interpret. TLBIMP imports this interface directly, because it is a public type in the Excel type library. You can also ignore this. This is actually a repetition of appevents, except that Appevents is declared as dispinterface in the type library, and Iappevents is declared as a dual interface type.

So, what do you really use? Basically, you should only use the application interface (derived from _application and appevents_events) and proxies in your code. You can usually pretend that no one else exists. An exception to this rule is when the method and event names conflict, as described earlier in this chapter. To disambiguate between methods and events to invoke the method or Appevents_event interface when you want to connect to an event, you must convert it to the _application interface. A summary is given in table 1-5.

The application interfaces created by TlbImp for coclass behave in an interesting way. You can write code in C # so that it looks like an instance of the application interface you are creating, and we all know that this is not possible:

New Excel.Application ();

Really, it's behind the scenes. Use ApplicationClass's syntax sugar (the application interface is attributed to associate it with ApplicationClass) to create an Excel Application object and return the appropriate interface.

Finally, we mentioned earlier that this pattern is repetitive for both chart,oleobject,querytable,worksheet and workbook. Chart maps are direct applications with chartevents charts and appevent, and you'll get a general idea. The worksheets are a little different. Its coclass looks like this:

coclass Worksheet {        [defaultinterface  _worksheet;        [default, source] dispinterface docevents;    };

So for worksheets, instead of replacing application with worksheet, replace appevents with Doceventsyielding Docevents_ * EventHandler as the proxy for worksheet events.

QueryTable even the limit. Its coclass looks like this:

coclass QueryTable {        [default] dispinterface _querytable;        [default, source] dispinterface refreshevents;    };

So for QueryTable, replace the application with QueryTable and replace the appevents with the refresh Event to generate refreshments * EventHandler as a delegate to the QueryTable event.

Dummy method

When you view the Excel PIA in the Object Browser in Visual Studio, you may notice a number of methods that contain text dummy. There is even an interface called Idummy.

No, this is not the way Excel insults your wisdom. Everything is virtual, and it's a test method that actually has a legitimate purpose and a more descriptive name in Microsoft's internal "debug" version. For example, Application.dummy6 is called Application.debugmemory in the Debug version of Excel. In the retail version of Excel, each method is renamed to dummy. All 508 of these dummy methods actually do something in debugging Excel, but in the retail version of Excel, they do nothing except throw an error on the call.

Excel marks these as "hidden," but the C # Object Browser shows hidden methods by default. You will see these dummy methods when you view the PIA in the C # Object Browser. If you create a Visual Basic project, the Visual Basic Object Browser hides the methods and properties that have this property.

Conclusion
This chapter describes the Office object model and examines the basic structure of the object model. You learned how to use objects, collections, and enumerations to find the base types in any object model. You also learned how to use the objects and collections in the Office object model to expose properties, methods, and events.

This chapter describes the Office primary interop assemblies that expose the Office object model to. NET code. You learned how to use and reference the Office PIA in a Visual Studio project. This chapter also describes what you can ignore when you view the PIA in the Object Browser.

The next chapter will begin with a look at the basic development patterns used in office programming and provide a variety of examples.

VSTO: Using C # to develop Excel, Word "6"