This chapter introduced the three basic patterns of Office solutions: an automation executable, an add-in, and code behind a document. The chapter also introduced how to build solutions following these three basic patterns using Visual Studio 2005 and Visual Studio 2005 Tools for Office.
Now that you know how to create a basic automation-executable, add-in, and code-behind-the-document solution, you will use these skills in the
This chapter has
Part II: Office Programming in .NET
Chapter 3. Programming Excel
Ways to Customize Excel
Excel is the application most frequently programmed against in the Office family. Excel has a very rich object model, with 196 objects that combined have more than 4,500 properties and
As mentioned in Chapter 2, "Introduction to Office Solutions," the simplest way to integrate with Excel is to start Excel from a console application or Windows Forms application and automate it from that external program. Chapter 2 provides a sample of an automation executable that automates Word.
Excel can load a COM add-in that is a DLL that contains a class that implements IDTExtensibility2. The class that implements IDTExtensibility2 must be registered in the registry so that it can be
A COM add-in is typically written to add application-level functionalityfunctionality that is available to any workbook opened by Excel. You might write a COM add-in that adds a menu item to convert a currency in the selected Excel worksheet
Excel has a COM Add-Ins dialog box that enables users to turn COM add-ins on and off. Note that the dialog box that you access by choosing Add-Ins from the Tools menu is not the COM Add-Ins dialog box. That dialog box is used to
After completing these steps, click the COM Add-Ins toolbar button you added to a toolbar. Figure 3.1 shows the COM Add-Ins dialog box.
Figure 3.1. The COM Add-Ins dialog box in Excel.
You can add COM add-ins by using the Add button and remove them by using the Remove button. Typically, you will not have your users use this dialog box to manage COM add-ins. Instead, you will install and remove a COM add-in by manipulating registry settings with the installer you create for your COM add-in.
Excel discovers the installed COM add-ins by reading from the registry. You can view the registry on your computer by going to the Windows Start menu and choosing Run. In the Run dialog box, type regedit for the program to run and then click the OK button. Excel looks for COM add-ins in the registry keys under HKEY_CURRENT_USER\Software\Microsoft\ Office\Excel\Addins. Excel also looks for COM add-ins in the registry keys under HKEY_LOCAL_MACHINE\Software\Microsoft\Office\ Excel\Addins. COM add-ins registered under HKEY_LOCAL_MACHINE are not shown in the COM Add-Ins dialog box and cannot be turned on or off by users. It is recommended that you do not register your COM add-in under HKEY_LOCAL_MACHINE because it hides the COM add-in from the user.
COM add-ins are discussed in detail in Chapter 23, "Developing COM Add-Ins for Word and Excel."
Automation add-ins are classes registered in the registry as COM objects that expose public functions that can be used in Excel formulas. Automation add-ins that have been installed are shown in the Add-Ins dialog box, which you can display by choosing Add-Ins from the Tools menu. This chapter examines automation add-ins in more detail during the discussion of how to create user-defined Excel functions for use in Excel formulas.
Visual Studio Tools for Office Code Behind
VSTO enables you to put C# or Visual Basic code behind Excel templates and workbooks. VSTO was designed from the ground up for C# and Visual Basicso this model is the most ".NET" of all the models used to customize Excel. This model is used when you want to customize the behavior of a particular workbook or a particular set of workbooks created from a common template. You might create a template for an expense-reporting workbook that is used whenever
VSTO's support for code behind a workbook is discussed in detail in Part III of this book.
Smart Documents and XML Expansion Packs
Smart Documents are another way to associate your code with an Excel template or workbook. Smart Documents rely on attaching an XML schema to a workbook or template and associating your code with that schema. The combination of the schema and associated code is called an XML Expansion Pack. An XML Expansion Pack can be associated with an Excel workbook by choosing Data > XML > XML Expansion Packs. Figure 3.2 shows the XML Expansion Packs dialog box.
Figure 3.2. The XML Expansion Packs dialog box in Excel.
When an XML Expansion Pack is attached to a workbook, Excel loads the associated code and runs it while that workbook is opened. Smart Document solutions can create a custom user interface in the Document Actions task pane. You can view the task pane in Excel by choosing Task Pane from the View menu. Figure 3.3 shows a custom Document Actions task pane in Excel.
Figure 3.3. A custom Document Actions task pane in Excel.
It is possible to write Smart Document solutions "from scratch" in C# or Visual Basic. This book does not cover this approach. Instead, this book focuses on the VSTO approach, which was designed to make Smart Document development much easier and to allow you to create a custom Document Actions task pane by using Windows Forms. Chapter 15, "Working with the Actions Pane," discusses this capability in more detail.
Smart Tags enable displaying a pop-up menu that contains actions relevant for a recognized piece of text in a workbook. You can control the text that Excel recognizes and the actions that are made available for that text by creating a Smart Tag DLL or by using VSTO code behind a document.
A Smart Tag DLL contains two types of components that are used by Excel: a recognizer and associated actions. A recognizer determines what text in the workbook is recognized as a Smart Tag. An action corresponds to a menu command displayed in the pop-up menu.
A recognizer could tell Excel to recognize stock-ticker symbols (such as the MSFT stock symbol) and display a set of actions that can be taken for that symbol: buy, sell, get the latest price, get history, and so on. A "get history" action, for example, could launch a Web browser to show a stock-history Web page for the stock symbol that was recognized.
When a recognizer recognizes some text, Excel displays a little triangle in the bottom-right corner of the associated cell. If the user hovers over the cell, a pop-up menu icon appears
Figure 3.4. Smart Tags in Excel.
Smart Tags are managed from the Smart Tags tab of the AutoCorrect dialog box, as shown in Figure 3.5. You can display the Smart Tags tab by choosing AutoCorrect Options from the Tools menu. Here, the user can turn on and off individual recognizers, as well as control other options relating to how Smart Tags display in the workbook.
Figure 3.5. The Smart Tags tab of the AutoCorrect dialog box.
VSTO provides a simple model for creating a Smart Tag that works at the workbook or template level. Chapter 16, "Working with Smart Tags in VSTO," describes the VSTO model for working with Smart Tags in more detail.
It is possible to write Smart Tag recognizer and action classes in a DLL that work at the application level, but it is much more complex than the VSTO model. Chapter 16 also describes that approach.
Also available in the Add-Ins dialog box (shown by selecting Add-Ins from the Tools menu) are XLA add-ins. An XLA add-in starts life as a workbook that has VBA code behind it. The developer can then save the workbook as an XLA or Excel add-in file by choosing Save As from the File menu and selecting XLA as the file format. An XLA file acts as an application-level add-in in the form of an invisible workbook that stays
VSTO enables you to write code on the server that populates an Excel workbook with data without starting Excel on the server. You might create an ASP.NET page that reads some data out of a database and then puts it in an Excel workbook and returns that workbook to the client of the Web page. VSTO provides a class called ServerDocument that makes it easy to do this. You can also use the XML file formats of Office to generate Excel documents in XML formats on the server, but this procedure is much more complex. In addition, the Excel XML file format is lossy, meaning that you cannot represent everything in an Excel spreadsheet in the Excel XML format. For this reason, we prefer the ServerDocument approach when generating documents on the server over the Excel XML file format.
Chapter 18, "Server Data Scenarios," describes generating documents on the server with ServerDocument.
Excel has a Research task pane that enables you to enter a search term and search various sources for that
Figure 3.6. The Research task pane.
Excel enables developers to write a special Web service called a research service that implements a set of Web methods defined by Excel. A research service can be registered with Excel and used in Office's Research task pane. You might write a research service that searches for a search term in a company database, for example.
Chapter 6, "Programming Word," discusses creating a research service in more detail.
Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath
VSTO for Mere Mortalsu2122: A VBA Developer's Guide to Microsoft Office Development Using Visual Studio 2005 Tools for Office
Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)
Professional Office Business Application Development: Using Microsoft Office SharePoint Server 2007 and VSTO (Wrox Programmer to Programmer)