The Table Slicer relies on table, so you must add a table first if you want to create a slicer.
You can add a slicer by using the add method, as shown in the following code:
Slicer consists of a slicer header and a slicer body.
The slicer header is composed of slicer caption, multiSelect and clear filter button. The slicer caption is used to show a caption. The clear filter button is used to un-filter the slicer and it is inactive by default. It is active only when a slicer item is filtered .
The slicer body is composed of slicer items. There are four kinds of slicer items:
No Data Items: The items have been filtered out by another slicer.
Has Data Items: The items haven't been filtered out by another slicer.
Selected Items: The items have been filtered out by themselves.
Unselected Items: The items haven't been filtered out by themselves.
The items are divided into No Data Items and Has Data Items when they are filtered out by another slicer, or divided into Selected Items and Unselected Items when they are filtered out by themselves.
window.onload = function () {
_disableInput();
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.suspendPaint();
var sheet = spread.getActiveSheet();
var dataColumns = ["Name", "City", "Birthday", "Sex", "Weight", "Height"];
var data = [
["Bob", "NewYork", "1968/6/8", "man", "80", "180"],
["Betty", "NewYork", "1972/7/3", "woman", "72", "168"],
["Cherry", "Washington", "1986/2/2", "woman", "58", "161"],
["Gary", "NewYork", "1964/3/2", "man", "71", "179"],
["Hunk", "Washington", "1972/8/8", "man", "80", "171"],
["Eva", "Washington", "1993/2/15", "woman", "71", "180"]];
sheet.tables.addFromDataSource("table1", 1, 1, data);
sheet.getRange(-1, 1, -1, 6).width(80);
var table = sheet.tables.findByName("table1");
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]);
table.setColumnName(5, dataColumns[5]);
var slicer1 = sheet.slicers.add("slicer1", "table1", "Name");
slicer1.position(new GC.Spread.Sheets.Point(10, 170));
// show data items in original position
slicer1.showNoDataItemsInLast(false);
var slicer2 = sheet.slicers.add("slicer2", "table1", "City");
slicer2.position(new GC.Spread.Sheets.Point(220, 170));
var slicer3 = sheet.slicers.add("slicer3", "table1", "Height");
slicer3.position(new GC.Spread.Sheets.Point(430, 170));
// do not show filtered out items
slicer3.showNoDataItems(false);
bindSlicerEvent(spread);
spread.resumePaint();
// filter a city and show how other slicers' related data was effected
table.getSlicerData().doFilter("City", {exclusiveRowIndexes: [0]});
};
function bindSlicerEvent(spread) {
spread.bind(GC.Spread.Sheets.Events.SlicerChanged, function (event, args) {
var sheet = args.sheet;
var slicer = args.slicer;
if (!slicer) {
return;
}
var propertyName = args.propertyName;
if (propertyName === "isSelected") {
if (slicer.isSelected()) {
_activeInput();
var selectedSlicers = getSelectedSlicers(sheet);
if (selectedSlicers && selectedSlicers.length > 1) {
document.getElementById('slicer_name').disabled = true;
clearSlicerSetting();
}
else if (selectedSlicers.length === 1) {
initSlicerSetting(slicer);
}
}
else {
_disableInput();
}
}
});
var sheet = spread.getActiveSheet();
// slicer name
document.getElementById('slicer_name').oninput = function (e) {
var name = e.target.value;
if (!name) {
return;
}
var isSameName = isExistedName(sheet, name);
if (isSameName === true) {
alert("Duplicated slicer name.");
return;
}
else if (isSameName === false) {
setSlicerProperty({sheet: sheet}, "name", name);
}
};
// slicer caption name
document.getElementById('slicer_caption_name').oninput = function (e) {
setSlicerProperty({sheet: sheet}, "captionName", e.target.value);
}
// slicer header
document.getElementById('show_header').onchange = function (e) {
setSlicerProperty({sheet: sheet}, "showHeader", e.target.checked);
};
// slicer show no data items
document.getElementById('show_nodata_items').onchange = function (e) {
setSlicerProperty({sheet: sheet}, "showNoDataItems", e.target.checked);
};
// slicer show no data items last
document.getElementById('show_nodata_items_last').onchange = function (e) {
setSlicerProperty({sheet: sheet}, "showNoDataItemsInLast", e.target.checked);
};
// slicer visually no data items
document.getElementById('visually_nodata_items').onchange = function (e) {
setSlicerProperty({sheet: sheet}, "visuallyNoDataItems", e.target.checked);
};
}
function setSlicerProperty(data, property, value) {
var sheet = data && data.sheet;
if (!needChangeSlicer(sheet)) {
return;
}
var selectedSlicers = getSelectedSlicers(sheet);
for (var item in selectedSlicers) {
var slicer = selectedSlicers[item];
switch (property) {
case "name":
slicer.name(value);
break;
case "captionName":
slicer.captionName(value);
break;
case "showHeader":
slicer.showHeader(value);
break;
case "showNoDataItems":
slicer.showNoDataItems(value);
break;
case "showNoDataItemsInLast":
slicer.showNoDataItemsInLast(value);
break;
case "visuallyNoDataItems":
slicer.visuallyNoDataItems(value);
break;
}
}
}
function isExistedName(sheet, name) {
if (!sheet) {
return null;
}
var slicers = sheet.slicers.all();
if (!slicers || _isEmptyObject(slicers)) {
return null;
}
for (var item in slicers) {
var slicer = slicers[item];
if (!slicer.isSelected() && slicer.name() === name) {
return true;
}
}
return false;
}
function initSlicerSetting(slicer) {
document.getElementById('slicer_name').value = slicer.name();
document.getElementById('slicer_caption_name').value = slicer.captionName();
document.getElementById('show_header').checked = slicer.showHeader();
document.getElementById('show_nodata_items').checked = slicer.showNoDataItems();
document.getElementById('show_nodata_items_last').checked = slicer.showNoDataItemsInLast();
document.getElementById('visually_nodata_items').checked = slicer.visuallyNoDataItems();
}
function clearSlicerSetting() {
document.getElementById('slicer_name').value = "";
document.getElementById('slicer_caption_name').value = "";
document.getElementById('show_header').checked = false;
document.getElementById('show_nodata_items').checked = false;
document.getElementById('show_nodata_items_last').checked = false;
document.getElementById('visually_nodata_items').checked = false;
}
function getSelectedSlicers(sheet) {
if (!sheet) {
return null;
}
var slicers = sheet.slicers.all();
if (!slicers || _isEmptyObject(slicers)) {
return null;
}
var selectedSlicers = [];
for (var item in slicers) {
var slicer = slicers[item];
if (slicer.isSelected()) {
selectedSlicers.push(slicer);
}
}
return selectedSlicers;
}
function needChangeSlicer(sheet) {
if (!sheet) {
return false;
}
var selectedSlicers = getSelectedSlicers(sheet);
if (!selectedSlicers || selectedSlicers.length === 0) {
return false;
}
return true;
}
function _disableInput() {
var inputs = document.querySelectorAll('.option-row input');
for (var inputIndex = 0; inputIndex < inputs.length; inputIndex++) {
inputs[inputIndex].disabled = true;
}
}
function _activeInput() {
var inputs = document.querySelectorAll('.option-row input');
for (var inputIndex = 0; inputIndex < inputs.length; inputIndex++) {
inputs[inputIndex].disabled = false;
}
}
function _isEmptyObject(obj) {
var name;
for (name in obj) {
return false;
}
return true;
}
<!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$/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">
<p style="padding:2px 10px; background-color:#F4F8EB">Select the slicer and then change the options.</p>
<div class="option-row">
<label for="slicer_name" style="display: inline-block;width: 150px">Name:</label>
<input type="text" id="slicer_name" />
</div>
<div class="option-row">
<label for="slicer_caption_name" style="display: inline-block;width: 150px">Caption Name:</label>
<input type="text" id="slicer_caption_name" />
</div>
<div class="option-row">
<input type="checkbox" id="show_header" checked />
<label for="show_header">ShowHeader</label>
</div>
<div class="option-row">
<input type="checkbox" id="show_nodata_items" checked />
<label for="show_nodata_items">ShowNoDataItems</label>
</div>
<div class="option-row">
<p class="desc">Check this box to show the unfiltered data items last in the slicer.</p>
<input type="checkbox" id="show_nodata_items_last" />
<label for="show_nodata_items_last">ShowNoDataItemsInLast</label>
<p style="padding:2px 10px; background-color:#F4F8EB">Check this box to gray out items that aren’t present in the table.</p>
<input type="checkbox" id="visually_nodata_items" checked />
<label for="visually_nodata_items">VisuallyNoDataItems</label>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: auto;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-group {
margin-bottom: 6px;
}
label {
display: inline-block;
min-width: 90px;
margin: 6px 0;
}
input {
padding: 4px 6px;
box-sizing: border-box;
margin-bottom: 6px;
}
hr {
border-color: #fff;
opacity: .2;
margin: 12px 0;
}
p {
padding: 2px 10px;
background-color: #F4F8EB;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
