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:
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular'; import GC from '@grapecity/spread-sheets'; import './styles.css'; const spreadNS = GC.Spread.Sheets; @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; hostStyle = { width: 'calc(100% - 280px)', height: '100%', overflow: 'hidden', float: 'left' }; constructor() { } initSpread($event: any) { this.spread = $event.spread; let spread = this.spread; let sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.setRowCount(50); sheet.setColumnCount(8); let rc = sheet.getRowCount(); let cc = sheet.getColumnCount(); for (let r = 0; r < rc; r++) { for (let 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); let filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(-1, 0, -1, 2)); sheet.rowFilter(filter); filter.filterButtonVisible(false); sheet.resumePaint(); } doFilter($event: any, type: string) { let sheet = this.spread.getActiveSheet(); let filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if ($event.target.checked) { let condition = this.getCondition(type); filter.addFilterItem(1, condition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } } getCondition(type: string) { switch(type) { case "text": return new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, { compareType: spreadNS.ConditionalFormatting.TextCompareType.contains, expected: "*e*" }); case "number": return new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, { compareType: spreadNS.ConditionalFormatting.GeneralComparisonOperators.lessThan, expected: 20 }); case "date": return new spreadNS.ConditionalFormatting.Condition.fromWeek(4); case "style": return new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, { compareType: spreadNS.ConditionalFormatting.ColorCompareType.backgroundColor, expected: "Cyan" }); case "custom": return new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, { customValueType: spreadNS.ConditionalFormatting.CustomValueType.empty, formula: null }); } } } @NgModule({ imports: [BrowserModule, SpreadSheetsModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent] }) export class AppModule {} enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/en/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> <div class="options-container"> <label >These condition rules filter apply to Column2</label> <hr> <div class="option-row"> <input type="checkbox" id="text_condition" (click)="doFilter($event, 'text')" /> <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" (click)="doFilter($event, 'number')" /> <label for="number_condition">Number Condition(less than 20)</label> </div> <div class="option-row"> <input type="checkbox" id="date_condition" (click)="doFilter($event, 'date')" /> <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" (click)="doFilter($event, 'style')" /> <label for="style_condition">Style Condition(Cyan background)</label> </div> <div class="option-row"> <input type="checkbox" id="custom_condition" (click)="doFilter($event, 'custom')" /> <label for="custom_condition" id="label_custom_condition">Customize Condition(Value is empty)</label> </div> </div> </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .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; }
(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/dist/zone.min.js', 'rxjs': 'npm:rxjs/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/bundles/core.umd.min.js', '@angular/common': 'npm:@angular/common/bundles/common.umd.min.js', '@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.min.js', '@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.min.js', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.min.js', '@angular/http': 'npm:@angular/http/bundles/http.umd.min.js', '@angular/common/http': 'npm:@angular/common/bundles/common-http.umd.min.js', '@angular/router': 'npm:@angular/router/bundles/router.umd.min.js', '@angular/forms': 'npm:@angular/forms/bundles/forms.umd.min.js', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': 'npm:plugin-typescript/lib/plugin.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-angular': 'npm:@grapecity/spread-sheets-angular/bundles/grapecity-spread-sheets-angular.umd.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);