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.):
Then add an item slicer to the "name" field:
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:
If you want to define the item height:
If you don't want to see the items that have the noData status:
A Sample to Create a Pivot Table
Custom Item Slicer Themes
SpreadJS supports customize the theme of the pivot table item slicer. Using code such as the following:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), {sheetCount: 2});
initCustomThemes(spread);
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
var pt = initPivotTable(pivotLayoutSheet);
initSlicer(pivotLayoutSheet, pt);
bindEvent(pivotLayoutSheet, pt);
};
function initCustomThemes(spread) {
const theme1 = new GC.Spread.Sheets.Slicers.SlicerStyle();
theme1.fromJSON(GC.Spread.Sheets.Slicers.SlicerStyles.light1().toJSON());
theme1.name('custom1');
theme1.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('rgb(225, 245, 254)'));
const theme2 = new GC.Spread.Sheets.Slicers.SlicerStyle();
theme2.fromJSON(GC.Spread.Sheets.Slicers.SlicerStyles.other2().toJSON());
theme2.name('custom2');
const wholeSlicerStyle = new GC.Spread.Sheets.Slicers.SlicerStyleInfo();
wholeSlicerStyle.backColor('#e3f2fd');
theme2.wholeSlicerStyle(wholeSlicerStyle);
spread.customSlicerThemes.add(theme1);
spread.customSlicerThemes.add(theme2);
}
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");
spread.resumePaint();
}
function initPivotTable(sheet) {
sheet.setRowCount(1000);
var option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true
};
var pivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8.name(), option);
pivotTable.suspendLayout();
pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("country", "countrys", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("city", "city", GC.Spread.Pivot.PivotTableFieldType.rowField);
var groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
};
pivotTable.group(groupInfo);
pivotTable.add("amount", "amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.none;
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
function initSlicer(sheet, pt) {
var slicer_region = sheet.slicers.add("slicer_region", pt.name(), "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark2().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_region.position(new GC.Spread.Sheets.Point(491, 20));
slicer_region.height(210);
var slicer_country = sheet.slicers.add("slicer_country", pt.name(), "country", GC.Spread.Sheets.Slicers.SlicerStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
slicer_country.position(new GC.Spread.Sheets.Point(691, 20));
slicer_country.height(460);
slicer_country.showNoDataItems(false);
var slicer_city = sheet.slicers.add("slicer_city", pt.name(), "city", GC.Spread.Sheets.Slicers.SlicerStyles.other2().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_city.position(new GC.Spread.Sheets.Point(891, 20));
slicer_city.height(320);
slicer_city.columnCount(2);
}
function bindEvent(sheet, pt) {
var slicer;
var slicerCount = 0;
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, pt);
break;
}
}
});
_getElementById("columnCount").addEventListener("change", function (e) {
var value = + e.target.value;
if (value && slicer) {
slicer.columnCount(value);
}
});
_getElementById("showHeader").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showHeader(checked);
}
});
_getElementById("showNoDataItems").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showNoDataItems(!checked);
}
});
_getElementById("visuallyNoDataItems").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.visuallyNoDataItems(checked);
}
});
_getElementById("showNoDataItemsInLast").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showNoDataItemsInLast(checked);
}
});
_getElementById("ascending").addEventListener("change", function (e) {
var checked = e.target.checked;
if (checked && slicer) {
slicer.sortState(GC.Spread.Sheets.SortState.ascending);
}
});
_getElementById("descending").addEventListener("change", function (e) {
var checked = e.target.checked;
if (checked && slicer) {
slicer.sortState(GC.Spread.Sheets.SortState.descending);
}
});
_getElementById("addSlicerBtn").addEventListener("click", function () {
var fieldName = _getElementById("slicerList").value;
if (fieldName) {
sheet.slicers.add(fieldName + "_" + slicerCount++, pt.name(), fieldName, GC.Spread.Sheets.Slicers.SlicerStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
}
});
_getElementById("changeStyle").addEventListener("click", function () {
var styleName = _getElementById("slicerStyle").value;
if (styleName && slicer) {
slicer.style(styleName);
}
});
}
function updateSlicerInfo(slicer, pt) {
_getElementById("columnCount").value = slicer.columnCount();
_getElementById("showHeader").checked = slicer.showHeader();
_getElementById("showNoDataItems").checked = ! slicer.showNoDataItems();
_getElementById("visuallyNoDataItems").checked = slicer.visuallyNoDataItems();
_getElementById("showNoDataItemsInLast").checked = slicer.showNoDataItemsInLast();
const slicerStyleName = slicer.style().name().toLowerCase();
_getElementById("slicerStyle").value = slicerStyleName.includes('custom') ? slicerStyleName : slicerStyleName.substr(11);
var sortState = slicer.sortState();
_getElementById("ascending").checked = sortState === 1;
_getElementById("descending").checked = sortState === 2;
}
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/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
</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>Column Count:</label>
<input class="info-input" id="columnCount" type="number"></input>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHeader">
<label for="showHeader">Display Header</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItems">
<label for="showNoDataItems">Hide Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="visuallyNoDataItems">
<label for="visuallyNoDataItems">Visually Indicate Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItemsInLast">
<label for="showNoDataItemsInLast">Show Items With No Data Last</label>
</div>
<div class="slicer-info">
<p>Sort State</p>
<input type="radio" id="ascending" name="sortState" value="1">
<label for="ascending">Ascending(A to Z)</label><br>
<input type="radio" id="descending" name="sortState" value="2">
<label for="descending">Descending(Z to A)</label><br>
</div>
</div>
<div class="block">
<div>Add Slicer</div>
<br />
<select class="select-list" name="slicerList" id="slicerList">
<option value="region">region</option>
<option value="country">country</option>
<option value="city">city</option>
<option value="Quarters (date)">Quarters (date)</option>
<option value="amount">amount</option>
<option value="id">id</option>
</select>
<button class="select-button" id="addSlicerBtn">Add</button>
</div>
<div class="block">
<div>Change Current Slicer Style</div>
<br />
<div class="slicerStyle">
<select class="select-list" name="slicerStyle" id="slicerStyle">
<option value="light1">light1</option>
<option value="light2">light2</option>
<option value="light3">light3</option>
<option value="light4">light4</option>
<option value="light5">light5</option>
<option value="light6">light6</option>
<option value="dark1">dark1</option>
<option value="dark2">dark2</option>
<option value="dark3">dark3</option>
<option value="dark4">dark4</option>
<option value="dark5">dark5</option>
<option value="dark6">dark6</option>
<option value="other1">other1</option>
<option value="other2">other2</option>
<option value="custom1">custom1</option>
<option value="custom2">custom2</option>
</select>
<button class="select-button" id="changeStyle">Change</button>
</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;
}
.select-list {
width: 120px;
}
.select-button {
width: 80px;
}
