(1) Create a custom TableSlicerData.
(2) Inherit the TableSlicerData API function.
(3) Create your function to invoke related TableSlicerData API function.
(4) Create your slicer which relates to your TableSlicerData.
(5) Build slicer UI and invoke the doFilter function.
(6) Add data to spread and create a table.
(7) Create your TableSlicerData and attach it to your slicer. Add slicer to DOM tree.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
var rowCount = 200;
sheet.setRowCount(rowCount);
var cityCount = cities.length;
sheet.setValue(0, 0, 'Continent');
sheet.setValue(0, 1, 'Country');
sheet.setValue(0, 2, 'City');
sheet.setValue(0, 3, 'Amount');
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 100);
for (var row = 1; row < rowCount; row++) {
var cityIndex = Math.floor(cityCount * Math.random());
var city = cities[cityIndex];
var country = getCountry(city);
var continent = getContinent(country);
sheet.setValue(row, 0, continent);
sheet.setValue(row, 1, country);
sheet.setValue(row, 2, city);
sheet.setValue(row, 3, Math.floor(10000 * Math.random()));
}
sheet.tables.add('table1', 0, 0, rowCount, 4, GC.Spread.Sheets.Tables.TableThemes.light19);
sheet.resumePaint();
var table = sheet.tables.find(0, 0);
var dataSource = new TreeSlicerData(table, [ 'Continent', 'Country', 'City' ]);
var treeSlicer = new TreeSlicer(dataSource, [ 'Continent', 'Country', 'City' ]);
document.getElementById('slicer_Tree').appendChild(treeSlicer.getDOMElement());
};
var cities = [
'New York',
'Los Angeles',
'Chicago',
'Bei Jing',
'Shang Hai',
'Xi An',
'Tokyo',
'Osaka',
'Yokohama',
'London',
'Liverpool',
'Manchester'
];
function getCountry(city) {
switch (city) {
case 'New York':
case 'Los Angeles':
case 'Chicago':
return 'USA';
case 'Bei Jing':
case 'Shang Hai':
case 'Xi An':
return 'China';
case 'London':
case 'Liverpool':
case 'Manchester':
return 'UK';
}
return 'Japan';
}
function getContinent(country) {
switch (country) {
case 'USA':
return 'North America';
case 'UK':
return 'Europe';
}
return 'Asia';
}
function TreeSlicerData(table, treeColumns) {
GC.Spread.Sheets.Slicers.TableSlicerData.call(this, table);
this.listeners = [];
this.suspended = false;
this.treeColumns = treeColumns;
this.lastFilterPath = [];
}
TreeSlicerData.prototype = GC.Spread.Sheets.Slicers.TableSlicerData.prototype;
TreeSlicerData.prototype.constructor = TreeSlicerData;
TreeSlicerData.prototype.buildDataTree = function() {
var treeData = (this.treeData = {});
this.build(treeData, this.treeColumns, 0, null);
};
TreeSlicerData.prototype.build = function(parentData, treeColumns, index, parentIndexes) {
var columnName = treeColumns[index];
var currentData;
var exclusiveIndexes = [];
var map = {};
if (!parentIndexes) {
var datas = this.getExclusiveData(columnName);
for (var k = 0; k < datas.length; k++) {
exclusiveIndexes.push(k);
map[k] = this.getRowIndexes(columnName, k);
}
} else {
for (var k = 0; k < parentIndexes.length; k++) {
var exclusivaIndex = this.getExclusiveRowIndex(columnName, parentIndexes[k]);
if (!map[exclusivaIndex]) {
map[exclusivaIndex] = [];
exclusiveIndexes.push(exclusivaIndex);
}
map[exclusivaIndex].push(parentIndexes[k]);
}
}
parentData.column = columnName;
if (!parentData.indexes) {
parentData.indexes = [];
}
for (var dateIndex = 0; dateIndex < exclusiveIndexes.length; dateIndex++) {
var exclusivaIndex = exclusiveIndexes[dateIndex];
var dataValue = this.getExclusiveData(columnName)[exclusivaIndex];
parentData.indexes.push(exclusivaIndex);
if (index + 1 < treeColumns.length) {
currentData = parentData[exclusivaIndex] = { indexes: [], value: dataValue };
this.build(currentData, treeColumns, index + 1, map[exclusivaIndex]);
} else {
currentData = parentData[exclusivaIndex] = map[exclusivaIndex];
currentData.value = dataValue;
}
}
};
TreeSlicerData.prototype.filter = function(path) {
this.suspended = true;
if (this.lastFilterPath) {
for (var i = 0; i < this.lastFilterPath.length; i++) {
this.doUnfilter(this.treeColumns[i]);
}
}
this.lastFilterPath = path;
var current = this.treeData;
for (var i = 0; i < path.length; i++) {
var exclusiveIndex = current.indexes ? current.indexes[path[i]] : path[i];
current = current[exclusiveIndex];
if (i === path.length - 1) {
this.suspended = false;
}
this.doFilter(this.treeColumns[i], { exclusiveRowIndexes: [ exclusiveIndex ] });
}
};
TreeSlicerData.prototype.clearFilter = function() {
this.suspended = true;
if (this.lastFilterPath) {
for (var i = 0; i < this.lastFilterPath.length; i++) {
if (i === this.lastFilterPath.length - 1) {
this.suspended = false;
}
this.doUnfilter(this.treeColumns[i]);
}
}
};
TreeSlicerData.prototype.onFiltered = function(filteredIndexes, isPreview) {
if (!this.suspended) {
for (var i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview });
}
}
};
TreeSlicerData.prototype.attachListener = function(listener) {
this.listeners.push(listener);
};
TreeSlicerData.prototype.dettachListener = function(listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
};
var root = null;
function TreeSlicer(slicerData, treeColumns) {
slicerData.buildDataTree();
this.data = slicerData;
this.slicerData = slicerData;
this.treeColumns = treeColumns;
this.treeDatas = slicerData.treeData;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
TreeSlicer.prototype.constructor = TreeSlicer;
TreeSlicer.prototype.getDOMElement = function() {
return root;
};
TreeSlicer.prototype.onDataLoaded = function() {
var self = this;
var treeDatas = this.treeDatas;
var treeItems = (this.treeItems = {});
root = document.createElement('div');
root.innerHTML = '<span class="expanded"></span><span>All</span>';
treeItems.dom = root.children[1];
treeItems.allDoms = [ root.children[1] ];
treeItems.allIcons = [ root.children[0] ];
treeItems.dom.treeItem = treeItems;
self.addOneNode(treeDatas, root, treeItems, treeItems);
treeItems.allDoms[0].classList.add('treeSlicer_Item');
document.getElementById('slicer_Tree').addEventListener('mousemove',function(e){
var target = e.target;
if(target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed'){
self.hoverItem = target;
target.classList.add("hover");
}
if (self.hoverItem === target) {
return;
}
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
}
})
document.getElementById('slicer_Tree').addEventListener('mouseout',function(e){
var target = e.target;
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
self.hoverItem = null;
}
})
document.getElementById('slicer_Tree').addEventListener('click', function(e) {
var target = e.target;
var childTree = target.parentElement.children[2];
if(target.className == 'expanded'){
childTree.style.display='none';
target.classList.remove('expanded');
target.classList.add('collapsed');
}else if(target.className == 'collapsed'){
childTree.style.display='block';
target.classList.remove('collapsed');
target.classList.add('expanded');
}
})
document.getElementById('slicer_Tree').addEventListener('mousedown', function(e) {
var target = e.target;
if (target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed') {
if (self.activeItem === target) {
return;
}
if (self.activeItem) {
self.activeItem.classList.remove('active');
self.setSelect(self.activeItem.treeItem, false);
}
self.activeItem = target;
var treeItem = self.activeItem.treeItem;
self.setSelect(self.activeItem.treeItem, true);
target.classList.add('active');
if (treeItem === treeItems) {
self.data.clearFilter();
} else if (treeItem) {
var path = [ treeItem.index ];
treeItem = treeItem.parent;
while (treeItem && treeItem.parent) {
path.unshift(treeItem.index);
treeItem = treeItem.parent;
}
self.data.filter(path);
}
}
});
self.activeItem = treeItems.dom;
self.setSelect(treeItems, true);
treeItems.dom.classList.add('active');
};
TreeSlicer.prototype.setSelect = function(treeItem, isSelect) {
if (!treeItem) {
return;
}
if (isSelect) {
treeItem.dom.classList.add('select');
} else {
treeItem.dom.classList.remove('select');
}
for (var i = 0; i < treeItem.children.length; i++) {
this.setSelect(treeItem.children[i], isSelect);
}
};
TreeSlicer.prototype.addOneNode = function(treeDatas, parent, parentItem, rootItem) {
var indexes = treeDatas.indexes;
var current = document.createElement('ul');
parent.appendChild(current);
parentItem.children = [];
var currentItem;
if (indexes) {
for (var i = 0; i < indexes.length; i++) {
var childData = treeDatas[indexes[i]];
var value = childData.value;
var childDom = this.addItem(current, value, parentItem, i, rootItem, false);
currentItem = childDom.children[1].treeItem;
this.addOneNode(childData, childDom, currentItem, rootItem);
}
} else {
parent.children[0].classList.remove('expanded');
}
};
TreeSlicer.prototype.addItem = function(current, value, parentItem, index, rootItem, isLeaf) {
var childDom = document.createElement('li');
if (isLeaf) {
childDom.innerHTML = '<span></span><span>' + value + '</span>';
} else {
childDom.innerHTML = '<span class="expanded"></span><span>' + value + '</span>';
}
current.appendChild(childDom);
var content = childDom.children[1];
rootItem.allDoms.push(content);
rootItem.allIcons.push(childDom.children[0]);
var item = { dom: content, parent: parentItem, index: index };
parentItem.children.push(item);
content.treeItem = item;
parentItem.children.push();
if (isLeaf) {
current.style.display = 'none';
}
return childDom;
};
TreeSlicer.prototype.onFiltered = function(data) {};
<!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" style="height:100%">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p class="desc">Click on different items in this tree to filter by those items.</p>
<div style="height: 440px;position: relative">
<div id="slicer_Tree"></div>
</div>
</div>
</div>
</body>
</html>
.hover {
background-color: lightgray;
font-weight: 700 !important;
}
.select {
font-weight: 700 !important;
color: blue;
}
.active {
font-weight: 700 !important;
color: red;
}
.treeSlicer_Item {
cursor: pointer;
}
.expanded {
background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAR0lEQVRYR+3UUQ0AIAwD0Zsi/LsYiiDDRPm4GWjzkrUIX4XzsYACCiigwAg0sEKT3FPghMJfrAUU+EIg+YbbJVRAAQUUUOAC99IJunjjuhUAAAAASUVORK5CYII=);
background-repeat: no-repeat;
background-position: center;
background-size: 10px 10px;
background-color: #d3d3d3;
height: 16px;
width: 16px;
float: left;
cursor: pointer;
}
.collapsed {
background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAaklEQVRYR+2WSwrAIBQD44na3v8SeqIWodsq000/jFuT8BgwvhJ+apLlwtbvNhJZiPjU7hMPykRiB5CABCTwWwKjbr/R1MhSexXPuh0lUrEDSOAVBJ58hs2NSAISkIAEPklg9He0JCvZig6rOyO69wJo3QAAAABJRU5ErkJggg==);
background-repeat: no-repeat;
background-position: center;
background-size: 10px 10px;
background-color: #d3d3d3;
height: 16px;
width: 16px;
float: left;
cursor: pointer;
}
li {
list-style: none;
}
.desc{
padding:2px 10px;
background-color:#F4F8EB;
}
.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;
}
span {
line-height: 18px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
