PivotTable Item Slicer
SpreadJS PivotTables supports item slicers, which can be used just like table slicers.
Try clicking on slicers in the below demo to see all of the available properties.
The Pivot Table Item Slicer is managed by SlicerCollection(WorkSheet.slicers), same as the Table Slicers.
Item Slicers can be added to any field (excluding the Calc field). The changes made on Item Slicer are equal to using the manual filter, which means "textItems" in the label filter.
Add Slicer
If we want to add a PivotTable Item Slicer, we can create a PivotTable named "pt" (The specific implementation of initPivotTable can be found at the end of the article.):
var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
initPivotTable();
Then add an item slicer to the "name" field:
var slicer_name = sheet.slicers.add("slicer_name", pt.name(), "name", GC.Spread.Sheets.Slicers.SlicerStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
Item Status
We defined the item with two status:
- selected: whether an item is chosen by filter.
- noData: if an item has been filtered by other filter, which means it will not take effect whether it is selected or not, we defined it as noData.
These two statuses build an item status. For example, "selected && noData" or "unselected && hasData"
Using Slicer
Then we can define the slicer_name: For example, if you want to show items in two column:
slicer_name.columnCount(2);
If you want to define the item height:
slicer_name.itemHeight(40);
If you don't want to see the items that have the noData status:
slicer_name.showNoDataItems(false);
A Sample to Create a Pivot Table
function initPivotTable () {
var dataSource = [
[ "name", "product", "date", "amount", "price", "sales" ],
[ "chris", "desk", new Date("2020-10-08T16:00:00.000Z"), 5, 199, 995 ],
[ "radow", "pen", new Date("2020-09-15T16:00:00.000Z"), 2, 5, 10 ],
[ "peyton", "pencil", new Date("2021-06-22T16:00:00.000Z"), 6, 1.5, 9 ],
[ "johnson", "chair", new Date("2021-07-19T16:00:00.000Z"), 7, 68, 476 ],
[ "vic", "notebook", new Date("2021-01-13T16:00:00.000Z"), 7, 3.2, 22.4 ],
[ "lan", "desk", new Date("2021-03-12T16:00:00.000Z"), 9, 199, 1791 ],
[ "chris", "pen", new Date("2021-03-06T16:00:00.000Z"), 4, 5, 20 ],
[ "chris", "pencil", new Date("2020-09-02T16:00:00.000Z"), 10, 1.5, 15 ],
[ "radow", "chair", new Date("2020-08-09T16:00:00.000Z"), 3, 68, 204 ],
[ "peyton", "notebook", new Date("2021-02-08T16:00:00.000Z"), 9, 3.2, 28.8 ],
[ "johnson", "desk", new Date("2021-07-03T16:00:00.000Z"), 7, 199, 1393],
[ "vic", "pen", new Date("2021-06-27T16:00:00.000Z"), 8, 5, 40],
[ "lan", "pencil", new Date("2020-10-10T16:00:00.000Z"), 2, 1.5, 3],
[ "chris", "chair", new Date("2021-03-04T16:00:00.000Z"), 2, 68, 136],
[ "chris", "notebook", new Date("2021-02-21T16:00:00.000Z"), 11, 3.2, 35.2],
[ "radow", "desk", new Date("2021-06-03T16:00:00.000Z"), 6, 199, 1194]
];
var sourceSheet = spread.sheets[0];
sourceSheet.setArray(0, 0, dataSource);
sourceSheet.tables.add("table1", 0, 0, 17, 6);
spread.sheets[0].name("sourceSheet");
var pivotSheet = new GC.Spread.Sheets.Worksheet('pivotTable1');
pivotSheet.setRowCount(2000);
pivotSheet.setColumnCount(30);
spread.addSheet(1, pivotSheet);
var pt = pivotSheet.pivotTables.add("pivotTable1", "table1", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.compact, GC.Spread.Pivot.PivotTableThemes.medium1.name(), null);
pt.suspendLayout();
pt.add("name", "name", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("product", "product", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.group({
originFieldName: 'date',
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
});
pt.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
pt.add("sales", "sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt.resumeLayout();
pt.autoFitColumn();
spread.setActiveSheet("pivotTable1");
}
Custom Item Slicer Themes
SpreadJS supports customize the theme of the pivot table item slicer. Using code such as the following:
const customTheme = new GC.Spread.Sheets.Slicers.SlicerStyle();
customTheme.name("custom0");
const wholeSlicerStyle = new GC.Spread.Sheets.Slicers.SlicerStyleInfo('red', 'white', '16px Calibri');
customTheme.wholeSlicerStyle(wholeSlicerStyle);
spread.customSlicerThemes.add(customTheme);
slicer.style("custom0");
slicer.getStyleName(); // "custom0"
// set default item slicer theme
spread.defaultSlicerTheme('custom0');
Feedback
Submit and view feedback for