Table Slicer

A Table Slicer allows you to quickly filter data. It provides filtering details without the need to use drop-down lists, and visually indicates the currently filtered state.

A Table Slicer is derived from the ShapeBase, and supports the following features:

• Gradient Fill: The items in the slicer show a gradient effect upon hovering.

• Multi-Select Mode: You can select more than one filter item at a time by selecting the Multiselect button on the top right of the slicer. You can deselect the Multiselect button to select only one filter item at a time.

• Improved Performance: Being a ShapeForm control, Table Slicer can be used to filter large chunks of data in the worksheet.

• Optimized style rendering: For more information on Table Slicer Styling, see Table Slicer Style.

Note: To work with table slicers in a SpreadJS worksheet, you need to reference the following plugins:

gc.spread.sheets.all.js

gc.spread.sheets.shapes.js

gc.spread.sheets.slicers.js

The Slicer Plugin relies on the Shape Plugin, so the Shape Plugin must be imported first.

The Table Slicer component comprises the slicer header and slicer body. The Slicer header is composed of a slicer Caption, Multiselect, and Clear filter button. The slicer caption is used to show a caption, and the Clear filter button is used to un-filter the slicer and it is inactive by default. It becomes active only when a slicer item is filtered.

The Table Slicer relies on the table, so to create a slicer, you must first add a table. You use the add method to add a slicer. You can set options for the slicer with the Slicers class.

The following code sample helps you to create a table and a slicer:

//create a table
datas = [
    ["1", "NewYork", "1968/6/8", "80", "180"],
    ["4", "NewYork", "1972/7/3", "72", "168"],
    ["4", "NewYork", "1964/3/2", "71", "179"],
    ["5", "Washington", "1972/8/8","80", "171"],
    ["6", "Washington", "1986/2/2", "89", "161"],
    ["7", "Washington", "2012/2/15", "71", "240"]
];
var table = activeSheet.tables.addFromDataSource("table1", 2, 2, datas);
dataColumns = ["Name", "City", "Birthday", "Weight", "Height"];
table.setColumnName(0, dataColumns[0]);
table.setColumnName(1, dataColumns[1]);
table.setColumnName(2, dataColumns[2]);
table.setColumnName(3, dataColumns[3]);
table.setColumnName(4, dataColumns[4]);

//add a slicer to the sheet and return the slicer instance.
var slicer = activeSheet.slicers.add("slicer1",table.name(),"Name");
//change the slicer properties.
slicer.width(200);
slicer.height(200);
slicer.position(new GC.Spread.Sheets.Point(100, 200));
slicer.style(GC.Spread.Sheets.Slicers.SlicerStyles.dark4());

You can move, resize, delete, cut, copy, paste, undo, or redo the slicer.

Items that are filtered out by another slicer are referred to as "has data items" and "no data items". Items that are filtered by the slicer are referred to as "selected items" and "unselected items".

The slicer synchronizes with the table filter. The following table changes cause changes in the slicer.

You can set options for the slicer using the Slicers class. You can specify whether the slicer is visible or locked using isVisible or isLocked methods respectively. The isLocked method has an effect only if the sheet is protected. You can add a slicer with the slicers.add method and remove a slicer with the slicers.remove method.

Resizing or moving a row or column can cause the slicer's location and size to change based on the settings for the dynamicMove and dynamicSize methods. The following table displays the result of the dynamicMove and dynamicSize settings.

Using Table Slicer Data

The TableSlicerData class provides table data and filtering information for the slicer. The data source used for the TableSlicerData is the SpreadJS SheetTable. You must set a SheetTable to TableSlicerData's constructor to use the Table Slicer Data.

The following code sample creates a table and adds a slicer using the TableSlicerData class.

//create table
var dataSource = [
    { Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
    { Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
    { Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = table.getSlicerData();
//create an item slicer
var slicer = new GC.Spread.Sheets.Slicers.ItemSlicer("slicer", slicerData, "Name");
//Add the item slicer to the dom tree.
//The "slicerHost" is the div you want to add the slicer's dom to.
$("#slicerHost").append(slicer.getDOMElement());
.....
<div id="slicerHost" style="height: 300px; width: 50%"></div>       

Note: To upgrade to the new slicers, see Breaking Changes.

Table Slicer Styles

You can use a built-in style or set a style for specific areas such as the header.

Refer to the SlicerStyles class for a list of built-in styles. Refer to the SlicerStyle class for a list of specific areas and the SlicerStyleInfo class for the styles that can be changed. Use the SlicerBorder class to create a border for a style.

The following code sample creates a table and adds a slicer with a header style.

//create a table
datas = [
    ["1", "NewYork", "1968/6/8", "80", "180"],
    ["4", "NewYork", "1972/7/3", "72", "168"],
    ["4", "NewYork", "1964/3/2", "71", "179"],
    ["5", "Washington", "1972/8/8","80", "171"],
    ["6", "Washington", "1986/2/2", "89", "161"],
    ["7", "Washington", "2012/2/15", "71", "240"]];
var table = activeSheet.tables.addFromDataSource("table1", 2, 2, datas);
dataColumns = ["Name", "City", "Birthday", "Weight", "Height"];
table.setColumnName(0, dataColumns[0]);
table.setColumnName(1, dataColumns[1]);
table.setColumnName(2, dataColumns[2]);
table.setColumnName(3, dataColumns[3]);
table.setColumnName(4, dataColumns[4]);
var hstyle = new GC.Spread.Sheets.Slicers.SlicerStyleInfo();
hstyle.backColor("red");
var style1 = new GC.Spread.Sheets.Slicers.SlicerStyle();
style1.headerStyle(hstyle);
//add a slicer to the sheet and return the slicer instance.
var slicer = activeSheet.slicers.add("slicer1",table.name(),"Name");
//change the slicer properties.
slicer.width(200);
slicer.height(200);
slicer.position(new GC.Spread.Sheets.Point(100, 200));
slicer.style(style1);