Overview

Use the built-in Excel-like filter dialog to allow your users to automatically filter their data or take advantage of creating custom filters to show or hide the relevant data as per your applications needs. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed.

To set the filter for a sheet, first create a row filter, then use the rowFilter method to set the row filter. You can also create a condition and use the addFilterItem method to add a specified filter to the row filter. For example:
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.setRowCount(50); sheet.setColumnCount(8); var rc = sheet.getRowCount(); var cc = sheet.getColumnCount(); for (var r = 0; r < rc; r++) { for (var c = 0; c < cc; c++) { if (c == 0) { sheet.setValue(r, c, "Value is Number"); } else { sheet.setValue(r, c, r + c); } } } sheet.setValue(0, 0, "Conditions", spreadNS.SheetArea.colHeader); sheet.setValue(0, 1, "Cell Value", spreadNS.SheetArea.colHeader); sheet.setValue(1, 0, "Text contains e"); sheet.setValue(10, 0, "Text doesn't contains e"); sheet.setValue(21, 0, "Text contains e"); sheet.setValue(1, 1, "begin"); sheet.setValue(10, 1, "during"); sheet.setValue(21, 1, "end"); sheet.setValue(2, 0, "Background is Cyan"); sheet.setValue(6, 0, "Background is Purple"); sheet.setValue(12, 0, "Background is Cyan"); sheet.getCell(2, 1).backColor("Cyan"); sheet.getCell(6, 1).backColor("Purple"); sheet.getCell(12, 1).backColor("Cyan"); sheet.setValue(3, 0, "Value is Thursday"); sheet.setValue(8, 0, "Value is Friday"); sheet.setValue(14, 0, "Value is Thursday"); sheet.setValue(3, 1, new Date(2011, 5, 30)); sheet.setValue(8, 1, new Date(2011, 6, 1)); sheet.setValue(14, 1, new Date(2011, 5, 30)); sheet.setValue(4, 0, "Value is null"); sheet.setValue(9, 0, "Value is null"); sheet.setValue(18, 0, "Value is null"); sheet.setValue(4, 1, null); sheet.setValue(9, 1, null); sheet.setValue(18, 1, null); sheet.setColumnWidth(0, 150); sheet.setColumnWidth(1, 150); var filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(-1, 0, -1, 2)); sheet.rowFilter(filter); filter.filterButtonVisible(false); sheet.resumePaint(); _getElementById("text_condition").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (this.checked) { var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, { compareType: spreadNS.ConditionalFormatting.TextCompareType.contains, expected: "*e*" }); filter.addFilterItem(1, condition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } }); _getElementById("number_condition").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (this.checked) { var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, { compareType: spreadNS.ConditionalFormatting.GeneralComparisonOperators.lessThan, expected: 20 }); filter.addFilterItem(1, condition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } }); _getElementById("date_condition").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (this.checked) { var dateExCondition = spreadNS.ConditionalFormatting.Condition.fromWeek(4); filter.addFilterItem(1, dateExCondition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } }); _getElementById("style_condition").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (this.checked) { var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, { compareType: spreadNS.ConditionalFormatting.ColorCompareType.backgroundColor, expected: "Cyan" }); filter.addFilterItem(1, condition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } }); _getElementById("custom_condition").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (this.checked) { var formulaCondition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, { customValueType: spreadNS.ConditionalFormatting.CustomValueType.empty, formula: null }); filter.addFilterItem(1, formulaCondition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } }); } 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"> <label >These condition rules filter apply to Column2</label> <hr> <div class="option-row"> <input type="checkbox" id="text_condition" /> <label for="text_condition" style="display: inline-block; width: 210px;">Text Condition(Contains e)</label> </div> <div class="option-row"> <input type="checkbox" id="number_condition" /> <label for="number_condition">Number Condition(less than 20)</label> </div> <div class="option-row"> <input type="checkbox" id="date_condition" /> <label for="date_condition" id="label_date_condition" style="display: inline-block; width: 210px;">Date Condition (is Thursday)</label> </div> <div class="option-row"> <input type="checkbox" id="style_condition" /> <label for="style_condition">Style Condition(Cyan background)</label> </div> <div class="option-row"> <input type="checkbox" id="custom_condition" /> <label for="custom_condition" id="label_custom_condition">Customize Condition(Value is empty)</label> </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: 3px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }