Custom Filters

After you set the row filter, you can customize it. This can be useful when certain criteria is intended to change how filters work, and you can just customize the filter to fit the requirements of your application.

<p>You can use the <strong>filterButtonVisible</strong> method to get or set whether the filter buttons are displayed. For example:</p> <pre><code class="hljs">var range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.<span class="hljs-constructor">Range(-1, 0, -1, 2)</span>; var rowFilter = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.<span class="hljs-constructor">HideRowFilter(<span class="hljs-params">range</span>)</span>; sheet.row<span class="hljs-constructor">Filter(<span class="hljs-params">rowFilter</span>)</span>; rowFilter.filter<span class="hljs-constructor">ButtonVisible(<span class="hljs-params">false</span>)</span>; </code></pre> <p>If you want to know whether any row is filtered, use the <strong>isFiltered</strong> method. You can also use the <strong>isRowFilteredOut</strong> method to determine whether the specified row is filtered out, Use the <strong>isColumnFiltered</strong> method to determine whether the specified column is filtered. For example:</p> <pre><code class="hljs js language-js"> sheet.setValue(<span class="hljs-number">0</span>,<span class="hljs-number">0</span>,<span class="hljs-number">1</span>); sheet.setValue(<span class="hljs-number">1</span>,<span class="hljs-number">0</span>,<span class="hljs-number">2</span>); sheet.setValue(<span class="hljs-number">2</span>,<span class="hljs-number">0</span>,<span class="hljs-number">3</span>); <span class="hljs-keyword">var</span> range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Range(<span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>); sheet.rowFilter(<span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.HideRowFilter(range)); <span class="hljs-keyword">var</span> compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo; <span class="hljs-keyword">var</span> condition = <span class="hljs-keyword">new</span> GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {<span class="hljs-attr">compareType</span>: compareType, <span class="hljs-attr">expected</span>: <span class="hljs-string">'3'</span>}); <span class="hljs-keyword">var</span> rowfilter = sheet.rowFilter(); rowfilter.addFilterItem(<span class="hljs-number">0</span>, condition); rowfilter.filter(<span class="hljs-number">0</span>); rowfilter.isFiltered(); <span class="hljs-comment">// true</span> rowfilter.isColumnFiltered(<span class="hljs-number">0</span>); <span class="hljs-comment">// true</span> rowfilter.isRowFilteredOut(<span class="hljs-number">2</span>); <span class="hljs-comment">// false</span> </code></pre> <p>You can use <strong>getFilterItems</strong> to get the filters (Conditions Array) for the specified column. You can also use <strong>getFilteredItems</strong> to get all filtered conditions. For example:</p> <pre><code class="hljs js language-js"> rowfilter.getFilterItems(<span class="hljs-number">0</span>); rowfilter.getFilteredItems(); </code></pre> <p>The filtered row can be sorted. Use <strong>sortColumn</strong> to sort the specified column in the specified order, and use <strong>getSortState</strong> to get the current sort state. For example:</p> <pre><code class="hljs js language-js"> rowfilter.SortColumns(<span class="hljs-number">0</span>, <span class="hljs-literal">true</span>); <span class="hljs-comment">// sort as ascending.</span> rowfilter.getSortState() === GC.Spread.Sheets.SortState.ascending; <span class="hljs-comment">// true</span> </code></pre> <p>If you don't want the filters, you can remove some filters or clear all filters. For example:</p> <pre><code class="hljs js language-js"> rowfilter.removeFilterItems(<span class="hljs-number">0</span>); rowfilter.unfilter(<span class="hljs-number">0</span>); rowfilter.reset(); </code></pre>
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); spread.suspendPaint(); initSpread(spread); spread.resumePaint(); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.setArray(1, 1, [ ["Number"],[1],[2],[3],[4],[5],[6],[7],[8] ]) sheet.setColumnWidth(2, 120); sheet.setArray(1, 2, [ ["Date"],['01/01/2017'],['02/01/2017'],['03/01/2017'],['04/01/2017'],['05/01/2017'],['06/01/2017'],['07/01/2017'],['08/01/2017'] ]) sheet.setArray(1, 3, [ ["String"],["Abby"],["Aimee"],["Alisa"],["Angelia"],["Anne"],["Bobe"],["Jack"],["Grace"] ]) var backColorArray = ['yellow','red','green','blue','orange','purple','pink','grey']; for(var i=0;i<backColorArray.length;i++){ sheet.getCell(2+i,4).backColor(backColorArray[i]); } _getElementById("Condition1").addEventListener('change',function () { var condition = _getElementById("Condition1").value; var type = _getElementById("optEnumType1"); setEnumType(condition, type); }); _getElementById("Condition2").addEventListener('change',function () { var condition = _getElementById("Condition2").value; var type = _getElementById("optEnumType2"); setEnumType(condition, type); }); function setEnumType(condition, type) { var data=[]; switch (condition) { case "0": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "1": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "2": data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain']; _appendChild(type,data); break; case "3": data = ['BackgroundColor','ForegroundColor']; _appendChild(type,data); break; case "4": data = ['Empty','NonEmpty','Error','NonError','Formula']; _appendChild(type,data); break; case "5": data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo']; _appendChild(type,data); break; case "6": data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear']; _appendChild(type,data); break; case "7": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "8": data = ['Top','Bottom']; _appendChild(type,data); break; default: data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; } } function getConditionBase(condition, type, value) { var sheet = spread.getActiveSheet(); var condtionbase; var formula; if ((value != null) && (value[0] == "=")) { formula = value; value = null; } else { formula = null; if (!isNaN(value)) { value = parseFloat(value); } } switch (condition) { case "0": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; case "1": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, { compareType: type, expected: value, formula: formula }); break; case "2": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, { compareType: type, expected: value, formula: formula }); break; case "3": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, { compareType: type, expected: value }); break; case "4": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, { customValueType: type, formula: formula }); break; case "5": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, { compareType: type, expected: value, formula: formula }); break; case "6": if (type < 10) { condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, { expected: type, }); } else if (type == 10) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromDay(value); } else if (type == 11) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromMonth(value); } else if (type == 12) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromQuarter(value); } else if (type == 13) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromWeek(value); } else { condtionbase = spreadNS.ConditionalFormatting.Condition.fromYear(value); } break; case "7": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, { compareType: type, expected: value, formula: formula }); break; case "8": var ranges = sheet.getSelections().slice(0); condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, { type: type, expected: value, ranges: ranges }); break; default: condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; } return condtionbase; } function getConditions() { var radio1 = _getElementById("rdoAND").checked; var radio2 = _getElementById("rdoOR").checked; var condition1 = _getElementById("Condition1").value; var condition2 = _getElementById("Condition2").value; var type1 = parseInt(_getElementById("optEnumType1").value); var type2 = parseInt(_getElementById("optEnumType2").value); var value1 = _getElementById("txtFormulas1").value; var value2 = _getElementById("txtFormulas2").value; var con1 = getConditionBase(condition1, type1, value1); var con2 = getConditionBase(condition2, type2, value2); var conditions; if (value2 != null || value2 != "" || value2 != undefined) { if (radio1) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.and, item1: con1, item2: con2 }); } else if (radio2) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.or, item1: con1, item2: con2 }); } else { conditions = con1; } } else { conditions = con1; } return conditions; } _getElementById("btnHideRowFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var sels = sheet.getSelections(); if (sels.length == 0) return; var sel = sels[0]; // set filter var _drf = new spreadNS.Filter.HideRowFilter(sel); sheet.rowFilter(_drf); var nc = getConditions(); nc.ignoreBlank(_getElementById('chkIgnoreBlank').checked); _drf.addFilterItem(sheet.getActiveColumnIndex(), nc); // filter _drf.filter((sel.col >= 0) ? sel.col : 0); sheet.invalidateLayout(); sheet.repaint(); }); _getElementById("btnClearFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); sheet.rowFilter(null); sheet.invalidateLayout(); sheet.repaint(); }); } function _getElementById(id){ return document.getElementById(id); } function _appendChild(type,data){ type.innerHTML=''; for(var i=0;i<data.length;i++){ var option = document.createElement('option'); var value = document.createAttribute('value'); value.nodeValue = i; option.setAttributeNode(value); option.innerHTML = data[i]; type.appendChild(option); } }
<!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> Use these options to create a custom filter in Spread. Highlight some names in column D and click the “Set Filter” to create a filter that allows the user to select which rows to show based on those names.</p> <div class="option-row"> <label>Conditions:</label> <select id="Condition1"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>CompareType:</label> <select id="optEnumType1"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>Compare value or Formula:</label> <input id="txtFormulas1" type="text" /> </div> <div class="option-row"> <label></label> <input type="radio" value="And" name="relation" id="rdoAND" /> <label for="rdoAND" >And</label> <input type="radio" value="Or" name="relation" id="rdoOR" /> <label for="rdoOR">OR</label> </div> <div class="option-row"> <label>Conditions:</label> <select id="Condition2"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>CompareType:</label> <select id="optEnumType2"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>Compare value or Formula:</label> <input id="txtFormulas2" type="text" /> </div> <div class="option-row"> <label></label> <input type="checkbox" id="chkIgnoreBlank" /> <label for="chkIgnoreBlank">Ignore Blank</label> </div> <div class="option-row"> <input type="button" value="Set Filter" id="btnHideRowFilter" /> <input type="button" value="Clear Filter" id="btnClearFilter" /> </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-row { font-size: 14px; padding: 5px; margin-top: 10px; } p{ padding:2px 10px; background-color:lavender; } input, select { width: 100%; padding: 4px 6px; box-sizing: border-box; } label { display:block; margin-bottom: 6px; } input[type="checkbox"], input[type="radio"] { display: inline-block; width: auto; } input[type="checkbox"]+label, input[type="radio"]+label { display: inline-block; } input[type="button"] { display: block; margin: 0 0 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }