Student Slicer

You can customize the UI of your slicer with standard DOM elements or 3rd party components.

Here we show you a student slicer which uses noUiSlider on the left.
window.onload = function() { var columnNames = [ 'Name', 'Class', 'Gender', 'Math', 'English', 'Total Score' ]; var datas = [ [ 'Student1', 1, 'female', '69', '66', '135' ], [ 'Student2', 1, 'female', '99', '85', '184' ], [ 'Student3', 1, 'male', '78', '77', '155' ], [ 'Student4', 1, 'male', '54', '80', '134' ], [ 'Student7', 2, 'male', '87', '98', '185' ], [ 'Student8', 2, 'male', '78', '85', '163' ], [ 'Student9', 2, 'female', '100', '90', '190' ], [ 'Student10', 2, 'female', '68', '54', '122' ], [ 'Student11', 2, 'female', '97', '100', '197' ], [ 'Student12', 2, 'female', '81', '90', '171' ], [ 'Student15', 2, 'female', '90', '68', '158' ], [ 'Student16', 3, 'male', '86', '70', '156' ], [ 'Student17', 3, 'male', '88', '89', '177' ], [ 'Student18', 3, 'male', '54', '80', '134' ], [ 'Student19', 3, 'male', '81', '75', '156' ], [ 'Student23', 3, 'female', '78', '98', '176' ], [ 'Student24', 3, 'female', '90', '98', '188' ], [ 'Student25', 3, 'male', '60', '30', '90' ], [ 'Student26', 3, 'female', '0', '0', '0' ], [ 'Student27', 3, 'female', '100', '100', '200' ], [ 'Student28', 3, 'male', '89', '78', '167' ] ]; init(datas, columnNames); // create GeneralSlicerData var dataSource = new GC.Spread.Slicers.GeneralSlicerData(datas, columnNames); // attach slicer to GeneralSlicerData. var genderSlicer = new StudentSlicer(document.getElementById('slicer_Gender')); genderSlicer.setData(dataSource, 'Gender'); var classSlicer = new StudentSlicer(document.getElementById('slicer_Class')); classSlicer.setData(dataSource, 'Class'); var mathSlicer = new ScoreSlicer(document.getElementById('slicer_Math')); mathSlicer.setData(dataSource, 'Math'); var enSlicer = new ScoreSlicer(document.getElementById('slicer_Englise')); enSlicer.setData(dataSource, 'English'); var totalSlicer = new ScoreSlicer(document.getElementById('slicer_Total')); totalSlicer.setData(dataSource, 'Total Score'); }; // add data source function init(datas, columnNames) { var table = document.createElement('table'); table.setAttribute('border', 1); table.setAttribute('cellpadding', 0); table.setAttribute('cellspacing', 0); var tableStr = '<thead><tr>'; for (var i = 0; i < columnNames.length; i++) { tableStr += '<th>' + columnNames[i] + '</th>'; } tableStr += '</tr></thead><tbody>'; for (var i = 0; i < datas.length; i++) { tableStr += '<tr>'; for (var j = 0; j < datas[i].length; j++) { tableStr += '<td>' + datas[i][j] + '</td>'; } tableStr += '</tr>'; } tableStr += '</tbody>'; table.innerHTML = tableStr; document.getElementById('ss').appendChild(table); } // create custom slicer function StudentSlicer(container) { this.container = container; } StudentSlicer.prototype.constructor = StudentSlicer; StudentSlicer.prototype.setData = function(slicerData, columnName) { this.slicerData = slicerData; this.columnName = columnName; this.data = slicerData.getData(columnName); this.exclusiveDatas = slicerData.getExclusiveData(columnName); this.slicerData.attachListener(this); this.onDataLoaded(); }; StudentSlicer.prototype.onDataLoaded = function() { var self = this, name = self.columnName; // get column data and build slicer UI. var datas = self.exclusiveDatas, domString = '<strong>' + name + ':</strong><br>'; for (var i = 0; i < datas.length; i++) { var id = name + (i + 1); domString +='<input type="checkbox" id="' +id +'" name="' +name +'" value="' +datas[i] +'" >' +'<label for="' +id +'">' +datas[i] +'</label>'; } self.container.innerHTML = domString; document.querySelector('.options-container').addEventListener('change', function() { var slicer = self, exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName), children = this.querySelectorAll('input'), indexes = []; for (var i = 0, length = children.length; i < length; i++) { if (children[i].checked) { var value = children[i].value; if (!isNaN(parseInt(value))) { value = parseInt(value); } if (exclusiveData.indexOf(value) != -1) { indexes.push(exclusiveData.indexOf(value)); } } } // respond UI event and invoke doFilter function and doUnfilter function. if (indexes.length === 0) { slicer.slicerData.doUnfilter(slicer.columnName); } else { slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes }); } }); }; // get the filtered result and update slicer UI. StudentSlicer.prototype.onFiltered = function() { var slicerdata = this.slicerData; var filteredRowIndexs = slicerdata.getFilteredRowIndexes(); var trs = document.getElementById('ss').querySelectorAll('tr'); for (var i = 0; i < slicerdata.data.length; i++) { if (filteredRowIndexs.indexOf(i) !== -1) { trs[i + 1].style.display = ''; } else { trs[i + 1].style.display = 'none'; } } }; function ScoreSlicer(container) { this.container = container; } ScoreSlicer.prototype.constructor = StudentSlicer; ScoreSlicer.prototype.setData = function(slicerData, columnName) { this.slicerData = slicerData; this.columnName = columnName; this.data = slicerData.getData(columnName); this.exclusiveDatas = slicerData.getExclusiveData(columnName); this.slicerData.attachListener(this); this.onDataLoaded(); }; ScoreSlicer.prototype.onDataLoaded = function() { var self = this; var connectSlider = document.createElement('div'); connectSlider.style.width = '120px'; connectSlider.style.marginTop = '16px'; connectSlider.style.marginLeft = '10px'; var max = 100; if (self.columnName === 'Total Score') { max = 200; } noUiSlider.create(connectSlider, { start: [ 0, 200 ], connect: false, range: { min: 0, max: max } }); var connectBar = document.createElement('div'), connectBase = connectSlider.getElementsByClassName('noUi-base')[0]; // Give the bar a class for styling and add it to the slider. connectBar.className += 'connect'; connectBase.appendChild(connectBar); var span = document.createElement('span'); span.innerHTML = self.columnName; self.container.appendChild(span); var $textSpan = document.createElement('span'); self.container.appendChild($textSpan); self.container.appendChild(connectSlider); var connectHandles = connectSlider.getElementsByClassName('noUi-origin'); connectSlider.noUiSlider.on('update', function(values, handle) { // Pick left for the first handle, right for the second. var side = handle ? 'right' : 'left', // Get the handle position and trim the '%' sign. offset = connectHandles[handle].style.left.slice(0, -1); // Right offset is 100% - left offset if (handle === 1) { offset = 100 - offset; } connectBar.style[side] = offset + '%'; var min = parseInt(values[0]), max = parseInt(values[1]); $textSpan.innerText = min + '-' + max; var slicer = self, exclusiveData = self.slicerData.getExclusiveData(slicer.columnName), indexes = []; for (var i = 0, length = exclusiveData.length; i < length; i++) { if (exclusiveData[i] >= min && exclusiveData[i] <= max) { indexes.push(i); } } slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes }); }); }; ScoreSlicer.prototype.onFiltered = function() { var slicerdata = this.slicerData; var filteredRowIndexs = slicerdata.getFilteredRowIndexes(); var trs = document.getElementById('ss').querySelectorAll('tr'); for (var i = 0; i < slicerdata.data.length; i++) { if (filteredRowIndexs.indexOf(i) !== -1) { trs[i + 1].style.display = ''; } else { trs[i + 1].style.display = 'none'; } } };
<!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"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/spread/source/css/external/noUISlider/nouislider.min.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/external/noUISlider/nouislider.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"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <p class="desc">Try changing these options to change the filter on the table.</p> <div id="slicer_Gender" class="control"></div> <div id="slicer_Class" class="control"></div> <div id="slicer_Math" class="control"></div> <div id="slicer_Englise" class="control"></div> <div id="slicer_Total" class="control"></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; } .option-group { margin-bottom: 6px; } .control { padding-bottom: 24px; } .noUi-origin { right: auto; width: 0; } .connect { position: absolute; top: 0; bottom: 0; background: #80C9F5; box-shadow: inset 0 0 3px rgba(51, 51, 51, 0.45); } .noUi-state-tap .connect { -webkit-transition: left 300ms, right 300ms; transition: left 300ms, right 300ms; } strong { display: inline-block; margin-bottom: 10px; } input + label { margin-right: 12px; } table th, table td { padding: 4px 8px; } .desc { padding:2px 10px; margin-top: 0; background-color:lavender; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }