Create a slicer data which inherits from GeneralSlicerData.
Overwrite attachListener and detachListener functions.
Overwrite the onFiltered function to implement your filter logic.
Create your own slicer.
Create your onDataLoaded function.
Create your onFiltered function.
Create your slicer data and attach it to your slicer.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets } from '@mescius/spread-sheets-react';
export function AppFunc() {
const initSpread = (spread) => {
let sd = data;
if (sd.length > 0) {
if (sd) {
if (!spread) {
return;
}
let sheet = spread.getActiveSheet();
initSlicer(sheet, sd);
}
}
}
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
<div class="options-container">
<div id="workers" class="sample-chart"></div>
<div id="yearsOnList" class="sample-chart"></div>
</div>
</div>);
}
class ChartSlicerData extends GC.Spread.Slicers.GeneralSlicerData {
constructor(datas, columnNames) {
super(datas, columnNames);
this.listeners = [];
}
onFiltered() {
let self = this;
self.listeners.forEach(function (listener) {
listener.onFiltered({
dataIndexes: self.getFilteredRowIndexes()
});
listener.refreshList();
})
}
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 ChartSlicer {
constructor(container, columnName, sheet, slicerData, title, legend) {
this.sheet = sheet;
this.data = slicerData;
this.container = container;
this.columnName = columnName;
this.slicerData = slicerData;
this.title = title;
this.legend = legend;
this.xAxis = [];
this.series = [];
this.verticalChart = null;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
getXAxis() {
let xAxis = [];
let exclusiveData = this.slicerData.getExclusiveData(this.columnName);
let maxValue = getMaxInArray(exclusiveData);
if (this.columnName === "years on list") {
let xAxisCount = 6,
xTick = Math.floor(maxValue / xAxisCount);
for (let i = 1; i <= xAxisCount; i++) {
xAxis.push(xTick * i);
}
} else if (this.columnName === "workers") {
if (typeof maxValue === "number") {
let base = 1;
while (maxValue > 1) {
xAxis.push(Math.pow(10, base));
base++;
maxValue = parseInt(maxValue / 10);
}
}
}
return xAxis;
}
getSeriesByXAxisScope(xAxis, columnName, slicerData) {
if (!xAxis || xAxis.length <= 0 || !slicerData) {
return;
}
let series = [],
data = slicerData.getData(columnName),
filterdRowIndexes = slicerData.getFilteredRowIndexes();
for (let x = 0, len1 = xAxis.length; x < len1; x++) {
let scopeStart = 0;
if (x > 0) {
scopeStart = xAxis[x - 1];
}
let scopeEnd = xAxis[x];
let seriesItem = 0;
for (let i = 0, len = filterdRowIndexes.length; i < len; i++) {
let rowIndex = filterdRowIndexes[i],
dataItem = data[rowIndex];
if (dataItem >= scopeStart && dataItem < scopeEnd) {
seriesItem++;
}
}
series.push(seriesItem);
}
return series;
}
onDataLoaded() {
let self = this;
let chartDiv = document.createElement('div'),
footerDiv = document.createElement('div');
chartDiv.id = 'chart_div';
chartDiv.style.width = '100%';
chartDiv.style.height = '90%';
footerDiv.innerHTML = '<span id="text_span"></span>' +
'<button>Remove Filter</button>';
let textSpan = footerDiv.firstChild;
let removeFilter = footerDiv.lastChild;
removeFilter.onclick = function (e) {
self.slicerData.doUnfilter(self.columnName);
footerDiv.style.display = 'none';
};
footerDiv.style.width = '100%';
footerDiv.style.height = '100%';
footerDiv.style.display = 'none';
self.container.appendChild(chartDiv);
self.container.appendChild(footerDiv);
this.xAxis = this.getXAxis();
this.series = this.getSeriesByXAxisScope(this.xAxis, this.columnName, this.slicerData);
this.verticalChart = echarts.init(chartDiv);
let option = {
title: {
subtext: this.title,
},
legend: {
data: [this.legend]
},
grid: {
x: "15%",
width: "80%",
x2: "5%",
y: "30%",
height: "59%",
y2: "15%"
},
xAxis: [{
type: 'category',
data: this.xAxis,
axisTick: {
show: true,
length: 2,
lineStyle: {
color: "#333",
width: 1
}
}
}],
yAxis: [{
type: 'value'
}],
series: [{
name: this.legend,
type: "bar",
data: this.series,
itemStyle: {
normal: {
color: "#9fd5b7",
label: {
show: true
}
},
emphasis: {
color: "#ababab"
}
}
}]
};
this.verticalChart.setOption(option);
function clickHandler(param) {
let dataIndex = param.dataIndex;
let startScope = 0,
endScope = self.xAxis[dataIndex];
if (dataIndex > 0) {
startScope = self.xAxis[dataIndex - 1];
}
let _footerDiv = self.container.lastChild;
_footerDiv.firstChild.innerText = self.title + ': ' + startScope + ' to ' + endScope;
_footerDiv.style.display = 'block';
let indexes = [];
let exclusiveData = self.slicerData.getExclusiveData(self.columnName);
for (let i = 0, len = exclusiveData.length; i < len; i++) {
if (exclusiveData[i] >= startScope && exclusiveData[i] < endScope) {
indexes.push(i);
}
}
self.slicerData.doFilter(self.columnName, {
exclusiveRowIndexes: indexes
});
}
this.verticalChart.on("click", clickHandler);
}
refreshList() {
this.verticalChart.setSeries([{
data: this.getSeriesByXAxisScope(this.verticalChart.getOption().xAxis[0].data, this.columnName, this.slicerData)
}]);
}
onFiltered(data) {
let sheet = this.sheet;
sheet.suspendPaint();
sheet.suspendEvent();
let filteredRowIndexs = data.dataIndexes;
for (let r = 0, len = sheet.getRowCount(); r < len; r++) {
sheet.setRowVisible(r, false);
}
for (let i = 0, len = filteredRowIndexs.length; i < len; i++) {
sheet.setRowVisible(filteredRowIndexs[i], true);
}
sheet.resumeEvent();
sheet.resumePaint();
}
}
const initSlicer = (sheet, datas) => {
sheet.suspendPaint();
sheet.name("The 2014 Inc. 5000.");
sheet.setDataSource(datas);
sheet.setColumnCount(20);
sheet.resumePaint();
let ret = parseJSONToArray(datas);
let slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames);
let yearsOnListChart = new ChartSlicer(document.getElementById('yearsOnList'), "years on list", sheet, slicerData, "Years on the List", "Number of companies");
let workersChart = new ChartSlicer(document.getElementById('workers'), "workers", sheet, slicerData, "Workers", "Number of companies");
}
const getMaxInArray = (array) => {
if (!array || array.length <= 0) {
return;
}
let max = array[0];
for (let i = 1, len = array.length; i < len; i++) {
if (array[i] > max) {
max = array[i];
}
}
return max;
}
const parseJSONToArray = (datas) => {
if (!datas || datas.length <= 0) {
return;
}
let columnNames = Object.keys(datas[0]),
keyCount = columnNames.length,
arrayDatas = [];
for (let i = 0, len = datas.length; i < len; i++) {
let data = datas[i];
if (data) {
let dataItem = [];
arrayDatas.push(dataItem);
for (let j = 0; j < keyCount; j++) {
dataItem.push(data[columnNames[j]]);
}
}
}
return {
columnNames: columnNames,
arrayDatas: arrayDatas
};
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets } from '@mescius/spread-sheets-react';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
}
render() {
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
<div class="options-container">
<div id="workers" class="sample-chart"></div>
<div id="yearsOnList" class="sample-chart"></div>
</div>
</div>);
}
initSpread(spread) {
let sd = data;
if (sd.length > 0) {
if (sd) {
if (!spread) {
return;
}
let sheet = spread.getActiveSheet();
initSlicer(sheet, sd);
}
}
}
}
class ChartSlicerData extends GC.Spread.Slicers.GeneralSlicerData {
constructor(datas, columnNames) {
super(datas, columnNames);
this.listeners = [];
}
onFiltered() {
let self = this;
self.listeners.forEach(function (listener) {
listener.onFiltered({
dataIndexes: self.getFilteredRowIndexes()
});
listener.refreshList();
})
}
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 ChartSlicer {
constructor(container, columnName, sheet, slicerData, title, legend) {
this.sheet = sheet;
this.data = slicerData;
this.container = container;
this.columnName = columnName;
this.slicerData = slicerData;
this.title = title;
this.legend = legend;
this.xAxis = [];
this.series = [];
this.verticalChart = null;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
getXAxis() {
let xAxis = [];
let exclusiveData = this.slicerData.getExclusiveData(this.columnName);
let maxValue = getMaxInArray(exclusiveData);
if (this.columnName === "years on list") {
let xAxisCount = 6,
xTick = Math.floor(maxValue / xAxisCount);
for (let i = 1; i <= xAxisCount; i++) {
xAxis.push(xTick * i);
}
} else if (this.columnName === "workers") {
if (typeof maxValue === "number") {
let base = 1;
while (maxValue > 1) {
xAxis.push(Math.pow(10, base));
base++;
maxValue = parseInt(maxValue / 10);
}
}
}
return xAxis;
}
getSeriesByXAxisScope(xAxis, columnName, slicerData) {
if (!xAxis || xAxis.length <= 0 || !slicerData) {
return;
}
let series = [],
data = slicerData.getData(columnName),
filterdRowIndexes = slicerData.getFilteredRowIndexes();
for (let x = 0, len1 = xAxis.length; x < len1; x++) {
let scopeStart = 0;
if (x > 0) {
scopeStart = xAxis[x - 1];
}
let scopeEnd = xAxis[x];
let seriesItem = 0;
for (let i = 0, len = filterdRowIndexes.length; i < len; i++) {
let rowIndex = filterdRowIndexes[i],
dataItem = data[rowIndex];
if (dataItem >= scopeStart && dataItem < scopeEnd) {
seriesItem++;
}
}
series.push(seriesItem);
}
return series;
}
onDataLoaded() {
let self = this;
let chartDiv = document.createElement('div'),
footerDiv = document.createElement('div');
chartDiv.id = 'chart_div';
chartDiv.style.width = '100%';
chartDiv.style.height = '90%';
footerDiv.innerHTML = '<span id="text_span"></span>' +
'<button>Remove Filter</button>';
let textSpan = footerDiv.firstChild;
let removeFilter = footerDiv.lastChild;
removeFilter.onclick = function (e) {
self.slicerData.doUnfilter(self.columnName);
footerDiv.style.display = 'none';
};
footerDiv.style.width = '100%';
footerDiv.style.height = '100%';
footerDiv.style.display = 'none';
self.container.appendChild(chartDiv);
self.container.appendChild(footerDiv);
this.xAxis = this.getXAxis();
this.series = this.getSeriesByXAxisScope(this.xAxis, this.columnName, this.slicerData);
this.verticalChart = echarts.init(chartDiv);
let option = {
title: {
subtext: this.title,
},
legend: {
data: [this.legend]
},
grid: {
x: "15%",
width: "80%",
x2: "5%",
y: "30%",
height: "59%",
y2: "15%"
},
xAxis: [{
type: 'category',
data: this.xAxis,
axisTick: {
show: true,
length: 2,
lineStyle: {
color: "#333",
width: 1
}
}
}],
yAxis: [{
type: 'value'
}],
series: [{
name: this.legend,
type: "bar",
data: this.series,
itemStyle: {
normal: {
color: "#9fd5b7",
label: {
show: true
}
},
emphasis: {
color: "#ababab"
}
}
}]
};
this.verticalChart.setOption(option);
function clickHandler(param) {
let dataIndex = param.dataIndex;
let startScope = 0,
endScope = self.xAxis[dataIndex];
if (dataIndex > 0) {
startScope = self.xAxis[dataIndex - 1];
}
let _footerDiv = self.container.lastChild;
_footerDiv.firstChild.innerText = self.title + ': ' + startScope + ' to ' + endScope;
_footerDiv.style.display = 'block';
let indexes = [];
let exclusiveData = self.slicerData.getExclusiveData(self.columnName);
for (let i = 0, len = exclusiveData.length; i < len; i++) {
if (exclusiveData[i] >= startScope && exclusiveData[i] < endScope) {
indexes.push(i);
}
}
self.slicerData.doFilter(self.columnName, {
exclusiveRowIndexes: indexes
});
}
this.verticalChart.on("click", clickHandler);
}
refreshList() {
this.verticalChart.setSeries([{
data: this.getSeriesByXAxisScope(this.verticalChart.getOption().xAxis[0].data, this.columnName, this.slicerData)
}]);
}
onFiltered(data) {
let sheet = this.sheet;
sheet.suspendPaint();
sheet.suspendEvent();
let filteredRowIndexs = data.dataIndexes;
for (let r = 0, len = sheet.getRowCount(); r < len; r++) {
sheet.setRowVisible(r, false);
}
for (let i = 0, len = filteredRowIndexs.length; i < len; i++) {
sheet.setRowVisible(filteredRowIndexs[i], true);
}
sheet.resumeEvent();
sheet.resumePaint();
}
}
const initSlicer = (sheet, datas) => {
sheet.suspendPaint();
sheet.name("The 2014 Inc. 5000.");
sheet.setDataSource(datas);
sheet.setColumnCount(20);
sheet.resumePaint();
let ret = parseJSONToArray(datas);
let slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames);
let yearsOnListChart = new ChartSlicer(document.getElementById('yearsOnList'), "years on list", sheet, slicerData, "Years on the List", "Number of companies");
let workersChart = new ChartSlicer(document.getElementById('workers'), "workers", sheet, slicerData, "Workers", "Number of companies");
}
const getMaxInArray = (array) => {
if (!array || array.length <= 0) {
return;
}
let max = array[0];
for (let i = 1, len = array.length; i < len; i++) {
if (array[i] > max) {
max = array[i];
}
}
return max;
}
const parseJSONToArray = (datas) => {
if (!datas || datas.length <= 0) {
return;
}
let columnNames = Object.keys(datas[0]),
keyCount = columnNames.length,
arrayDatas = [];
for (let i = 0, len = datas.length; i < len; i++) {
let data = datas[i];
if (data) {
let dataItem = [];
arrayDatas.push(dataItem);
for (let j = 0; j < keyCount; j++) {
dataItem.push(data[columnNames[j]]);
}
}
}
return {
columnNames: columnNames,
arrayDatas: arrayDatas
};
}
<!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="$DEMOROOT$/spread/source/js/external/echart/echarts.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/chartSlicer.js" type="text/javascript"></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% - 300px);
height: 100%;
overflow: auto;
float: left;
}
.options-container {
float: right;
width: 290px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.sample-chart {
width: 100%;
height: 48%;
box-sizing: border-box;
}
button {
background: #9fd5b7;
border: none;
border-radius: 0;
margin-left: 3px;
}
button:hover {
background: #ababab;
border: none;
border-radius: 0;
}
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-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);
