Chart Slicer

For a complex scenario, you can customize GeneralSlicerData as shown in the following example:

Description
app.js
index.html
styles.css
Copy to CodeMine

Create a slicer data which inherits from GeneralSlicerData.

    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;

Overwrite attachListener and detachListener functions.

    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;
            }
        }
    };

Overwrite the onFiltered function to implement your filter logic.

    ChartSlicerData.prototype.onFiltered = function () {
        for (var i = 0; i < this.listeners.length; i++) {
            this.listeners[i].onFiltered({ dataIndexes: this.getFilteredRowIndexes() });
            // invoke refreshLish to refresh chart UI
            this.listeners[i].refreshList();
        }
    };

Create your own slicer.

    function ChartSlicer(container, columnName, slicerData, title, legend) {
        this.data = slicerData;
        this.container = container;
        // Some code omitted here, you can see it on the right.
        ...
        this.slicerData.attachListener(this);
        // incoke onDataLoaded function to build UI
        this.onDataLoaded();
    }
    ChartSlicer.prototype.constructor = ChartSlicer;

Create your onDataLoaded function.

    ChartSlicer.prototype.onDataLoaded = function () {
        var self = this;
        ...
        $(this.container).append($chartDiv);
        $(this.container).append($footerDiv);
        ...
        var indexes = [];
        ...
        // invoke slicerData's doFilter function
        self.slicerData.doFilter(self.columnName, { exclusiveRowIndexes: indexes });
        ...
    };

Create your onFiltered function.

    ChartSlicer.prototype.onFiltered = function (data) {
        ...
        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);
        }
        ...
    };

Create your slicer data and attach it to your slicer.

    var slicerData = new ChartSlicerData(ret.arrayDatas, ret.columnNames);
    var yearsOnListChart = new ChartSlicer($('#yearsOnList')[0], 'years on list', slicerData, 'Years on the List', 'Number of companies');
    var workersChart = new ChartSlicer($('#workers')[0], 'workers', slicerData, 'Workers', 'Number of companies');
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; }