Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources] نسخه متنی

Code Behind a Document

VSTO supports code behind a document by requiring that the developer use classes generated in a VSTO project that have pre-hooked-up context and pre-hooked-up events. These classes are sometimes called "code behind" classes because they are code associated with a particular document or worksheet. In Word, there is one code behind class corresponding to the document. In Excel, there are multiple code behind classesone for the workbook and one for each worksheet or chart sheet in the workbook.

The first time your code runs in a VSTO code behind the document project is when Office raises the Startup event handled by any of the code behind classes created for you. VSTO provides context via the base class of the code behind class you are writing code in. A VSTO code behind class customizing an Excel worksheet derives from a base class that contains all the methods, properties, and events of an Excel worksheet. This enables you to write code such as this in the Startup method of a worksheet class.

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

By using this.Name, you are referring to the Name property of the Excel Worksheet object inherited from the base class. Listing 2-6 shows a VSTO code behind class for an Excel Worksheet. In addition to the Startup and Shutdown methods in the code behind class, there is also a generated method called InternalStartup. You should not put any of your code in this InternalStartup method because it is auto-generated by VSTO and modifying it can break Visual Studio's support for code behind classes. Instead, your startup code should go in the Startup event handler. VSTO code behind document classes also use partial classes to hide some additional code generated by VSTO.

Listing 2-6. A VSTO Excel Workbook Customization

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace ExcelWorkbook1
{
public partial class Sheet1
{
private void Sheet1_Startup(object sender, EventArgs e)
{
// Initial entry point.
// This code gets run first when the code behind is created
// The context is implicit in the Sheet1 class
MessageBox.Show("Code behind the document running.");
MessageBox.Show(String.Format("{0} is the sheet name", 
this.Name));
}
private void Sheet1_Shutdown(object sender, EventArgs e)
{
}
#region VSTO Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(Sheet1_Startup);
this.Shutdown += new System.EventHandler(Sheet1_Shutdown);
}
#endregion
}
}

VSTO Code Behind a Document in Excel

In this section, we create some simple code behind a document in Excel using VSTO. First, start up VSTO and choose the File > New > Project menu item. As you have seen previously, navigate to the Office node under the Visual C# root.

We will create an Excel workbook project using C#. If you already have a workbook that you want to add VSTO customization code behind, you can specify its location in the dialog box shown in Figure 2-8 that appears after you click OK in the New Project dialog. This time we will just start from scratch, creating a new, blank workbook.

After we have created the project, the Designer view appears, as shown in Figure 2-9.

Notice a few interesting things in Figure 2-9. First, Excel is running inside Visual Studio 2005 as a designer, just the same as a Windows Forms designer would when developing a Windows Forms project.

Second, look at the menu bar shown in Figure 2-10. VSTO merges the Visual Studio menus (Build, Debug, and so on) and the Excel menu items (Format, Data, and so on) together. Menu items that appear in both Visual Studio and Excel (Tools, for example) merge by adding a submenu to the Visual Studio menu, such as Microsoft Office Excel Tools, that can be selected to see the Excel Tools menu.

Third, notice in Figure 2-9 that the toolbox contains a new category: Excel Controls. When designing a document using Visual Studio, you can create named ranges and list objects using the Excel menu items familiar to Excel users, or the toolbox idiom familiar to Visual Studio users.

Fourth, notice that the Properties window shows the properties of the selected objectin this case, Sheet1. You can use the Properties window to edit properties of Excel's objects the same way that you would edit properties of controls and forms in a Windows Forms project.

Fifth, notice that the Solution Explorer has four classes in it already. Each underlying Excel Worksheet and Workbook object is represented by a .NET class that you can extend and customize. As you make changes to the document in the designer, the code behind updates automatically. For example, drag a list object from the toolbox onto the Sheet1 designer, and draw it to be 10 rows by 4 columns, as shown in Figure 2-11.

As you can see from the Properties window, the designer has chosen a default name for the new list object. We could edit it, but in this example, we will keep 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 the Solution Explorer and choose View Code. We are going to briefly illustrate 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 Startup event, we will show that button in the Document Actions task pane of Excel by adding it to the ActionsPane's Controls collection. Doing so will cause Excel to show the Document Actions task pane and display our button. We will also handle the Click event of the button, and when the button is clicked we will data bind our list object to a randomly generated DataTable. Listing 2-7 shows this code.

Listing 2-7. A VSTO Customization That Adds a Control to the Document Actions Task Pane and Data Binds a ListObject Control to a DataTable

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace ExcelWorkbook1
{
public partial class Sheet1
{
Button myButton = new Button();
DataTable table;
private void Sheet1_Startup(object sender, EventArgs e)
{
myButton.Text = "Databind!";
myButton.Click += new EventHandler(myButton_Click);
Globals.ThisWorkbook.ActionsPane.Controls.Add(myButton);
}
void myButton_Click(object sender, EventArgs e)
{
List1.DataSource = null;
table = new DataTable();
Random r = new Random();
for (int i = 0; i < 4; i++)
table.Columns.Add("Col " + i.ToString());
for (int i = 0; i < 20; i++)
table.Rows.Add(r.NextDouble(), r.NextDouble(), 
r.NextDouble(), r.NextDouble());
List1.DataSource = table;
}
private void Sheet1_Shutdown(object sender, EventArgs e)
{
}
#region VSTO Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(Sheet1_Startup);
this.Shutdown += new System.EventHandler(Sheet1_Shutdown);
}
#endregion
}
}

Build and run the code, and sure enough Excel starts up, the Startup event is raised for the sheet, and the button is added to the actions pane. Click the button and a random DataTable is generated and bound to the list object, as shown in Figure 2-12. Exit Excel to end your debugging session.