(1) 사용자 정의 TableSlicerData를 만듭니다.
(2) TableSlicerData API 함수를 상속합니다.
(3) 관련 TableSlicerData API 함수를 호출하기 위한 함수를 만듭니다.
(4) TableSlicerData와 관련된 슬라이서를 만듭니다.
(5) 슬라이서 UI를 작성하고 doFilter 함수를 호출합니다.
(6) 데이터를 Spread에 추가하고 표를 만듭니다.
(7) TableSlicerData를 만들고 슬라이서에 연결합니다. DOM 트리에 슬라이서를 추가합니다.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
var rowCount = 200;
sheet.setRowCount(rowCount);
var cityCount = cities.length;
sheet.setValue(0, 0, 'Continent');
sheet.setValue(0, 1, 'Country');
sheet.setValue(0, 2, 'City');
sheet.setValue(0, 3, 'Amount');
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 100);
for (var row = 1; row < rowCount; row++) {
var cityIndex = Math.floor(cityCount * Math.random());
var city = cities[cityIndex];
var country = getCountry(city);
var continent = getContinent(country);
sheet.setValue(row, 0, continent);
sheet.setValue(row, 1, country);
sheet.setValue(row, 2, city);
sheet.setValue(row, 3, Math.floor(10000 * Math.random()));
}
sheet.tables.add('table1', 0, 0, rowCount, 4, GC.Spread.Sheets.Tables.TableThemes.light19);
sheet.resumePaint();
var table = sheet.tables.find(0, 0);
var dataSource = new TreeSlicerData(table, [ 'Continent', 'Country', 'City' ]);
var treeSlicer = new TreeSlicer(dataSource, [ 'Continent', 'Country', 'City' ]);
document.getElementById('slicer_Tree').appendChild(treeSlicer.getDOMElement());
};
var cities = [
'New York',
'Los Angeles',
'Chicago',
'Bei Jing',
'Shang Hai',
'Xi An',
'Tokyo',
'Osaka',
'Yokohama',
'London',
'Liverpool',
'Manchester'
];
function getCountry(city) {
switch (city) {
case 'New York':
case 'Los Angeles':
case 'Chicago':
return 'USA';
case 'Bei Jing':
case 'Shang Hai':
case 'Xi An':
return 'China';
case 'London':
case 'Liverpool':
case 'Manchester':
return 'UK';
}
return 'Japan';
}
function getContinent(country) {
switch (country) {
case 'USA':
return 'North America';
case 'UK':
return 'Europe';
}
return 'Asia';
}
function TreeSlicerData(table, treeColumns) {
GC.Spread.Sheets.Slicers.TableSlicerData.call(this, table);
this.listeners = [];
this.suspended = false;
this.treeColumns = treeColumns;
this.lastFilterPath = [];
}
TreeSlicerData.prototype = GC.Spread.Sheets.Slicers.TableSlicerData.prototype;
TreeSlicerData.prototype.constructor = TreeSlicerData;
TreeSlicerData.prototype.buildDataTree = function() {
var treeData = (this.treeData = {});
this.build(treeData, this.treeColumns, 0, null);
};
TreeSlicerData.prototype.build = function(parentData, treeColumns, index, parentIndexes) {
var columnName = treeColumns[index];
var currentData;
var exclusiveIndexes = [];
var map = {};
if (!parentIndexes) {
var datas = this.getExclusiveData(columnName);
for (var k = 0; k < datas.length; k++) {
exclusiveIndexes.push(k);
map[k] = this.getRowIndexes(columnName, k);
}
} else {
for (var k = 0; k < parentIndexes.length; k++) {
var exclusivaIndex = this.getExclusiveRowIndex(columnName, parentIndexes[k]);
if (!map[exclusivaIndex]) {
map[exclusivaIndex] = [];
exclusiveIndexes.push(exclusivaIndex);
}
map[exclusivaIndex].push(parentIndexes[k]);
}
}
parentData.column = columnName;
if (!parentData.indexes) {
parentData.indexes = [];
}
for (var dateIndex = 0; dateIndex < exclusiveIndexes.length; dateIndex++) {
var exclusivaIndex = exclusiveIndexes[dateIndex];
var dataValue = this.getExclusiveData(columnName)[exclusivaIndex];
parentData.indexes.push(exclusivaIndex);
if (index + 1 < treeColumns.length) {
currentData = parentData[exclusivaIndex] = { indexes: [], value: dataValue };
this.build(currentData, treeColumns, index + 1, map[exclusivaIndex]);
} else {
currentData = parentData[exclusivaIndex] = map[exclusivaIndex];
currentData.value = dataValue;
}
}
};
TreeSlicerData.prototype.filter = function(path) {
this.suspended = true;
if (this.lastFilterPath) {
for (var i = 0; i < this.lastFilterPath.length; i++) {
this.doUnfilter(this.treeColumns[i]);
}
}
this.lastFilterPath = path;
var current = this.treeData;
for (var i = 0; i < path.length; i++) {
var exclusiveIndex = current.indexes ? current.indexes[path[i]] : path[i];
current = current[exclusiveIndex];
if (i === path.length - 1) {
this.suspended = false;
}
this.doFilter(this.treeColumns[i], { exclusiveRowIndexes: [ exclusiveIndex ] });
}
};
TreeSlicerData.prototype.clearFilter = function() {
this.suspended = true;
if (this.lastFilterPath) {
for (var i = 0; i < this.lastFilterPath.length; i++) {
if (i === this.lastFilterPath.length - 1) {
this.suspended = false;
}
this.doUnfilter(this.treeColumns[i]);
}
}
};
TreeSlicerData.prototype.onFiltered = function(filteredIndexes, isPreview) {
if (!this.suspended) {
for (var i = 0; i < this.listeners.length; i++) {
this.listeners[i].onFiltered({ columnIndexes: filteredIndexes, isPreview: isPreview });
}
}
};
TreeSlicerData.prototype.attachListener = function(listener) {
this.listeners.push(listener);
};
TreeSlicerData.prototype.dettachListener = function(listener) {
for (var i = 0; i < this.listeners.length; i++) {
if (this.listeners[i] === listener) {
this.listeners.splice(i);
break;
}
}
};
var root = null;
function TreeSlicer(slicerData, treeColumns) {
slicerData.buildDataTree();
this.data = slicerData;
this.slicerData = slicerData;
this.treeColumns = treeColumns;
this.treeDatas = slicerData.treeData;
this.slicerData.attachListener(this);
this.onDataLoaded();
}
TreeSlicer.prototype.constructor = TreeSlicer;
TreeSlicer.prototype.getDOMElement = function() {
return root;
};
TreeSlicer.prototype.onDataLoaded = function() {
var self = this;
var treeDatas = this.treeDatas;
var treeItems = (this.treeItems = {});
root = document.createElement('div');
root.innerHTML = '<span class="expanded"></span><span>All</span>';
treeItems.dom = root.children[1];
treeItems.allDoms = [ root.children[1] ];
treeItems.allIcons = [ root.children[0] ];
treeItems.dom.treeItem = treeItems;
self.addOneNode(treeDatas, root, treeItems, treeItems);
treeItems.allDoms[0].classList.add('treeSlicer_Item');
document.getElementById('slicer_Tree').addEventListener('mousemove',function(e){
var target = e.target;
if(target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed'){
self.hoverItem = target;
target.classList.add("hover");
}
if (self.hoverItem === target) {
return;
}
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
}
})
document.getElementById('slicer_Tree').addEventListener('mouseout',function(e){
var target = e.target;
if (self.hoverItem) {
self.hoverItem.classList.remove("hover");
self.hoverItem = null;
}
})
document.getElementById('slicer_Tree').addEventListener('click', function(e) {
var target = e.target;
var childTree = target.parentElement.children[2];
if(target.className == 'expanded'){
childTree.style.display='none';
target.classList.remove('expanded');
target.classList.add('collapsed');
}else if(target.className == 'collapsed'){
childTree.style.display='block';
target.classList.remove('collapsed');
target.classList.add('expanded');
}
})
document.getElementById('slicer_Tree').addEventListener('mousedown', function(e) {
var target = e.target;
if (target.tagName == 'SPAN' && target.className !== 'expanded' && target.className !== 'collapsed') {
if (self.activeItem === target) {
return;
}
if (self.activeItem) {
self.activeItem.classList.remove('active');
self.setSelect(self.activeItem.treeItem, false);
}
self.activeItem = target;
var treeItem = self.activeItem.treeItem;
self.setSelect(self.activeItem.treeItem, true);
target.classList.add('active');
if (treeItem === treeItems) {
self.data.clearFilter();
} else if (treeItem) {
var path = [ treeItem.index ];
treeItem = treeItem.parent;
while (treeItem && treeItem.parent) {
path.unshift(treeItem.index);
treeItem = treeItem.parent;
}
self.data.filter(path);
}
}
});
self.activeItem = treeItems.dom;
self.setSelect(treeItems, true);
treeItems.dom.classList.add('active');
};
TreeSlicer.prototype.setSelect = function(treeItem, isSelect) {
if (!treeItem) {
return;
}
if (isSelect) {
treeItem.dom.classList.add('select');
} else {
treeItem.dom.classList.remove('select');
}
for (var i = 0; i < treeItem.children.length; i++) {
this.setSelect(treeItem.children[i], isSelect);
}
};
TreeSlicer.prototype.addOneNode = function(treeDatas, parent, parentItem, rootItem) {
var indexes = treeDatas.indexes;
var current = document.createElement('ul');
parent.appendChild(current);
parentItem.children = [];
var currentItem;
if (indexes) {
for (var i = 0; i < indexes.length; i++) {
var childData = treeDatas[indexes[i]];
var value = childData.value;
var childDom = this.addItem(current, value, parentItem, i, rootItem, false);
currentItem = childDom.children[1].treeItem;
this.addOneNode(childData, childDom, currentItem, rootItem);
}
} else {
parent.children[0].classList.remove('expanded');
}
};
TreeSlicer.prototype.addItem = function(current, value, parentItem, index, rootItem, isLeaf) {
var childDom = document.createElement('li');
if (isLeaf) {
childDom.innerHTML = '<span></span><span>' + value + '</span>';
} else {
childDom.innerHTML = '<span class="expanded"></span><span>' + value + '</span>';
}
current.appendChild(childDom);
var content = childDom.children[1];
rootItem.allDoms.push(content);
rootItem.allIcons.push(childDom.children[0]);
var item = { dom: content, parent: parentItem, index: index };
parentItem.children.push(item);
content.treeItem = item;
parentItem.children.push();
if (isLeaf) {
current.style.display = 'none';
}
return childDom;
};
TreeSlicer.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="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial" style="height:100%">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p class="desc">Click on different items in this tree to filter by those items.</p>
<div style="height: 440px;position: relative">
<div id="slicer_Tree"></div>
</div>
</div>
</div>
</body>
</html>
.hover {
background-color: lightgray;
font-weight: 700 !important;
}
.select {
font-weight: 700 !important;
color: blue;
}
.active {
font-weight: 700 !important;
color: red;
}
.treeSlicer_Item {
cursor: pointer;
}
.expanded {
background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAR0lEQVRYR+3UUQ0AIAwD0Zsi/LsYiiDDRPm4GWjzkrUIX4XzsYACCiigwAg0sEKT3FPghMJfrAUU+EIg+YbbJVRAAQUUUOAC99IJunjjuhUAAAAASUVORK5CYII=);
background-repeat: no-repeat;
background-position: center;
background-size: 10px 10px;
background-color: #d3d3d3;
height: 16px;
width: 16px;
float: left;
cursor: pointer;
}
.collapsed {
background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAaklEQVRYR+2WSwrAIBQD44na3v8SeqIWodsq000/jFuT8BgwvhJ+apLlwtbvNhJZiPjU7hMPykRiB5CABCTwWwKjbr/R1MhSexXPuh0lUrEDSOAVBJ58hs2NSAISkIAEPklg9He0JCvZig6rOyO69wJo3QAAAABJRU5ErkJggg==);
background-repeat: no-repeat;
background-position: center;
background-size: 10px 10px;
background-color: #d3d3d3;
height: 16px;
width: 16px;
float: left;
cursor: pointer;
}
li {
list-style: none;
}
.desc{
padding:2px 10px;
background-color:#F4F8EB;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: auto;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
span {
line-height: 18px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
