Excel 2013 Timeline: creating data visualizations with VB.NET, C#

Now that Microsoft Office 2013 is available, it’s time to start digging through the “What’s New for Office Developers” topics. I’m not sure where these topics will take us because, with each release of Office, Microsoft touts the various new features and objects available to developers.

Each release includes some hits.
Each release includes some misses.

Over the next few weeks, I’ll do the hard work that must be done. The hard work of deciding what’s a hit and what’s a miss for us… the developers of Office-based solutions.

• Excel 2013 Timeline control from a user’s perspective
• Developing with the Excel Timeline control
• Excel 2013 Timeline feature: overall verdict

Excel 2013 Timeline control from a user’s perspective

The new timeline control is a new addition to Excel 2013 slicer control family. Slicers are visual controls you place on an Excel worksheet that allow you to filter a PivotTable. So, yes, it needs to be a worksheet that contains PivotTable.

Slicers allow users add some solid interactivity to Excel spreadsheets. Combined with charts and good formatting, they can be solid dashboard reports for the head honcho CEO in the corner office. But something was missing.

That something was the ability to filter using a timeline. This is a problem no longer as you can see below.

Creating a PivotTable report in Excel 2013:

Easy and powerful, right? It’s bound to be useful in an Office solution… so let’s take a look. Oh, the steps I just showed you will be useful later. I recommend building this spreadsheet now. If you need to watch it again, I’ll wait. You can use the file I provided with the source code. That’s probably what I’d do.

Developing with the Excel Timeline control

My thinking is it would cool to build an Excel add-in that can…

1. Recognize anytime the active cell resides within a pivot table…
2. Let the user click a custom Excel ribbon button to insert a timeline control…
3. Provided the active column contains a date data type…
4. If it does, we’ll read the data field and make the magic happen.

I’m not making any promises but this is my premise. Soooooo, open up Visual Studio 2012 and create a new ADX COM Add-in Project with Add-in Express for Office and .net! As you move through the New Microsoft Office COM Add-in Wizard, be sure to select Microsoft Office 2013 as the Minimum supported Office version (see below).

Make sure you select Microsoft Excel as a supported application and name the project TimelineVisualizations.

When ready, add an ADXRibbonControl, an ADXRibbonGroup, and an ADXRibbonButton to the AddinModule. The look and feel doesn’t really matter here. If you make it look like the image below, you are doing just fine.

What does matter is the code

All we need is a single method called AddTimeline. This method grabs the active cell’s underlying data field and uses it as the basis for the timeline.

Private Sub AddTimeline()
  Try
    With Me.ExcelApp
      Dim sheet As Excel.Worksheet = .ActiveSheet
      Dim slice As Excel.Slicer
      Dim fieldSelected As Excel.PivotField = .ActiveCell.PivotTable.RowFields(1)
      Dim pivotName As String = .ActiveCell.PivotTable.Name
      Dim fieldName As String = fieldSelected.SourceName
 
      slice = .ActiveWorkbook.SlicerCaches.Add2( _
        sheet.PivotTables(pivotName), fieldName, , Excel.XlSlicerCacheType.xlTimeline). _
        Slicers.Add(sheet, , fieldName, fieldName, 200, 315, 250, 100)
 
      slice.TimelineViewState.Level = Excel.XlTimelineLevel.xlTimelineLevelMonths
 
    End With
  Catch ex As Exception
     MsgBox(ex.Message)
  End Try
End Sub

The two keylines utilize the xlTimeLine and xlTimelineLevel objects. The first inserts a a new slicer control of type xlTimeline. The second line specifes the timeline to utilize a monthly interval.

Excel 2013 Timeline feature in action:

Excel 2013 Timeline feature: overall verdict

This is a new feature that will be more useful to Excel users than to developers. Sure, we can insert a timeline and manipulate the few properties available to us. But, it isn’t as sexy as it could be. Not yet.

Available downloads:

This sample Excel 2013 add-in was developed using Add-in Express for Office and .net:

TimeLine add-in for Excel 2013 (VB.NET)

You may also be interested in:

• Creating COM add-ins for Excel 2013, 2010 and 2007. Customizing Excel Ribbon
• Excel 2013 development – 7 questions developers should ask