aggregateData(columnName: string, aggregateType: SlicerAggregateType, range?: ISlicerRangeConditional): number
SlicerAggregateType은 다음과 같이 11가지 집계 함수 유형을 제공합니다.
AVERAGE
COUNT
COUNTA
MAX
MIN
PRODUCT
STDEV
STDEVP
SUBTOTAL
SUM
VARS
VARP
다음 코드와 같이 자신만의 데이터 집계 슬라이서를 만들 수도 있습니다.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
};
function initSpread(spread) {
var sd = data;
if (sd && sd[0].sheets) {
if (!spread) {
return;
}
spread.suspendPaint();
spread.fromJSON(sd[0]);
spread.resumePaint();
addAggregationSlicer(spread);
}
}
function addAggregationSlicer(spread) {
var sheet = spread.getActiveSheet();
var table = sheet.tables.find(0, 0);
var dataSource = new AggregationSlicerData(sheet, table, 1, 5);
var timeSlicer = new TimelineSlicer(dataSource, "Date");
var 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();
}
var AggregationSlicerData_YEAR = 0, AggregationSlicerData_MONTH = 1, AggregationSlicerData_OTHER = 2;
function AggregationSlicerData(sheet, table, dataStartIndex, dataEndIndex) {
GC.Spread.Sheets.Slicers.TableSlicerData.call(this, table);
this.dateGroup = {years: []};
this.listeners = [];
this.dataStartIndex = dataStartIndex;
this.dataEndIndex = dataEndIndex;
this.sheet = sheet;
}
AggregationSlicerData.prototype = GC.Spread.Sheets.Slicers.TableSlicerData.prototype;
AggregationSlicerData.prototype.constructor = AggregationSlicerData;
AggregationSlicerData.prototype.buildDateGroups = function (columnName) {
var allDates = this.getExclusiveData(columnName);
var dateGroup = this.dateGroup;
for (var dateIndex = 0; dateIndex < allDates.length; dateIndex++) {
var value = this.getOneRecordValue(columnName, dateIndex);
var date = new Date(allDates[dateIndex]);
var year = date.getFullYear(), month = date.getMonth(), day = date.getDate();
var yearGroup = dateGroup[year];
if (!yearGroup) {
yearGroup = dateGroup[year] = {value: 0, monthes: [], indexes: []};
dateGroup.years.push(year);
}
var monthGroup = yearGroup[month];
if (!monthGroup) {
monthGroup = yearGroup[month] = {value: 0, days: [], indexes: []};
yearGroup.monthes.push(month);
}
var 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);
}
};
AggregationSlicerData.prototype.getOneRecordValue = function (columnName, exclusiveIndex) {
var columnIndexes = this.getRowIndexes(columnName, exclusiveIndex);
var sheet = this.sheet, table = this.getTable(), dataRangeInSheet = table.dataRange(), startRow = dataRangeInSheet.row, startCol = dataRangeInSheet.col, result = 0;
for (var r = 0; r < columnIndexes.length; r++) {
for (var c = this.dataStartIndex; c <= this.dataEndIndex; c++) {
var value = sheet.getValue(columnIndexes[r] + startRow, c + startCol);
result += value;
}
}
return result;
};
AggregationSlicerData.prototype.getDatasByYear = function () {
var dateGroup = this.dateGroup;
var years = dateGroup.years;
var result = [];
for (var i = 0; i < years.length; i++) {
var year = years[i];
result.push({title: year, value: dateGroup[year].value});
}
return result;
};
AggregationSlicerData.prototype.getMonthDatasByYear = function (year) {
var dateGroup = this.dateGroup;
var yearGroup = dateGroup[year], monthes = yearGroup.monthes;
var result = [];
for (var i = 0; i < monthes.length; i++) {
var month = monthes[i];
result.push({title: month, value: yearGroup[month].value});
}
return result;
};
AggregationSlicerData.prototype.getDayDatasByMonth = function (year, month) {
var dateGroup = this.dateGroup;
var yearGroup = dateGroup[year], monthGroup = yearGroup[month], days = monthGroup.days;
var result = [];
for (var i = 0; i < days.length; i++) {
var day = days[i];
result.push({title: day, value: monthGroup[day].value});
}
return result;
};
AggregationSlicerData.prototype.filterOnYear = function (columnName, year) {
var yearGroup = this.dateGroup[year];
var indexes = yearGroup.indexes;
this.mode = AggregationSlicerData_YEAR;
this.doFilter(columnName, {exclusiveRowIndexes: indexes});
this.mode = AggregationSlicerData_OTHER;
};
AggregationSlicerData.prototype.filterOnMonth = function (columnName, year, month) {
var yearGroup = this.dateGroup[year];
var monthGroup = yearGroup[month];
var indexes = monthGroup.indexes;
this.mode = AggregationSlicerData_MONTH;
this.doFilter(columnName, {exclusiveRowIndexes: indexes});
this.mode = AggregationSlicerData_OTHER;
};
AggregationSlicerData.prototype.onFiltered = function (filteredIndexes, isPreview) {
for (var i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({columnIndexes: filteredIndexes, isPreview: isPreview, mode: this.mode});
}
};
AggregationSlicerData.prototype.attachListener = function (listener) {
this.listeners.push(listener);
};
AggregationSlicerData.prototype.dettachListener = function (listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
};
AggregationSlicerData.prototype.clearFilter = function (columnName) {
this.doUnfilter(columnName);
};
var 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;
})();
var root = null;
var header = null;
function TimelineSlicer(slicerData, columnName) {
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();
}
TimelineSlicer.prototype.constructor = TimelineSlicer;
TimelineSlicer.prototype.getDOMElement = function () {
return root;
}
TimelineSlicer.prototype.getHeader = function () {
return header;
}
TimelineSlicer.prototype.onDataLoaded = function () {
var self = this;
self.initHeader();
var div = document.createElement('div');
div.style.width = '100%';
div.style.height = '100%';
div.innerHTML = '<canvas width=250 height=200></canvas>'
var canvas = div.firstChild;
this.canvas = canvas;
root = div;
canvas.onmousemove = function (event) {
var 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) {
var 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();
};
TimelineSlicer.prototype.initHeader = function () {
var 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">';
header = headerContainer;
};
TimelineSlicer.prototype.attachEventsToHeader = function () {
var canExpand = document.getElementById("canExpand");
canExpand.checked=this._canExpand;
canExpand.onchange = function (sender, args) {
self._canExpand = canExpand.checked;
};
var returnButton = document.getElementById("return");
returnButton.style.width = "100%";
var 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);
}
};
TimelineSlicer.prototype.hitTest = function (x, y) {
var bars = this.bars;
for (var i = 0; i < bars.length; i++) {
var bar = bars[i];
if (x >= bar.x && x < bar.x + bar.width) {
return bar;
}
}
return null;
};
TimelineSlicer.prototype.paint = function () {
var context = this.canvas.getContext("2d");
var bars = this.bars;
var topValue = this.getTopValue(bars);
var width = this.canvas.width;
var height = this.canvas.height;
var ruleHeight = 20, borderWidth = 2;
var maxBarHeight = height - ruleHeight;
context.clearRect(0, 0, width, height);
for (var i = 0; i < bars.length; i++) {
var 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);
var barHeight = maxBarHeight * bar.value / topValue;
context.fillRect(bar.x, maxBarHeight - barHeight, bar.width, barHeight);
}
};
TimelineSlicer.prototype.getTitle = function (title) {
var 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;
};
TimelineSlicer.prototype.getTopValue = function (bars) {
var max = bars[0].value;
for (var i = 1; i < bars.length; i++) {
max = max < bars[i].value ? bars[i].value : max;
}
var base = 1;
while (max / base >= 10) {
base *= 10;
}
var head = max / base;
return Math.ceil(head) * base;
};
TimelineSlicer.prototype.currentSelection = function () {
if (this.canExpand()) {
return -1;
}
switch (this.mode) {
case AggregationSlicerData_YEAR:
return this.selectedYear;
case AggregationSlicerData_MONTH:
return this.selectedMonth;
}
return -1;
};
TimelineSlicer.prototype.getBars = function () {
var result = [];
var 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);
}
var length = datas.length;
var barLayout = this.getBarLayout(length);
var current = this.currentSelection();
for (var 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;
};
TimelineSlicer.prototype.getX = function (layout, count, index) {
if (count <= 12) {
return layout.margin * (index + 1) + layout.width * index;
}
else {
var current = this.currentSelection();
if (current < 5) {
return layout.margin * (index + 1) + layout.width * index;
}
else {
var displayIndex = index - 5;
if (displayIndex < 0) {
return -100;
}
return layout.margin * (displayIndex + 1) + layout.width * displayIndex - layout.width / 2;
}
}
};
TimelineSlicer.prototype.canExpand = function () {
return this._canExpand && this.mode !== AggregationSlicerData_MONTH;
};
TimelineSlicer.prototype.getBarLayout = function (count) {
var fullWidth = this.canvas.width, margin, width;
var 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};
};
TimelineSlicer.prototype.onFiltered = function (data) {
};
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ko-kr"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/aggregationSlicer.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="slicer_Timeline"></div>
</div>
</div>
</body>
</html>
.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;
}