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

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control
  5. Displaying open workbooks & worksheets in a TreeView

It’s time to wrap-up the first part of this tutorial, and hook up our tree view to Excel events, to update the contents of the tree when the user changes what is open.

We need to capture the following: we need to update the TreeView when the user

  • Opens, creates or closes a workbook
  • Adds or deletes a worksheet

Added Worksheets and Workbooks

Let’s start with adding a worksheet to a workbook. Excel exposes that event, through the Workbook.NewSheet event. We want the WorkbookViewModel to take care of his children, so we modify the constructor the following way:

internal WorkbookViewModel(Excel.Workbook workbook)
{
   this.workbook = workbook;
   workbook.NewSheet += new Excel.WorkbookEvents_NewSheetEventHandler(workbook_NewSheet);
   // no change here, code stays the same
}

void workbook_NewSheet(object newSheet)
{
   var worksheet = newSheet as Excel.Worksheet;
   if (worksheet != null)
   {
      var worksheetViewModel = new WorksheetViewModel(worksheet);
      this.worksheetViewModels.Add(worksheetViewModel);
   }
}

Creating event handlers can be a bit tedious; fortunately, Visual Studio simplifies the task quite a bit. When you type workbook.NewSheet +=, you should see a tooltip appear, which “suggests” an event handler. Type Tab, and Tab again – Visual Studio will create for you the empty event handler, with the right arguments and types, where you can now insert the logic of what should happen when the event is triggered.

We will do some minor code refactoring here, to remove some code duplication, the explicit event handler declaration and clean up the method names. Both the constructor and handler perform the same validation when adding a sheet – let’s extract it into a method AddWorksheet, and rename the handler to something more palatable, to end up with the following code:

internal WorkbookViewModel(Excel.Workbook workbook)
{
   this.workbook = workbook;
   workbook.NewSheet += this.AddSheet;
   this.worksheetViewModels = new ObservableCollection<WorksheetViewModel>();
   var worksheets = workbook.Worksheets;
   foreach (var sheet in worksheets)
   {
      this.AddSheet(sheet);
   }
}

private void AddSheet(object newSheet)
{
   var worksheet = newSheet as Excel.Worksheet;
   if (worksheet != null)
   {
      var worksheetViewModel = new WorksheetViewModel(worksheet);
      this.worksheetViewModels.Add(worksheetViewModel);
   }
}

If you run the add-in in debug mode now, you’ll see the following: if you expand the workbook in the TreeView, and add a Worksheet, the TreeView will automatically refresh, and add that sheet to the list. This happens because we have established a binding to the ObservableCollection of WorksheetViewModel(s) in the WorkbookViewModel; as a result, the observable collection notifies the control that the collection has changed, and automatically updates its display.

Let’s do the same type of transformation, at the ExcelViewModel level. We will leverage the following events, which should be self-explanatory: NewWorkbook, WorkbookBeforeClose, WorkbookBeforeSave and WorkbookOpen.

We need a reference to the Excel application to hook our events; temporarily, we will access it in the constructor through Globals.ThisAddIn.Application. However, we run into a small issue here. When you type in the following code in the constructor:

Excel.Application excel = Globals.ThisAddIn.Application;
excel.NewWorkbook+=

… instead of auto-completing the handler, we get a warning that NewWorkbook is ambiguous. The reason for this can be understood if you dig into the Excel.Application interface. Excel.Application implements 2 interfaces, _Application, which covers most of the Excel methods and properties, and AppEvents_Event, which exposes all the Excel events. The reason for this somewhat confusing design isn’t quite clear to me, but if you look through the members of each of these two interfaces, you’ll see that _Application has a NewWorkbook property, and AppEvents_Event a NewWorkbook event, and these two names collide.

EventNameCollision

To resolve that issue, we simply need to disambiguate our call, by explicitly casting Excel.Application to the interface we are interested in, with the following code:

internal ExcelViewModel()
{
   this.workbooks = new ObservableCollection<WorkbookViewModel>();
   Excel.Application excel = Globals.ThisAddIn.Application;
   ((Excel.AppEvents_Event) excel).NewWorkbook +=
      new Excel.AppEvents_NewWorkbookEventHandler(ExcelViewModel_NewWorkbook);
   this.RefreshWorkbooks();
}

private void ExcelViewModel_NewWorkbook(Excel.Workbook newWorkbook)
{
   throw new System.NotImplementedException();
}

The other event related to new workbooks, Application.WorkbookOpen, does not have that collision issue, so the cast here isn’t required, and, after some cleanup and refactoring, we end up with the following code, which automatically updates the TreeView when workbooks are opened or created:

internal ExcelViewModel()
{
   this.workbookViewModels = new ObservableCollection<WorkbookViewModel>();
   Excel.Application excel = Globals.ThisAddIn.Application;
   ((Excel.AppEvents_Event) excel).NewWorkbook += this.AddWorkbook;
   excel.WorkbookOpen += this.AddWorkbook;
   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);
      }
   }
}

private void AddWorkbook(Excel.Workbook newWorkbook)
{
   var workbookViewModel = new WorkbookViewModel(newWorkbook);
   this.workbookViewModels.Add(workbookViewModel);
}

Removed worksheets and Workbooks

Now we just need to perform the reverse operations when a workbook or a Worksheet is closed – and this is where the pain begins. <bitter>I had forgotten that Excel – whether through VBA or VSTO - exposes only a surprising limited set of events regarding deletions, which are rather inadequate to track whether a workbook or worksheet has been closed.</bitter>

There is simply no event to signal Worksheet deletion, and Application.WorkbookBeforeClose is only marginally helpful. It captures whether the user requested the workbook being closed, which is useful in some scenarios (preventing closure if some conditions are not met), but the user can still chose to cancel after the event has been fired. As a result, the Workbook could end up staying open even though that event fired. So we have no direct reliable way to know whether either a Workbook or Worksheet is gone from the workspace, and should be removed from the TreeView.

So we’ll do with what we have available. Typically, when a user closes a workbook or worksheet, that item is “active” – and upon deletion, the active element is changed. So rather than track whether items have been closed, we will look for changes in the active workbook and worksheet, and whenever this occurs, we will run a cleanup procedure. We will iterate over every item in the TreeView, and if it is no longer open at that point, we will remove it from the tree.

As I explained in my previous post, there is still a chance that the tree is out of sync with Excel, because it is technically feasible to delete an element without impacting active items, but this will capture the most standard use case, and re-sync over time, as soon as the user changes the active element in Excel.

Here is the code I ended up with. First, we subscribe to the events WorkbookActivate and SheetActivate in the ExcelViewModel constructor:

internal ExcelViewModel()
{
   this.workbookViewModels = new ObservableCollection<WorkbookViewModel>();
   Excel.Application excel = Globals.ThisAddIn.Application;
   ((Excel.AppEvents_Event)excel).NewWorkbook += this.AddWorkbook;
   excel.WorkbookOpen += this.AddWorkbook;
   excel.WorkbookActivate += ActiveWorkbookChanged;
   excel.SheetActivate += ActiveSheetChanged;
   // same as before
}

Then, in the handlers for these events, we call a common routine, which iterates over the tree elements and deletes “obsolete” ones:

private void ActiveSheetChanged(object activatedSheet)
{
   this.RemoveClosedWorkbooksAndWorksheets();
}

private void ActiveWorkbookChanged(Excel.Workbook activatedWorkbook)
{
   this.RemoveClosedWorkbooksAndWorksheets();
}

private void RemoveClosedWorkbooksAndWorksheets()
{
   var workbooks = Globals.ThisAddIn.Application.Workbooks;
   foreach (var workbookViewModel in this.workbookViewModels)
   {
      var workbookIsOpen = false;
      foreach (var workbook in workbooks)
      {
         if (workbookViewModel.Workbook == workbook)
         {
            workbookIsOpen = true;
            break;
         }
      }

      if (workbookIsOpen == false)
      {
         this.workbookViewModels.Remove(workbookViewModel);
      }
      else
      {
         var workbook = workbookViewModel.Workbook;
         var worksheets = workbook.Worksheets;
         foreach (var worksheetViewModel in workbookViewModel.Worksheets)
         {
            var worksheetIsOpen = false;
            foreach (var sheet in worksheets)
            {
               var worksheet = sheet as Excel.Worksheet;
               if (worksheet != null)
               {
                  if (worksheet == worksheetViewModel.Worksheet)
                  {
                     worksheetIsOpen = true;
                     break;
                  }
               }
            }

            if (worksheetIsOpen == false)
            {
               workbookViewModel.Worksheets.Remove(worksheetViewModel);
            }
         }
      }
   }
}

I have to confess that I am not overly proud of this code, for a few reasons. First, stylistically, the RemoveClosedWorkbooksAndWorksheets method is too long and could (should) be broken into two methods, one for Workbooks, one for Worksheets, but that’s easy enough to fix. Then, we are running a complete “refresh” pass over every element, every time the user activates a worksheet and workbook. It shouldn’t create performance problems, because the number of elements is fairly small, and we will need to track these changes anyways later for other reasons, but just thinking of how much nicer it would have looked had the right event hook been available makes me unhappy… Then, while the foreach loops are pretty clear, they are also clumsy, especially after you have had a taste of Linq – but I couldn’t manage to use Linq here (I haven’t tried very hard, though). Finally, this may be a misplaced concern, but because we observe deletions after they have taken place, we have no opportunity to remove the event subscription in WorkbookViewModel, or clean up the references to the now-gone Workbook and Worksheet objects inside the view models. I don’t think it really matters, and I can’t think of how to address the issue anyways, so I’ll have to let go I guess.

As an aside, I have left out another update of the TreeView which would be needed, too, but is complicated for the same reason, the lack of adequate events: we would also need to update the name of the Workbook when it gets saved, and of the Worksheet when it is renamed. This could be done in our “refresh” loop, by updating the property of elements that are not deleted – I won’t illustrate how to do it here, but will put it in the code when I post it sometime later this week, once I have completed cleanup.

If you have suggestions on improving this code, or simply questions about it, please let me know!

In the next installments, now that we are reasonably done with the TreeView, we will focus on a totally different topic: how to generate comparisons between the current active sheet, and the sheet selected in the treeview, and how to navigate through these differences and reconcile them if needed. Stay tuned!

Comments

3/27/2010 11:18:25 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

8/3/2010 10:57:10 AM #

Gina

Hi,

I have several problems on these lines, I've tried figure it out for about 3 hrs, no luck Frown

private void AddSheet(object newSheet)
{
   var worksheet = newSheet as Excel.Worksheet;
   if (worksheet == null)
   {
      var worksheetViewModel = new WorksheetViewModel(worksheet);
      this.worksheetViewModels,Add(worksheetViewModel);
   }
}

Thanks in advance!

Regards,
Gina

Gina Denmark | Reply

8/3/2010 11:06:24 AM #

mathias

Hi Gina,
Sorry to hear about your problem. Can you provide some details about the issue, i.e. which line, what error message? There is a typo in this.worksheetViewModels.Add (you have a comma instead of a dot) but I assume this is not what is blocking you.
Mathias

mathias United States | Reply

8/10/2010 10:20:33 PM #

Gina

Hi Mathias,

It's fixed, yes it was only minor problem like you mentioned. I added comma instead of dot, my bad hadn't check it in the first place. Thanks anyway, you've been very  helpful.

Regards,
Gina

Gina United States | Reply

10/17/2010 6:12:19 AM #

Frogger Gamer

Man, it's amazing that one stupid comma can break an entire code.  I didn't even look closely enough at it to find that until you mentioned it.  Works well now however.  Thanks for the update.

Frogger Gamer United States | Reply

11/28/2010 10:07:25 PM #

Nick Marshall

that's a very nice tutorial.  Thanks for taking the time to write that up, very useful info.  I think you're being a bit overcritical of yourself, it's a job well done.

Nick Marshall United States | Reply

1/15/2011 5:48:42 AM #

flecx

Great tutorial the example code saved me hours, thanks mate

flecx United Kingdom | Reply

1/16/2011 7:41:57 AM #

Sem

Thanks for such a nice tutorial, I following it from the beginning. The code is not cluttered and it's quite easy to follow. Have bookmarked your website for the future visits. Thanks again!

Sem United States | Reply

2/20/2011 8:55:51 AM #

trackback

Excel 2007 VSTO add-in: table of contents

Excel 2007 VSTO add-in: table of contents

Clear Lines Blog | Reply

3/28/2011 9:37:17 PM #

Bruce

I'm still just getting my head around C#, but have spent years working in VBA, so apologies if this suggestion is not valid:
In your RemoveClosedWorkbooksAndWorksheets procedure you have basically done the following

ForEach workbook In MyTree
   ForEach workbook In Excel
      Check to see if they are the same. If  they are, Break.
   Next workbook in Excel
Next Workbook in MyTree

and similar for worksheets.

This could be made quite a bit quicker and neater (I think) by using the name of the item.
I would do it as
ForEach workbookViewModel in ExcelViewModel.Workbooks
    Excel.Workbook wb = ExcelViewModel.Workbooks(workbook.Name)
    if(wb==null){
         wvm.Delete
     }
Next workbookViewModel

As you can see, we have removed one of the loops so we no longer have a nested loop.
This is quite safe because it is not possible to have two workbooks with the same name open at the same time, or two sheets with the same name in the same workbook.

As I said, I'm only just getting to grips with C#, so there may be excellent reasons you didn't do it this way that I don't understand. If so, I'd really like to know.
        

Bruce United Kingdom | Reply

2/7/2012 4:21:30 AM #

Bryan

I have a VSTO Addin where I need to capture the event of the SelectionChange.  The problem is the worksheet object is NULL on the ADDIN_STARTUP so I cant create a listener for it.  Once the sheet loads the object is available but I have no good place to capture this event.  Is there anyway of doing this in the ADDIN_STARTUP or can you recommend a different location?

Bryan United States | Reply

2/12/2012 10:16:31 AM #

Mathias

Hi Brian,
As you pointed out, the issue is that the SelectionChange event is a Worksheet event, so you would need to create an event subscription specifically for the Worksheet(s) you are interested in. Can you say a bit more about what you are trying to attempt? I assume you are not trying to capture SelectionChange on every single sheet that is open, but rather tracking it on a specific Worksheet, is that correct?

Mathias | Reply

2/24/2012 2:09:40 AM #

Florin

I'm trying to replicate your tutorial in VB.NET and it's going very well so far.
But I'm stuck at
workbook.NewSheet += this.AddSheet;
Since the AddSheet method has a parameter, VB is asking for one.
In C# your code works great, of course.
Any suggestion?

Florin Germany | Reply

10/16/2012 8:57:04 PM #

Srikanth

This a nice and descriptive article wrt Excel.

Srikanth India | Reply

10/16/2012 9:02:11 PM #

Srikanth

I have one problem in my application. I created an Excel Addin application. I wrote code in ThisAddin Class, i was able to write data onto the sheet without any problem. But if i want to write data onto the sheet from another class, it is throwing an exception. Is it mandatory that i should write all the accessing or updating logic related to the sheet in ThisAddin class only? Please help me.

Srikanth India | Reply

10/17/2012 1:54:44 AM #

Bryan

Hi Srikath,

What you need to use is the globals object.  You can create any number of classes that use this.  In fact I would reccomend refraining from putting code in the ThisAddin just due to the fact it would be messy.  It should look like Globals.ThisAddIn.Application, using the Globals you can expose what sheet you are trying to write data against.  When I was coding a VSTO application I used this article to help with writing data to other worksheets,
stackoverflow.com/.../access-excel-worksheet-in-c-sharp-class-file-using-vsto

Bryan | Reply

10/17/2012 3:49:52 PM #

Srikanth

Thanks a lot Bryan. This article was of great help. Please suggest me a design pattern which would suit an Excel Addin application for 1) organising code in a better way and 2) for unit testing most of the code written by me.

Srikanth India | Reply

10/18/2012 2:08:26 AM #

Bryan

I've done a fair amount of VSTO over the years.  Most people recommend using the M-V-VM pattern.  I would also agree with this.  The problem is that most MVVM material is related to WPF.  I stumbled across this article last year, aviadezra.blogspot.com/.../...s-data-binding.html.  The author does a really good job of explaining it.  

The other option which should be possible is SCSF, I played around with this about a year ago.  The problem with SCSF is that it is difficult to learn and even more difficult to use.  I wish I could point you to a good MVVM tutorial for VSTO but i've never found one.  I suppose I should create one for VSTO developers.  

Bryan United States | Reply

10/21/2012 12:12:12 PM #

Mathias

Hi Srikanth,
testing VSTO is unfortunately not the easiest thing to do. I agree with Bryan on the MVVM pattern for WPF UI aspects. However, that covers only a small part of the VSTO world. My approach is to leave as little code as possible in the add-in itself, and move the logic into a separate dll, which the add-in references - this simplifies unit testing a lot. I have never used it myself, but the following link might be also of interest to you, it discusses UI testing for Excel: msdn.microsoft.com/en-us/library/ff398055.aspx
Hope this helps!
Mathias

Mathias United States | Reply

10/30/2012 2:10:49 AM #

Andrei

Hi,

Writing just to say THANK YOU for the excellent Excel programming tutorial. I've been developing an add-in in the last 3 weeks and your tutorial was much appreciated and very helpful (helped me in situations where I was banging my head against the wall: I still can't believe it that there's no event for Worksheet close(d), that there is no direct way to find out if a cell/range is in edit mode and that you can only check this at Application level by trying to set Interactive to false and catching the exception...)!

Off topic: there is no option for Cyprus in the  countries drop-down Smile

Andrei Romania | Reply

11/3/2012 7:45:51 AM #

Mathias

Hi Andrei,
Thanks for the positive feedback, and I am happy that you found this helpful! I have been banging my head against the wall a few times, too - and agree that the incompleteness of events can be very, very frustrating. In any case, good luck with your add-in - and sorry about Cyprus not being in the drop down Smile
Mathias

Mathias United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS