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 * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity/spread-sheets'; import { SpreadSheets, Worksheet } from '@grapecity/spread-sheets-react'; import './styles.css'; const Component = React.Component; const spreadNS = GC.Spread.Sheets; function _getElementById(id) { return document.getElementById(id); } class App extends Component { constructor(props) { super(props); this.spread = null; } render() { return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}> <Worksheet> </Worksheet> </SpreadSheets> </div> <Panel doFilter={(e, type)=>{this.doFilter(e, type)}} /> </div>; } initSpread(spread) { this.spread = 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(); } doFilter(e, type) { var sheet = this.spread.getActiveSheet(); var filter = sheet.rowFilter(); if (filter) { filter.removeFilterItems(1); if (e.target.checked) { var condition = this.getCondition(type); filter.addFilterItem(1, condition); } filter.filter(1); sheet.invalidateLayout(); sheet.repaint(); } } getCondition(type) { 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 }); } } } class Panel extends Component{ constructor(props){ super(props); } render(){ return( <div class="options-container"> <label >These condition rules filter apply to Column2</label> <hr /> <div class="option-row"> <input type="checkbox" id="text_condition" onClick={(e)=>{this.props.doFilter(e, "text")}} /> <label htmlFor="text_condition" style={{display: "inline-block", width: "210px"}}>Text Condition(Contains e)</label> </div> <div class="option-row"> <input type="checkbox" id="number_condition" onClick={(e)=>{this.props.doFilter(e, "number")}} /> <label htmlFor="number_condition">Number Condition(less than 20)</label> </div> <div class="option-row"> <input type="checkbox" id="date_condition" onClick={(e)=>{this.props.doFilter(e, "date")}} /> <label htmlFor="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" onClick={(e)=>{this.props.doFilter(e, "style")}} /> <label htmlFor="style_condition">Style Condition(Cyan background)</label> </div> <div class="option-row"> <input type="checkbox" id="custom_condition" onClick={(e)=>{this.props.doFilter(e, "custom")}} /> <label htmlFor="custom_condition" id="label_custom_condition">Customize Condition(Value is empty)</label> </div> </div> ) } } ReactDOM.render(<App />, _getElementById('app'));
<!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/react/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/en/lib/react/license.js').then(function () { System.import('./src/app'); }); </script> </head> <body> <div id="app"></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; } #app { height: 100%; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-react': 'npm:@grapecity/spread-sheets-react/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.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' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);