(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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<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>
</template>
<script setup>
import '@mescius/spread-sheets-vue'
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-slicers";
const spreadRef = ref(null);
function initSpread(spread) {
spreadRef.value = 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());
}
const 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';
}
class TreeSlicerData extends GC.Spread.Sheets.Slicers.TableSlicerData {
constructor(table, treeColumns) {
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, treeColumns, index, parentIndexes) {
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) {
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, isPreview) {
if (!this.suspended) {
for (let i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview });
}
}
}
attachListener(listener) {
this.listeners.push(listener);
}
dettachListener(listener) {
for (let i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
}
class TreeSlicer {
constructor(slicerData, treeColumns) {
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 = (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 = 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) {
let target = e.target;
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
self.hoverItem = null;
}
})
document.getElementById('slicer_Tree').addEventListener('click', function (e) {
let target = 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 = 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, isSelect) {
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, parent, parentItem, rootItem) {
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 = childDom.children[1].treeItem;
this.addOneNode(childData, childDom, currentItem, rootItem);
}
} else {
parent.children[0].classList.remove('expanded');
}
}
addItem(current, value, parentItem, index, rootItem, isLeaf) {
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 = 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(data) { }
}
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@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-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
