I am pretty excited that the new Silverlight Toolkit now supports Stacked Series. If you develop business applications, at some point or another, you will have to produce charts. A while back I looked into the WPF / Silverlight toolkit, which offers charting capabilities, and I really liked it, because its design supports mvvm-style databinding pretty well. There was one major drawback, though: it didn’t include any stacked series. The point of reference for most business users is Excel charts, and this left a large chunk of the standard charts out. Problem solved with the last release: the stacked histogram and some of his friends are in! The Excel “Outdoors Bar” type is still not included, but I don’t think anyone will complain. I can’t wait to play with this release.

You can find the complete list of episodes, and a link to the source code of the Excel VSTO add-in tutorial here.

In the second part of this series, we will generate a comparison between two open spreadsheets, and create a user interface to navigate between the differences, and reconcile them if need be.

For today, let’s assume that the add-in has already figured out what the differences are, and build first a user interface that allows the user to navigate to the cells that have differences. In later posts, we will focus on actually generating these differences.

To achieve this, I will leverage the Goto method of Excel. Application.Goto(object reference, object scroll) will navigate to the range defined by reference. If scroll is set to true, it will force the window to scroll so that the range is the upper-left corner, otherwise it will scroll only if necessary.

This is a good starting point for design. A spreadsheet comparison will be a collection of differences, and each difference should map to a cell. One approach is to store the row and column of each difference, so that from a Difference object, we can retrieve the corresponding range, and go to it:

private void NavigateToDifference(Difference difference)
{
   var row = difference.Row;
   var column = difference.Column;
   var activeSheet = (Excel.Worksheet)this.excel.ActiveSheet;
   var differenceLocation = activeSheet.Cells[row, column];
   this.excel.Goto(differenceLocation, Type.Missing);
}

More...

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

I had an Eureka moment today, and realized that I was trying hard to resolve the wrong problem, when what was needed was a change of frame.

I have been developing an application which tracks differences between Excel worksheets, and displays them in a layout similar to Excel itself. The user can navigate through the sheet on display by using two scrollbars, just like he would with Excel.

To achieve this, my approach so far has been to read the data, create a grid using WPF, adding one cell control to the grid for each cell in the spreadsheet, and navigate “over” that grid using the scroll bars.

This approach works well as long as the spreadsheet is small, but for larger sheets – say, 200 x 200 cells - the time needed to create the cells and add them to the grid was getting pretty long, so I focused my attention on figuring out a way to populate the grid faster, without much success.

Then yesterday, someone wrote this on StackOverflow:

I'm going to go out on a limb here and say that trying to add 40,000 controls is your real bottleneck; not so much as to how you're adding the controls.

Indeed – that’s completely correct.This didn’t answer my issue directly, but it has been liberating, and got me to rethink the question from a different angle – do I really need 40,000 cells?

More...