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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div class="options-container">
<div id="slicer_Timeline"></div>
</div>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue'
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-slicers";
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
let sheet = spreadRef.value.getSheet(0);
sheet.suspendPaint();
let sd = data;
if (sd && sd[0].sheets) {
if (!spread) {
return;
}
spread.suspendPaint();
spread.fromJSON(sd[0]);
spread.resumePaint();
addAggregationSlicer(spread);
}
}
function addAggregationSlicer(spread) {
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 {
constructor(sheet, table, dataStartIndex, dataEndIndex) {
super(table);
this.dateGroup = { years: [] };
this.listeners = [];
this.dataStartIndex = dataStartIndex;
this.dataEndIndex = dataEndIndex;
this.sheet = sheet;
}
buildDateGroups(columnName) {
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, exclusiveIndex) {
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) {
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, month) {
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, year) {
let yearGroup = this.dateGroup[year];
let indexes = yearGroup.indexes;
this.mode = AggregationSlicerData_YEAR;
this.doFilter(columnName, { exclusiveRowIndexes: indexes });
this.mode = AggregationSlicerData_OTHER;
}
filterOnMonth(columnName, year, month) {
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, isPreview) {
for (let i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview, mode: this.mode });
}
}
attachListener(listener) {
this.listeners.push(listener);
}
detachListener(listener) {
for (let i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
}
clearFilter(columnName) {
this.doUnfilter(columnName);
}
}
let Bar = (function () {
function Bar(title, value, x, width, disable) {
if (disable === void 0) { disable = false; }
this.disable = false;
this.title = title;
this.value = value;
this.disable = disable;
this.x = x;
this.width = width;
}
return Bar;
})();
class TimelineSlicer {
constructor(slicerData, columnName) {
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 = div.firstChild;
this.canvas = canvas;
this.root = div;
canvas.onmousemove = function (event) {
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) {
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 = document.getElementById("canExpand");
canExpand.checked = this._canExpand;
canExpand.onchange = function (sender, args) {
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, y) {
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) {
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) {
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, count, index) {
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) {
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(data) {
};
}
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/aggregationSlicer.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@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-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
