Working with Excel Slicer

24 Jul 202617 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 the following 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 the 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

The slicer caption can be modified through the 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

A slicer can be positioned in the worksheet as shown 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 items can also be 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

Set the number of columns inside the slicer as shown 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 the 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

The slicer style can be set 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 the 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 previously 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