Filter Dialog

The filter dialog is display filter details in it. Click a filter arrow will pop the filter dialog and select options filter the data.

<p>Use the Filter or Table object's <strong>filterButtonVisible</strong> method to get or set whether the filter buttons are displayed. This allows you to control what you want to allow users to filter without completely getting rid of filters, such as when you have user identification in your application, and only certain users can filter specific columns or rows.</p> <p>The filterButtonVisible method's get and set data depends on the following arguments:</p> <ul> <li><strong>No arguments: Get whether filter buttons are displayed. True if one is visible; otherwise, false.</strong></li> <li><strong>1 argument: If it's a number, then use it as a column index that returns whether the corresponding column's filter button is displayed. If it's a boolean, set all filter buttons to the specified value.</strong></li> <li><strong>2 arguments: The first argument is the column index and the second one is the value. Sets whether the corresponding column's filter button is displayed.</strong></li> </ul> <pre><code class="hljs js language-js"> <span class="hljs-comment">// Hide all filter buttons</span> filter.filterButtonVisible(<span class="hljs-literal">false</span>); <span class="hljs-comment">// table.filterButtonVisible(false);</span> <span class="hljs-comment">// Show all filter buttons</span> filter.filterButtonVisible(<span class="hljs-literal">true</span>); <span class="hljs-comment">// table.filterButtonVisible(true);</span> <span class="hljs-comment">// Hide special column filter button</span> filter.filterButtonVisible(colIndex, <span class="hljs-literal">false</span>); <span class="hljs-comment">// table.filterButtonVisible(tableColumnIndex, false);</span> <span class="hljs-comment">// Get special column filter button visible</span> filter.filterButtonVisible(colIndex); <span class="hljs-comment">// table.filterButtonVisible(tableColumnIndex);</span> </code></pre> <p>The filter dialog container contains sortByValue, sortByColor, filterByColor, filterByValue and listFilterArea. and the <strong>filterDialogVisibleInfo</strong> method to get or set whether this options are displayed.</p> <pre><code class="hljs js language-js"> filter.filterDialogVisibleInfo({ <span class="hljs-attr">sortByValue</span>: <span class="hljs-literal">false</span>, <span class="hljs-comment">//SortByValue item is visible.</span> <span class="hljs-attr">sortByColor</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">//SortByColor item is visible.</span> <span class="hljs-attr">filterByColor</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">//FilterByColor item is visible.</span> <span class="hljs-attr">filterByValue</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">//FilterByValue item is visible.</span> <span class="hljs-attr">listFilterArea</span>: <span class="hljs-literal">false</span> <span class="hljs-comment">//ListFilterArea item is visible.</span> }); </code></pre> <p><strong>Enhanced functionality</strong>: Add Current Selection To Filter</p> <p>Let's say you've filtered it once, and you've filtered some of it out.If you check "add current selection to filter" during your second filter, your second filter will be displayed at the same time as your first filter, instead of clearing the first filter to show only the second filter.</p>
var spreadNS = GC.Spread.Sheets; var salesData = [ ["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"], ["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26], ["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99], ["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141], ["Erich", new Date("1994/05/23"), "West", 410, 0.12, 49.2], ["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120], ["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135], ["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110], ["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2], ["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76], ["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35]]; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.options.allowCellOverflow = true; sheet.name("FilterDialog"); sheet.setArray(1, 1, salesData); var filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(2, 1, salesData.length - 1, salesData[0].length)); sheet.rowFilter(filter); prepareFilterItems(sheet, salesData[0]); sheet.defaults.rowHeight = 28; sheet.setColumnWidth(1, 110); sheet.setColumnWidth(2, 80); sheet.setColumnWidth(3, 100); sheet.setColumnWidth(4, 80); sheet.setColumnWidth(5, 80); sheet.setColumnWidth(6, 80); sheet.getRange(2, 2, 10, 1).formatter("yyyy/mm/dd"); var SpreadNS = GC.Spread.Sheets; var ComparisonOperators = SpreadNS.ConditionalFormatting.ComparisonOperators; var equalsTo = ComparisonOperators.equalsTo; var range = sheet.getRange(1, 1, 11, 6); range.setBorder(new spreadNS.LineBorder("gray", spreadNS.LineStyle.thin), {all: true}); var ranges = [new SpreadNS.Range(2, 3, 10, 1)]; var style1 = new SpreadNS.Style(); style1.foreColor = "Accent 2"; var rule1 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, equalsTo, "West", ""); sheet.conditionalFormats.addRule(rule1); var style2 = new SpreadNS.Style(); style2.foreColor = "Accent 3"; var rule2 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, equalsTo, "East", ""); sheet.conditionalFormats.addRule(rule2); var style3 = new SpreadNS.Style(); style3.foreColor = "Accent 6"; var rule3 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, equalsTo, "North", ""); sheet.conditionalFormats.addRule(rule3); var style4 = new SpreadNS.Style(); style4.foreColor = "Accent 1"; var rule4 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style4, equalsTo, "South", ""); sheet.conditionalFormats.addRule(rule4); var ranges = [new SpreadNS.Range(2, 2, 10, 1)]; var style1 = new SpreadNS.Style(); style1.backColor = "rgb(241, 135, 102)"; var rule1 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, ComparisonOperators.lessThan, "1990/01/01", ""); sheet.conditionalFormats.addRule(rule1); var style2 = new SpreadNS.Style(); style2.backColor = "lightGreen"; var rule2 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, ComparisonOperators.between, "1990/01/01", "2000/01/01"); sheet.conditionalFormats.addRule(rule2); var style3 = new SpreadNS.Style(); style3.backColor = "deepSkyBlue"; var rule3 = new SpreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, ComparisonOperators.greaterThan, "2000/01/01", ""); sheet.conditionalFormats.addRule(rule3); sheet.resumePaint(); var sheet2 = spread.sheets[1]; initOultineColumnFilter(sheet2); sheet2.name("outlineColumnFilter"); _getElementById("showAll").addEventListener('click',function () { if (filter) { filter.filterButtonVisible(true); var checkBoxs = document.querySelectorAll("#tableColumnsContainer input[type='checkbox']") for(var i=0;i<checkBoxs.length;i++){ checkBoxs[i].checked = true; } } }); _getElementById("hideAll").addEventListener('click',function () { if (filter) { filter.filterButtonVisible(false); var checkBoxs = document.querySelectorAll("#tableColumnsContainer input[type='checkbox']") for(var i=0;i<checkBoxs.length;i++){ checkBoxs[i].checked = false; } } }); _getElementById("sortByValue").addEventListener('change',function () { var val = this.checked; filter.filterDialogVisibleInfo({sortByValue:val}); }); _getElementById("sortByColor").addEventListener('change',function () { var val = this.checked; filter.filterDialogVisibleInfo({sortByColor:val}); }); _getElementById("filterByColor").addEventListener('change',function () { var val = this.checked; filter.filterDialogVisibleInfo({filterByColor:val}); }); _getElementById("filterByValue").addEventListener('change',function () { var val = this.checked; filter.filterDialogVisibleInfo({filterByValue:val}); }); _getElementById("listFilterArea").addEventListener('change',function () { var val = this.checked; filter.filterDialogVisibleInfo({listFilterArea:val}); }); } function prepareFilterItems(sheet, headers) { var container = _getElementById("tableColumnsContainer"), items = []; var filter = sheet.rowFilter(), range = filter.range, startColumn = range.col; for (var c = 0, length = headers.length; c < length; c++) { var name = headers[c]; items.push('<div><input type="checkbox" checked id="@" style="margin-right: 6px"><label for="@" style="margin-right: 20px">'.replace(/@/g, (startColumn + c) + '_' + name) + name + '</label></div>'); } _getElementById("tableColumnsContainer").innerHTML = items.join(""); var inputs = document.querySelectorAll("#tableColumnsContainer input"); for(var i=0;i<inputs.length;i++){ inputs[i].style.marginRight = "6px"; } var labels = document.querySelectorAll("#tableColumnsContainer label"); for(var i=0;i<labels.length;i++){ labels[i].style.marginRight = "20px"; } var checkBoxs = document.querySelectorAll("#tableColumnsContainer input[type='checkbox']"); for(var i=0;i<checkBoxs.length;i++){ checkBoxs[i].addEventListener('change',function () { var id = this.id, checked = this.checked, index = +id.substr(0, id.indexOf("_")); if (filter) { filter.filterButtonVisible(index, checked); } }); } } function initOultineColumnFilter(sheet) { sheet.setColumnWidth(2, 120); sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(-1, 0, -1, 1))); sheet.suspendPaint(); sheet.setColumnWidth(0, 200); sheet.setRowCount(12); sheet.outlineColumn.options({ columnIndex: 0, showIndicator: true, }); var sd = data; sheet.setDataSource(sd); sheet.bindColumn(0, "name"); sheet.setColumnCount(3); sheet.setColumnWidth(0, 300); for (var r = 0; r < sd.length; r++) { var level = sd[r].level; sheet.getCell(r, 0).textIndent(level); } sheet.showRowOutline(false); sheet.outlineColumn.refresh(); sheet.resumePaint(); } function _getElementById(id){ return document.getElementById(id); }
<!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="$DEMOROOT$/spread/source/data/outlineColumn.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"> <div class="option-group"> <input id="showAll" type="button" value="Show All" title="Show all filter buttons of the table"/> <input id="hideAll" type="button" value="Hide All" title="Hide all filter buttons of the table"/> </div> <div class="option-group"> <h4>Show filter buttons:</h4> <span id="tableColumnsContainer"></span> <h4>Filter dialog visible info:</h4> <div><input type="checkbox" checked id="sortByValue" style="margin-right: 6px"><label for="sortByValue" style="margin-right: 20px">ShowSortByValue</label></div> <div><input type="checkbox" checked id="sortByColor" style="margin-right: 6px"><label for="sortByColor" style="margin-right: 20px">ShowSortByColor</label></div> <div><input type="checkbox" checked id="filterByColor" style="margin-right: 6px"><label for="filterByColor" style="margin-right: 20px">ShowFilterByColor</label></div> <div><input type="checkbox" checked id="filterByValue" style="margin-right: 6px"><label for="filterByValue" style="margin-right: 20px">ShowFilterByValue</label></div> <div><input type="checkbox" checked id="listFilterArea" style="margin-right: 6px"><label for="listFilterArea" style="margin-right: 20px">ShowListFilterArea</label></div> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; 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; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }