カスタムTableSlicerDataを作成します。
TableSlicerData APIを継承して独自処理に必要なロジックを実装します。
必要に応じてTableSlicerData APIを呼び出す独自処理を実装します。
スライサーを作成します。
スライサーUIを作成しdoFilter処理を実装します。
Spreadにデータを追加し、テーブルを作成します。
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="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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">ツリー内の項目をクリックすると、選択した項目に基づいたフィルタリングが行われます。</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;
}
