Slicers in Excel Addin

Slicers in Excel Addin

Slicers:

An advanced level of filter which provides better visualization. I would not write much here, If you wish to see how to create Slicers in Excel please refer my previous article Slicers in Excel VBA. Let’s take a look in VSTO C# how you can create slicers. There are couple of things which needs to be taken under consideration before creating slicers in Excel C#.

  • Slicers can be formed over a Data Table or Pivot Table
  • Slicers requires Cache like Pivot
  • Table name should be remember (Please refer below screen shot) which can be obtained from Design Tab under table properties

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example to create Slicers:

private void btnSlicer_Click(object sender, RibbonControlEventArgs e)
{
	//Active workbook reference
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
	
	//Active worksheet reference
	excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;
							
	//Retain selection
	excel.Range oSelection = Globals.ThisAddIn.Application.Selection;
	
	//build slicer caches collection
	excel.SlicerCaches oSlicerCaches = oWorkbook.SlicerCaches;

	
	//Creating Address
	excel.SlicerCache oSlicerCacheAddress = oSlicerCaches.Add2(oWorksheet.ListObjects["Table1"], "Address", "mSlicer");
	//Add slicer
	oSlicerCacheAddress.Slicers.Add(oWorksheet, System.Reflection.Missing.Value, "Address", "Address", 156,
		394, 144, 198);
	

	//Creating Address
	excel.SlicerCache oSlicerCacheYearBuildIn = oSlicerCaches.Add2(oWorksheet.ListObjects["Table1"], "YearBuilt", "mSlicer1");
	//Add slicer
	oSlicerCacheYearBuildIn.Slicers.Add(oWorksheet, System.Reflection.Missing.Value, "YearBuilt", "YearBuilt", 200,
		450, 144, 198);
	
}

Code example to delete slicers:

private void btnSlicer_Click(object sender, RibbonControlEventArgs e)
{
	//Active workbook reference
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
	
	//Active worksheet reference
	excel.Worksheet oWorksheet = oWorkbook.ActiveSheet;
							
	//Retain selection
	excel.Range oSelection = Globals.ThisAddIn.Application.Selection;
	
	//build slicer caches collection
	excel.SlicerCaches oSlicerCaches = oWorkbook.SlicerCaches;

	//Delete exisiting slicers
	foreach (excel.SlicerCache osCache in oSlicerCaches)
	{
		foreach (excel.Slicer oCache in osCache.Slicers)
		{
			oCache.Delete();
		}
	}
}

Output:

Next : Add Timeline in Excel C#

Leave a Reply

Your email address will not be published.