Slicers are UI tools for filtering a table in the spreadsheet.
Slicers enable the user to see the filter items available in the column and to use Ctrl+Click and Shift+Click to select multiple filter items more easily than using the filter context menu.
Slicers can be placed like a floating object anywhere in the sheet, so you can arrange the slicers to create a dashboard for selecting the filter criteria for the important columns in the table.
The slicer feature improves the Excel-compatibility in Spread.NET WinForms and provides a new tool to create a dashboard interface.
Use Cases for Spreadsheet Slicers:
- Slicers are UI tools for filtering a table.
- Slicers can be arranged and copied/pasted to other worksheets to create dashboard tools for use with charts based on the table data.
- Slicers allow the user to see the filter items and use the mouse to create multiple selections with Ctrl+Click and/or Shift+Click more easily than using the filter dialog.
To use a slicer, you must first create a table:
Enabling Slicers
To enable using slicers, you must use first enable the Enhanced Shape Engine. In Visual Studio design-time, or when using the Spread Designer tool, you can do this using the Property Grid.
First use the drop-down above the Property Grid to select the Spread, then scroll to Behavior - Features, expand it, and change EnhancedShapeEngine to True:
Alternatively, you can enable EnhancedShapeEngine in code:
[C#]
Enable EnhancedShapeEngine
fpSpread1.Features.EnhancedShapeEngine = true;
[VB]
Enable EnhancedShapeEngine
fpSpread1.Features.EnhancedShapeEngine = True
Inserting a Slicer
To insert a new slicer using the Spread Designer tool, you can use the Insert Slicer tool in the Insert tab of the ribbon bar:
When the Insert Slicer tool is clicked, then the Insert Slicer dialog shows:
You can also show the Insert Slicer dialog in code:
[C#]
Show Insert Slicer dialog in code
SlicerInsertForm dlg = new SlicerInsertForm(table, new Point(25, 25));
dlg.ShowDialog(this);
[VB]
Show Insert Slicer dialog in code:
Dim dlg As SlicerInsertForm = new SlicerInsertForm(table, new Point(25, 25))
dlg.ShowDialog(Me)
Using the Insert Slicer dialog, you can select one or more columns in the table, and then click OK, and a new slicer is created for each of the selected columns for filtering the table.
For example, selecting Country in the Insert Slicer dialog will create the slicer for the Country column, which can be moved and sized using the mouse:
You can also move the slicer using the Left/Right/Up/Down arrow keys, and use the same keyboard shortcuts as shapes for sizing – these include Shift+LeftArrow to reduce width, Shift+RightArrow to increase width, Shift+DownArrow to reduce height, and Shift+UpArrow to increase height. You can also use the ribbon bar tools for Height and Width in the Slicer tab.
The slicer is an alternative interface for filtering the table on the specified column, and it will reflect any changes made using the filter/sort drop-down for that column.
When a slicer is selected, the Slicer tools will show in the ribbon bar:
The Buttons tab has tools for adjusting the number of Columns of items in the slicer and the Height and Width of each item.
You can edit the slicer caption directly in the ribbon bar. Click the button under the slicer caption text box to open the Slicer Settings dialog:
The Name of the slicer can be referenced in formulas. Controls at the bottom determine sort order, whether items with no data show, whether to visually indicate items with no data, and whether to show items with no data last.
The Caption is separate from the Name and is displayed in the slicer header part, if Display Header is checked – if not, then the slicer will show without the header:
Next in the Slicer Tools tab is the Slicer Styles drop-down:
Next, in the Arrange group are controls for Bring Forward:
And also in the Arrange group are the controls for Send Backward:
Those tools can be used to adjust the Z-order of the slicers, which controls how the slicers overlap one another.
The tools in the Align menu are useful for creating nice layouts for the slicers:
To use the tools in the Align menu, you must have multiple slicers and/or shapes selected.
Finally, there is the Group menu:
The Group tool can be used to merge one or more selected slicers and/or selected shapes together into a single Group Shape that can be moved and sized together.
Next, in the Buttons group are controls for setting the number of Columns of slicer buttons and the Height and Width of the buttons, and in the Size group are controls for setting the slicer Height and Width.
Using Code
The following code will create the slicer for the "Country" field:
[C#]
Slicer Sample Code:
1
ISlicerCache cache = workbook.SlicerCaches.Add(table, 8, "Country");
2
ISlicer slicer = cache.Slicers.Add(worksheet, "Country", "Country", 100, 220, 250, 200);
[VB]
Slicer Sample Code:
Dim cache As ISlicerCache = workbook.SlicerCaches.Add(table, 8, "Country")
Dim slicer As ISlicer = cache.Slicers.Add(worksheet, "Country", "Country", 100, 220, 250, 200)
Slicer Dialogs
The following code will create and show the built-in Insert Slicer dialog:
[C#]
Show Insert Slicer Dialog
SlicerInsertForm dlg = new SlicerInsertForm(table, new Point(25, 25));
dlg.ShowDialog(this);
[VB]
Show Insert Slicer Dialog:
Dim dlg As New SlicerInsertForm(table, New Point(25, 25))
dlg.ShowDialog(Me)
The end-user can select any number of columns in the table to create slicers for filtering the table.
[C#]
Show Slicer Settings Dialog:
// show the slicer settings form for the specified slicers
SlicerSettingsForm form = new SlicerSettingsForm(slicers, workbook);
form.ShowDialog();
[VB]
Show Slicer Settings Dialog:
Dim form As New SlicerSettingsForm(selectedSlicers.ToArray(), workbook)
form.ShowDialog()
The end-user can select any number of columns in the table to create slicers for filtering the table.
The following code will create and show the built-in Slicer Settings dialog:
Applying Built-in Slicer Styles
The following code applies a built-in slicer style to the selected slicers:
[C#]
Apply Built-in Slicer Style
// this code uses a menu initialized with the built-in slicer styles in the order of BuiltInSlicerStyles
IWorkbook workbook = fpSpread1.AsWorkbook();
ITableStyle style = workbook.TableStyles[(int)typeof(BuiltInSlicerStyles).GetEnumValues().GetValue(styleToolStripMenuItem.DropDownItems.IndexOf((ToolStripItem)sender))];
ISlicers slicers = workbook.ActiveSheet.Slicers;
int count = slicers.Count;
for (int i = 0; i < count; i++)
{
if (slicers[i].Shape.Selected)
{
slicers[i].Style = style;
}
}
[VB]
Apply Built-in Slicer Style
' this code uses a menu initialized with the built-in slicer styles in the order of BuiltInSlicerStyles
Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
Dim style As ITableStyle = workbook.TableStyles(CInt(GetType(BuiltInSlicerStyles).GetEnumValues().GetValue(styleToolStripMenuItem.DropDownItems.IndexOf(DirectCast(sender, ToolStripItem)))))
Dim slicers As ISlicers = workbook.ActiveSheet.Slicers
Dim count As Integer = slicers.Count
For i As Integer = 0 To count - 1
If slicers(i).Shape.Selected Then
slicers(i).Style = style
End If
Next
The complete sample code is available in the Spread.NET 13 WinForms Control Explorer, which is included (for both C# and VB) in the setup installer.
If you have any questions, please leave them in the comment thread below.
Sean Lawyer