Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources]

Eric Carter, Eric Lippert

نسخه متنی -صفحه : 213/ 14
نمايش فراداده

Office Object Models

Almost all Office programming involves writing code that uses the object model of an Office application. The object model is the set of objects provided by the Office application that running code can use to control the Office application. The object model of each Office application is organized hierarchically with the object called Application forming the root of the hierarchy. From the Application object, other objects that make up the object model of the Office application can be accessed.

As an example of how object model objects are related in the object model hierarchy, Figure 1-1 shows some of the most important objects in the Word object model. The root object is the Application object. Also shown in this diagram are some other objects, including Documents, Document, Paragraphs, and Paragraph. The Application object and Documents object are related because the Documents object is returned via a property on the Application object. Other objects are not directly accessible from the root Application object, but are accessible by traversing a path. For example, the Paragraphs object is accessed by traversing the path from Application to Documents to Document to Paragraphs. Figure 1-2 shows a similar diagram for some major objects in the Excel object model hierarchy.

Figure 1-1. Hierarchy in the Word object model.

Figure 1-2. Hierarchy in the Excel object model.

Objects

Each Office application's object model consists of many objects that you can use to control the Office application. Word has 248 distinct objects, Excel has 196, and Outlook has 67. Objects tend to correspond to features and concepts in the application itself. For example, Word has objects such as Document, Bookmark, and Paragraphall of which correspond to features of Word. Excel has objects such as Workbook, Worksheet, Font, Hyperlink, Chart, and Seriesall of which correspond to features of Excel. As you might suppose, the most important and most used objects in the object models are the ones that correspond to the application itself, the document, and key elements in a document such as a range of text in Word. Most solutions use these key objects and only a small number of other objects in the object models. Chapter 3, "Programming Excel."

Listing 1-1. Navigating from the Application Object to a Worksheet in Excel
Excel.Workbooks myWorkbooks = app.Workbooks;
Excel.Workbook myWorkbook = myWorkbooks.get_Item(1);
Excel.Worksheets myWorksheets = myWorkbook.Worksheets;
Excel.Worksheet myWorksheet = myWorksheets.get_Item(1) as Excel.Worksheet;

If the code does not need to cache each object model object in a variable as it goes but only needs to get a Worksheet object, a more efficient way to write this code is as follows:

Excel.Worksheet myWorksheet2 = app.Workbooks.get_Item(1).
Worksheets.get_Item(1) as Excel.Worksheet;

Collections

Paragraphs and Documents are examples of a type of object called a collection. A collection is a specialized object that represents a group of objects. Typically, a collection is named so that its name is the plural of the type of the object it contains. For example, the Documents collection object is a collection of Document objects. Some collection objects may be collections of a value type such as a string.

Collections typically have a standard set of properties and methods. A collection has a Count property, which returns the number of objects in the collection. A collection also has an Item method, which takes a parameter, typically a number, to specify the index of the desired object in the collection. A collection may have other properties and methods in addition to these standard properties and methods.

Listing 1-2 shows iteration over a collection using the Count property of the collection and the Item method of the collection. Although this is not the preferred way of iterating over a collection (you typically use foreach instead), it does illustrate two key points. First, collections in Office object models are almost always 1-based, meaning they start at index 1 rather than index 0. Second, the parameter passed to the get_Item method is often passed as an object so you can either specify a numeric index as an int or the name of the object within the collection as a string.

Listing 1-2. Iterating Over a Collection Using the Count Property and the get_Item Method with Either an int or a string Index
Excel.Workbooks myWorkbooks = app.Workbooks;
int workbookCount = myWorkbooks.Count;
for (int i = 1; i <= workbookCount; i++)
{
// Get the workbook by its int index
Excel.Workbook myWorkbook = myWorkbooks.get_Item(i);
// Get the workbook by its string index
string workbookName = myWorkbook.Name;
Excel.Workbook myWorkbook2 = myWorkbooks.get_Item(workbookName);
MessageBox.Show(String.Format("Workbook {0}", myWorkbook2.Name));
}

If you were to look at the definition for the Workbooks collection's get_Item method, you would see that it takes an object parameter. Even though the get_Item method takes an object parameter, we pass an int value and a string value to it in Listing 1-2. This works because C# can automatically convert a value type such as an int or a string to an object when you pass the value type to a method that takes an object. This automatic conversion is called boxing. C# automatically creates an object instance known as a box to put the value type into when passing it to the method.

The preferred way of iterating over a collection is using the foreach syntax of C#, as shown in Listing 1-3.

Listing 1-3. Iterating over a Collection Using foreach
Excel.Workbooks myWorkbooks = app.Workbooks;
foreach (Excel.Workbook workbook in myWorkbooks)
{
MessageBox.Show(String.Format("Workbook {0}", workbook.Name));
}

Sometimes you may want to iterate over a collection and delete objects from the collection by calling a Delete method on each object as you go. This is a risky practice because behavior of a collection in the Office object models is sometimes undefined if you are deleting items from it as you iterate over it. Instead, as you iterate over the Office object model collection, add the objects you want to delete to a .NET collection you have created, such as a list or an array. After you have iterated over the Office object model collection and added all the objects you want to delete to your collection, iterate over your collection and call the Delete method on each object.

Enumerations

An enumeration is a type defined in an object model that represents a fixed set of possible values. The Word object model contains 252 enumerations, Excel 195, and Outlook 55.