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

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
  6. Using Excel events to update the TreeView

Today is the day, we will finally close “chapter one” of these series, with some minor improvements of the tree view display of open workbooks and worksheets. The final result of our work looks like this, with a TreeView displaying all open workbooks and worksheets, refreshing its contents (quasi) automatically, and with some home-made icons just for kicks.

FullTreeView

Rather than a systematic walk-through, I will just explain the changes I implemented to the code base, which I have now posted on a dedicated Wiki page.

Download Anakin project code

More...

by Mathias 21. March 2010 15:22

Finally performed the upgrade to BlogEngine 1.6.0. I apologize to my readers who may have experienced outages – the process wasn’t quite as smooth as for the previous versions, in part due to the documentation, which was less thorough than usual. Thanks to David Wynne for his step-by-step walkthrough which came in very handy. I would also have appreciated if somewhere, it was mentioned that the database schema had been slightly modified…

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.

More...

by Mathias 11. March 2010 12:15

While working on my VSTO Excel add-in tutorial, I came across the following issue: I need to know whether a worksheet has been deleted. The reason I care is that when it happens, I need to update the display of the worksheets that are currently open, and remove it from there.

I was very surprised to find out that there seems to be no event for this. The Application object, which represents the Excel application, has a WorkbookBeforeClose event; the Workbook object has an event BeforeClose, triggered when the Workbook is being closed. So naturally, I expected to find something equivalent for the Worksheet object, at either the Application, Workbook, Sheets, Worksheets, or Worksheet level – no such luck.

I looked around on the web, and from what I can tell, there is no native event for this, and I came across multiple posts advocating to handle this through Worksheet.Activate and/or Worksheet.Deactivate. I see how this catches the obvious use case, namely, the user selects the sheet and deletes it – which causes the sheet to be activated, and then another worksheet to be activated once the deletion is performed. Unfortunately, this doesn’t catch all the cases: as far as I can tell, it is perfectly possible to delete a worksheet without ever changing which sheet is active. To prove the point, create a workbook, and add the following macro:

Public Sub DeleteSheet3()
    Application.DisplayAlerts = False
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
End Sub

More...

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.

More...

Comments

Comment RSS