(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.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-slicers";
import './styles.css';
const cities = [
'New York',
'Los Angeles',
'Chicago',
'Bei Jing',
'Shang Hai',
'Xi An',
'Tokyo',
'Osaka',
'Yokohama',
'London',
'Liverpool',
'Manchester'
];
function getCountry(city: string) {
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: string) {
switch (country) {
case 'USA':
return 'North America';
case 'UK':
return 'Europe';
}
return 'Asia';
}
class TreeSlicerData extends GC.Spread.Sheets.Slicers.TableSlicerData {
suspended: boolean;
listeners: any[];
treeColumns: string[];
lastFilterPath: string[];
treeData: any;
constructor (table: GC.Spread.Sheets.Tables.Table, treeColumns: string[]) {
super(table);
this.listeners = [];
this.suspended = false;
this.treeColumns = treeColumns;
this.lastFilterPath = [];
}
buildDataTree () {
let treeData = (this.treeData = {});
this.build(treeData, this.treeColumns, 0, null);
}
build (parentData: any, treeColumns: string[], index: number, parentIndexes: number[]) {
let columnName = treeColumns[index];
let currentData;
let exclusiveIndexes = [];
let map = {};
if (!parentIndexes) {
let datas = this.getExclusiveData(columnName);
for (let k = 0; k < datas.length; k++) {
exclusiveIndexes.push(k);
map[k] = this.getRowIndexes(columnName, k);
}
} else {
for (let k = 0; k < parentIndexes.length; k++) {
let 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 (let dateIndex = 0; dateIndex < exclusiveIndexes.length; dateIndex++) {
let exclusivaIndex = exclusiveIndexes[dateIndex];
let 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;
}
}
}
filter (path: string[]) {
this.suspended = true;
if (this.lastFilterPath) {
for (let i = 0; i < this.lastFilterPath.length; i++) {
this.doUnfilter(this.treeColumns[i]);
}
}
this.lastFilterPath = path;
let current = this.treeData;
for (let i = 0; i < path.length; i++) {
let 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 ] });
}
}
clearFilter () {
this.suspended = true;
if (this.lastFilterPath) {
for (let i = 0; i < this.lastFilterPath.length; i++) {
if (i === this.lastFilterPath.length - 1) {
this.suspended = false;
}
this.doUnfilter(this.treeColumns[i]);
}
}
}
onFiltered (filteredIndexes: number[], isPreview: boolean) {
if (!this.suspended) {
for (let i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview });
}
}
}
attachListener (listener: any) {
this.listeners.push(listener);
}
dettachListener (listener: any) {
for (let i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
}
class TreeSlicer {
root: HTMLElement;
data: any;
slicerData: TreeSlicerData;
treeColumns: string[];
treeDatas: any;
treeItems: any;
hoverItem: any;
activeItem: any;
constructor (slicerData: TreeSlicerData, treeColumns: string[]) {
slicerData.buildDataTree();
this.root = null;
this.data = slicerData;
this.slicerData = slicerData;
this.treeColumns = treeColumns;
this.treeDatas = slicerData.treeData;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
getDOMElement () {
return this.root;
}
onDataLoaded () {
let self = this;
let treeDatas = this.treeDatas;
let treeItems: any = (this.treeItems = {});
let root = document.createElement('div');
this.root = root;
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){
let target: any = 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){
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
self.hoverItem = null;
}
})
document.getElementById('slicer_Tree').addEventListener('click', function(e) {
let target: any = e.target;
let 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) {
let target: any = 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;
let treeItem = self.activeItem.treeItem;
self.setSelect(self.activeItem.treeItem, true);
target.classList.add('active');
if (treeItem === treeItems) {
self.data.clearFilter();
} else if (treeItem) {
let 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');
}
setSelect (treeItem: any, isSelect: boolean) {
if (!treeItem) {
return;
}
if (isSelect) {
treeItem.dom.classList.add('select');
} else {
treeItem.dom.classList.remove('select');
}
for (let i = 0; i < treeItem.children.length; i++) {
this.setSelect(treeItem.children[i], isSelect);
}
}
addOneNode (treeDatas: any, parent: any, parentItem: any, rootItem: any) {
let indexes = treeDatas.indexes;
let current = document.createElement('ul');
parent.appendChild(current);
parentItem.children = [];
let currentItem;
if (indexes) {
for (let i = 0; i < indexes.length; i++) {
let childData = treeDatas[indexes[i]];
let value = childData.value;
let childDom = this.addItem(current, value, parentItem, i, rootItem, false);
currentItem = (<any> childDom.children[1]).treeItem;
this.addOneNode(childData, childDom, currentItem, rootItem);
}
} else {
parent.children[0].classList.remove('expanded');
}
}
addItem (current: any, value: any, parentItem: any, index: any, rootItem: any, isLeaf: boolean) {
let 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);
let content: any = childDom.children[1];
rootItem.allDoms.push(content);
rootItem.allIcons.push(childDom.children[0]);
let 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;
}
onFiltered () {}
}
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
hostStyle = {
width: 'calc(100% - 280px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
initSpread($event: any) {
let spread = $event.spread;
let sheet = spread.getActiveSheet();
sheet.suspendPaint();
let cityCount = cities.length;
let rowCount = sheet.getRowCount();
sheet.setValue(0, 0, 'Continent');
sheet.setValue(0, 1, 'Country');
sheet.setValue(0, 2, 'City');
sheet.setValue(0, 3, 'Amount');
for (let row = 1; row < rowCount; row++) {
let cityIndex = Math.floor(cityCount * Math.random());
let city = cities[cityIndex];
let country = getCountry(city);
let 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();
let table = sheet.tables.find(0, 0);
let dataSource = new TreeSlicerData(table, [ 'Continent', 'Country', 'City' ]);
let treeSlicer = new TreeSlicer(dataSource, [ 'Continent', 'Country', 'City' ]);
document.getElementById('slicer_Tree').appendChild(treeSlicer.getDOMElement());
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet [rowCount]="200">
<gc-column [width]="100"></gc-column>
<gc-column [width]="100"></gc-column>
<gc-column [width]="100"></gc-column>
<gc-column [width]="100"></gc-column>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<p class="desc">Click on different items in this tree to filter by those items.</p>
<div class="slicer-tree-container">
<div id="slicer_Tree"></div>
</div>
</div>
</div>
.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;
}
.slicer-tree-container {
height: 440px;
position: relative;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-slicers': 'npm:@mescius/spread-sheets-slicers/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
