Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 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.

Our first step will be to add a WPF User Control to our project. In the TaskPane folder, where we have the TaskPaneView control already, let’s right click, add a new item, select “User Control (WPF)” – and not User Control – and name it “AnakinView”. A few things will happen: a new file, AnakinView.xaml is added to our folder, with a code-behind associated file, AnakinView.xaml.cs, Visual Studio automatically adds a few references into the References folder, to support the usage of WPF, and the screen is split in a dual mode: the top section shows how our control renders, and the bottom section displays the xaml code that forms our control.

ProjectWithXamlFile

We will keep things simple to start with, and implement the following behavior: the control will display a button, which when pressed will look up all the open workbooks, and display them and their worksheets in a treeview. Later on, we will look into something more fancy, and see if we can automatically refresh the contents of the tree, by tracking whether workbooks and worksheets have been opened, created or closed.

Let’s create the button first. In Visual Studio, click on the empty square in the design surface, and from the ToolBox, select a Button and drag it on the surface. You will see something like this appear:

DraggingButton

The button you just dragged is now visually represented, and in the XAML section, some code has been added. That code fully represents the button on display in the Design surface, and any change you do to it will be reflected in the representation. Let’s prove that point, and edit the code (note that when you click on the code for the button, the control gets highlighted in the Design surface):

<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"
    Height="300" Width="300">
    <StackPanel>
        <Button Width="75" Height="30" 
                HorizontalAlignment="Left">
            Refresh
        </Button>
    </StackPanel>
</UserControl>

If you have worked with html before, you should have a mysterious feeling of deja-vu: this is pretty similar to the markers used on a classic webpage. We have a <StackPanel> opening, which closes at </StackPanel>, and contains a <Button>, which contains some text, “Refresh” (A StackPanel is a container, where each item will be displayed as stacked on top of each other).

Just for kicks, try this – inside the button, replace “Refresh” by “<CheckBox/>”. You’ll see that your button, instead of text, now contains a… checkbox. Now think how difficult it would be to create a button-with-a-checkbox with classic Windows forms. Granted, this is a silly example, but it gives a taste of the flexibility of WPF: you can combine virtually any control into more complex controls, to do whatever you want. This can prove very useful for situations where, for instance, you need to create a list of items where each item has text editable in textboxes, images, calendars, buttons, and whatnot; it’s not necessarily easy, but it is possible.

Now let’s make some final minor modifications: add a TreeView control, some margins, and remove the default 300x300 size of the control, so that rather than having a fixed size, the control will adjust to its container, and fill in the entire space available if docked.

<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 Height="200"></TreeView>
    </StackPanel>
</UserControl>

It is time to add our control to the TaskPane. Open the TaskPaneView, select ElementHost in the WPF Interoperability section of the ToolBox, and drag it onto the TaskPaneView. The following menu will appear

HostedElement

In the Select Hosted Content combo box, you should see AnakinView listed (if not, build your solution once and try again) – select that, and click “Dock in parent container”. Your control should look like this now, with the WPF control completely filling the TaskPaneView control:

WpfControlInsideTaskPane

In essence, what we did is use the Task Pane as an empty shell, whose only purpose is to provide a location for our WPF control to exist.

Hit F5 to run in debug mode: when you click “Compare” in the Review tab, you’ll see that the Task Pane now proudly displays our WPF control.

ExcelWithTaskPaneAndWpfControl

That’s it for today! The TreeView control is in place, next time, we will fill it in with the workbooks and worksheets currently open.

Comments

2/28/2010 11:18:51 AM #

Dennis Wallentin (XL-Dennis)

Mathias,

Good work and I find the WPF case to be straight and understandable. Who knows, maybe I shall start to use WPF Wink

Kind regards,
Dennis

Dennis Wallentin (XL-Dennis) Sweden | Reply

2/28/2010 2:37:36 PM #

mathias

Hi Dennis,
Thanks for the kind words! I highly recommend giving a shot at WPF, which is one of my favorite .Net 3.0 additions. Initially, the learning curve is a bit steep, but once over the initial hurdle, it is fairly straightforward to use, and really opens up tons of possibilities regarding what you can do with the user interface, and makes a big difference in testability. Windows Forms are great, and usually sufficient, but sometimes there are cases where you are stuck and have to force your design, because you have to work with the existing controls. With WPF, you can design pretty much any control to behave the way you want to. It's not always trivial, but you can program it to do whatever you want!

mathias United States | Reply

3/6/2010 12:50:09 AM #

trackback

Create an Excel 2007 VSTO add-in: display open worksheets in a TreeView

Create an Excel 2007 VSTO add-in: display open worksheets in a TreeView

Clear Lines Blog | Reply

7/26/2010 3:38:55 AM #

Son Best

I think I will try out WPF as well. But first I will go through your next posts where we fill in the workbook and worksheets that are open Smile great and easy to understand so far Smile

Regards
Son Best

Son Best United Kingdom | Reply

9/18/2010 7:08:38 PM #

shoaib hussain

I am fairly new to .NET,but after reading this post of yours m thinking of moving on with some main stream courses.

Regards shoaib

shoaib hussain India | Reply

11/23/2010 6:10:21 AM #

Ron

I've been having issues learning WPF and your post gave  me some insight. This is the only kink of Net 3.0 that im still struggling with and would love to see more tutorials.

Ron United States | Reply

1/3/2011 4:53:45 PM #

trackback

Create an Excel 2007 VSTO add-in: Excel events

Create an Excel 2007 VSTO add-in: Excel events

Clear Lines Blog | Reply

1/15/2011 6:10:40 AM #

flecx

Any chance of getting an updated version of this post for Excel 2010. your examples are so clear!

flecx United Kingdom | Reply

2/1/2011 1:03:42 PM #

Lauren

Nice Share! It would be great if you share an update version of this post for Excel 2010. Thanks

Lauren United Kingdom | Reply

3/23/2011 7:34:13 AM #

Akrapovic

Thanks Mathias!  I have Excel 2010 on my computer, but another I tried this on another computer with 07 and it worked!  The menus on 2010 are so much different than 07.

Akrapovic United States | Reply

3/28/2011 1:25:38 AM #

Bruce

This is a really helpful tutorial. Thanks.
However, just a wee note: some things are magically happening. e.g. 1/3 of the way down this page when you add the button to the xaml form we can see the xaml in a screenshot and it shows a Grid. In the very next screenshot the Grid has been replaced with a StackPanel.
Obviously this can be changed by the user, but it would be nice to know what you've done and why you've done it.
Thanks,
Bruce.

Bruce United Kingdom | Reply

4/3/2011 10:17:45 AM #

mathias

Hi Bruce,
Thanks for the comment. I try my best to keep the explanations clear, but sometimes things fall through the cracks - nice catch! And no, I won't pretend that I did it to check that the reader was paying attention Smile
Specifically here, the default layout for a WPF control is a Grid, but I usually prefer to use a StackPanel. A Grid gives a bit more control for layout, but I find that the StackPanel, which simply stacks controls on top of each other, is usually sufficient for my needs, and requires less code modification when I decide to re-organize controls...
Take care,
Mathias

mathias United States | Reply

6/26/2011 11:27:06 PM #

Samuel

I stumbled across the tutorial by accident and decided to give it a go.  Never knew this was possible.  Thanks for sharing.

Samuel Australia | Reply

2/3/2015 4:51:49 AM #

Healthy Weight Loss Tips After Pregnancy

This could possibly be enough to help you fit comfortably with your clothes. Try wearing a pedometer and measuring the volume of steps you adopt each day, looking to accumulate more out of your current average. A fantastic way to start paying more awareness of your diet plan is usually to start keeping a regular food diary.

Healthy Weight Loss Tips After Pregnancy United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS