(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 * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-slicers";
import Panel from './panel';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
const { useState } = React;
let dataSource, setSource, activeItem, hoveritem;
const cities = [
'New York',
'Los Angeles',
'Chicago',
'Bei Jing',
'Shang Hai',
'Xi An',
'Tokyo',
'Osaka',
'Yokohama',
'London',
'Liverpool',
'Manchester'
];
export const AppFunc = () => {
const [dataSource, setDataSource] = useState(null);
setSource = setDataSource;
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread, setDataSource)}>
<Worksheet>
<Column width={100}></Column>
<Column width={100}></Column>
<Column width={100}></Column>
<Column width={100}></Column>
</Worksheet>
</SpreadSheets>
</div>
<Panel
onMouseMove={(e) => { onMouseMove(e) }} dataSource={dataSource}
onMouseOut={(e) => { onMouseOut(e) }} onClick={(e) => { onClick(e) }} onMouseDown={(e) => { onMouseDown(e) }}
></Panel>
</div>
)
}
function onMouseDown(e) {
var target = e.target;
if (target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed') {
if (activeItem) {
delete activeItem.active;
}
let dataItem = Object.assign({}, dataSource.treeData);
if (!target.id) {
dataSource.clearFilter();
dataItem.active = true;
setSource(dataItem);
} else {
var path = target.id.split(" ").map((item) => {
return parseInt(item);
});
let data = dataItem;
let pathCopy = [...path];
while(path.length > 0) {
let index = path.shift();
dataItem = dataItem[dataItem.indexes[index]];
}
dataItem.active = true;
activeItem = dataItem;
dataSource.filter(pathCopy);
setSource(data);
}
}
}
function onClick(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');
}
}
function onMouseMove(e) {
var target = e.target;
if (target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed') {
if (hoveritem) {
delete hoveritem.hover;
}
let dataItem = Object.assign({}, dataSource.treeData);
if (!target.id) {
dataItem.hover = true;
setSource(dataItem);
} else {
var path = target.id.split(" ").map((item) => {
return parseInt(item);
});
let data = dataItem;
while(path.length > 0) {
let index = path.shift();
dataItem = dataItem[dataItem.indexes[index]];
}
dataItem.hover = true;
hoveritem = dataItem;
setSource(data);
}
}
}
function onMouseOut(e) {
if (hoveritem) {
delete hoveritem.hover;
}
setSource(Object.assign({}, dataSource.treeData));
}
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 initSpread(spread) {
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');
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);
dataSource = new TreeSlicerData(table, ['Continent', 'Country', 'City']);
dataSource.buildDataTree();
let dataItem = Object.assign({}, dataSource.treeData);
dataItem.active = true;
setSource(dataItem);
}
class TreeSlicerData extends GC.Spread.Sheets.Slicers.TableSlicerData {
constructor(table, treeColumns) {
super(table);
this.listeners = [];
this.suspended = false;
this.treeColumns = treeColumns;
this.lastFilterPath = [];
}
buildDataTree() {
var treeData = (this.treeData = {});
this.build(treeData, this.treeColumns, 0, null);
}
build(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;
}
}
}
filter(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] });
}
}
clearFilter() {
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]);
}
}
}
onFiltered(filteredIndexes, isPreview) {
if (!this.suspended) {
for (var i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview });
}
}
}
attachListener(listener) {
this.listeners.push(listener);
}
dettachListener(listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
}
import * as React from 'react';
export default function Panel(props) {
const { onMouseMove, onMouseOut, onClick, onMouseDown, dataSource } = props;
return (
<div class="options-container">
<p className="desc">Click on different items in this tree to filter by those items.</p>
<div style={{ height: '440px', position: 'relative' }}>
<div id="slicer_Tree" onMouseMove={(e) => { onMouseMove(e) }}
onMouseOut={(e) => { onMouseOut(e) }} onClick={(e) => { onClick(e) }} onMouseDown={(e) => { onMouseDown(e) }}>
{createDomWithDataSoyrce(dataSource)}
</div>
</div>
</div>
);
}
function createDomWithDataSoyrce(dataSource) {
return (
<div>
<span className="expanded">
</span>
<span className={dataSource && (dataSource.active ? (dataSource.hover ? 'active hover' : 'active') : (dataSource.hover ? "hover" : ""))}>All</span>
<ul style={{ padding: '0' }}>
{
dataSource && createItemDom(dataSource)
}
</ul>
</div>
);
}
function createItemDom(item, index) {
return (
<ul>
{
item.indexes && item.indexes.map((nextitem, itemIndex) => {
return (
<li>
{!!item[nextitem].indexes && <span className="expanded"></span>}
<span className={item[nextitem].active ? (item[nextitem].hover ? "select active hover" : "select active") : (item[nextitem].hover ? "hover select" : "select")} key={nextitem} id={index !== undefined ? index + ' ' + itemIndex : itemIndex}>{item[nextitem].value}</span>
{
item[nextitem].indexes && createItemDom(item[nextitem], index !== undefined ? index + ' ' + itemIndex : itemIndex)
}
</li>
)
})
}
</ul>
)
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app" style="height: 100%;"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.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;
}
.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;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
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-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.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'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
