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

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.

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();
      this.workbookViewModels.Add(fakeWorkbookViewModel1);
      this.workbookViewModels.Add(fakeWorkbookViewModel2);
   }

   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();
      this.worksheetViewModels.Add(fakeWorksheetViewModel1);
      this.worksheetViewModels.Add(fakeWorksheetViewModel2);
   }

   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:

ProjectStructure

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.

We will do so in the Add-In startup method, so that as soon as the control is created, it is supplied with an access to the Add-In functionality. First, we need to provide access to the AnakinView control: it is currently not visible through the TaskPaneView, because the field which was created when we added the element host and the user control are private. Let’s add an internal property to the TaskPaneView: right-click on the TaskPaneView.cs file, select “show code”, and edit the code to the following:

public partial class TaskPaneView : UserControl
{
   public TaskPaneView()
   {
      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 taskPaneView = new TaskPaneView();
   this.taskPane = this.CustomTaskPanes.Add(taskPaneView, "Anakin");
   this.taskPane.Visible = false;

   var anakinViewModel = new AnakinViewModel();
   var anakinView = taskPaneView.AnakinView;
   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"
    xmlns:TreeView="clr-namespace:ClearLines.Anakin.TaskPane.TreeView">
    <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:

AddInWithFakeWorkbooks

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:

AddInWithFakeWorkbooksAndWorksheets

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;
   private readonly Excel.Workbook workbook;

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

   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 excel = Globals.ThisAddIn.Application;
      var workbooks = excel.Workbooks;
      foreach (var workbook in workbooks)
      {
         var book = workbook as Excel.Workbook;
         if (book != null)
         {
            var workbookViewModel = new WorkbookViewModel(book);
            this.workbookViewModels.Add(workbookViewModel);
         }
      }
   }
}

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;
   private readonly Excel.Workbook workbook;

   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);
            this.worksheetViewModels.Add(worksheetViewModel);
         }
      }
   }

   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:

RealWorkbooksAndWorksheets

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:

TreeWithAuthor

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!

As usual, I welcome comments, questions and criticisms from my readers!

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.

Comments

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

Dennis Wallentin

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

Dennis Wallentin Sweden | Reply

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

Mathias

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" Smile
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

Mathias | Reply

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

Dennis Wallentin

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

Dennis Wallentin Sweden | Reply

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

trackback

Create an Excel 2007 VSTO add-in: Excel events

Create an Excel 2007 VSTO add-in: Excel events

Clear Lines Blog | Reply

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

Maxi

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.

Maxi United Kingdom | Reply

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

Mathias

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

Mathias United States | Reply

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

Santanu

I especially like this one.

Santanu United States | Reply

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

matunas

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

matunas United States | Reply

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

Mandy

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

Mandy United States | Reply

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

Patricio Cuaron

Hi. Great series!

I have a question on this line of code in the AnakinViewModel:
             using ClearLines.Anakin.TaskPane.TreeView;

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

Patricio Cuaron Argentina | Reply

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

Chris Howl

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

Chris Howl United States | Reply

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

Mario Cerna

I cant build the Add-in
I get an "Undefined CLR Namespace" error at this line

xmlns:TreeView="clr-namespace:FirstExcelAddIn1.WorkbookViewModel"
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#

Mario Cerna Canada | Reply

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

Lauren

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 Smile

Lauren United Kingdom | Reply

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

Mario Cerna

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:

<UserControl x:Class="MyAddIn.TaskPane.AnakinView"
             xmlns="schemas.microsoft.com/.../presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:TreeView="clr-namespace:MyAddIn">

Mario Cerna Canada | Reply

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

trackback

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

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

Clear Lines Blog | Reply

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

Jeong

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

--> return this.anakinView1;

Where can I solve this?

Jeong Australia | Reply

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

Mathias

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

Mathias | Reply

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

Kaur

I always getting this error while running the above.

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

Please help me. I cannot find the solution to this anywhere.

Kaur United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS