Item Slicer

SpreadJS also provides an ItemSlicer. ItemSlicer is a filter UI. It provides a convenient way to filter the data.

<p>ItemSlicer is a slicer component and is independent from the sheet. You can add the ItemSlicer anywhere.</p> <p>ItemSlicer should be used with slicer data (such as GeneralSlicerData, TableSlicerData, or slicer data that extends from <strong>GeneralSlicerData</strong>). If you use ItemSlicer with TableSlicerData, the table can also be synchronized when filtered by ItemSlicer.</p> <p>You can create your own ItemSlicer as follows:</p> <p>Create data source and a new GeneralSlicerData.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> columnNames = [<span class="hljs-string">'Name'</span>, <span class="hljs-string">'Class'</span>, <span class="hljs-string">'Gender'</span>, <span class="hljs-string">'Math'</span>, <span class="hljs-string">'English'</span>, <span class="hljs-string">'Total Score'</span>]; <span class="hljs-keyword">var</span> data = [[<span class="hljs-string">'Student1'</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'female'</span>, <span class="hljs-string">'69'</span>, <span class="hljs-string">'66'</span>, <span class="hljs-string">'135'</span>], ... [<span class="hljs-string">'Student5'</span>, <span class="hljs-number">3</span>, <span class="hljs-string">'male'</span>, <span class="hljs-string">'60'</span>, <span class="hljs-string">'30'</span>, <span class="hljs-string">'90'</span>] ]; <span class="hljs-keyword">var</span> slicerData = <span class="hljs-keyword">new</span> GC.Spread.Slicers.GeneralSlicerData(data, columnNames); </code></pre> <p>Get column data and build UI.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> tableStr = <span class="hljs-string">"&lt;table border='1' cellpadding='0' cellspacing='0'&gt;&lt;tr&gt;"</span>; <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; columnNames.length; i++) { tableStr += <span class="hljs-string">"&lt;th&gt;"</span> + columnNames[i] + <span class="hljs-string">"&lt;/th&gt;"</span>; } ... tableStr += <span class="hljs-string">'&lt;/table&gt;'</span>; $listTable = $(tableStr); $(<span class="hljs-string">'#container'</span>).append($listTable); </code></pre> <p>Get the filtered result and update UI.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> filteredRowIndexs = slicerData.getFilteredRowIndexes(); <span class="hljs-keyword">var</span> trs = $listTable.find(<span class="hljs-string">'tr'</span>); <span class="hljs-keyword">for</span> (<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span>; i &lt; slicerData.data.length; i++) { <span class="hljs-keyword">if</span> (filteredRowIndexs.indexOf(i) !== <span class="hljs-number">-1</span>) { $(trs[i+<span class="hljs-number">1</span>]).show(); } <span class="hljs-keyword">else</span> { $(trs[i+<span class="hljs-number">1</span>]).hide(); } } </code></pre> <p>Create an ItemSlicer, and attach it to GeneralSlicerData then append it to the DOM tree. You can also adjust the size of ItemSlicer. Now you can easily filter data with the ItemSlicer, enjoy it :-)</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> genderSlicer = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Slicers.ItemSlicer(<span class="hljs-string">'Gender'</span>, slicerData, <span class="hljs-string">'Gender'</span>); $(<span class="hljs-string">'#genderSlicerHost'</span>).append(genderSlicer.getDOMElement()); genderSlicer.height(<span class="hljs-number">200</span>); genderSlicer.width(<span class="hljs-number">200</span>); </code></pre>
window.onload = function () { var columnNames = ["Name", "Class", "Gender", "Math", "English", "Total Score"]; var data = [["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"] ]; var slicerData = new GC.Spread.Slicers.GeneralSlicerData(data, columnNames); var onFiltered = slicerData.onFiltered; slicerData.onFiltered = function () { onFiltered.call(slicerData); refreshList(slicerData); } var nameSlicer = new GC.Spread.Sheets.Slicers.ItemSlicer("Name", slicerData, "Name"); nameSlicer.height(200); nameSlicer.width(180) nameSlicer.columnCount(2); document.getElementById('nameSlicerHost').appendChild(nameSlicer.getDOMElement()); var classSlicer = new GC.Spread.Sheets.Slicers.ItemSlicer("Class", slicerData, "Class"); classSlicer.height(200); classSlicer.width(180) document.getElementById('classSlicerHost').appendChild(classSlicer.getDOMElement()); initList(data, columnNames); }; function initList(data, columnNames) { var tableStr = '<tr>'; for (var i = 0; i < columnNames.length; i++) { tableStr += "<th>" + columnNames[i] + "</th>"; } tableStr += '</tr>'; for (var i = 0; i < data.length; i++) { tableStr += "<tr>"; for (var j = 0; j < data[i].length; j++) { tableStr += "<td>" + data[i][j] + "</td>"; } tableStr += "</tr>"; } var table = document.createElement('table'); table.innerHTML = tableStr; table.border = 1; table.cellPadding = 0; table.cellSpacing = 0; document.getElementById('ss').appendChild(table); } function refreshList(slicerData) { var filteredRowIndexs = slicerData.getFilteredRowIndexes(); var trs = document.getElementsByTagName('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"> <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"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <p class="desc">Click on the items in this slicer to filter by that class. You can Ctrl+Left Click to select multiple items.</p> <div id="nameSlicerHost" class="slicer"></div> <div id="classSlicerHost" class="slicer"></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-row { font-size: 14px; padding: 5px; margin-top: 10px; } .slicer { height: 220px; position: relative; } table th, table td { padding: 4px 8px; } .desc{ padding:2px 10px; background-color:lavender; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }