Tree Slicer

You can customize TableSlicerData to meet your needs. Here is how to use a tree slicer:

<p>(1) Create a custom TableSlicerData.</p> <pre><code class="hljs js language-js"> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">TreeSlicerData</span>(<span class="hljs-params">table, treeColumns</span>) </span>{ GC.Spread.Sheets.Slicers.TableSlicerData.call(<span class="hljs-keyword">this</span>, table); <span class="hljs-keyword">this</span>.listeners = []; <span class="hljs-keyword">this</span>.suspended = <span class="hljs-literal">false</span>; ... } TreeSlicerData.prototype = GC.Spread.Sheets.Slicers.TableSlicerData.prototype; TreeSlicerData.prototype.constructor = TreeSlicerData; </code></pre> <p>(2) Inherit the TableSlicerData API function.</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// inherit attachListener function of TableSlicerData</span> TreeSlicerData.prototype.attachListener = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">listener</span>) </span>{ <span class="hljs-comment">// Some code omitted here, you can see it on the right.</span> ... }; <span class="hljs-comment">// inherit dettachListener function of TableSlicerData</span> TreeSlicerData.prototype.dettachListener = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">listener</span>) </span>{ ... }; </code></pre> <p>(3) Create your function to invoke related TableSlicerData API function.</p> <pre><code class="hljs js language-js"> TreeSlicerData.prototype.filter = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">path</span>) </span>{ <span class="hljs-keyword">this</span>.suspended = <span class="hljs-literal">true</span>; <span class="hljs-keyword">if</span> (<span class="hljs-keyword">this</span>.lastFilterPath) { <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; <span class="hljs-keyword">this</span>._lastFilterPath.length; i++) { <span class="hljs-comment">// invoke TableSlicerData API function</span> <span class="hljs-keyword">this</span>.doUnfilter(<span class="hljs-keyword">this</span>.treeColumns[i]); } } <span class="hljs-keyword">this</span>.lastFilterPath = path; <span class="hljs-keyword">var</span> current = <span class="hljs-keyword">this</span>.treeData; <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; path.length; i++) { ... <span class="hljs-comment">// invoke TableSlicerData API function</span> <span class="hljs-keyword">this</span>.doFilter(<span class="hljs-keyword">this</span>.treeColumns[i], { <span class="hljs-attr">exclusiveRowIndexes</span>: [exclusiveIndex] }); } }; </code></pre> <p>(4) Create your slicer which relates to your TableSlicerData.</p> <pre><code class="hljs js language-js"> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">TreeSlicer</span>(<span class="hljs-params">slicerData, treeColumns</span>) </span>{ slicerData.buildDataTree(); <span class="hljs-keyword">this</span>.data = slicerData; <span class="hljs-keyword">this</span>.slicerData = slicerData; <span class="hljs-keyword">this</span>.treeColumns = treeColumns; <span class="hljs-keyword">this</span>.treeDatas = slicerData.treeData; <span class="hljs-keyword">this</span>.slicerData.attachListener(<span class="hljs-keyword">this</span>); <span class="hljs-keyword">this</span>.onDataLoaded(); } TreeSlicer.prototype.constructor = TreeSlicer; </code></pre> <p>(5) Build slicer UI and invoke the doFilter function.</p> <pre><code class="hljs js language-js"> TreeSlicer.prototype.onDataLoaded = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params"></span>) </span>{ ... $(treeItems.allDoms).addClass(<span class="hljs-string">'treeSlicer_Item'</span>).mousedown(<span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">obj</span>) </span>{ ... if (treeItem === treeItems) { self.data.clearFilter(); <span class="hljs-comment">// invoke TableSlicerData function</span> } <span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (treeItem) { <span class="hljs-keyword">if</span> (treeItem === treeItems) { ... self.data.filter(path); <span class="hljs-comment">// invoke TableSlicerData function</span> } }); ... }; </code></pre> <p>(6) Add data to spread and create a table.</p> <pre><code class="hljs js language-js"> spread = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Workbook(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'ss'</span>)); <span class="hljs-keyword">var</span> sheet = spread.getActiveSheet(); sheet.suspendPaint(); <span class="hljs-comment">// add data to sheet</span> ... sheet.tables.add(<span class="hljs-string">'table1'</span>, <span class="hljs-number">0</span>, <span class="hljs-number">0</span>, <span class="hljs-number">6</span>, <span class="hljs-number">5</span>); sheet.resumePaint(); </code></pre> <p>(7) Create your TableSlicerData and attach it to your slicer. Add slicer to DOM tree.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> table = sheet.tables.find(<span class="hljs-number">0</span>, <span class="hljs-number">0</span>); <span class="hljs-keyword">var</span> dataSource = <span class="hljs-keyword">new</span> TreeSlicerData(table, [<span class="hljs-string">'Continent'</span>, <span class="hljs-string">'Country'</span>, <span class="hljs-string">'City'</span>]); <span class="hljs-keyword">var</span> treeSlicer = <span class="hljs-keyword">new</span> TreeSlicer(dataSource, [<span class="hljs-string">'Continent'</span>, <span class="hljs-string">'Country'</span>, <span class="hljs-string">'City'</span>]); <span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'slicer_Tree'</span>).appendChild(treeSlicer.getDOMElement()); </code></pre>
window.onload = function() { spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss')); var sheet = (window.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 = {}); console.log(treeDatas); 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 charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.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:lavender; } 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; }