My first "complex" VSTO 2005 Project

  • Article

I know I am behind in posting materials in the resource center... working on that. Actually I got caught up in trying to build a project with VSTO instead.

So, I've started putting my experiences to good use and have begun development on an Excel based VSTO 2005 project. We have an application here at the MTC that handles all our scheduling info. It is currently built just using Excel linked lists to SharePoint. The problem here is that the complex calendar view is really read-only and you have to go to other sheets and make edits once you have found your entry. Plus there really should be some data from other data sources presented here. So my project got off to a great start. I decided to start with a single dataset as my data source. This was a dataset that I constucted in Visual Studio since my data will really be coming from a lot of different sources. The goal is to use the offline caching capability and then I would write a manager that applies the changes and syncs with the authoritative sources. So as much as I prefer C#, I've forced myself to use VB.NET just because the COM interop syntax seems easier in VSTO.

So my first task was to databind to some root data tables with list objects down the left hand side of the spreadsheet. This was extremely easy with the databinding experience provided by VSTO. Pretty much drag and drop. But my lists each had a header and a footer that enabled new entries to be added. For my application, I wanted these three lists to really blend together without distinct headers or that footer. I gave up trying to find a property that simply turned them off and added some code that used the length of the list in order to hide these rows:

Private Sub HideRows()
        Me.Rows(Me.resourcesListObject.HeaderRowRange.Row).Hidden = True
        Me.Rows(Me.roomsListObject.HeaderRowRange.Row).Hidden = True
        Me.Rows(Me.showsListObject.HeaderRowRange.Row).Hidden = True
        Me.Rows(Me.resourcesListObject.HeaderRowRange.Row + Me.resourcesListObject.Range.Count).Hidden = True
        Me.Rows(Me.roomsListObject.HeaderRowRange.Row + Me.roomsListObject.Range.Count).Hidden = True
        Me.Rows(Me.showsListObject.HeaderRowRange.Row + Me.showsListObject.Range.Count).Hidden = True
End Sub

One thing I noticed is a missed feature of the Excel menu merge with Visual Studio. This feature of VSTO is supposed to provide the Excel menu options within Visual Studio. The problem is that they seem to have forgotten "Freeze Panes" which is supposed to be merged into the "Window" menu tab. Ooops! You could always start your project with a spreadsheet that has the panes frozen to begin with. Or in my case (I discovered it way too late), I froze the panes after running the program and just made sure to save the spreadsheet.

Here are some tasks I'm working on now that I will blog about soon:

  1. Setting up my task pane control structures
  2. Adding a date picker in the task pane so that when a date is chosen a whole row of date columns updates with that date as the first column
  3. Resizing the named range which is my interesection of the above lists and the date columns
  4. Building a wrapper for detecting if the user is "online" or "offline"

I'll include some screen shots of progress as I get things going...