- The Three Basic Patterns of Office Solutions
- Office Automation Executables
- Office Add-Ins
- Code Behind a Document
- Conclusion
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 classes—one 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.
)
Figure 2-7 Using the New Project dialog to create an Excel Workbook project.
)
Figure 2-8 Selecting the workbook to associate with your code behind.
After we have created the project, the Designer view appears, as shown in Figure 2-9.
)
Figure 2-9 The design view for VSTO Excel code behind.
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.
)
Figure 2-10 The merging of Visual Studio and Excel menus.
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 object—in 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.
)
Figure 2-11 Creating a ListObject in the designer.
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.
)
Figure 2-12 The result of running Listing 2-7 and clicking the button we added to the Document Actions task pane.
We have briefly illustrated VSTO’s support for the Document Actions task pane and the ability to data bind that VSTO adds to Excel’s list object. For more information on VSTO’s support for the Document Actions task pane, see Chapter 15, "Working with Actions Pane." For more information on VSTO’s support for data binding, see Chapter 17, "VSTO Data Programming."