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.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.options.tabStripRatio = 0.6;
initSpread(spread);
};
function initSpread(spread) {
var sd = data;
if (sd.length > 0) {
if (sd) {
if (!spread) {
return;
}
var sheet = spread.getActiveSheet();
initSlicer(sheet, sd);
}
}
}
function parseJSONToArray(datas) {
if (!datas || datas.length <= 0) {
return;
}
var columnNames = Object.keys(datas[0]), keyCount = columnNames.length,
arrayDatas = [];
for (var i = 0, len = datas.length; i < len; i++) {
var data = datas[i];
if (data) {
var dataItem = [];
arrayDatas.push(dataItem);
for (var j = 0; j < keyCount; j++) {
dataItem.push(data[columnNames[j]]);
}
}
}
return {
columnNames: columnNames,
arrayDatas: arrayDatas
};
}
function getMaxInArray(array) {
if (!array || array.length <= 0) {
return;
}
var max = array[0];
for (var i = 1, len = array.length; i < len; i++) {
if (array[i] > max) {
max = array[i];
}
}
return max;
}
function ChartSlicerData(datas, columnNames) {
GC.Spread.Slicers.GeneralSlicerData.call(this, datas, columnNames);
this.listeners = [];
}
ChartSlicerData.prototype = GC.Spread.Slicers.GeneralSlicerData.prototype;
ChartSlicerData.prototype.constructor = ChartSlicerData;
ChartSlicerData.prototype.onFiltered = function () {
let self = this;
self.listeners.forEach(function (listener) {
listener.onFiltered({dataIndexes: self.getFilteredRowIndexes()});
listener.refreshList();
})
};
ChartSlicerData.prototype.attachListener = function (listener) {
this.listeners.push(listener);
};
ChartSlicerData.prototype.dettachListener = function (listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
};
function ChartSlicer(container, columnName, slicerData, title, legend) {
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();
}
ChartSlicer.prototype.constructor = ChartSlicer;
ChartSlicer.prototype.getXAxis = function () {
var xAxis = [];
var exclusiveData = this.slicerData.getExclusiveData(this.columnName);
var maxValue = getMaxInArray(exclusiveData);
if (this.columnName === "years on list") {
var xAxisCount = 6,
xTick = Math.floor(maxValue / xAxisCount);
for (var i = 1; i <= xAxisCount; i++) {
xAxis.push(xTick * i);
}
}
else if (this.columnName === "workers") {
if (typeof maxValue === "number") {
var base = 1;
while (maxValue > 1) {
xAxis.push(Math.pow(10, base));
base++;
maxValue = parseInt(maxValue / 10);
}
}
}
return xAxis;
};
ChartSlicer.prototype.getSeriesByXAxisScope = function (xAxis, columnName, slicerData) {
if (!xAxis || xAxis.length <= 0 || !slicerData) {
return;
}
var series = [], data = slicerData.getData(columnName),
filterdRowIndexes = slicerData.getFilteredRowIndexes();
for (var x = 0, len1 = xAxis.length; x < len1; x++) {
var scopeStart = 0;
if (x > 0) {
scopeStart = xAxis[x - 1];
}
var scopeEnd = xAxis[x];
var seriesItem = 0;
for (var i = 0, len = filterdRowIndexes.length; i < len; i++) {
var rowIndex = filterdRowIndexes[i], dataItem = data[rowIndex];
if (dataItem >= scopeStart && dataItem < scopeEnd) {
seriesItem++;
}
}
series.push(seriesItem);
}
return series;
}
ChartSlicer.prototype.onDataLoaded = function () {
var self = this;
var 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>';
textSpan = footerDiv.firstChild;
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);
var 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) {
var dataIndex = param.dataIndex;
var startScope = 0,
endScope = self.xAxis[dataIndex];
if (dataIndex > 0) {
startScope = self.xAxis[dataIndex - 1];
}
var _footerDiv = self.container.lastChild;
_footerDiv.firstChild.innerText = self.title + ': ' + startScope + ' to ' + endScope;
_footerDiv.style.display = 'block';
var indexes = [];
var exclusiveData = self.slicerData.getExclusiveData(self.columnName);
for (var 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);
};
ChartSlicer.prototype.refreshList = function () {
this.verticalChart.setSeries([{data: this.getSeriesByXAxisScope(this.verticalChart.getOption().xAxis[0].data, this.columnName, this.slicerData)}]);
};
ChartSlicer.prototype.onFiltered = function (data) {
var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));;
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
sheet.suspendEvent();
var filteredRowIndexs = data.dataIndexes;
for (var r = 0, len = sheet.getRowCount(); r < len; r++) {
sheet.setRowVisible(r, false);
}
for (var i = 0, len = filteredRowIndexs.length; i < len; i++) {
sheet.setRowVisible(filteredRowIndexs[i], true);
}
sheet.resumeEvent();
sheet.resumePaint();
};
function initSlicer(sheet, datas) {
sheet.suspendPaint();
sheet.name("The 2014 Inc. 5000.");
sheet.setDataSource(datas);
sheet.setColumnCount(20);
sheet.resumePaint();
var ret = parseJSONToArray(datas);
var slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames);
var yearsOnListChart = new ChartSlicer(document.getElementById('yearsOnList'), "years on list", slicerData, "Years on the List", "Number of companies");
var workersChart = new ChartSlicer(document.getElementById('workers'), "workers", slicerData, "Workers", "Number of companies");
}
<!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$/spread/source/js/external/echart/echarts.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/chartSlicer.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">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div id="workers" class="sample-chart"></div>
<div id="yearsOnList" class="sample-chart"></div>
</div>
</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;
}
