TimeLine in Excel Addin

TimeLine in Excel Addin

TimeLine:

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.