Developing Application-Level Data-Centric Solutions Using Visual Studio 2005 Tools for the Office System SE
Summary: Learn how to create a Microsoft Visual Studio 2005 Tools for the Microsoft Office system add-in that enables the user to query a data source and view the results. After you understand the process of creating a data-driven task pane, you learn how to add data from the result set to the active document. (28 printed pages)
Ken Getz, MCW Technologies, LLC
Jan Fransen, A23 Consulting
November 2007
Applies to: Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System Second Edition, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, Microsoft Office Word 2007
Contents
Application-Level Data-Centric Solution Overview
Investigating the Finished Add-in
Creating the Add-in Project
Adding the Custom Task Pane
Adding a Data Source
Laying Out the Custom Task Pane
Hooking Up the Data Binding
Interacting with the Host Application
Showing and Hiding the Task Pane
Creating a Data-Centric Task Pane for Excel
Creating a Data-Centric Task Pane for Word
Conclusion
Additional Resources
Application-Level Data-Centric Solution Overview
Ever since Microsoft introduced the task pane to the Microsoft Office user interface palette, developers sought ways to make use of that screen real estate in their own Microsoft Office solutions. With Microsoft Office 2003, Microsoft provided limited developer access to the task pane in two ways: The Research task pane and Smart Documents. The Research task pane enables the user to search a data source and view the results. Developers created the user interface through a simple HTML-like markup, and were limited to smart tag actions if they wanted to interact with the host’s active document. Smart Documents enabled the developer to use the Actions pane in Microsoft Office Word 2003 and Microsoft Office Excel 2003 XML-based documents.
Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office system opened up the task pane to developers in a new way. It enabled developers to add Windows Forms controls to the task pane and interact with the active document through the host’s object model. However, Visual Studio 2005 Tools for Office solutions could be used only with document-level solutions.
Visual Studio 2005 Tools for the 2007 Microsoft Office system Second Edition extends the development tools already available for document-level solutions. Now developers can create Visual Studio Tools for Office Second Edition add-ins that use Windows Forms controls in the task pane as part of an application-wide solution.
In this article, you learn how to create a Visual Studio Tools for Office Second Edition add-in that lets the user query a data source and view the results. When you understand the process of creating a data-driven task pane, you learn how to add data from the result set to the active document, whether it is a Microsoft Office Word document, a Microsoft Office Excel workbook, or a Microsoft Office PowerPoint presentation.
Investigating the Finished Add-in
In this article, you learn how to use Visual Studio 2005 Tools for Office Second Edition to develop add-ins for three products: Microsoft Office PowerPoint 2007, Microsoft Office Word 2007, and Microsoft Office Excel 2007. The add-ins all include an identical task pane that draws data from the Northwind sample database, as shown in Figure 1.
Figure 1. Filtering product list by category and selecting a product
.gif "Filtering product list by category")
Only the code that interacts with the host application—PowerPoint 2007, Excel 2007, or Word 2007—is different from one add-in to the next. If a PowerPoint 2007 user chooses a product and clicks Insert Selected Product, the add-in code inserts a new slide that includes the product name as the title and information about the product, such as price and units in stock, as bullet points. In Excel 2007, clicking this button adds product information as a row in the active worksheet. In Word 2007, clicking the button adds product information to a new table at the cursor. If the cursor is already in a table of the appropriate size, the add-in adds the product information to that table as a new row.
Creating the Add-in Project
As with any other managed add-in, you must start by creating a new project in Microsoft Visual Studio 2005. Because each Office add-in that you create requires a separate project, when you use Visual Studio 2005 Tools for Office Second Edition, you must repeat these steps for each different add-in that you create.
Follow these steps to create the sample PowerPoint add-in.
To create a PowerPoint add-in
In Visual Studio 2005, press CTRL+N to create a new project.
In the Project types pane, select the language of your choice (Microsoft Visual Basic or Microsoft Visual C#), and expand the Office node.
Select 2007 Add-ins, and in the Templates pane, select PowerPoint Add-in.
Enter a meaningful name for your add-in and a location in which to save the new project (see Figure 2). Click OK when you are finished.
Figure 2. Creating a new 2007 Microsoft Office system add-in
.gif "Creating a new 2007 Office system add-in")
Visual Studio 2005 creates and loads your add-in solution. The Visual Studio 2005 Tools for Office Second Edition add-in template includes two projects: the actual add-in project, and a setup project that makes it easier for you to test and deploy your add-in.
Adding the Custom Task Pane
To create a custom task pane, you can add a new item to your project, and then add controls and code to that new task pane. Follow these steps to create the new task pane.
To create a task pane
In Solution Explorer, right-click your add-in project (the second node in the tree displayed in the window), click Add, and then click User Control.
In the Add New Item dialog box, set the name for your user control to NorthwindTaskPane, and click Add.
At this point, you created a new task pane. Of course, it does not actually do anything yet, but you can still configure and view the task pane in PowerPoint 2007. Continue following the steps to display the task pane.
In Solution Explorer, find the node that corresponds to the add-in class that the template created for you (ThisAddIn.vb or ThisAddIn.cs). Right-click the node, and then click View Code.
At the top of the file, add the following statement, which makes it easier to write code using the CustomTaskPane class.
Imports Microsoft.Office.Toolsusing Microsoft.Office.Tools; using PowerPoint = Microsoft.Office.Interop.PowerPoint;Within the ThisAddIn class, find the ThisAddIn_Startup procedure. Modify the procedure so that it looks like the following code, which creates a new CustomTaskPane instance and displays the task pane.
Public ctp As CustomTaskPane = Nothing Private Sub ThisAddIn_Startup( _ ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles Me.Startup ctp = _ Me.CustomTaskPanes.Add(New NorthwindTaskPane, _ "Northwind Data") ctp.Width = 200 ctp.Visible = True End Subpublic CustomTaskPane ctp = null; private void ThisAddIn_Startup( object sender, System.EventArgs e) { CustomTaskPane ctp = this.CustomTaskPanes.Add( new NorthwindTaskPane(), "Northwind Data"); ctp.Width = 200; ctp.Visible = true; }This code first creates a new instance of the user control you created and passes this new instance to the Add method of the CustomTaskPanes property of the current add-in class along with the caption for the new task pane. The Add method returns a reference to the new custom task pane. The following code sets the width of the new task pane, and finally makes the task pane visible.
.gif "Note")
NoteYou might also find it interesting to investigate other properties of the CustomTaskPane class, including the DockPosition property (allows you to specify where you want your task pane docked) and DockPositionRestrict property (allows you to restrict your task pane docking—you can disallow vertical docking, for example).
At this point, you can test your new task pane (although it does not yet provide any functionality).
To test the task pane
Click File, and then click Save All to save your solution.
Press CTRL+F5 to start debugging.
Visual Studio loads PowerPoint 2007 with your task pane displayed, as shown in Figure 3.
Figure 3. Task pane not yet functional in PowerPoint 2007
.gif "Task pane not yet functional in PowerPoint")
Close PowerPoint 2007 to return to Visual Studio—now you can add some functionality to the add-in.
Adding a Data Source
Working with data in a custom task pane is similar to working with data in a typical Windows-based application. You follow the same set of steps to add data functionality to a Visual Studio 2005 Tools for Office Second Edition add-in as you would when you create a Windows form.
Before you create a data source, make sure that you have the Northwind sample database available. This demonstration assumes that you have the Microsoft SQL Server 2000 version of the sample database available, attached to an instance of SQL Server that you can use for the sample. If you are using SQL Server 2000, you most likely have the Northwind sample database available. If you are using Microsoft SQL Server 2005, or Microsoft SQL Server 2005 Express, you most likely do not have the sample available to you. To download the sample, see Northwind and Pubs Sample Databases for SQL Server 2000. The remainder of this demonstration assumes that you are using SQL Server 2005 Express edition, and that you have the Northwind sample database available to you.
Follow these steps to add a data source that refers to the Northwind sample database.
To add a data source that refers to the Northwind database
In Visual Studio 2005, click View, and then click Server Explorer.
If you already set up a connection to the Northwind sample database, you can go to step 8.
In Server Explorer, right-click Data Connections, and then click Add Connection.
In the Choose Data Source window, click Microsoft SQL Server, and then click Continue.
In the Add Connection dialog box, in the Server name field, enter the value .\SQLEXPRESS. (If you are using a different instance of SQL Server, enter the name of the instance here.)
If you already attached the Northwind sample database to your instance of SQL Server, in the Select or enter a database name drop-down list, select Northwind, and go to step 7. If you do not see Northwind in the list, go to step 6.
Select the Attach a database file option, click Browse, and find the NORTHWND.MDF file.
Set the Logical Name field to Northwind.
The Add Connection dialog box should look like Figure 4.
Figure 4. Setting up a connection to Northwind sample database
.gif "Setting up connection to Northwind database")
Click Test Connection to verify that you can access the data in the Northwind sample database, and then click OK.
Server Explorer now displays a new data connection. If you see a name other than Northwind in Server Explorer, you can right-click the connection, click Rename, and enter Northwind as the connection name.
NoteIf you followed the previous steps, you are now using an attached copy of the Northwind sample database. The ability to refer to an attached database file (as opposed to using SQL Server to attach the database before you use it) is a nice addition to the functionality in SQL Server 2005.
Click Data, and then click Show Data Sources.
Visual Studio 2005 displays the Data Sources window.
Click Data, and then click Add New Data Source (or click the link in the Data Sources window to create a new data source).
In the Data Source Configuration Wizard, on the Choose a Data Source Type page, select Database, and then click Next.
On the Choose Your Data Connection page, select Northwind in the drop-down list.
NoteThis page includes an option to create a new connection—you could have used this option rather than creating the connection explicitly in the previous steps.
Click Next.
Depending on how you configured your data source, you may be asked whether you want to copy the attached file into the current project. Although there are benefits to copying the data file locally, such as data separation, there is no point in copying it for this example. Click No, if prompted.
On the Save the Connection String to the Application Configuration File page, accept the default option, and click Next.
On the Choose Your Database Objects page, select Categories and Products, and change the DataSet name to NorthwindDataSet.
The dialog box should look like Figure 5. Click Finish.
Figure 5. Setting dataset options
.gif "Setting dataset options")
Your project now includes a new item, NorthwindDataSet.xsd, and its supporting files.
In Solution Explorer, right-click NorthwindDataSet.xsd, and then click View Designer. In the designer, you see two TableAdapter instances (one for each DataTable in the DataSet), as shown in Figure 6.
Figure 6. DataSet containing two distinct TableAdapter instances
.gif "DataSet containing two TableAdapter instances")
By default, each TableAdapter provides queries to get and fill a DataTable (Fill and GetData). To display a list of products that match a specified category, you can add a second query, set up to retrieve products within a particular category. Follow these steps to add this query.
To add a query
Right-click the Products TableAdapter, click Add, and then click Query.
On the Choose a Command Type page, click Next to accept the default option (to create a new query by using a SELECT SQL statement).
On the Choose a Query Type page, click Next to accept the default option (to create a SELECT statement that returns rows).
On the Specify a SQL SELECT statement page, modify the existing SQL statement so that it looks like the following, adding the WHERE clause.
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM dbo.Products WHERE CategoryID=@CategoryIDClick Next.
On the Choose Methods to Generate page, modify the names of the methods to FillByCategoryID and GetDataByCategoryID.
Click Finish.
Products TableAdapter now displays a second row containing the new queries.
Click File, and then click Save All.
Click Window, and then click Close All Documents.
Laying Out the Custom Task Pane
Now that you have created the data source, you can create the controls on the custom task pane and bind them to the data that you retrieve by using the data source. When you are creating Windows-based applications and need to use data binding, it is often easiest to just drag items from the Data Sources window directly to the form designer. When you build a user interface that displays a single data item at a time, the drag-and-drop support does not help as much—in these circumstances, it is generally easier to lay out the interface, and then hook up the data binding. That is the approach you will see here.
Follow these steps to create the content in the custom task pane.
To create content in the custom task pane
In Solution Explorer, right-click the NorthwindTaskPane node, and then click View Designer.
Make sure that the Toolbox is visible: click View, and then click Toolbox.
Add controls, and set the control properties listed in Table 1, so that the controls appear as shown in Figure 7. (You can set other layout properties, but the properties listed in Table 1 are the most important properties for this demonstration.)
Table 1. Set properties for your controls
Control
Property
Value
PictureBox
Name
categoryPictureBox
Size
100,70
SizeMode
CenterImage
Label
Text
Select a category:
ComboBox
Name
categoryComboBox
DropDownStyle
DropDownList
Label
Text
Select a product:
ComboBox
Name
productComboBox
DropDownStyle
DropDownList
Button
Name
insertButton
Text
Insert Selected Product
Figure 7. Layout of controls in the task pane designer
.gif "Layout of controls")
Hooking Up the Data Binding
Now that you have laid out your task pane, you can set up the data binding. In the following steps, you bind the PictureBox and the two ComboBox controls to retrieve data from the data source you created. In addition, you add code to handle necessary events.
To hook up data binding for the PictureBox control
In the Designer window for the task pane, select the PictureBox control.
In the Properties window, expand the DataBindings property.
In the Image property setting, click the drop-down arrow to the right of the property value. Expand nodes until you find the Picture property in the Categories TableAdapter, as shown in Figure 8.
Figure 8. Selecting the data source for the Image property of the PictureBox control
.gif "Selecting data source for the Image property")
When you set up data binding for the PictureBox control, the Visual Studio form designer adds three objects to the form’s tray area:
A DataSet instance (NorthwindDataSet)
A BindingSource instance (CategoriesBindingSource)
A TableAdapter instance (CategoriesTableAdapter)
You can use the CategoriesBindingSource as the data source for the corresponding ComboBox control also. Follow these steps to hook up data binding for the ComboBox control, and then test the data binding in PowerPoint.
To hook up data binding for the ComboBox control
In the Designer window for the task pane, select the categoryComboBox control.
In the Properties window, set the DataSource property to CategoriesBindingSource.
Set the DisplayMember property to CategoryName, and set the ValueMember property to CategoryID.
In the Designer window for the task pane, select the task pane itself by clicking in the task pane, but not within a control.
(You can also select the task pane in the drop-down list at the top of the Properties window.)
In the Properties window, click the Events button on the toolbar to display the list of task pane events.
(Notice the long list of events—the CustomTaskPane class provides a very rich object model.)
Double-click the Load event to create the Load event handler for the task pane.
In the task pane’s Load event handler, add the following line of code.
CategoriesTableAdapter.Fill(NorthwindDataSet.Categories)categoriesTableAdapter.Fill(northwindDataSet.Categories);Save and run the project.
You should see PowerPoint with your add-in loaded, displaying the task pane with its list of categories. You should see the image that corresponds to the selected category. Select a different category, and the image should update to match.
Close PowerPoint when you are finished, returning to Visual Studio.
In Solution Explorer, right-click NorthwindTaskPane, and then click View Designer.
In the Designer window for the task pane, select productComboBox.
In the Properties window, in the DataSource property, click the drop-down arrow to the right of the property value. Expand the available nodes, select Other Data Sources, select Project Data Sources, select NorthwindDataSet, and then select Products.
This adds a new BindingSource named ProductsBindingSource and a new TableAdapter named ProductsTableAdapter to the task pane.
For productComboBox, set the DisplayMember property to ProductName, and the ValueMember property to ProductID.
Double-click categoryComboBox to open the Code Editor so you can enter code into the SelectedIndexChanged event handler.
Add the following procedure to the task pane's class.
Private Sub FillProducts() ProductsTableAdapter.FillByCategoryID( _ NorthwindDataSet.Products, _ CInt(categoryComboBox.SelectedValue)) End Subprivate void FillProducts() { productsTableAdapter.FillByCategoryID( northwindDataSet.Products, (int)categoryComboBox.SelectedValue); }Modify the task pane Load event handler by adding a call to the FillProducts method.
Private Sub NorthwindTaskPane_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load CategoriesTableAdapter.Fill(NorthwindDataSet.Categories) FillProducts() End Subprivate void NorthwindTaskPane_Load( object sender, EventArgs e) { categoriesTableAdapter.Fill(northwindDataSet.Categories); FillProducts(); }Click View, and then click Designer.
Select categoryComboBox, and in the Properties window, double-click the SelectedIndexChanged event.
Modify the Load event handler to call the FillProducts method.
Private Sub categoryComboBox_SelectedIndexChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles categoryComboBox.SelectedIndexChanged FillProducts() End Subprivate void productComboBox_SelectedIndexChanged( object sender, EventArgs e) { FillProducts(); }Although you do not use the information until later in this example, you need to retrieve a strongly typed row that corresponds to the selected product.
Add the following procedure to the task pane's class, so that you can retrieve the product information when you add host application-specific code to the project.
This code casts the row returned by the Current property of the BindingSource into a strongly typed ProductsRow.
Private Function GetProduct() As NorthwindDataSet.ProductsRow Return CType( _ CType(ProductsBindingSource.Current, DataRowView).Row, _ NorthwindDataSet.ProductsRow) End Functionprivate NorthwindDataSet.ProductsRow GetProduct() { return (NorthwindDataSet.ProductsRow) (((DataRowView)productsBindingSource.Current).Row); }Save and run the project. You should see PowerPoint with the add-in loaded, displaying the task pane. As you select categories, you should see the image and list of products change to match.
Exit PowerPoint when you are finished, returning to Visual Studio.
Interacting with the Host Application
Now that you created the task pane and bound its controls to the appropriate data sources, you can interact with the host application. In this example, you create a new slide in PowerPoint, and then your code inserts information about the product that you selected into the new slide. (Of course, when you revise the add-in to work with Excel or Word, your host interaction code performs slightly different tasks.)
To interact with the host application
Add the following procedure to the NorthwindTaskPane class that you edited.
Private Sub InsertInformation() ' This procedure interacts with the host application, ' inserting content based on the selected product. Dim product As NorthwindDataSet.ProductsRow = GetProduct() Dim app As PowerPoint.Application = _ Globals.ThisAddIn.Application Dim presentation As PowerPoint.Presentation = Nothing ' Create a new presentation, if necessary. If app.Presentations.Count > 0 Then presentation = app.ActivePresentation Else presentation = app.Presentations.Add( _ Microsoft.Office.Core.MsoTriState.msoTrue) End If Dim slide As PowerPoint.Slide = _ presentation.Slides.Add(presentation.Slides.Count + 1, _ PowerPoint.PpSlideLayout.ppLayoutText) slide.Shapes(1).TextFrame.TextRange.Text = _ product.ProductName Dim sw As New System.IO.StringWriter sw.WriteLine("Category: {0}", categoryComboBox.Text) sw.WriteLine("Quantity per unit: {0}", _ product.QuantityPerUnit) sw.WriteLine("Unit Price: {0:C}", product.UnitPrice) sw.WriteLine("Units in stock: {0}", product.UnitsInStock) sw.WriteLine("Reorder level: {0} units", _ product.ReorderLevel) slide.Shapes(2).TextFrame.TextRange.Text = sw.ToString() slide.Select() End Subprivate void InsertInformation() { NorthwindDataSet.ProductsRow product = GetProduct(); PowerPoint.Application app = Globals.ThisAddIn.Application; PowerPoint.Presentation presentation = null; // Create a new presentation, if necessary. if (app.Presentations.Count > 0) presentation = app.ActivePresentation; else presentation = app.Presentations.Add( Microsoft.Office.Core.MsoTriState.msoTrue); PowerPoint.Slide slide = presentation.Slides.Add( presentation.Slides.Count + 1, PowerPoint.PpSlideLayout.ppLayoutText); slide.Shapes[1].TextFrame.TextRange.Text = product.ProductName; System.IO.StringWriter sw = new System.IO.StringWriter(); sw.WriteLine("Category: {0}", categoryComboBox.Text); sw.WriteLine("Quantity per unit: {0}", product.QuantityPerUnit); sw.WriteLine("Unit Price: {0:C}", product.UnitPrice); sw.WriteLine("Units in stock: {0}", product.UnitsInStock); sw.WriteLine("Reorder level: {0} units", product.ReorderLevel); slide.Shapes[2].TextFrame.TextRange.Text = sw.ToString(); slide.Select(); }In Solution Explorer, right-click NorthwindTaskPane, and then click View Designer.
In the task pane designer, double-click insertButton to load the Click event handler for the control in the code window.
Modify the Click event handler by adding a call to the procedure that you previously added.
Private Sub insertButton_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles insertButton.Click InsertInformation() End Subprivate void insertButton_Click( object sender, EventArgs e) { InsertInformation(); }Save and run your project.
In PowerPoint, select a design for your presentation. Select a category and then a product, and click the button on the custom task pane.
If you followed the directions carefully, you should see a slide inserted into the presentation each time you click the button, as shown in Figure 9. Try inserting several items.
Close PowerPoint and return to Visual Studio when you are finished.
Figure 9. Creating a new PowerPoint slide from the custom task pane
.gif "Creating new slide from the custom task pane")
Showing and Hiding the Task Pane
The 2007 Microsoft Office system applications do not include a built-in interface for showing and hiding your custom task pane. You can always use the COM Add-ins button in the application’s Options dialog box to remove the add-in, but you cannot expect users to do this. Your add-in really requires some simple interface, perhaps a toggle button on the Office Fluent Ribbon, to show and hide the task pane.
In this section, you work through the steps involved in creating and using this toggle button, but it is a little more complicated than you might imagine—in addition to showing and hiding the task pane, you want the state of the toggle button to update if the user closes the task pane manually. These steps take this issue into account.
To add a button to the Office Fluent Ribbon
Click Project, and then click Add New Item.
In the Add New Item dialog box, select Ribbon support. (Visual Studio 2005 Tools for Office Second Edition adds this template to the standard list of templates.)
Accept the default name for the Ribbon (Ribbon1.vb or Ribbon1.cs) and click Add.
Examine the contents of the new Ribbon1.vb or Ribbon1.cs file. The file contains several sections, each handling a different issue:
The partial class ThisAddIn contains an override for the RequestService method. This method allows the add-in to hand back to the host application a reference to the instance of the Ribbon class that is also contained in the same code file.
The Ribbon1 class contains the code that is required for the Office Fluent Ribbon customization.
The code section titled Ribbon callbacks contains sample callbacks, and is where you will add your own callbacks.
The code section titled Helpers contains the GetResourceText method, which you do not need and can delete as you follow the steps in this article.
Examine the contents of the XML file that contains the Ribbon customization, Ribbon1.xml. By default, the Visual Studio 2005 Tools for Office Second Edition Ribbon support adds the following customization to the XML file. This customization adds a new tab containing a new group to the Office Fluent Ribbon. The new group contains a toggle button that calls the OnToggleButton1 callback procedure in the Ribbon1 class. You replace this customization with your own, in the following steps.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"> <ribbon> <tabs> <tab id="MyTab" label="My Tab"> <group id="MyGroup" label="My Group"> <toggleButton id="toggleButton1" size="large" label="My Button" screentip="My Button Screentip" onAction="OnToggleButton1" imageMso="HappyFace" /> </group> </tab> </tabs> </ribbon> </customUI>In the Ribbon1.vb or Ribbon1.cs file, uncomment the Partial class named ThisAddIn, so that the RequestService method becomes available.
In Ribbon1.xml, replace the existing XML content with the following modified version. This markup creates a new group and toggle button on the Insert tab.
<!--XML --> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"> <ribbon> <tabs> <tab idMso="TabInsert"> <group id="CustomTaskPanesGroup" label="Insert Northwind Data"> <toggleButton id="northwindTaskPaneToggleButton" size="large" label="Northwind Task Pane" screentip= "Show/Hide the Northwind custom task pane" imageMso="ImportAccess" /> </group> </tab> </tabs> </ribbon> </customUI>It is easier to work with the Ribbon customization if you add the XML content to the project’s resources. Follow these steps to add the resource.
In Solution Explorer, right-click the project, and then click Properties.
Select the Resources tab.
From Solution Explorer, drag Ribbon1.xml onto the resources design surface, adding the XML file to the project’s resources.
Close the Properties window, saving the changes when prompted.
In the Ribbon1 class, modify the GetCustomUI method so that it returns the data from the resource.
Public Function GetCustomUI(ByVal ribbonID As String) As String Implements Office.IRibbonExtensibility.GetCustomUI Return My.Resources.Ribbon1 End Functionpublic string GetCustomUI(string ribbonID) { return Properties.Resources.Ribbon1; }Because you do not call this procedure, you can now delete the GetResourceText procedure.
Save and run the project. Click the Insert tab, and the screen should look like Figure 10.
Of course, you did not add the callback procedures that provide functionality for the Office Fluent Ribbon yet, so it cannot perform any actions.
Close PowerPoint and return to Visual Studio when you are finished.
Figure 10. Creating a new group and button on the Insert tab
.gif "Creating a new group and button on the Insert tab")
You communicate with Office Fluent Ribbon controls by using callback procedures. That is, as the 2007 Microsoft Office system application draws the Office Fluent Ribbon, it calls your procedures, as described in your XML customization, to determine the behavior of the items you add to the Ribbon. In addition, as you interact with the Ribbon controls, the application calls the procedures you specify in your add-in. In this example, the XML customization indicates that the application should call your code as it initializes the toggle button, retrieving its pressed state (by calling the GetPressed procedure). In addition, the onAction attribute in the XML indicates that the application should call your HandleTaskPane procedure when the user changes the state of the button. Each callback procedure must conform to a particular procedure signature, and your code does not work correctly if you do not supply the correct number and types of parameters and the correct return value. See the Office Fluent Ribbon documentation for more information about creating the callback procedures.
For more information about the various callback procedures and their specific signatures, see the series of three articles on Office Fluent Ribbon customization on MSDN (specifically, see Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3) for a complete list of callback signatures).
Modify the Ribbon.xml file, adding support for two callback procedures. The first (GetPressed) returns the state of the toggle button (a Boolean value indicating whether the button is pressed). The second, HandleTaskPane, controls the behavior when you click the button. When you are finished, the Ribbon1.xml file should look like the following example.
<!--XML -->
<customUI
xmlns="http://schemas.microsoft.com/office/2006/01/customui"
onLoad="OnLoad">
<ribbon>
<tabs>
<tab idMso="TabInsert">
<group id="CustomTaskPanesGroup"
label="Insert Northwind Data">
<toggleButton id="northwindTaskPaneToggleButton"
size="large"
label="Northwind Task Pane"
screentip=
"Show/Hide the Northwind custom task pane"
imageMso="ImportAccess"
getPressed="GetPressed"
onAction="HandleTaskPane"
/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
In the Ribbon Callbacks section of the Ribbon1 class, add the following callback procedures.
Public Function GetPressed( _
ByVal control As Office.IRibbonControl) As Boolean
Return Globals.ThisAddIn.ctp.Visible
End Function
Public Sub HandleTaskPane( _
ByVal control As Office.IRibbonControl, _
ByVal isPressed As Boolean)
Globals.ThisAddIn.ctp.Visible = isPressed
End Sub
Public Sub Refresh()
ribbon.InvalidateControl("northwindTaskPaneToggleButton")
End Sub
public Boolean GetPressed(Office.IRibbonControl control)
{
return Globals.ThisAddIn.ctp.Visible;
}
public void HandleTaskPane(
Office.IRibbonControl control, Boolean isPressed)
{
Globals.ThisAddIn.ctp.Visible = isPressed;
}
public void Refresh()
{
ribbon.InvalidateControl("northwindTaskPaneToggleButton");
}
Remember that your code cannot just set properties of the various Ribbon controls. For example, in this add-in, your code must set the pressed state of the toggle button, depending on the visibility of the custom task pane. The XML customization indicates that the toggle button can retrieve its state by calling the GetPressed callback procedure, which returns True if the task pane is visible, and False otherwise. When you manually close the task pane, you need code that can force the button to change its state. To make this happen, your code can react to the VisibleChanged event of the CustomTaskPane class, and force the toggle button to reinitialize itself—the Refresh procedure takes care of this for you. This procedure, in the previous code example, calls the InvalidateControl method of the Office Fluent Ribbon, which forces the ToggleButton control to reinitialize (and therefore, to set its toggled state appropriately).
The following steps hook up the code that handles the VisibleChanged event of the custom task pane.
To handle the VisibleChanged event of the custom task pane
In the ThisAddIn.vb or ThisAddIn.cs class, add the following event-handling procedure.
Private Sub HandleVisibleChanged(ByVal sender As Object, _ ByVal e As EventArgs) ribbon.Refresh() End Subprivate void HandleVisibleChanged(Object sender, EventArgs e) { ribbon.Refresh(); }Modify the ThisAddIn_Startup procedure by adding code to set up the event handler for the VisibleChanged event.
Private Sub ThisAddIn_Startup( _ ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles Me.Startup ctp = _ Me.CustomTaskPanes.Add( _ New NorthwindTaskPane, "Northwind Data") ctp.Width = 200 ctp.Visible = True AddHandler ctp.VisibleChanged, AddressOf HandleVisibleChanged End Subprivate void ThisAddIn_Startup( object sender, System.EventArgs e) { ctp = this.CustomTaskPanes.Add( new NorthwindTaskPane(), "Northwind Data"); ctp.Width = 200; ctp.Visible = true; ctp.VisibleChanged +=new EventHandler(HandleVisibleChanged); }Modify the existing ThisAddIn_Shutdown procedure by unhooking the VisibleChanged event handler.
Private Sub ThisAddIn_Shutdown( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles Me.Shutdown RemoveHandler ctp.VisibleChanged, _ AddressOf HandleVisibleChanged End Subprivate void ThisAddIn_Shutdown(object sender, System.EventArgs e) { ctp.VisibleChanged -= new EventHandler(HandleVisibleChanged); }Save and run your project. In PowerPoint, click the Insert tab.
Your custom group and button appear, enabling you to display and hide the custom task pane. Close the task pane manually, and the toggle button updates its display accordingly.
Close PowerPoint and return to Visual Studio 2005 when you are finished.
Creating a Data-Centric Task Pane for Excel
The add-in that you created works only in PowerPoint 2007, but you can use almost all the steps to create a similar add-in for Excel 2007 or Word 2007. Although you cannot just convert your add-in to work with Excel, you follow the same steps to build a similar Excel add-in, perhaps one that inserts data about the selected product, as shown in Figure 11. This add-in lets you select a product from the task pane. Clicking the button on the task pane inserts formatted information about the selected product.
Figure 11. Inserting product information into a worksheet
.gif "Inserting product information into a worksheet")
To create the Excel version of your add-in, follow all the steps you followed to create the PowerPoint add-in, except in the New Project dialog box, instead of selecting PowerPoint Add-in, select Excel Add-in. When you add the Imports/using statement, reference Excel rather than PowerPoint.
Imports Excel = Microsoft.Office.Interop.Excel
using Excel = Microsoft.Office.Interop.Excel;
When you create the InsertInformation procedure, (that is, the procedure that interacts with the host application), use the following procedure instead of the one shown previously.
Private Sub InsertInformation()
' This procedure interacts with the host application,
' inserting content based on the selected product.
Dim product As NorthwindDataSet.ProductsRow = GetProduct()
Dim app As Excel.Application = Globals.ThisAddIn.Application
' If there’s no current workbook, add one.
If app.Workbooks.Count = 0 Then
app.Workbooks.Add()
End If
If TypeOf (app.ActiveSheet) Is Excel.Worksheet Then
Dim range As Excel.Range = app.ActiveCell
' You'll probably want to insert column headers yourself.
' This code doesn't do that, as you may not want them.
range.Offset(0, 0).Value2 = product.ProductName
range.Offset(0, 1).Value2 = product.QuantityPerUnit
Dim priceRange As Excel.Range = range.Offset(0, 2)
priceRange.NumberFormat = "$#,##0.00"
priceRange.Value2 = product.UnitPrice
range.Offset(0, 3).Value2 = product.UnitsInStock
range.Offset(0, 4).Value2 = product.ReorderLevel
range.CurrentRegion.Columns.AutoFit()
End If
End Sub
private void InsertInformation()
{
NorthwindDataSet.ProductsRow product = GetProduct();
Excel.Application app = Globals.ThisAddIn.Application;
// If there's no current workbook, add one.
if (app.Workbooks.Count == 0)
app.Workbooks.Add(Type.Missing);
if (app.ActiveSheet is Excel.Worksheet)
{
Excel.Range range = app.ActiveCell;
// You'll probably want to insert column headers yourself.
// This code doesn't do that, as you may not want them.
range.get_Offset(0, 0).Value2 = product.ProductName;
range.get_Offset(0, 1).Value2 =
product.QuantityPerUnit;
Excel.Range priceRange = range.get_Offset(0, 2);
priceRange.NumberFormat = "$#,##0.00";
priceRange.Value2 = product.UnitPrice;
range.get_Offset(0, 3).Value2 = product.UnitsInStock;
range.get_Offset(0, 4).Value2 = product.ReorderLevel;
range.CurrentRegion.Columns.AutoFit();
}
}
Save and run your project. Visual Studio starts Excel 2007 for you. When you select a category and product from the task pane, your code inserts information about the item in the selected cell and the cells to its right.
| Note |
|---|
Theoretically, you can create a new add-in by copying bits and pieces from the original add-in to your new add-in, and fixing up references. The steps are specific to the particular add-in, and there is no simple generalized solution. You may find it easier to follow this route when you are creating multiple application-centric add-ins that share behavior, or you may find it easier to just start from scratch and follow the same steps each time. You may also find it useful to investigate techniques for factoring code into shared assemblies, as shown in the article Factoring Add-ins for the 2007 Office System By Using Visual Studio 2005 Tools for the Office System SE (coming soon). |
Creating a Data-Centric Task Pane for Word
Just as you can modify your original add-in to support Excel, you can also modify it to support Word. As before, follow all the steps provided in this article up to the point when you add the InsertInformation method. When you find steps that mention PowerPoint, replace the reference with Word.
Add the following version of the InsertInformation procedure, which adds information about the selected product to a five-column table, as shown in Figure 12. This procedure first checks whether the current selection is within a five-column table—if it is not, the procedure creates the table and adds the header row. Either way, the procedure then inserts a new row and displays data for the selected product. The additional two overloaded procedures, CreateAlignedCell, insert text and handle formatting for the columns within the table.
Figure 12. Using the task pane to insert data into a Word document
.gif "Using task pane to insert data into a document")
Private Sub InsertInformation()
' This procedure interacts with the host application,
' inserting content based on the selected product.
Dim product As NorthwindDataSet.ProductsRow = GetProduct()
Dim app As Word.Application = Globals.ThisAddIn.Application
Dim doc As Word.Document = Nothing
If app.Documents.Count = 0 Then
doc = app.Documents.Add()
Else
doc = app.ActiveDocument
End If
Dim table As Word.Table = Nothing
Dim row As Word.Row = Nothing
Dim cell As Word.Cell = Nothing
Dim needNewTable As Boolean = True
' Check to see if the selection is within a table.
' If so, and if the table has five columns,
' there's no need to create a new table.
If CBool(app.Selection.Information( _
Word.WdInformation.wdWithInTable)) Then
table = app.Selection.Tables(1)
' If you're in a table with five columns, you'll just
' assume you can add to the existing table.
If table.Columns.Count = 5 Then
needNewTable = False
End If
End If
If needNewTable Then
table = app.ActiveDocument.Tables.Add( _
app.Selection.Range, 1, 5, _
Word.WdDefaultTableBehavior.wdWord9TableBehavior, _
Word.WdAutoFitBehavior.wdAutoFitContent)
row = table.Rows(1)
row.Cells(1).Range.Text = "Product"
CreateAlignedCell(row.Cells(2), "Unit Price", _
Word.WdParagraphAlignment.wdAlignParagraphRight)
CreateAlignedCell(row.Cells(3), "Quantity Per Unit")
CreateAlignedCell(row.Cells(4), "Units in Stock")
CreateAlignedCell(row.Cells(5), "Reorder Level")
table.Style = "Medium Shading 1 - Accent 5"
End If
row = table.Rows.Add()
row.Cells(1).Range.Text = product.ProductName
CreateAlignedCell(row.Cells(2), String.Format( _
"{0:C}", product.UnitPrice), _
Word.WdParagraphAlignment.wdAlignParagraphRight)
CreateAlignedCell(row.Cells(3), product.QuantityPerUnit)
CreateAlignedCell(row.Cells(4), product.UnitsInStock)
CreateAlignedCell(row.Cells(5), product.ReorderLevel)
End Sub
Private Sub CreateAlignedCell(ByVal cell As Word.Cell, _
ByVal text As Object)
' Assume centered text -- that's the most common scenario.
CreateAlignedCell(cell, text, _
Word.WdParagraphAlignment.wdAlignParagraphCenter)
End Sub
Private Sub CreateAlignedCell(ByVal cell As Word.Cell, _
ByVal text As Object, _
ByVal alignment As Word.WdParagraphAlignment)
cell.Range.Text = text.ToString()
cell.Range.ParagraphFormat.Alignment = alignment
End Sub
private void InsertInformation()
{
object missing = Type.Missing;
NorthwindDataSet.ProductsRow product = GetProduct();
Word.Application app = Globals.ThisAddIn.Application;
Word.Document doc = null;
if (app.Documents.Count == 0)
doc = app.Documents.Add(
ref missing, ref missing, ref missing, ref missing);
else
doc = app.ActiveDocument;
Word.Table table = null;
Word.Row row = null;
Word.Cell cell = null;
Boolean needNewTable = true;
// Check to see if the selection is within a table.
// If so, and if the table has five columns,
// there's no need to create a new table.
if ((bool)app.Selection.get_Information(
Word.WdInformation.wdWithInTable))
{
table = app.Selection.Tables[1];
// If you're in a table with five columns, you'll just
// assume you can add to the existing table.
if (table.Columns.Count == 5)
needNewTable = false;
}
if (needNewTable)
{
object tableBehavior =
Word.WdDefaultTableBehavior.wdWord9TableBehavior;
object autofit = Word.WdAutoFitBehavior.wdAutoFitContent;
table = app.ActiveDocument.Tables.Add(
app.Selection.Range, 1, 5,
ref tableBehavior,
ref autofit);
row = table.Rows[1];
row.Cells[1].Range.Text = "Product";
CreateAlignedCell(row.Cells[2], "Unit Price",
Word.WdParagraphAlignment.wdAlignParagraphRight);
CreateAlignedCell(row.Cells[3], "Quantity Per Unit");
CreateAlignedCell(row.Cells[4], "Units in Stock");
CreateAlignedCell(row.Cells[5], "Reorder Level");
object style = "Medium Shading 1 - Accent 5";
table.set_Style(ref style);
}
row = table.Rows.Add(ref missing);
row.Cells[1].Range.Text = product.ProductName;
CreateAlignedCell(row.Cells[2], String.Format(
"{0:C}", product.UnitPrice),
Word.WdParagraphAlignment.wdAlignParagraphRight);
CreateAlignedCell(row.Cells[3], product.QuantityPerUnit);
CreateAlignedCell(row.Cells[4], product.UnitsInStock);
CreateAlignedCell(row.Cells[5], product.ReorderLevel);
}
After you create the add-in, save and run it. Visual Studio loads Word 2007, and you can experiment with creating a new table or adding to an existing table, displaying information about the selected product.
Conclusion
Clearly, after you create a single data-centric custom task pane for one 2007 Microsoft Office system product by using Visual Studio 2005 Tools for Office Second Edition, creating multiple task panes for multiple products is just a matter of writing the application-specific code. In this article, you started by creating a task pane for PowerPoint and then created similar task panes in Word and Excel. Because custom task panes that you create using Visual Studio 2005 Tools for Office Second Edition are just containers for standard custom controls, it is easy to take advantage of the data-binding features provided by Visual Studio 2005 to create rich, data-centric custom task panes for use in 2007 Microsoft Office system applications.
About the Authors
Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken co-authored AppDev's C#, ASP.NET, Visual Basic .NET, and ADO.NET courseware. Ken is a technical editor for Advisor Publications' Visual Basic .NET Technical Journal, and he is a columnist for both MSDN Magazine and CoDe Magazine. Ken speaks regularly at many industry events, including Advisor Media's Advisor Live events, FTP's VSLive, and Microsoft Tech-Ed.
Jan Fransen is a writer, trainer, and developer who specializes in Microsoft products. Jan has developed training courses in Microsoft Visual Studio Tools for Office, Microsoft Office, and Microsoft Visual Basic for Applications for AppDev, contributed to books about Microsoft Office, written white papers for publication on MSDN, and created samples designed to help developers get up to speed quickly on new Microsoft products and features.
Additional Resources
This section lists several resources that you can use to learn more about the products and technologies mentioned or used in this article.