4. VSTO Bug Tracker : Getting the data into Excel

The next step is to get more of the bug data into Excel so we can start to analyze it.  To do this, we will use VSTO’s data bind to ListObject support.

I’m going to approach this in a different “more advanced” way that will make sense later in the demo. Long time VSTO developer will know that the simple way to bind data to a list object in VSTO is to create an Excel Workbook project, add a data source, then drag and drop the data source onto the Excel Workbook.

The problem with that approach for this demo is that the code that is generated is deeply tied to the specific workbook being designed.  Since I’m going to show some advanced tips and tricks here including using document features in an add-in, I’m going to do this in a way that doesn’t tie it to a specific workbook project.

We want to add the new Excel Workbook project to our existing solution that has our BugService project in it.  To do this, select File > Add > New Project.  Pick Visual Basic or Visual C# then Office then 2007 in the Project types outline.  Then pick Excel 2007 Workbook project and name it BugWorkbook:

Select Create a new document in the next dialog box that appears then click OK.

Now we want to add a web reference to our BugService web service.  Right click on the BugWorkbook project node in Solution Explorer and choose Add Service Reference...  Then click on the Advanced button then the Add Web Reference.. button. Click the “Web Services in this Solution” link.  Then click “BugService”.  Change the web reference name from localhost to BugService and click the Add Reference button:

Now, right click on Sheet1.vb (or Sheet1.cs) and choose View Code.

Add these two member variable to the ThisWorkbook class.  We need a variable to hold onto our dataset and one to hold onto a button we will be dynamically creating.  The reason we are using a dictionary object to store our button will be apparent later in the demo, for this section it will feel like overkill.

     Public bugDataset As DataSet
    Public buttonDictionary As New Dictionary(Of Excel.Worksheet, Microsoft.Office.Tools.Excel.Controls.Button)

Startup and Shutdown for the sheet look like this:

     Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        PopulateSpreadsheet(True)
    End Sub

    Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
        CleanUpDynamicButton()
    End Sub

The PopulateSpreadsheet method takes a boolean parameter that sets whether or not to force that the data be added.  The reason for this parameter will be apparent later as well.  This code finds the existing listobject with the bug data (if it has already been inserted into the spreadsheet in a previous run of the spreadsheet).  If it hasn’t yet been created, it creates the list.  It then contacts the web service (it does this every time the document opens), gets all the bug data, binds the resulting dataset to the  list object, then it disconnects from the list object once it has been populated.  Finally, it dynamically creates a button that if clicked will create a new sheet with a pivot table for the data in the list.  It puts the button in a dictionary along with the worksheet it was created on so it can be cleaned up later (in CleanUpDynamicButton).  The FindList method is a helper method that looks through the workbook to see if we’ve already added a listobject—it does this by looking for a listobject named “BugListObject”.

     Public Sub PopulateSpreadsheet(ByVal alwaysAdd As Boolean)
        Dim foundList As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing

        foundList = FindList()

        ' If not there create it if always add is true
        If foundList Is Nothing Then
            If alwaysAdd = True Then
                s = Application.ActiveSheet
                foundList = s.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, s.Range("A1", "D2"))
                foundList.Name = "BugListObject"
            Else
                Exit Sub
            End If
        Else
            s = TryCast(foundList.Parent, Excel.Worksheet)
        End If

        ' Dynamically create the list object
        Dim vstoSheet As Microsoft.Office.Tools.Excel.Worksheet
        vstoSheet = GetVstoObject(s)

        ' Do we already have a listobject, if so exit.
        If vstoSheet.Controls.Contains("BugListObject") Then
            Exit Sub
        End If

        Dim list As Microsoft.Office.Tools.Excel.ListObject = vstoSheet.Controls.AddListObject(foundList)

        'If NeedsFill("bugDataset") Then
        Application.StatusBar = "Updating data from bug web service..."
        Dim bugService As BugService.BugService = New BugService.BugService()
        bugDataset = bugService.BulkDataExport("", "")
        'End If

        list.AutoSetDataBoundColumnHeaders = True
        list.DataSource = bugDataset.Tables(0)
        list.Range.Columns.AutoFit()
        list.Disconnect()

        Dim buttonRange As Excel.Range = s.Range("E1")
        Dim pivotButton As Microsoft.Office.Tools.Excel.Controls.Button
        pivotButton = vstoSheet.Controls.AddButton(buttonRange, "CreatePivotTable")
        buttonDictionary.Add(s, pivotButton)
        AddHandler pivotButton.Click, AddressOf pivotButton_Click
        pivotButton.Text = "Pivot"

        Application.StatusBar = ""
        s.Name = "Bug Data"
        s.Activate()
    End Sub

    Private Function FindList() As Excel.ListObject
        ' Do we already have a created list object?
        Dim l As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        For Each s In Application.ActiveWorkbook.Worksheets
            For Each l In s.ListObjects
                If l.Name = "BugListObject" Then
                    Return l
                End If
            Next
        Next
        Return Nothing
    End Function

The callback function for the dynamically created button looks like this.  It creates a new pivot table on a new worksheet that is bound to the list object we created.  It creates two page fields to filter the data by team and column.  Finally, it prompts for the team name and sets the team filter to that team name (and sets the name of the newly created worksheet to the team name).  This is basic Excel code, but it took me a while to figure out the Pivot Table object model.

     Private Sub pivotButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim s As Excel.Worksheet = Nothing
        Dim pivotCache As Excel.PivotCache
        Dim pivotTables As Excel.PivotTables
        Dim pivotTable As Excel.PivotTable
        Dim pivotField As Excel.PivotField

        s = Application.ActiveWorkbook.Worksheets.Add()
        pivotCache = s.Parent.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, FindList().Range, Excel.XlPivotTableVersionList.xlPivotTableVersion12)
        pivotTables = s.PivotTables()
        pivotTable = pivotTables.Add(pivotCache, s.Range("A1"))

        pivotTable.AddDataField(pivotTable.PivotFields(4))
        pivotTable.AddFields("Date")

        pivotField = pivotTable.PivotFields("Column")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        pivotField.CurrentPage = "Active"

        pivotField = pivotTable.PivotFields("Team")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        Dim project As String = InputBox("Enter the team", "Team name", "Project - Office Client")
        pivotField.CurrentPage = project

        s.Name = project
    End Sub

Finally, the CleanUpDynamicButton method finds the dynamically created button in the dictionary and removes it and cleans up the event handler, etc.  This is in a try catch block just in case a button wasn’t created and therefore isn’t found in the dictionary which will be the case later in this demo.

     Private Sub CleanUpDynamicButton()
        Try
            Dim l As Excel.ListObject = FindList()
            If (l Is Nothing) Then
                Exit Sub
            End If
            Dim vstoButton As Microsoft.Office.Tools.Excel.Controls.Button
            vstoButton = buttonDictionary(l.Parent)
            RemoveHandler vstoButton.Click, AddressOf pivotButton_Click
            buttonDictionary.Remove(l.Parent)
        Catch ex As Exception

        End Try
    End Sub

Now set the BugWorkbook as the active project.   So what’s the result when we run this document?  It adds a list object bound to the data set to the active worksheet along with a dynamically created button with the caption Pivot.

When you click on the Pivot button, a new worksheet is created with a pivot table bound to the list object on the Bug Data worksheet.  You are prompted during this process to set the Team filter—here we se tit to “Project – Office Client”

From here, it is a simple matter to add a nice pivot chart as well.  Click on the Options tab in the Pivot Tables tab group, then press the PivotChart button.  I can quickly get a nice bug chart as shown below. Pretty, but I wish the bug count was going down!

If I save my workbook then reopen it, the hook up code will run, detect that there is already a bug list object in the workbook, and it will fetch fresh data from the web service, rebind to the bug list object to update it, and re-add the dynamic “Pivot” button on the Bug Data page.  I can click the Pivot button as often as I want to add new worksheets with new pivots on the data—for example, looking at different teams or the same team and different columns.

Sometimes the internal Microsoft server that returns the bug data can be slow or unavailable.  So rather than fetch fresh data from the web server every time you open the document, I’m going to show you how cached data can let you refresh the data in the document during off times—we’re going to write a console application that can update the data in the document without starting Excel.  It therefore could be run nightly on all my bug workbooks to refresh their data (since I don’t really need bug data more often than nightly anyway).