Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
16. January 2011 13:08

One of my favorite features in VSTO is the custom task pane. It provides a very natural and unobtrusive mechanism to expose your add-in functionality, fully integrated into Office, and makes it possible to use WPF for user interface development.

If you want your Task Pane to look seamless to your user, you will probably need to play a bit with Docking. If not, two specific issues could arise:

• Your WPF control is fairly small, and doesn’t take all the surface of the Task Pane, leaving the original WinForms color background visible in the uncovered areas,
• Your WPF control is too large for the Task Pane surface, leaving parts of the control invisible to the user, who cannot access them.

The first situation is mostly aesthetics (it just looks ugly), but the second case is a bit problematic, as it could make your Task Pane virtually unusable.

To illustrate the issue, let’s create an Excel 2007 Add-In project “AddInLab” in Visual Studio, add a WinForms control “TaskPaneWpfHostControl”, drop an ElementHost control in there, which we rename to wpfElementHost, instead of elementHost1, and set its Dock property to Fill so that it takes up the entire surface of the control. We’ll edit the code-behind, to provide access to the ElementHost via a public property:

namespace AddInLab
{
using System.Windows.Forms;
using System.Windows.Forms.Integration;

public partial class TaskPaneWpfControlHost : UserControl
{
{
InitializeComponent();
}

public ElementHost WpfElementHost
{
get
{
return this.wpfElementHost;
}
}
}
}

More...

2. March 2010 07:21

Now that our Custom Task Pane is in place, and that we can drive its visibility with the Ribbon, it’s time to begin adding some real functionality to the add-in. In our next two installments, we will create a tree view in the task pane, which will display all the workbooks that are currently open, and the worksheets within each workbook. Later on, we will use that tree view to select the worksheet we want to compare the current active worksheet to.

I will use WPF to create our tree view, instead of a Windows Form user control. While WinForms is probably more familiar to most developers, I really wanted to use WPF in this example, because I love the flexibility it provides in user interface design, and because this is where the future of UI design is at. I can’t do a full tutorial on WPF here; I’ll try my best to explain what is going on and provide pointers, but if you haven’t seen xaml before, you will probably find some parts confusing – I hope the result will be interesting enough to motivate further exploration!

For the Windows Forms fans, Dennis Wallentin has an excellent tutorial on how to populate a WinForms tree view, for a very similar problem; I encourage you to check it out.

More...

22. February 2010 10:23

In our previous installment, we went through adding a Custom Task Pane to Excel to host the user interface of our VSTO add-in. However, we left off with one problem to solve. The task pane is shown when the add-in starts up, but if the user closes it, there is no mechanism to show it again. We will resolve that problem by using the ribbon, adding a button that restores the task pane visibility.

First, we will create a new folder in our project, called “Ribbon”. Right-click the folder, select “Add > New Item”, and pick “Ribbon (Visual Designer)” from the available templates. We will call our ribbon “AnakinRibbon”.

By now, your solution should look like this:

Visual Studio displays a visual interface, representing the ribbon we will use for Anakin:

By default, the ribbon comes pre-populated with a tab called “TabAddIns”, labeled Built-In. This reflects the fact that, by default, your add-in ribbon will show up in the standard Add-Ins tab of the ribbon.

While this would be perfectly acceptable, we actually want to add our add-in to an existing Ribbon tab, the “Review” tab. It seems like a natural place to find functionality related to comparing different versions of a spreadsheet, and this way, we can avoid crowding the Ribbon with new tabs, and integrate seamlessly with Office, without minimal disturbance to the user experience.

More...

16. February 2010 19:27

Now that we have created the VSTO add-in project, it’s time to add some functionality to it. We want to provide a user interface to select what sheets we want to compare, and navigate between the differences that the add-in has found. In order to do this, we will create a custom task pane.

You can think of a custom task pane as a placeholder for controls. The best way to illustrate the concept is to simply do it. In our project, we will add a folder “TaskPane”, and add a new User Control by right-clicking on the TaskPane folder, which we will name “TaskPaneView”.

If you double-click on TaskPaneView, visual studio will display a gray empty area. This is the “canvas” on which we will add controls later, to allow the user to call the operations our add-in will expose. For now, we’ll leave it at that, and just focus on displaying the task pane.

Now go to the ThisAddIn class, and add the following code in the startup method:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}