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
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.
My thinking is it would cool to build an Excel add-in that can…
- Recognize anytime the active cell resides within a pivot table…
- Let the user click a custom Excel ribbon button to insert a timeline control…
- Provided the active column contains a date data type…
- 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:
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.
This sample Excel 2013 add-in was developed using Add-in Express for Office and .net: