The VSTO Programming Model

The VSTO Programming Model

VSTO Extensions to Word and Excel Objects

Dynamic Controls

Advanced Topic: Dynamic Host Items

Advanced Topic: Inspecting the Generated Code

VSTO Extensions to the Word and Excel Object Models

Conclusion

In Windows Forms programming, a form is a window that contains controls, such as buttons, combo boxes, and so on. To implement a form, you can drag and drop controls from the Visual Studio toolbox onto the form's designer. The form designer then generates a customized subclass of the Form class. Because each form is implemented by its own class, you can then further customize the form code by adding properties and methods of your own to the class. And because all the controls are added as properties on the form class, you can use IntelliSense to more rapidly program those custom methods.

VSTO's system of host items and host controls is directly analogous to Windows Forms. By "host" we mean the applicationWord or Excelwhich hosts the customization. Host items are like forms: programmable objects that contain user interface elements called host controls. The Workbook, Worksheet, and Chartsheet objects are host items in Excel; the Document object is the sole host item in Word. In Outlook, the Outlook Application object is exposed as a host item.

As we saw back in Chapter 2, "Introduction to Office Solutions," the Visual Studio Excel and Word designers create custom classes which extend the Worksheet and Document base classes. As you place host controls such as lists, named ranges, charts and buttons onto the worksheet they are exposed as fields on the customized subclass.

Separation of Data and View

Some people use spreadsheet software solely for its original purpose: to lay out financial data on a grid of cells that automatically recalculates sums, averages and other formulas as they update the data. For example, you might have a simple Excel spreadsheet that calculates the total expenses for a wedding given all the costs involved. Similarly, some people use word-processing software solely for its original purpose: to automatically typeset letters, memos, essays, books and other written material.

However, in a business setting spreadsheets and documents have evolved to have both high internal complexity and external dependencies. Unlike a wedding budget, a spreadsheet containing an expense report or a document containing an invoice is likely to be just one small part of a much larger business process. This fact has implications on the design of a programming model. Consider this VBA code that might be found in a spreadsheet that is part of a larger business process:

SendUpdateEmail ThisWorkbook.Sheets(1).Cells(12,15).Value2

 

Clearly, the unreadable snippet is sending an e-mail to someone, but because the Excel object model emphasizes how the spreadsheet represents the data, not what the data represent, it is hard to say what exactly this is doing. The code is not only hard to read, it is brittle; redesigning the spreadsheet layout could break the code. We could improve this code by using a named range rather than a hard-coded direct reference to a particular cell:

SendUpdateEmail ThisWorkbook.Names("ApproverEmail").RefersToRange.Value2

 

Better, but it would be even nicer if the particular range showed up in IntelliSense. VSTO builds a convenient custom object model for each worksheet, workbook, or document so that you can more easily access the named items contained therein:

SendUpdateEmail(ExpenseReportSheet.ApproverEmail.Value2);

 

A more readable, maintainable, and discoverable object model is a welcome addition. However, even in the preceding snippet, the VSTO programming model still does not address the more fundamental problem: We are manipulating the data via an object model that treats them as part of a spreadsheet. The spreadsheet is still the lens through which we see the data; instead of writing a program that manipulates ice cream sales records, we wrote a program that manipulates a list and a chart.

The crux of the matter is that Word and Excel are editors; they are for designing documents that display data. Therefore, their object models thoroughly conflate the data themselves with the "view," the information about how to display them. To mitigate this conflation, the VSTO programming model was designed to enable developers to logically separate view code from data code. Host items and host controls represent the "view" elements; host items and host controls can be data bound to classes that represent the business data.

Model-View-Controller

If you're familiar with design patterns, you will have already recognized this as based on the Model-View-Controller (MVC) design pattern. In the MVC pattern, the data model code represents the business data and the processes that manipulate it. The view code reads the data, listens to Change events from the data, and figures out how to display it. The controller code mediates between the view and the data code, updating the data based upon the gestures the user makes in the view (mouse clicks, key presses, and so on).

Figure 13-1. Model-View-Controller architecture.

 

Benefits of Separation

Logically separating the data code from the view code leads to a number of benefits when building more complex business documents on top of Word and Excel:

• Business data and rules can be encapsulated in ADO.NET datasets and reused in different applications.
• Changes to view code are less likely to unexpectedly break data code (and vice versa).
• Data code can cache local copies of database state for offline processing.
• Server-side code can manipulate cached data inside the document without starting up Word/Excel.

Now that you know some of the design philosophy behind VSTO, let's take a look at how the host items and host controls actually extend the Word and Excel object models. (The data side is covered in Chapter 17, "VSTO Data Programming," and server-side data manipulation is covered in Chapter 18, "Server Data Scenarios.")