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 { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { FormsModule } from '@angular/forms';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import './styles.css';
class ChartSlicerData extends GC.Spread.Slicers.GeneralSlicerData {
listeners: any[];
constructor(datas: any, columnNames: string[]) {
super(datas, columnNames);
this.listeners = [];
}
onFiltered() {
let self = this;
self.listeners.forEach(function(listener) {
listener.onFiltered({
dataIndexes: (<any>self).getFilteredRowIndexes()
});
listener.refreshList();
})
}
attachListener(listener: any) {
this.listeners.push(listener);
}
dettachListener(listener: any) {
for (let i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
}
class ChartSlicer {
sheet: GC.Spread.Sheets.Worksheet;
data: ChartSlicerData;
container: HTMLElement;
columnName: string;
slicerData: ChartSlicerData;
title: string;
legend: string;
xAxis: any;
series: any;
verticalChart: any;
constructor(container: HTMLElement, columnName: string, sheet: GC.Spread.Sheets.Worksheet, slicerData: ChartSlicerData, title: string, legend: string) {
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 = (<any>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: any, columnName: string, slicerData: any) {
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;
(<any>removeFilter).onclick = function(e: any) {
(<any>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: any) {
let dataIndex = param.dataIndex;
let startScope = 0,
endScope = self.xAxis[dataIndex];
if (dataIndex > 0) {
startScope = self.xAxis[dataIndex - 1];
}
let _footerDiv = self.container.lastChild;
(<any>_footerDiv.firstChild).innerText = self.title + ': ' + startScope + ' to ' + endScope;
(<HTMLElement>_footerDiv).style.display = 'block';
let indexes = [];
let exclusiveData = (<any>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);
}
}
(<any>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: any) {
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: GC.Spread.Sheets.Worksheet, datas: any) => {
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: any[]) => {
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: any[]) => {
if (!datas || datas.length <= 0) {
return;
}
let columnNames: string[] = 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: any[] = [];
arrayDatas.push(dataItem);
for (let j = 0; j < keyCount; j++) {
dataItem.push(data[columnNames[j]]);
}
}
}
return {
columnNames: columnNames,
arrayDatas: arrayDatas
};
}
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook = null;
hostStyle = {
width: 'calc(100% - 290px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
initSpread($event: any) {
let spread = $event.spread;
let sd = data;
if (sd.length > 0) {
if (sd) {
if (!spread) {
return;
}
let sheet = spread.getActiveSheet();
initSlicer(sheet, sd);
}
}
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule, FormsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.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>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
</gc-spread-sheets>
<div class="options-container">
<div id="workers" class="sample-chart"></div>
<div id="yearsOnList" class="sample-chart"></div>
</div>
</div>
.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: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.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',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
