- 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
Visual Studio Tools for Office 2005 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. A VSTO project can have multiple startup classes that handle a Startup and Shutdown event raised on each startup class. In Word, there is only one startup class corresponding to the document. In Excel, there are multiple startup 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 startup classes created for you. VSTO provides context via the base class of the class you are writing code in. A VSTO class customizing an Excel worksheet derives from a base class that aggregates all the methods, properties, and events of an Excel worksheet. This enables you to write code like this in the Startup method of a worksheet class.
MessageBox.Show(this.Name + " is the sheet name.");
By using this.Name, you are referring to the Name property of the Excel Worksheet object aggregated by the base class. Listing 2-6 shows a VSTO 2005 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 as it is auto-generated by VSTO 2005 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 2005 code behind class.
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, System.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(this.Name + " is the sheet name. ");
}
private void Sheet1_Shutdown(object sender, System.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 2005 Code behind a Document in Excel
In this section, we are going to create some simple code behind a document in Excel using VSTO 2005. First, start up VSTO 2005 and select the File / New / Project menu item. As we've seen previously, navigate to the Office node under the Visual C# root.
)
Figure 2-7: Using the New Project dialog to create an Excel Workbook project.
We will create an Excel Workbook project using C#. If you already have a workbook which you would like to add VSTO 2005 customization code behind, the dialog box shown in Figure 2-8 pops up and asks you where it can be found. This time we will just start from scratch, creating a new, blank workbook.
)
Figure 2-8: Selecting the workbook to associate with your code behind.
Once we have created the project, the designer view appears as shown in Figure 2-9.
)
Figure 2-9: The design view for VSTO 2005 Excel code behind.
There are a few interesting things to notice in Figure 2-9. First, Excel is running inside the Visual Studio 2005 IDE as a designer, just the same as a Windows Forms designer would when developing a Windows Forms project.
Second, look at the menu bar as 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 sub menu 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 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, let's drag a ListObject from the toolbox onto the Sheet1 designer, and draw it to be ten rows by four 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 on Sheet1.cs in the Solution Explorer and select View Code. We are going to briefly illustrate two VSTO features—ActionsPane and ListObject databinding. 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. This 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 databind our list object to a randomly generated DataTable. This code is shown in Listing 2-7.
Listing 2-7: A VSTO 2005 code behind class that illustrates adding a control to the Document Actions task pane and databinding a ListObject control to a DataTable.
using System;
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, System.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, System.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 ListObject 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 on 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 databind that VSTO adds to Excel's ListObject. For more information on VSTO's support for the Document Actions task pane, see Chapter 15. For more information on VSTO's support for databinding, see Chapter 17.