Table Slicer Data

Besides GeneralSlicerData, SpreadJS also provides TableSlicerData. The data source of TableSlicerData is a SpreadJS SheetTable. You must set a SheetTable to TableSlicerData's constructor if you want to use it.

<p>The information for rowFilter and the checked state on the filter dialog of SheetTable are synchronized with the filter result of Slicers attached with TableSlicerData. All of the slicers (attached with TableSlicerData) receive the onFilter notice and get the filtered result after the SheetTable filters completely with code or the filter dialog.</p> <p>You can use TableSlicerData as follows:</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// Get table in current sheet</span> <span class="hljs-keyword">var</span> table = sheet.tables.findByName(<span class="hljs-string">'table1'</span>); <span class="hljs-comment">// Get TableSlicerData from table</span> <span class="hljs-keyword">var</span> slicerData = table.getSlicerData(); <span class="hljs-comment">// Create a custom slicer and add it to dom tree</span> <span class="hljs-keyword">var</span> slicer1 = <span class="hljs-keyword">new</span> CustomSlicer(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'cityContainer'</span>)); <span class="hljs-comment">// Set column data to slicer </span> slicer1.setData(slicerData, <span class="hljs-string">'City'</span>); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); var sheet = spread.getActiveSheet(); // Define data source. var columnNames = ["Name", "Sex", "City", "Birthday"], data = [ ["Bob", "Man", "NewYork", "1968/06/08"], ["Betty", "Woman", "Washington", "1972/07/03"], ["Alice", "Woman", "Atlanta", "1964/03/02"], ["Tom", "Man", "Houston", "1986/12/03"], ["Jenny", "Woman", "Washington", "1956/10/13"], ["Nacy", "Woman", "NewYork", "1989/01/14"], ["John", "Man", "Houston", "1995/01/01"], ["Mark", "Man", "Atlanta", "1965/11/11"], ["Susan", "Woman", "Atlanta", "1983/07/08"]]; // Create a table. var table = sheet.tables.addFromDataSource("table1", 1, 1, data); table.setColumnName(0, columnNames[0]); table.setColumnName(1, columnNames[1]); table.setColumnName(2, columnNames[2]); table.setColumnName(3, columnNames[3]); sheet.getRange(-1, 1, -1, 6).width(80); // Get TableSlicerData from table. var slicerData = table.getSlicerData(); // Create a custom slicer and add it to dom tree. var slicer1 = new CustomSlicer(document.getElementById("cityContainer"),'City'); slicer1.setData(slicerData, "City"); var slicer2 = new CustomSlicer(document.getElementById("sexContainer"),'Sex'); slicer2.setData(slicerData, "Sex"); }; // Define custom slicer. function CustomSlicer(container,name) { this.container = container; this.name = name; this.slicerData = null; this.columnName = null; } CustomSlicer.prototype.setData = function (slicerData, columnName) { this.slicerData = slicerData; this.columnName = columnName; this.slicerData.attachListener(this); this.onDataLoaded(); } CustomSlicer.prototype.onDataLoaded = function () { var columnName = this.columnName, exclusiveData = this.slicerData.getExclusiveData(columnName); // Create slicer dom tree. var strong = document.createElement('strong'); strong.innerText = this.name+':'; var br = document.createElement('br'); this.container.appendChild(strong); this.container.appendChild(br); var domString = "", id; var div = document.createElement('div'); div.setAttribute('class','option-group') for (var i = 0; i < exclusiveData.length; i++) { id = columnName + (i + 1); domString += '<input type="checkbox" name="' + columnName + '" value="' + exclusiveData[i] + '" id="' + id + '" checked>'; domString += '<label for="' + id + '">' + exclusiveData[i] + '</label></br>'; } div.innerHTML= domString; this.container.appendChild(div); // Attach events to dom element. var self = this; document.querySelector('.options-container').addEventListener('change',function (e) { // Invoke getExclusiveData method to get exclusive data from slicerData. var exclusiveData = self.slicerData.getExclusiveData(self.columnName); // parent = this.parentNode.parentNode; items = document.querySelectorAll('.options-container input'); indexes = []; for (var i = 0, length = items.length; i < length; i++) { if (items[i].checked) { var value = items[i].value; if (!isNaN(parseInt(value))) { value = parseInt(value); } if(exclusiveData.indexOf(value) != -1){ indexes.push(exclusiveData.indexOf(value)) } } } if (indexes.length === 0) { // Invoke doUnfilter method when all item are not selected. self.slicerData.doUnfilter(self.columnName); } else { // Invoke doFilter method when any item is selected. self.slicerData.doFilter(self.columnName, { exclusiveRowIndexes: indexes }); } }); };
<!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"> <div id="cityContainer" class="sample-group"></div> <div id="sexContainer" class="sample-group"></div> </div> </div> </body> </html>
.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; } label { display: inline-block; margin: 6px 0; } strong { display: inline-block; margin: 12px 0; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }