Previous episodes

1. Getting Started
2. Using the Custom Task Pane
3. Using the Ribbon
4. Adding a WPF control

The shell of our control is ready – today we will fill the TreeView with all the open workbooks and worksheets. We will use a common design pattern in WPF: we will create objects that act as intermediary between the user interface and the domain objects. This approach is know as MVVM (Model-View-ViewModel) in WPF, and is a variation on the classic Model-View-Presenter pattern – the main difference being that MVVM relies heavily on the data binding capabilities of WPF.

As usual, Josh Smith has some great material on how to use the WPF TreeView, which is highly recommended reading – and was a life-saver in figuring out how things work.

In a first step, we will fill in the TreeView with fake data, and once the UI “works”, we will hook up the objects to retrieve real data from Excel.

To quote Josh Smith, “the WinForms TreeView control is not really providing a “view” of a tree: it is the tree”, whereas “the TreeView in our WPF programs to literally provide a view of a tree”, to which we want to bind. In our case, the tree we want to represent is that Excel has a collection of Workbook objects, which each has a collection of Worksheet objects. Let’s build that structure.

More...

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...

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)
{
    var taskPaneView = new TaskPaneView();
    var myTaskPane = this.CustomTaskPanes.Add(taskPaneView, "Anakin");
    myTaskPane.Visible = true;
}

Hit F5 to debut, and you should see the following:

More...

In the recent past, I have been developing solutions for clients using VSTO – Visual Studio Tools for Office – and I believe that VSTO is a technology which has a lot to offer. I used to write a lot of VBA code to automate Excel, but after spending the past 5 years or so writing C# code using .Net, I find VBA limiting, and the development environment extremely frustrating. So when I saw that Jon Peltier had begun a tutorial series on how to build a classic Excel add-in, I thought, why not do the same with VSTO?

My baseline objective will be to write an add-in which tracks the differences between two open worksheets, and allows the user to merge some of the contents, if appropriate.

As a secondary objective, I will try to showcase some of the benefits of using VSTO over VBA; for that reason, the add-in will be built for Excel 2007, and use the Ribbon, and .Net 3.5, so that we can use some WPF controls. I will do my best to highlight some of the differences between VBA and C# along the path, as well as highlight some important aspects of the Excel object model. As a result, some places will probably seem slow to the experienced .Net developer, and some others to the VBA veteran.

Unlike Jon, I have not written my add-in yet; I will share the successes and struggles as I progress along. The main tasks/areas we will have to cover are

• hooking the add-in to the Excel user interface,
• accessing the Excel objects through the add-in,
• creating custom controls to select sheets,
• identifying differences between worksheets,
• listening to Excel events,
• deploying the add-in.

I will likely dig into Excel 2010, .Net 4.0 and Visual Studio 2010 along the path, but given that Office 2007 hasn’t been adopted by all yet, this will not be my initial focus.

More...

I have grown a bit frustrated by the difficulty to gather information about local .Net development community events. Of all people, I am one of the best-positioned to hear about these events, because of my involvement with the San Francisco .Net user group, and yet, I routinely discover that I missed an event, because there is no clear place to go to get that information and I never heard about it.

So here we go – ladies and gentlemen, introducing NorCalWeekly.Net, your weekly serving of .Net events in north California! My goal will be to provide in one place, on a weekly basis, announcements for all upcoming .Net events in the area – so if you know of anything relevant taking place, please let me know! The design and features are somewhat minimalist for the moment, I’ll improve it over time.