لطفا منتظر باشید ...
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 methods. It supports several models for integrating your code, including add-ins and code behind documents. Most of these models were originally designed to allow the integration of COM components written in VB 6, VBA, C, or C++. However, through COM interop, managed objects written in C# or Visual Basic can masquerade as COM objects and participate in most of these models. This chapter briefly considers several of the ways that you can integrate your code with Excel and refers you to other chapters that discuss these approaches in more depth. This chapter also explores building user-defined functions for Excel and introduces the Excel object model.
Automation Executable
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.
COM Add-Ins
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 discovered and talked to like other COM add-ins that extend Excel.A COM add-in is typically written to add application-level functionalityfunctionality that is available to any workbook opened by Excel. For example, you might write a COM add-in that adds a menu item to convert a currency in the selected Excel worksheet cell to another currency based on current exchange rates.Excel has a COM Add-Ins dialog box that enables users to turn COM add-ins on and off. Note that the dialog that is accessed by choosing Add-Ins from the Tools menu is not the COM Add-Ins dialog. That dialog is used to turn on and off automation add-ins and XLA add-ins, which are discussed later in this chapter. To access the COM Add-Ins dialog, you must perform the following steps:
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.
Figure 3-1. The COM Add-Ins dialog in Excel.
Automation Add-Ins
Automation add-ins are classes that are 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, 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
Visual Studio 2005 Tools for Office (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. For example, you might create a template for an expense reporting workbook that is used whenever anyone in your company creates an expense report. This template can add commands and functionality that are always available when the workbook created with it is opened.Part Three 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 XML Expansion Packs from the XML menu in the Data menu. Figure 3-2 shows the XML Expansion Packs dialog.
Figure 3-2. The XML Expansion Packs dialog in Excel.
Figure 3-3. A custom Document Actions task pane in Excel.
Smart Tags
Smart Tags enable a pop-up menu to be displayed containing 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 be created that tells 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 instance, 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 lower-right corner of the associated cell. If the user hovers over the cell, a pop-up menu icon appears next to the cell that the user can click to drop down a menu of actions for the recognized piece of text. Figure 3-4 shows an example menu. When an action is selected, Excel calls back into the associated action to execute your code.
Figure 3-4. Smart Tags in Excel.
Figure 3-5. The Smart Tags page in the AutoCorrect dialog.
XLA Add-Ins
Also found in the Add-Ins dialog (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 using 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 open for the lifetime of Excel. Although it is possible to save a workbook customized with VSTO as an XLA file, many of the features of VSTO do not work when the workbook is converted to an XLA file. Some of the features that do not work include VSTO's support for the Document Actions task pane and for Smart Tags. For this reason, Microsoft does not support or recommend saving a workbook customized with VSTO as an XLA file. Therefore, this book does not cover it further.
Server-Generated Documents
VSTO enables you to write code on the server that populates an Excel workbook with data without starting Excel on the server. For example, 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 is much more complex. In addition, the Excel XML file format is lossy, meaning 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.
Research Services
Excel has a task pane called the Research task pane that enables you to enter a search term and search various sources for that search term. Figure 3-6 shows the Research task pane.
Figure 3-6. The Research task pane.
