TimeLine in Excel Addin

TimeLine in Excel Addin


It refers to slicer cache type which identified by XlSlicerCacheType enum having constant xlTimeLine. In this article we will see how to create time line in Excel using VSTO C#. Note : To create Time Line in Excel it requires Data Table and a Data Source. Also Time Line is based on time series hence, your data should have one Period column in this article I have “PeriodStart“. Let’s see how we can create it a time line manually then we will put code to achieve the same.

Step 1: Open Excel and put your data a form a Data table out of it as shown below:

The above screenshot depicts that I have created a table out of my data and its name is “Table1“.

Step 2: On Insert Ribbon tab click on Timeline and it will popup a dialog with two tabs as:

  • Connections: If your data is associated from any other database the connection can be configured here
  • Data Model: It refers to local workbook data model as shown below

Take a note in above screenshot the “Table1” populated as data model source.

Step 3: Click Open button and a small popup dialog will appear having all the date or period columns available within your data source:

Since my data source has only one period column hence, it shows only one column in the list as “PeriodStart

Step 4: Check the “PeriodStart” in above screenshot and click OK, that’s it the next you see the output of your job that you just finished!!!

Let’s put code to build same:

private void btnTimeLine_Click(object sender, RibbonControlEventArgs e)
	//Active workbook reference
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;

	//Declare worksheet
	excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;

	//Create slicer cache
	excel.SlicerCaches oSlicerCaches = oWorkbook.SlicerCaches;

	//Adding slicer cache
	excel.SlicerCache oSlicerCacheAddress = oSlicerCaches.Add2("ThisWorkbookDataModel", "[Table1].[PeriodStart]",
		System.Reflection.Missing.Value, SlicerCacheType: excel.XlSlicerCacheType.xlTimeline);

	//adding slicer
	oSlicerCacheAddress.Slicers.Add(oWorksheet, System.Reflection.Missing.Value, "PeriodStart", "PeriodStart", 156,
		394, 144, 198);

Next : Add hyperlinks in cell range Excel C# example

Leave a Reply

Your email address will not be published. Required fields are marked *