aggregateData(columnName: string, aggregateType: SlicerAggregateType, range?: ISlicerRangeConditional): number
SlicerAggregateType provides 11 aggregate function types as follows:
AVERAGE
COUNT
COUNTA
MAX
MIN
PRODUCT
STDEV
STDEVP
SUBTOTAL
SUM
VARS
VARP
You can also create your own data aggregation slicer as shown in the following code.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-slicers";
import './styles.css';
function addAggregationSlicer(spread: GC.Spread.Sheets.Workbook) {
let sheet = spread.getActiveSheet();
let table = sheet.tables.find(0, 0);
let dataSource = new AggregationSlicerData(sheet, table, 1, 5);
let timeSlicer = new TimelineSlicer(dataSource, "Date");
let slicerTimeLine = document.getElementById('slicer_Timeline');
slicerTimeLine.innerHTML = '<p style="padding:2px 10px; background-color:#F4F8EB">Click on the items in the chart slicer to filter by</p>';
slicerTimeLine.appendChild(timeSlicer.getDOMElement());
slicerTimeLine.appendChild(timeSlicer.getHeader());
timeSlicer.attachEventsToHeader();
}
const AggregationSlicerData_YEAR = 0, AggregationSlicerData_MONTH = 1, AggregationSlicerData_OTHER = 2;
class AggregationSlicerData extends GC.Spread.Sheets.Slicers.TableSlicerData {
dateGroup: any;
listeners: any[];
dataStartIndex: number;
dataEndIndex: number;
sheet: GC.Spread.Sheets.Worksheet;
mode: number;
constructor (sheet: GC.Spread.Sheets.Worksheet, table: GC.Spread.Sheets.Tables.Table, dataStartIndex: number, dataEndIndex: number) {
super(table);
this.dateGroup = {years: []};
this.listeners = [];
this.dataStartIndex = dataStartIndex;
this.dataEndIndex = dataEndIndex;
this.sheet = sheet;
}
buildDateGroups (columnName: string) {
let allDates = this.getExclusiveData(columnName);
let dateGroup = this.dateGroup;
for (let dateIndex = 0; dateIndex < allDates.length; dateIndex++) {
let value = this.getOneRecordValue(columnName, dateIndex);
let date = new Date(allDates[dateIndex]);
let year = date.getFullYear(), month = date.getMonth(), day = date.getDate();
let yearGroup = dateGroup[year];
if (!yearGroup) {
yearGroup = dateGroup[year] = {value: 0, monthes: [], indexes: []};
dateGroup.years.push(year);
}
let monthGroup = yearGroup[month];
if (!monthGroup) {
monthGroup = yearGroup[month] = {value: 0, days: [], indexes: []};
yearGroup.monthes.push(month);
}
let dayGroup = monthGroup[day];
if (!dayGroup) {
dayGroup = monthGroup[day] = {value: 0};
monthGroup.days.push(day);
}
yearGroup.value += value;
monthGroup.value += value;
dayGroup.value += value;
yearGroup.indexes.push(dateIndex);
monthGroup.indexes.push(dateIndex);
}
}
getOneRecordValue (columnName: string, exclusiveIndex: number) {
let columnIndexes = this.getRowIndexes(columnName, exclusiveIndex);
let sheet = this.sheet, table = this.getTable(), dataRangeInSheet = table.dataRange(), startRow = dataRangeInSheet.row, startCol = dataRangeInSheet.col, result = 0;
for (let r = 0; r < columnIndexes.length; r++) {
for (let c = this.dataStartIndex; c <= this.dataEndIndex; c++) {
let value = sheet.getValue(columnIndexes[r] + startRow, c + startCol);
result += value;
}
}
return result;
}
getDatasByYear () {
let dateGroup = this.dateGroup;
let years = dateGroup.years;
let result = [];
for (let i = 0; i < years.length; i++) {
let year = years[i];
result.push({title: year, value: dateGroup[year].value});
}
return result;
}
getMonthDatasByYear (year: number) {
let dateGroup = this.dateGroup;
let yearGroup = dateGroup[year], monthes = yearGroup.monthes;
let result = [];
for (let i = 0; i < monthes.length; i++) {
let month = monthes[i];
result.push({title: month, value: yearGroup[month].value});
}
return result;
}
getDayDatasByMonth (year: number, month: number) {
let dateGroup = this.dateGroup;
let yearGroup = dateGroup[year], monthGroup = yearGroup[month], days = monthGroup.days;
let result = [];
for (let i = 0; i < days.length; i++) {
let day = days[i];
result.push({title: day, value: monthGroup[day].value});
}
return result;
}
filterOnYear (columnName: string, year: number) {
let yearGroup = this.dateGroup[year];
let indexes = yearGroup.indexes;
this.mode = AggregationSlicerData_YEAR;
this.doFilter(columnName, {exclusiveRowIndexes: indexes});
this.mode = AggregationSlicerData_OTHER;
}
filterOnMonth (columnName: string, year: number, month: number) {
let yearGroup = this.dateGroup[year];
let monthGroup = yearGroup[month];
let indexes = monthGroup.indexes;
this.mode = AggregationSlicerData_MONTH;
this.doFilter(columnName, {exclusiveRowIndexes: indexes});
this.mode = AggregationSlicerData_OTHER;
}
onFiltered (filteredIndexes: number[], isPreview: boolean) {
for (let i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({columnIndexes: filteredIndexes, isPreview: isPreview, mode: this.mode});
}
}
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;
}
}
}
clearFilter (columnName: string) {
this.doUnfilter(columnName);
}
}
class Bar {
disable: boolean;
title: string;
value: any;
x: number;
width: number;
constructor (title: string, value: any, x: number, width: number, disable: boolean) {
if (disable === void 0) {disable = false;}
this.disable = false;
this.title = title;
this.value = value;
this.disable = disable;
this.x = x;
this.width = width;
}
}
class TimelineSlicer {
root: HTMLElement;
header: HTMLElement;
_canExpand: boolean;
mode: number;
aggregationData: AggregationSlicerData;
slicerData: AggregationSlicerData;
columnName: string;
data: any;
exclusiveDatas: any[];
canvas: any;
hoverBar: HTMLElement;
selectedBar: HTMLElement;
selectedYear: number;
selectedMonth: number;
bars: Bar[];
constructor (slicerData: AggregationSlicerData, columnName: string) {
this.root = null;
this.header = null;
this._canExpand = true;
this.mode = AggregationSlicerData_YEAR;
slicerData.buildDateGroups(columnName);
this.aggregationData = slicerData;
this.slicerData = slicerData;
this.columnName = columnName;
this.data = slicerData.getData(columnName);
this.exclusiveDatas = slicerData.getExclusiveData(columnName);
this.slicerData.attachListener(this);
this.onDataLoaded();
}
getDOMElement () {
return this.root;
}
getHeader () {
return this.header;
}
onDataLoaded () {
let self = this;
self.initHeader();
let div = document.createElement('div');
div.style.width = '100%';
div.style.height = '100%';
div.innerHTML = '<canvas width=250 height=200></canvas>'
let canvas: any = div.firstChild;
this.canvas = canvas;
this.root = div;
canvas.onmousemove = function (event: any) {
let bar = self.hitTest(event.offsetX, event.offsetY);
if (bar !== self.hoverBar) {
self.hoverBar = bar;
self.paint();
}
};
canvas.onmouseout = function () {
self.hoverBar = null;
self.paint();
};
canvas.onclick = function (event: any) {
let bar = self.hitTest(event.offsetX, event.offsetY);
if (!bar) {
return;
}
if (bar !== self.selectedBar) {
if (!self.canExpand()) {
self.selectedBar = bar;
if (self.mode === AggregationSlicerData_YEAR) {
self.selectedYear = parseInt(bar.title, 10);
self.aggregationData.filterOnYear(self.columnName, self.selectedYear);
}
else if (self.mode === AggregationSlicerData_MONTH) {
self.selectedMonth = parseInt(bar.title, 10);
self.aggregationData.filterOnMonth(self.columnName, self.selectedYear, self.selectedMonth);
}
}
else {
if (self.mode === AggregationSlicerData_YEAR) {
self.selectedBar = null;
self.selectedYear = parseInt(bar.title, 10);
self.mode = AggregationSlicerData_MONTH;
self.bars = self.getBars();
self.aggregationData.filterOnYear(self.columnName, self.selectedYear);
}
}
self.paint();
}
};
this.bars = this.getBars();
self.paint();
}
initHeader () {
let headerContainer = document.createElement('div');
headerContainer.innerHTML = '<p class="desc">Check this to allow the user to expand the year into months.</p>'
+ '<input id="canExpand" type="checkbox"/>'
+ '<label for="canExpand">Allow Year to Month Aggregation</label>'
+ '<input id="return" type="button" value="Change back to year aggregation">';
this.header = headerContainer;
}
attachEventsToHeader () {
let canExpand: any = document.getElementById("canExpand");
canExpand.checked=this._canExpand;
canExpand.onchange = function () {
self._canExpand = canExpand.checked;
};
let returnButton = document.getElementById("return");
returnButton.style.width = "100%";
let self = this;
returnButton.onclick=function () {
if (self.mode === AggregationSlicerData_YEAR) {
return;
}
self.mode = AggregationSlicerData_YEAR;
self.selectedBar = null;
self.bars = self.getBars();
self.paint();
self.aggregationData.doUnfilter(self.columnName);
}
}
hitTest (x: number, y: number) {
let bars = this.bars;
for (let i = 0; i < bars.length; i++) {
let bar = bars[i];
if (x >= bar.x && x < bar.x + bar.width) {
return bar;
}
}
return null;
}
paint () {
let context = this.canvas.getContext("2d");
let bars = this.bars;
let topValue = this.getTopValue(bars);
let width = this.canvas.width;
let height = this.canvas.height;
let ruleHeight = 20, borderWidth = 2;
let maxBarHeight = height - ruleHeight;
context.clearRect(0, 0, width, height);
for (let i = 0; i < bars.length; i++) {
let bar = bars[i];
if (bar === this.selectedBar) {
context.fillStyle = "#1b1b1b";
context.fillRect(bar.x - borderWidth * 2, 0, bar.width + 4 * borderWidth, height);
context.fillStyle = "#E1E1E1";
context.fillRect(bar.x - borderWidth, 0 + borderWidth, bar.width + 2 * borderWidth, height - 2 * borderWidth);
}
else if (bar === this.hoverBar) {
context.fillStyle = "#C1C1C1";
context.fillRect(bar.x - borderWidth, 0 + borderWidth, bar.width + 2 * borderWidth, height - 2 * borderWidth);
}
if (this.selectedBar && bar !== this.selectedBar) {
context.fillStyle = "#003c4d";
}
else {
context.fillStyle = "#0096c0";
}
context.fillText(this.getTitle(bar.title), bar.x + borderWidth * 2, height - borderWidth * 4);
let barHeight = maxBarHeight * bar.value / topValue;
context.fillRect(bar.x, maxBarHeight - barHeight, bar.width, barHeight);
}
}
getTitle (title: string) {
let monthes = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
if (this.mode === AggregationSlicerData_MONTH) {
return monthes[parseInt(title, 10)];
}
return title;
}
getTopValue (bars: Bar[]) {
let max = bars[0].value;
for (let i = 1; i < bars.length; i++) {
max = max < bars[i].value ? bars[i].value : max;
}
let base = 1;
while (max / base >= 10) {
base *= 10;
}
let head = max / base;
return Math.ceil(head) * base;
}
currentSelection () {
if (this.canExpand()) {
return -1;
}
switch (this.mode) {
case AggregationSlicerData_YEAR:
return this.selectedYear;
case AggregationSlicerData_MONTH:
return this.selectedMonth;
}
return -1;
}
getBars () {
let result = [];
let datas;
if (this.mode === AggregationSlicerData_YEAR) {
datas = this.aggregationData.getDatasByYear();
}
else if (this.mode === AggregationSlicerData_MONTH) {
datas = this.aggregationData.getMonthDatasByYear(this.selectedYear);
}
else {
datas = this.aggregationData.getDayDatasByMonth(this.selectedYear, this.selectedMonth);
}
let length = datas.length;
let barLayout = this.getBarLayout(length);
let current = this.currentSelection();
for (let i = 0; i < length; i++) {
result.push(new Bar(datas[i].title + "", datas[i].value, this.getX(barLayout, length, i), barLayout.width, false));
}
return result;
}
getX (layout: any, count: number, index: number) {
if (count <= 12) {
return layout.margin * (index + 1) + layout.width * index;
}
else {
let current = this.currentSelection();
if (current < 5) {
return layout.margin * (index + 1) + layout.width * index;
}
else {
let displayIndex = index - 5;
if (displayIndex < 0) {
return -100;
}
return layout.margin * (displayIndex + 1) + layout.width * displayIndex - layout.width / 2;
}
}
}
canExpand () {
return this._canExpand && this.mode !== AggregationSlicerData_MONTH;
}
getBarLayout (count: number) {
let fullWidth = this.canvas.width, margin, width;
let fold = 5;
if (count <= 12) {
margin = fullWidth / ((fold + 1) * count + 1);
width = fold * margin;
}
else {
margin = fullWidth / ((fold + 1) * 10 + 1);
width = fold * margin;
}
return {margin: margin, width: width};
}
onFiltered () {
};
}
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
hostStyle = {
width: 'calc(100% - 280px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {}
initSpread($event: any) {
let spread = $event.spread;
let sd = data;
if (sd && sd[0].sheets) {
if (!spread) {
return;
}
spread.suspendPaint();
spread.fromJSON(sd[0]);
spread.resumePaint();
addAggregationSlicer(spread);
}
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
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">
<script src="$DEMOROOT$/spread/source/data/aggregationSlicer.js" type="text/javascript"></script>
<!-- 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="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="slicer_Timeline"></div>
</div>
</div>
.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;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
input[type=button] {
padding: 4px 6px;
box-sizing: border-box;
margin: 6px 0;
display: block;
}
#slicer_Timeline{
padding-bottom: 20px;
}
.desc {
padding:2px 10px;
background-color:#F4F8EB;
}
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-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-slicers': 'npm:@mescius/spread-sheets-slicers/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);
