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

The code behind the file
The code behind the VSTO support document requires the developer to use the classes generated in the VSTO project with pre-connection context and pre-connect events. These classes are sometimes referred to as "code behind" classes because they are code that is associated with a particular document or worksheet. In Word, there is a code behind the class that corresponds to the document. In Excel, Classone in a workbook has multiple codes, and each worksheet or chart sheet in the workbook has one code.

When your code first runs in VSTO code after a document project, Office raises the startup event that is handled by any code that is behind the class that you create. VSTO provides context through the base class of code for the class that you are writing code for. The VSTO code that follows the class of the custom Excel worksheet originates from the base class of all the methods, properties, and events that contain the Excel worksheet. This enables you to write such code in the startup method of the worksheet class.

MessageBox.Show (String.Format ("{0} is the sheet name"this.) Name));

By using this. Name, you refer to the Name property of the Excel worksheet object that inherits from the base class. Listing 2-6 shows the VSTO code behind the class for Excel worksheet. In addition to the start and close methods in the code in the class, there is a build method named Internalstartup. You should not put any code in this internalstartup method because it is automatically generated by VSTO, and modifying it may break Visual Studio's support for the code behind the class. Instead, your startup code should be in the Startup event handler. The VSTO code behind the document class also uses a partial class to hide some of the additional code generated by VSTO.

Checklist 2-6 VSTO Excel Workbook customization

usingSystem;usingSystem.Data;usingSystem.Drawing;usingSystem.Windows.Forms;usingMicrosoft.VisualStudio.Tools.Applications.Runtime;usingExcel =Microsoft.Office.Interop.Excel;usingOffice =Microsoft.Office.Core;namespaceexcelworkbook1{ Public Partial classSheet1 {Private voidSheet1_Startup (Objectsender, EventArgs e) {      //Initial entry point. //This code gets run first, the code behind is created//The context is implicit in the Sheet1 classMessageBox.Show ("Code behind the document running."); MessageBox.Show (String.Format ("{0} is the sheet name", This.    Name)); }    Private voidSheet1_shutdown (Objectsender, EventArgs e) {    }    #regionVSTO Designer Generated code/// <summary>    ///Required method for Designer support-do not modify///The contents of this method with the code Editor. /// </summary>    Private voidInternalstartup () { This. Startup + =NewSystem.EventHandler (Sheet1_Startup);  This. Shutdown + =NewSystem.EventHandler (Sheet1_shutdown); }    #endregion  }}

VSTO code after a document in Excel
In this section, we use VSTO to create some simple code for the document in Excel. First, start VSTO and select File > New > Project menu item. Navigate to the Office node under the Visual C # root as described earlier.

We will use C # to create an Excel workbook project. If you already have a workbook that you want to add VSTO custom code to, you can specify its location in the dialog box that appears after you click OK in the New Project dialog box. This time, we will start from scratch and create a new, blank workbook.

Figure 2-8 Selecting the workbook associated with your code

Figure 2-7 Creating an Excel Workbook project using the New Project dialog box

After you create the project, the designer view appears, as shown in 2-9.

Figure 2-9 The Design view of the VSTO Excel code.

Take note of some interesting things in figure 2-9. First, Excel runs as a designer in Visual Studio 2005, the same as the Windows Forms Designer when you develop a Windows Forms project.

Next, look at the menu bar 2-10 shown. VSTO merges Visual Studio menus (build, debug, and so on) and Excel menu items (format, data, and so on) together. The menu items that are displayed in Visual Studio and Excel (such as tools) are merged by adding submenus to the Visual Studio menu, such as Microsoft Office Excel tools, which you can select to view the Excel Tools menu.

Figure 2-10 Merging Visual Studio and Excel menus

Third, note that the Toolbox in Figure 2-9 contains a new category: Excel control. When you use Visual Studio to design a document, you can create named scopes and list objects by using Excel menu items that Excel users are familiar with, or familiar toolbox habits for Visual Studio users.

Note, in this case, that the Properties window displays the properties of the selected object Sheet1. You can use the Properties window to edit the properties of an Excel object, which is the same as the properties of an edit control and form in a Windows Forms project.

Five, note that Solution Explorer already has four classes. Each underlying Excel worksheet and workbook object is represented by a. NET class that can be expanded and customized. When you make changes to a document in the designer, the following code is automatically updated. For example, drag a list object from the Toolbox onto the Sheet1 designer and draw it as a 10-row x4 column, as shown in 2-11.

Figure 2-11 Creating a ListObject in the designer

As you can see from the Properties window, the designer chooses a default name for the new list object. We can edit it, but in this case we will leave the default name List1.

Let's take a look at the code behind this worksheet and make some simple changes to it. Right-click Sheet1.cs in Solution Explorer and select View Code. We will briefly describe two VSTO features: ActionsPane and List Object data binding. We will declare a Windows Forms button as a member variable of the class and call it mybutton. In the launch event, we will display the button in the Document Actions task pane in Excel by adding it to the Controls collection in ActionsPane. Doing so causes Excel to display the Document Actions task pane and display our buttons. We will also handle the button's click event, and when we click the button we bind our list object to a randomly generated DataTable. Listing 2-7 shows this piece of code.

Listing 2-7 the VSTO customization of the Document Actions task pane and the Data add control binds the ListObject control to the data table

usingSystem;usingSystem.Data;usingSystem.Drawing;usingSystem.Windows.Forms;usingMicrosoft.VisualStudio.Tools.Applications.Runtime;usingExcel =Microsoft.Office.Interop.Excel;usingOffice =Microsoft.Office.Core;namespaceexcelworkbook1{ Public Partial classSheet1 {Button MyButton=NewButton ();    DataTable table; Private voidSheet1_Startup (Objectsender, EventArgs e) {Mybutton.text="databind!"; Mybutton.click+=NewEventHandler (MyButton_Click);    GLOBALS.THISWORKBOOK.ACTIONSPANE.CONTROLS.ADD (MyButton); }    voidMyButton_Click (Objectsender, EventArgs e) {List1.datasource=NULL; Table=NewDataTable (); Random R=NewRandom ();  for(inti =0; I <4; i++) table. Columns.Add ("Col"+i.tostring ());  for(inti =0; I < -; i++) table.      Rows.Add (R.nextdouble (), r.nextdouble (), r.nextdouble (), r.nextdouble ()); List1.datasource=table; }    Private voidSheet1_shutdown (Objectsender, EventArgs e) {    }    #regionVSTO Designer Generated code/// <summary>    ///Required method for Designer support-do not modify///The contents of this method with the code Editor. /// </summary>    Private voidInternalstartup () { This. Startup + =NewSystem.EventHandler (Sheet1_Startup);  This. Shutdown + =NewSystem.EventHandler (Sheet1_shutdown); }    #endregion  }}

Build and run the code, make sure Excel starts, raise the start event for the worksheet, and add the button to the actions pane. Click the button to generate a random DataTable and bind to the list object, as shown in 2-12. Exit Excel to end the debugging session.

Figure 2-12 Run listing 2-7 and click the result of the button we added to the Document Actions task pane

We briefly describe the support that VSTO has for the Document Actions task pane and the data binding capabilities of the list object that VSTO adds to Excel. For more information about VSTO support for the Document Actions task pane, see Chapter 15th, "Using the action pane." For more information about VSTO support for data binding, see Chapter 17th, "Programming VSTO data."

Conclusion
This chapter describes the three basic modes of Office solutions: an Automation executable file, an add-in, and code behind a document. This chapter also describes how to build solutions for these three basic patterns using Visual Studio 2005 and Visual Studio 2005 Tools for Office.

Now that you know how to create basic automation executables, add-ins, and code in a document solution, you will use these skills in the next chapter, as the focus on specific features of Excel,word,outlook and InfoPath can be used in your solution

This chapter is only described as an add-in and code in a document. The 24th chapter describes the VSTO add-ins for Outlook. The 23rd chapter describes the COM add-ins for Word and Excel. The 3rd chapter describes the Automation Add-ons for Excel. The 13th to 17th chapter describes the code behind the VSTO document model in detail.

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