Introduction
SpreadJS PivotTable supports two types of slicers:
Item slicerTimeline slicer
Same with table slicer, pivot slicer is also managed through slicer collection.
SpreadJS defined SlicerType to distinguish which type of slicer will be added.
Pivot Slicers work with 3 plugins: shapes, slicers, pivot-addon.
Item Slicers can be added to any field (excluding the Calc field).
The changes made on an Item Slicer are equal to using the manual filter, which means "textItems" in the label filter.
Only Timeline Slicer can be added to the Date type field.
The changes made on Timeline Slicer are equal to using the label condition filter, which means "condition" in the label filter.
For example, if we want to add a Pivot Table Item Slicer.
Assuming that we have created a PivotTable named "pt".
If want to add an item slicer to the "name" field.
Also, assuming that pivot table "pt" has a date field named "birthday",
and we want to add a Pivot Table Timeline Slicer.
You may have a question:
It is obvious that item slicer and timeline slicer can be added to a date field at the same time.
Can they work at the same time?
By default, no.
They all work through labelFilter. Setting textItems will cause the condition filter to be lost.
You can solve this problem with an option in PivotTable: allowMultipleFiltersPerField.
Try to Filter by clicking and dragging items in the Slicer.
With Item Slicer, you can also try pressing the shift and ctrl keys (for Windows) / command key (for Mac) when clicking or dragging.
Shape Base
Pivot Slicer is based on Shape, so you can use some Shape API to control Pivot Slicer
For example
Connect With PivotTable
Pivot Slicer supports connection management, including connecting or disconnecting the PivotTable.
If disconnecting from a PivotTable, the slicer filter action will not affect the PivotTable.
PivotTable filter actions will also not affect a Slicer.
For example, through this capability, you can use a slicer to manage multiple PivotTables from the same source.
Assuming that there are two PivotTables "pt1" and "pt2", both of them are created from a table called "table1", so they have same data source and fields.
Now you can use slicer_name to control the filters of two PivotTables.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 });
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
initPivotTable(pivotLayoutSheet);
initSlicer(pivotLayoutSheet);
bindEvent(spread.getSheet(0));
};
function initSpread(spread) {
spread.suspendPaint();
let sheet = spread.getSheet(1);
sheet.name("DataSource");
sheet.setRowCount(650);
sheet.setColumnWidth(5, 120);
sheet.getCell(-1, 5).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,1).formatter("$ #,##0");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 0, 0, 637, 6);
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
let sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
sheet0.setValue(0, 0, "Pivot Table 1");
sheet0.setValue(0, 10, "Pivot Table 2");
spread.resumePaint();
}
function initPivotTable(sheet) {
var groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, {by: GC.Pivot.DateGroupType.years}, {by: GC.Pivot.DateGroupType.months}] };
var pt1 = sheet.pivotTables.add("pt1", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
pt1.suspendLayout();
pt1.options.showRowHeader = true;
pt1.options.showColumnHeader = true;
pt1.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.top;
pt1.layoutType(GC.Spread.Pivot.PivotTableLayoutType.compact);
pt1.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("country", "country", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("city", "city", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("amount", "amounts", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt1.resumeLayout();
pt1.autoFitColumn();
var pt2 = sheet.pivotTables.add("pt2", "tableSales", 1, 10, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium23);
pt2.suspendLayout();
pt2.options.showRowHeader = true;
pt2.options.showColumnHeader = true;
pt2.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.top;
pt2.layoutType(GC.Spread.Pivot.PivotTableLayoutType.compact);
pt2.group(groupInfo);
pt2.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("Quarters", "Quarters", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("date", "date", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("amount", "Amounts", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt2.resumeLayout();
pt2.autoFitColumn();
}
function initSlicer (sheet) {
sheet.suspendPaint();
var yearSlicer = sheet.slicers.add("Years", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light5(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
var monthSlicer = sheet.slicers.add("Months", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
this.setTimelineProp(yearSlicer, 967, 20, 310, 160, 1, "Years");
this.setTimelineProp(monthSlicer, 967, 160, 310, 160, 3, "Months");
var regionSlicer = sheet.slicers.add("region", 'pt1', "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
var countrySlicer = sheet.slicers.add("country", 'pt1', "country", GC.Spread.Sheets.Slicers.SlicerStyles.dark2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
var citySlicer = sheet.slicers.add("city", 'pt1', "city", GC.Spread.Sheets.Slicers.SlicerStyles.dark3(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
this.setSlicerProp(regionSlicer, 285, 20, 140, 200, 1, true, true);
this.setSlicerProp(countrySlicer, 425, 20, 140, 200, 1, true, true);
this.setSlicerProp(citySlicer, 565, 20, 140, 200, 1, true, true);
sheet.resumePaint();
}
function setSlicerProp(slicer, x, y, width, height, columnCount, showHeader, showNoDataItemsInLast) {
slicer.position({x, y});
slicer.width(width);
slicer.height(height);
if (columnCount) {
slicer.columnCount(columnCount);
}
slicer.showHeader(!!showHeader);
slicer.showNoDataItemsInLast(!!showNoDataItemsInLast);
slicer.allowMove(false);
slicer.allowResize(false);
}
function setTimelineProp (timeline, x, y, width, height, level, caption) {
timeline.position({x, y});
timeline.width(width);
timeline.height(height);
timeline.level(level);
timeline.captionName(caption);
timeline.allowMove(false);
timeline.allowResize(false);
}
function bindEvent (sheet) {
var slicer;
sheet.bind(GC.Spread.Sheets.Events.SlicerChanged, function () {
var slicers = sheet.slicers.all();
for (var i = 0; i < slicers.length; i++) {
if (slicers[i].isSelected()) {
slicer = slicers[i];
updateSlicerInfo(slicer);
break;
}
}
});
['name', 'captionName'].forEach(prop => {
_getElementById(prop).addEventListener("change", function (e) {
var value = e.target.value;
if (value && slicer) {
slicer[prop](value);
}
});
});
['x', 'y', 'width', 'height'].forEach(prop => {
_getElementById(prop).addEventListener("change", function (e) {
var value = +e.target.value;
if (value && slicer) {
slicer[prop](value);
}
});
});
['pt1', 'pt2'].forEach(ptName => {
_getElementById(ptName).addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
if (checked) {
slicer.connectPivotTable(ptName);
} else {
slicer.disconnectPivotTable(ptName);
}
}
});
});
}
function updateSlicerInfo (slicer) {
_getElementById("name").value = slicer.name();
_getElementById("captionName").value = slicer.captionName();
_getElementById("x").value = slicer.x();
_getElementById("y").value = slicer.y();
_getElementById("width").value = slicer.width();
_getElementById("height").value = slicer.height();
_getElementById("pt1").checked = slicer.isConnectedPivotTable('pt1');
_getElementById("pt2").checked = slicer.isConnectedPivotTable('pt2');
}
function _getElementById(id) {
return document.getElementById(id);
}
function _isNullOrUndefined(o) {
return o === null || o === undefined;
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="block slicer-infos">
<div>Current Selected Slicer Info:</div><br>
<div class="slicer-info">
<label class="info-label">Slicer Name:</label>
<input class="info-input" id="name"></input>
</div>
<div class="slicer-info">
<label class="info-label">Caption Name:</label>
<input class="info-input" id="captionName"></input>
</div>
<div class="slicer-info">
<label class="info-label">X:</label>
<input type="number" class="info-input" name="x" id="x", min="0", max="1000", step="1">
</div>
<div class="slicer-info">
<label class="info-label">Y:</label>
<input type="number" class="info-input" name="y" id="y", min="0", max="1000", step="1">
</div>
<div class="slicer-info">
<label class="info-label">Width:</label>
<input type="number" class="info-input" name="width" id="width", min="0", max="1000", step="1">
</div>
<div class="slicer-info">
<label class="info-label">Height:</label>
<input type="number" class="info-input" name="height" id="height", min="0", max="1000", step="1">
</div>
</div>
<div class="block">
<div>Slicer Connection</div><br/>
<div class="Connection">
<div class="slicer-info">
<input type="checkbox" id="pt1">
<label for="pt1">Connect Pivot Table 1</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="pt2">
<label for="pt2">Connect Pivot Table 2</label>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 330px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 330px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.slicer-info {
margin-top: 5px;
margin-bottom: 5px;
}
.block {
border: 1px solid gray;
padding-left: 5px;
padding-top: 10px;
padding-bottom: 10px;
margin-bottom: 1px;
}
.info-label {
width: 35%;
display: inline-block;
}
.info-input {
width: 58%;
display: inline-block;
}
