Working with Excel Slicer

24 Nov 202517 minutes to read

Slicers are UI filters that help to display only specific data based on the selection criteria. They provide easy and simple UI options to enable or disable the filters in a table.

Syncfusion^® XlsIO supports below features.

• Create slicers and filter table data.
• Format slicers with built-in styles.

Create Slicer

The following code snippet explains how to create a table slicer.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access the table.
  IListObject table = sheet.ListObjects[0];

  //Add slicer for the table.
  sheet.Slicers.Add(table, 3, 11, 2);

  workbook.SaveAs("Output.xlsx");
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access the table.
  IListObject table = sheet.ListObjects[0];

  //Add slicer for the table.
  sheet.Slicers.Add(table, 3, 11, 2);

  workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Access the first table.
  Dim table As IListObject = sheet.ListObjects(0)

  'Add slicer for the table.
  sheet.Slicers.Add(table, 3, 11, 2)

  workbook.SaveAs("Output.xlsx")
End Using

Slicer Properties

Slicer name

The existing name of a slicer can be obtained or changed through Name property.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.Name = "Slicer1";

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.Name = "Slicer1";

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.Name = "Slicer1"

Slicer caption

Slicer caption can be modified through Caption property.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.Caption = "Select any value";

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.Caption = "Select any value";

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.Caption = "Select any value"

Positioning a Slicer

Slicer can be positioned in the worksheet as required, as below.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.Top = 100;
slicer.Left = 300;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.Top = 100;
slicer.Left = 300;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.Top = 100
slicer.Left = 300

Resize a Slicer

A slicer can be resized as shown below.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.Height = 200;
slicer.Width = 150;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.Height = 200;
slicer.Width = 150;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.Height = 200
slicer.Width = 150

Resize Slicer item

Slicer item can also the resized, as shown below.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.SlicerItemHeight = 0.4;
slicer.SlicerItemWidth = 80;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.SlicerItemHeight = 0.4;
slicer.SlicerItemWidth = 80;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.SlicerItemHeight = 0.4
slicer.SlicerItemWidth = 80

Slicer columns

Select the number of columns inside a slicer as below.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.NumberOfColumns = 2;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.NumberOfColumns = 2;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.NumberOfColumns = 2

Slicer header

A slicer header can be shown or hidden through DisplayHeader property.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.DisplayHeader = true;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.DisplayHeader = true;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.DisplayHeader = True

Slicer style

Slicer style can be selected as shown below.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2;

VB.NET [Windows-specific]

ISlicer slicer = sheet.Slicers(0)
slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2

Select a Slicer Item

The following example shows how to select items in a slicer.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.SlicerCacheItems[0].IsSelected = true;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.SlicerCacheItems[0].IsSelected = true;

VB.NET [Windows-specific]

Dim slicer As ISlicer = sheet.Slicers(0)
Dim cache As ISlicerCache = slicer.SlicerCache
cache.SlicerCacheItems(0).IsSelected = True

Select slicer filter type

The following example shows how to select the slicer filter type.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop;

VB.NET [Windows-specific]

Dim slicer As ISlicer = sheet.Slicers(0)
Dim cache As ISlicerCache = slicer.SlicerCache
cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop

Sort the slicer items

The following example shows how to sort the slicer items.

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.IsAscending = true;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.IsAscending = true;

VB.NET [Windows-specific]

Dim slicer As ISlicer = sheet.Slicers(0)
Dim cache As ISlicerCache = slicer.SlicerCache
cache.IsAscending = True

Sort the slicer items using Custom list sorting option

The following example shows how to sort the slicer items using Custom list sorting option

C# [Cross-platform]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.UseCustomListSorting = true;

C# [Windows-specific]

ISlicer slicer = sheet.Slicers[0];
ISlicerCache cache = slicer.SlicerCache;
cache.UseCustomListSorting = true;

VB.NET [Windows-specific]

Dim slicer As ISlicer = sheet.Slicers(0)
Dim cache As ISlicerCache = slicer.SlicerCache
cache.UseCustomListSorting = True

The following code snippet illustrates how to format an existing slicer with all the above discussed properties.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access the table
  IListObject table = sheet.ListObjects[0];

  //Add slicer for the table
  sheet.Slicers.Add(table, 3, 11, 2);

  //Access the slicer
  ISlicer slicer = sheet.Slicers[0];

  //Slicer name
  slicer.Name = "Slicer1";

  //Slicer caption
  slicer.Caption = "Select any value";

  //Positioning a Slicer
  slicer.Top = 100;
  slicer.Left = 300;

  //Resize a Slicer
  slicer.Height = 200;
  slicer.Width = 150;

  //Resize Slicer item
  slicer.SlicerItemHeight = 0.4;
  slicer.SlicerItemWidth = 80;

  //Slicer columns
  slicer.NumberOfColumns = 2;

  //Slicer header
  slicer.DisplayHeader = true;

  //Slicer style
  slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2;
  
  //Select the slicer item
  ISlicerCache cache = slicer.SlicerCache;
  cache.SlicerCacheItems[0].IsSelected = true;

  //Set the slicer filter type
  cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop;

  //Sort the slicer items in ascending order
  cache.IsAscending = true;

  //Custom list sorting
  cache.UseCustomListSorting = true;

  workbook.SaveAs("Output.xlsx");
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access the table
  IListObject table = sheet.ListObjects[0];

  //Add slicer for the table
  sheet.Slicers.Add(table, 3, 11, 2);

  //Access the slicer
  ISlicer slicer = sheet.Slicers[0];

  //Slicer name
  slicer.Name = "Slicer1";

  //Slicer caption
  slicer.Caption = "Select any value";

  //Positioning a Slicer
  slicer.Top = 100;
  slicer.Left = 300;

  //Resize a Slicer
  slicer.Height = 200;
  slicer.Width = 150;

  //Resize Slicer item
  slicer.SlicerItemHeight = 0.4;
  slicer.SlicerItemWidth = 80;

  //Slicer columns
  slicer.NumberOfColumns = 2;

  //Slicer header
  slicer.DisplayHeader = true;

  //Slicer style
  slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2;
  
  //Select the slicer item
  ISlicerCache cache = slicer.SlicerCache;
  cache.SlicerCacheItems[0].IsSelected = true;

  //Set the slicer filter type
  cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop;

  //Sort the slicer items in ascending order
  cache.IsAscending = true;

  //Custom list sorting
  cache.UseCustomListSorting = true;

  workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Access the first table.
  Dim table As IListObject = sheet.ListObjects(0)

  'Add slicer for the table
  sheet.Slicers.Add(table, 3, 11, 2)

  'Access the slicer
  Dim slicer As ISlicer = sheet.Slicers(0)

  'Slicer name
  slicer.Name = "Slicer1"

  'Slicer caption
  slicer.Caption = "Select any value"

  'Positioning a Slicer
  slicer.Top = 100
  slicer.Left = 300

  'Resize a Slicer
  slicer.Height = 200
  slicer.Width = 150

  'Resize Slicer item
  slicer.SlicerItemHeight = 0.4
  slicer.SlicerItemWidth = 80

  'Slicer columns
  slicer.NumberOfColumns = 2

  'Slicer header
  slicer.DisplayHeader = True

  'Slicer style
  slicer.SlicerStyle = ExcelSlicerStyle.SlicerStyleDark2
  
  'Select the slicer item
  Dim cache As ISlicerCache = slicer.SlicerCache
  cache.SlicerCacheItems(0).IsSelected = True

  'Set the slicer filter type
  cache.CrossFilterType = SlicerCrossFilterType.ShowItemsWithDataAtTop

  'Sort the slicer items in ascending order
  cache.IsAscending = True

  'Custom list sorting
  cache.UseCustomListSorting = True

  workbook.SaveAs("Output.xlsx")
End Using