Working With Slicers

In This Topic

Spread for WinForms provides support for using slicers (visual filters) in the worksheet. Slicers enable users to perform quick filtration of the data in tables without using drop-down lists.

Users can have multiple "copies" of a slicer operating together on different worksheets. You can select an item or drag across a number of items by using the keyboard shortcuts "Ctrl + Click" or "Shift + Click" to include the filter items and the data for that item will be displayed in the report as per the specific requirements.

Add a Slicer

To add a slicer in the worksheet, users first need to set the value of the EnhancedShapeEngine property to "true" in order to enable the new shape engine. After enabling the EnhancedShapeEngine, users can either use the Add() method of the ISlicerCaches interface or simply click anywhere on the table and select the "Insert" button on the ribbon bar to add a slicer to the worksheet.

A slicer contains a header, a caption, slicer items and a clear button. The following image shows a slicer added to the worksheet.

Slicers example

The following example code shows how to add a slicer in the spreadsheet.

C#	Copy Code
// Enable Shape Engine using EnhancedShapeEngine property to use the slicer feature fpSpread1.Features.EnhancedShapeEngine = true; // Add the Slicer private static void AddSlicer(IWorkbook WorkBook, IWorksheet WorkSheet) { // Initialize Data in worksheet WorkSheet.Cells[0, 0].Text = "Name"; WorkSheet.Cells[0, 1].Text = "City"; WorkSheet.Cells[0, 2].Text = "Weight"; WorkSheet.Cells[1, 0].Text = "Bob"; WorkSheet.Cells[1, 1].Text = "NewYork"; WorkSheet.Cells[1, 2].Value = 80; WorkSheet.Cells[2, 0].Text = "Betty"; WorkSheet.Cells[2, 1].Text = "Chicago"; WorkSheet.Cells[2, 2].Value = 72; WorkSheet.Cells[3, 0].Text = "Alice"; WorkSheet.Cells[3, 1].Text = "Washington"; WorkSheet.Cells[3, 2].Value = 71; // Create Table ITable table = WorkSheet.Tables.Add("A1:C4", YesNoGuess.Yes); // Add SlicerCache using the Column index ISlicerCache slicerCache = WorkBook.SlicerCaches.Add(table, 0, "slicerCache"); // Add Slicer to SlicerCache ISlicer slicer = slicerCache.Slicers.Add(WorkSheet, "slicer", "Name", 200, 20, 200, 200); }

Copy Code

// Enable Shape Engine using EnhancedShapeEngine property to use the slicer feature
fpSpread1.Features.EnhancedShapeEngine = true;
// Add the Slicer
  private static void AddSlicer(IWorkbook WorkBook, IWorksheet WorkSheet)
{ 
// Initialize Data in worksheet
WorkSheet.Cells[0, 0].Text = "Name";
WorkSheet.Cells[0, 1].Text = "City";
WorkSheet.Cells[0, 2].Text = "Weight";
WorkSheet.Cells[1, 0].Text = "Bob";
WorkSheet.Cells[1, 1].Text = "NewYork";
WorkSheet.Cells[1, 2].Value = 80;
WorkSheet.Cells[2, 0].Text = "Betty";
WorkSheet.Cells[2, 1].Text = "Chicago";
WorkSheet.Cells[2, 2].Value = 72;
WorkSheet.Cells[3, 0].Text = "Alice";
WorkSheet.Cells[3, 1].Text = "Washington";
WorkSheet.Cells[3, 2].Value = 71;
// Create Table
ITable table = WorkSheet.Tables.Add("A1:C4", YesNoGuess.Yes);

// Add SlicerCache using the Column index
ISlicerCache slicerCache = WorkBook.SlicerCaches.Add(table, 0, "slicerCache");

// Add Slicer to SlicerCache
ISlicer slicer = slicerCache.Slicers.Add(WorkSheet, "slicer", "Name", 200, 20, 200, 200);
}

Add Multiple Slicers

Users can also add multiple slicers to the worksheet by using the Add() method of the ISlicerCaches interface. The following image shows a spreadsheet with multiple slicers.

Multiple Slicers Example

The following example code shows how to add multiple slicers in a spreadsheet.

C#	Copy Code
// Enable Shape Engine using EnhancedShapeEngine property to use the slicer feature fpSpread1.Features.EnhancedShapeEngine = true; private static void AddMultipleSlicer(IWorkbook WorkBook, IWorksheet WorkSheet) { // Initialize Data in worksheet WorkSheet.Cells[0, 0].Text = "Name"; WorkSheet.Cells[0, 1].Text = "City"; WorkSheet.Cells[0, 2].Text = "Weight"; WorkSheet.Cells[1, 0].Text = "Bob"; WorkSheet.Cells[1, 1].Text = "NewYork"; WorkSheet.Cells[1, 2].Value = 80; WorkSheet.Cells[2, 0].Text = "Betty"; WorkSheet.Cells[2, 1].Text = "Chicago"; WorkSheet.Cells[2, 2].Value = 72; WorkSheet.Cells[3, 0].Text = "Alice"; WorkSheet.Cells[3, 1].Text = "Washington"; WorkSheet.Cells[3, 2].Value = 71; // Create Table ITable table = WorkSheet.Tables.Add("A1:C4", YesNoGuess.Yes); // Add SlicerCache using the Column index ISlicerCache slicerCache = WorkBook.SlicerCaches.Add(table, 0, "slicerCache"); // Add Slicer to SlicerCache ISlicer slicer = slicerCache.Slicers.Add(WorkSheet, "slicer", "Name", 200, 20, 200, 200); // Add another SlicerCache using the Column Name ISlicerCache slicerCache2 = WorkBook.SlicerCaches.Add(table, "City", "slicerCache2"); // Add another Slicer to another SlicerCache ISlicer slicer2 = slicerCache2.Slicers.Add(WorkSheet, "slicer2", "City", 410, 20, 200, 200); }

Copy Code

// Enable Shape Engine using EnhancedShapeEngine property to use the slicer feature
fpSpread1.Features.EnhancedShapeEngine = true;
private static void AddMultipleSlicer(IWorkbook WorkBook, IWorksheet WorkSheet)
{
// Initialize Data in worksheet
  WorkSheet.Cells[0, 0].Text = "Name";
  WorkSheet.Cells[0, 1].Text = "City";
  WorkSheet.Cells[0, 2].Text = "Weight";
  WorkSheet.Cells[1, 0].Text = "Bob";
  WorkSheet.Cells[1, 1].Text = "NewYork";
  WorkSheet.Cells[1, 2].Value = 80;
  WorkSheet.Cells[2, 0].Text = "Betty";
  WorkSheet.Cells[2, 1].Text = "Chicago";
  WorkSheet.Cells[2, 2].Value = 72;
  WorkSheet.Cells[3, 0].Text = "Alice";
  WorkSheet.Cells[3, 1].Text = "Washington";
  WorkSheet.Cells[3, 2].Value = 71;
// Create Table
  ITable table = WorkSheet.Tables.Add("A1:C4", YesNoGuess.Yes);
  
// Add SlicerCache using the Column index
  ISlicerCache slicerCache = WorkBook.SlicerCaches.Add(table, 0, "slicerCache");
  
// Add Slicer to SlicerCache
  ISlicer slicer = slicerCache.Slicers.Add(WorkSheet, "slicer", "Name", 200, 20, 200, 200);
  
// Add another SlicerCache using the Column Name 
 ISlicerCache slicerCache2 = WorkBook.SlicerCaches.Add(table, "City", "slicerCache2");

// Add another Slicer to another SlicerCache
 ISlicer slicer2 = slicerCache2.Slicers.Add(WorkSheet, "slicer2", "City", 410, 20, 200, 200);
}

Select Slicer Items

Users can select and deselect slicer items by setting the Selected property of the ISlicerItem interface to "true" or "false" respectively.

The following example code shows how to select slicer items in the spreadsheet.

C#	Copy Code
private static void Filter(IWorkbook WorkBook) { WorkBook.SlicerCaches[0].SlicerItems["Bob"].Selected = false; WorkBook.SlicerCaches[0].SlicerItems["Betty"].Selected = true; WorkBook.SlicerCaches[0].SlicerItems["Alice"].Selected = false; }

Copy Code

private static void Filter(IWorkbook WorkBook)
{
  WorkBook.SlicerCaches[0].SlicerItems["Bob"].Selected = false;
  WorkBook.SlicerCaches[0].SlicerItems["Betty"].Selected = true;
  WorkBook.SlicerCaches[0].SlicerItems["Alice"].Selected = false;
}

Alternatively, you can use the keyboard shortcut "Ctrl + Click" or "Alt+S" to enable the multi selection mode. Then, you can click on the specific sites to filter out the data of selected items. To select multiple slicer items, you simply need to click the first item on the slicer, drag the mouse pointer towards the last item and then all the items between the first and last item will be selected.

Clear Slicer Filters

Users can also remove the slicer filters when they want to show all the data by using the ClearAllFilters() method, the ClearManualFilter() method and the ClearDateFilter() method of the ISlicerCache interface. Alternatively, you can also click the "Clear Filter" button at the top right of the slicer or use the keyboard shortcut "Alt + C" to clear slicer filters from the spreadsheet.

The following example code shows how to clear slicer filters from the spreadsheet.

C#	Copy Code
private static void ClearFilter(IWorkbook WorkBook) { // Clear all filters from the Slicer WorkBook.SlicerCaches[0].ClearAllFilters(); }

Modify Slicer Settings

Users can also modify slicer settings as per their specific preferences by using the Caption property of the ISlicer interface and the SortItems property of the ISlicerCache interface.

The following image shows the slicer with changed slicer caption and modified sorting order (names are showing up in the descending order).

Slicer wtih modified slicer caption and sorting order

The following example code shows how to modify slicer settings in the spreadsheet.

C#	Copy Code
private static void ModifySlicerSetting(IWorkbook WorkBook, IWorksheet WorkSheet) { // Change the Caption Name WorkSheet.Slicers["slicer"].Caption = "Name Changed"; // Change the Sorting Order WorkBook.SlicerCaches["slicerCache"].SortItems = SlicerSort.Descending; }

Copy Code

private static void ModifySlicerSetting(IWorkbook WorkBook, IWorksheet WorkSheet)
 {
  // Change the Caption Name
  WorkSheet.Slicers["slicer"].Caption = "Name Changed";
  
  // Change the Sorting Order
  WorkBook.SlicerCaches["slicerCache"].SortItems = SlicerSort.Descending;
}

Change Slicer Formatting

Users can also modify the formatting of the slicer by using the NumberOfColumns property (to set the number of columns), the Width property (to set the width of the slicer), the Height property (to set the height of the slicer), the ColumnWidth property (to set the width of the columns), the RowHeight property (to set the height of the rows) and the DisplayHeader property (to configure the visibility of the header) of the ISlicer interface.

The following image shows a slicer with custom formatting.

Slicer with custom formatting

The following example code shows how to change slicer formatting in the spreadsheet.

C#	Copy Code
private static void ChangeSlicerFormatting(IWorksheet WorkSheet) { // Change Slicer Formatting WorkSheet.Slicers["slicer"].NumberOfColumns = 2; WorkSheet.Slicers["slicer"].Width = 250; WorkSheet.Slicers["slicer"].Height = 150; WorkSheet.Slicers["slicer"].ColumnWidth = 100; WorkSheet.Slicers["slicer"].RowHeight = 70; WorkSheet.Slicers["slicer"].DisplayHeader = false; }

Copy Code

private static void ChangeSlicerFormatting(IWorksheet WorkSheet)
{
// Change Slicer Formatting
WorkSheet.Slicers["slicer"].NumberOfColumns = 2;
WorkSheet.Slicers["slicer"].Width = 250;
WorkSheet.Slicers["slicer"].Height = 150;
WorkSheet.Slicers["slicer"].ColumnWidth = 100;
WorkSheet.Slicers["slicer"].RowHeight = 70;
WorkSheet.Slicers["slicer"].DisplayHeader = false;
}

Customize Slicer Style

Users can customize the slicer style as per their specific preferences by defining a custom style, setting the ShowAsAvailableSlicerStyle property of the ITableStyle interface to true and applying the custom style to the slicer.

The following image shows a slicer with custom font style, font size and font color configured in the spreadsheet.

Slicer with customized style

The following example code shows how to customize slicer styles in the spreadsheet.

C#	Copy Code
private static void CustomizeSlicerStyle(IWorkbook WorkBook, IWorksheet WorkSheet) { // Set Custom Style to Slicer ITableStyle slicerStyle = WorkBook.TableStyles.Add("CustomStyle1"); // Enable ShowAsAvailableSlicerStyle to true to set custom style to Slicer slicerStyle.ShowAsAvailableSlicerStyle = true; slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Name = "Arial"; slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Bold = true; slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Italic = true; slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Size = 20; slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Color = GrapeCity.Spreadsheet.Color.FromThemeColor(GrapeCity.Core.ThemeColors.Accent3); WorkSheet.Slicers["slicer"].Style = slicerStyle; }

Copy Code

private static void CustomizeSlicerStyle(IWorkbook WorkBook, IWorksheet WorkSheet)
{ 
// Set Custom Style to Slicer
ITableStyle slicerStyle = WorkBook.TableStyles.Add("CustomStyle1");

// Enable ShowAsAvailableSlicerStyle to true to set custom style to Slicer
slicerStyle.ShowAsAvailableSlicerStyle = true;
slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Name = "Arial";
slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Bold = true;
slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Italic = true;
slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Size = 20;
slicerStyle.TableStyleElements[TableStyleElementType.SelectedItemWithData].Font.Color = GrapeCity.Spreadsheet.Color.FromThemeColor(GrapeCity.Core.ThemeColors.Accent3);
WorkSheet.Slicers["slicer"].Style = slicerStyle;
}

Delete Slicer

Users can delete the slicer from the spreadsheet by using the Delete() method of the ISlicer interface.

If you want to delete the slicer cache and all its slicers, you can use the Delete() method of the ISlicerCache interface.

The following example code shows how to delete a slicer from the spreadsheet.

C#	Copy Code
private static void DeleteSlicer(IWorkbook WorkBook) { // Delete Slicer WorkBook.SlicerCaches["slicerCache"].Slicers["slicer"].Delete(); }