Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
8. March 2010 12:50

Previous episodes

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.

Let’s add a folder “TreeView” inside our existing TaskPane folder, and create 3 public classes: ExcelViewModel, WorkbookViewModel and WorksheetViewModel.

The ExcelViewModel will expose an ObservableCollection of its WorkbookViewModel(s), which will provide the View with information on how to display each workbook. We will temporarily add a few “fake” workbooks to the ExcelViewModel:

using System.Collections.ObjectModel;

public class ExcelViewModel
{
private ObservableCollection<WorkbookViewModel> workbookViewModels;

internal ExcelViewModel()
{
this.workbookViewModels = new ObservableCollection<WorkbookViewModel>();
var fakeWorkbookViewModel1 = new WorkbookViewModel();
var fakeWorkbookViewModel2 = new WorkbookViewModel();
}

public ObservableCollection<WorkbookViewModel> Workbooks
{
get
{
return this.workbookViewModels;
}
}
}

Let’s do the same for the WorkbookViewModel, and add a Property Name, which will temporarily return a fake value “My Workbook”:

using System.Collections.ObjectModel;

public class WorkbookViewModel
{
private ObservableCollection<WorksheetViewModel> worksheetViewModels;

internal WorkbookViewModel()
{
this.worksheetViewModels = new ObservableCollection<WorksheetViewModel>();
var fakeWorksheetViewModel1 = new WorksheetViewModel();
var fakeWorksheetViewModel2 = new WorksheetViewModel();
}

public ObservableCollection<WorksheetViewModel> Worksheets
{
get
{
return this.worksheetViewModels;
}
}

public string Name
{
get
{
return "My Workbook";
}
}
}

And finally, the WorksheetViewModel simply exposes a temporarily fake name:

public class WorksheetViewModel
{
public string Name
{
get
{
return "My Worksheet";
}
}
}

Now that the tree structure is in place, let’s hook it up to the control. We will expose the add-in functionality to the AnakinView user control through one class, the AnakinViewModel; it will handle the actions received from the user through the AnakinView, and transform data coming from Excel in a format suitable for user interface consumption. Let’s create that class, in the same place as the AnakinViewControl, so that our project looks like this:

We need to associate the AnakinView with its view model; WPF controls have a property, DataContext, which enables this association: any object can be passed as DataContext to the control, and the control will do its best to bind to the object properties.

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

internal AnakinView AnakinView
{
get
{
return this.anakinView1;
}
}
}

Now that we have access to the WPF control, let’s set the DataContext in the startup method, like this:

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

var anakinViewModel = new AnakinViewModel();
anakinView.DataContext = anakinViewModel;
}

At that point, we are largely done; what remains to do is to tell the TreeView in the AnakinView user control where it should find data in the DataContext, that is, the AnakinViewModel. The tree view is, more or less, a list of list of list (etc…); for the control to be able to bind to the ViewModel, we need to tell it where the root element is, and where the “next” list is located. Let’s start by the root, the ExcelViewModel. First, the AnakinViewModel needs to provide access to the ExcelViewModel, so let’s add the following code:

using ClearLines.Anakin.TaskPane.TreeView;

public class AnakinViewModel
{
private ExcelViewModel excelViewModel;

public ExcelViewModel ExcelViewModel
{
get
{
if (this.excelViewModel == null)
{
this.excelViewModel = new ExcelViewModel();
}

return this.excelViewModel;
}
}
}

We are using lazy-loading here: the AnakinViewModel itself creates its ExcelViewModel “on-demand”, when it is requested by the property.

Next, we can start filling the tree, by telling it that it should look for the root element of the tree in the ExcelViewModel property of the AnakinViewModel, and how it should render the WorkbookViewModel(s) it will find there. Let’s change the xaml code of the AnakinView.xaml to the following:

<UserControl x:Class="ClearLines.Anakin.TaskPane.AnakinView"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
<StackPanel Margin="5">
<Button Width="75" Height="30"
Margin="0,0,0,5"
HorizontalAlignment="Left">
Refresh
</Button>
<TreeView ItemsSource="{Binding Path=ExcelViewModel.Workbooks}" Height="200">
<TreeView.Resources>
<HierarchicalDataTemplate
DataType="{x:Type TreeView:WorkbookViewModel}">
<StackPanel Margin="0,0,0,3">
<TextBlock Text="{Binding Name}"/>
</StackPanel>
</HierarchicalDataTemplate>
</TreeView.Resources>
</TreeView>
</StackPanel>
</UserControl>

The main changes to note are:

• We added ItemsSource="{Binding Path=ExcelViewModel.Workbooks}" in the opening TreeView tag. This instructs the TreeView that the source of data is to be found at the path ExcelViewModel.Workbooks, in the DataContext.
• The section TreeView.Resources provides the TreeView with instructions on the way the data is organized, and how it should render elements. The HierarchicalDataTemplate declares that when items of type WorkbookViewModel are encountered, they should be rendered using a StackPanel, and display in a TextBlock the text that is found in the Name property.
• The line xmlns:TreeView="clr-namespace:ClearLines.Anakin.TaskPane.TreeView", which has been added at the top of the control, is the equivalent of a using statement, and points to the namespace where the WorkbookViewModel resides.

If you debug the add-in at that point, you should see the following:

Nothing amazing yet, but it proves that the binding does take place: the TreeView now shows our two fake workbooks names. Let’s add the binding to the worksheets, by adding another HierarchicalDataTemplate:

<TreeView.Resources>
<HierarchicalDataTemplate
DataType="{x:Type TreeView:WorkbookViewModel}"
ItemsSource="{Binding Worksheets}">
<StackPanel Margin="0,0,0,3">
<TextBlock Text="{Binding Name}"/>
</StackPanel>
</HierarchicalDataTemplate>
<HierarchicalDataTemplate
DataType="{x:Type TreeView:WorksheetViewModel}">
<StackPanel Margin="0,0,0,3">
<TextBlock Text="{Binding Name}"/>
</StackPanel>
</HierarchicalDataTemplate>
</TreeView.Resources>

The second template pretty much replicates what we did for the workbooks, and simply tells the control how to render a worksheet. Note that in the first template, the following line has been added: ItemsSource="{Binding Worksheets}". This announces to the control that when displaying a Workbook, it should look for a “nested”, hierarchical list of items, called Worksheets – which will be rendered using the second template.

Hit F5, and now we see a complete tree:

The bindings work – now we just have to hook up the various View Models so that instead of fake data, they display “real” data coming from Excel. Piece of cake. Let’s start by feeding real workbooks into the WorkbookViewModel: we add a reference/alias to Excel, change the constructor, which now expects a workbook, and re-pipe the Name property to retrieve the name of the actual workbook.

using System.Collections.ObjectModel;
using Excel = Microsoft.Office.Interop.Excel;

public class WorkbookViewModel
{
private ObservableCollection<WorksheetViewModel> worksheetViewModels;

internal WorkbookViewModel(Excel.Workbook workbook)
{
this.worksheetViewModels = new ObservableCollection<WorksheetViewModel>();
this.workbook = workbook;
var fakeWorksheetViewModel1 = new WorksheetViewModel();
var fakeWorksheetViewModel2 = new WorksheetViewModel();
}

public ObservableCollection<WorksheetViewModel> Worksheets
{
get
{
return this.worksheetViewModels;
}
}

public string Name
{
get
{
return workbook.Name;
}
}
}

Now we need to modify the ExcelViewModel. Instead of creating fake workbooks, we will call a method, PopulateWorkbooks, which accesses Excel through the Add-In, iterates through all open workbooks, and creates a WorkbookViewModel for each:

using System.Collections.ObjectModel;
using Excel = Microsoft.Office.Interop.Excel;

public class ExcelViewModel
{
private ObservableCollection<WorkbookViewModel> workbookViewModels;

internal ExcelViewModel()
{
this.workbookViewModels = new ObservableCollection<WorkbookViewModel>();
this.PopulateWorkbooks();
}

public ObservableCollection<WorkbookViewModel> Workbooks
{
get
{
return this.workbookViewModels;
}
}

private void PopulateWorkbooks()
{
var workbooks = excel.Workbooks;
foreach (var workbook in workbooks)
{
var book = workbook as Excel.Workbook;
if (book != null)
{
var workbookViewModel = new WorkbookViewModel(book);
}
}
}
}

If you run the Add-In at that point, you’ll see that instead of our 2 fake workbooks, only one Workbook appears, with the proper name – but it is still filled with fake worksheets. Let’s address that in the same fashion, by first modifying the WorksheetViewModel, which will use a real Worksheet:

using Excel = Microsoft.Office.Interop.Excel;

public class WorksheetViewModel
{
private Excel.Worksheet worksheet;

public WorksheetViewModel(Excel.Worksheet worksheet)
{
this.worksheet = worksheet;
}

public string Name
{
get
{
return this.worksheet.Name;
}
}
}

… and update the WorkbookViewModel, to iterate over its worksheets and create one WorksheetViewModel for each:

using System.Collections.ObjectModel;
using Excel = Microsoft.Office.Interop.Excel;

public class WorkbookViewModel
{
private ObservableCollection<WorksheetViewModel> worksheetViewModels;

internal WorkbookViewModel(Excel.Workbook workbook)
{
this.worksheetViewModels = new ObservableCollection<WorksheetViewModel>();
this.workbook = workbook;
var worksheets = workbook.Worksheets;
foreach (var sheet in worksheets)
{
var worksheet = sheet as Excel.Worksheet;
if (worksheet != null)
{
var worksheetViewModel = new WorksheetViewModel(worksheet);
}
}
}

public ObservableCollection<WorksheetViewModel> Worksheets
{
get
{
return this.worksheetViewModels;
}
}

public string Name
{
get
{
return workbook.Name;
}
}
}

And that’s it. If you hit F5 right now, you will see the following: the TreeView displays a Workbook, with the proper Worksheet names:

This may look like a lot of work, just to populate a simple tree. On the other hand, now that the artillery is in place, we can customize the way items are rendered in the tree, without much extra work. For instance, let’s add the name of the Author of the workbook discreetly below the name. To do this, we need to add that property to the WorkbookViewModel, like this:

public string Author
{
get
{
return this.workbook.Author;
}
}

And let’s alter the HierarchicalDataTemplate slightly:

<HierarchicalDataTemplate
DataType="{x:Type TreeView:WorkbookViewModel}"
ItemsSource="{Binding Worksheets}">
<StackPanel Margin="0,0,0,3">
<TextBlock Text="{Binding Name}"/>
<TextBlock Text="{Binding Author}" FontSize="9" Foreground="Gray"/>
</StackPanel>
</HierarchicalDataTemplate>

The TreeView now shows the following:

The point here is that while creating a ViewModel for different types of entities creates some overhead, once they are broken up that way, it is fairly easy to display each of them in a specific way – and customizing the way they are rendered is straightforward.

We are almost done with the TreeView at that point. The two issues we still have to cope with are that right now, it is populated with the default workbook that is opened when Excel launches, and we need to refresh the contents when the “Refresh” button is clicked. We also need to convey to the ViewModel which worksheet is currently selected – that is, after all, the whole point of that part of the control. This is what we will handle in our next installment!

I also realize that at that point in time, there is enough code that it is becoming worthwhile to post it, I will do so very shortly.

3/7/2010 6:25:32 AM #

Thanks for a very interesting article! I have taken the challenge to create a sample on how to populate a WPF TreeView.

At present I have only one question (as well as input to improve the article itself): How do we add icons to the nodes? I know how to add an icon to the WPF Form but now seeking to add icons to a TreeView's nodes.

Kind regards,
Dennis

3/10/2010 7:06:48 AM #

Hi Dennis,
It's funny, because when I read your post on using a TreeView, my first thought was "damn, he has a very nice tree with images, I'll have to make sure that I can do it too"
The easiest way to do this would be along these lines. Let's say you wanted to add an image for the Worksheet.
1) I created an image called "SmileyFace.jpg", added it to the TreeView folder, right with the WorksheetViewModel, made it a Resource in the Properties/Build Action
2) I added a Property "ImagePath" to the WorksheetViewModel, which returns "SmileyFace.jpg".
3) In the HierarchicalDataTemplate that applies to the WorksheetViewModel, I added <Image Source="{Binding ImagePath}"/> in the StackPanel.
In other words, have the ViewModel expose a valid path to an image, and simply bind the Template to it.
That's it - you now have an image! I haven't tried with an icon file, but I don't think it should be different.
Hope this answers your question - and thanks a lot for the feedback, this is very helpful to me.
Mathias

3/11/2010 4:59:57 AM #

Mathias,

Thanks for coming back to me. Yes, it looks like a workable approach which I will try. Thanks for providing a link to Josh Smith's excellent article.

Kind regards,
Dennis

3/16/2010 10:10:34 PM #

Create an Excel 2007 VSTO add-in: Excel events

Create an Excel 2007 VSTO add-in: Excel events

6/5/2010 10:36:26 AM #

Hey,  just wondering if you have managed to get this populating in real time/refreshing yet.  I don't want to sound lazy but this is more of a solution to a problem I am having but don't have the knowledge to find my own answer, yet.

6/5/2010 10:48:46 AM #

Hi Maxi,
I have left it at that temporarily, because I haven't had the time to look into it further, but I saw this post recently which looks interesting. The biggest issue with refreshing is properly catching whether a workbook or worksheet has been closed, and it looks like this catches whether a workbook has been closed:
exceptionalcode.wordpress.com/.../
I have not played with the code yet, so I can't guarantee whether this actually works - and if it can be used for worksheets - but it seems promising. I intend to review it in the near future, and see whether this can do the trick.
Hope this helps - and thanks for the feedback!
Mathias

8/2/2010 7:40:06 PM #

I especially like this one.

9/26/2010 8:49:05 AM #

The step by step instructions helped me a ton, thank you!

10/27/2010 12:50:16 PM #

Here's some great material on how to use the WPF TreeView, which is highly recommended reading:
http://joshsmithonwpf.wordpress.com/

12/5/2010 2:32:47 PM #

Hi. Great series!

I have a question on this line of code in the AnakinViewModel:

The addin I'm trying to create following this instructions is called, say, AAAA (that's the name of the root element on the solution explorer). When I type in the equivalent of AnakinViewModel imports AAAA. , I don't get the TaskPane object. Neither do I get it under AAAA.ThisAddIns.. I have implemented the property that returns the object called TaskPane, as described here: www.clear-lines.com/.../...vsto-add-in-ribbon.aspx .
I guess I have some namespace problem.... Could you clarify this? What's ClearLines.Anakin?

Thanks!!!!!

1/2/2011 1:25:25 AM #

I'm not able to reproduce your problem, Patricio. Could you specify it a bit? Why you think it's a namespace problem?
KR, Chris

1/26/2011 10:46:00 AM #

I get an "Undefined CLR Namespace" error at this line

in the AnakinView.xaml

My project is named "FirstExcelAddIn1" and I have the same structure in my project as your example, the only one difference is that I'm building it in VB instead of C#

2/1/2011 12:58:42 PM #

I am getting this same error mate. Let me know if you find out the problem. I am working on it since last 6 hours...I am sleepy now

2/2/2011 3:38:03 AM #

I'm guessing that you're using VS2010 as me, i resolved the issue deleting the additional rows that are built, I just put them the following:

xmlns="schemas.microsoft.com/.../presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

2/13/2011 4:57:06 AM #

Create an Excel 2007 VSTO add-in: wrapping up part 1

Create an Excel 2007 VSTO add-in: wrapping up part 1

3/20/2013 10:26:48 AM #

I can't find anakinView1 on TaskPaneView.cs fine, so I can't compile that.

--> return this.anakinView1;

Where can I solve this?

3/22/2013 3:35:56 PM #

Did you follow the steps in the previous post? The anakinView should be the control that was previously added on the WPF Host.

6/11/2013 6:40:52 AM #

I always getting this error while running the above.

"Items collection must be empty before using ItemsSource." at anakinView.DataContext = anakinViewModel;