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:
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> <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> </template> <script> import Vue from 'vue'; import '@grapecity/spread-sheets-vue' import GC from '@grapecity/spread-sheets'; import './styles.css'; const spreadNS = GC.Spread.Sheets; let App = Vue.extend({ name: "app", methods:{ initSpread: function (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: function (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: function (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 }); } } } }); new Vue({ render: h => h(App) }).$mount('#app'); </script>
<!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/vue/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); System.import('$DEMOROOT$/en/lib/vue/license.js'); </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; } .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: 'plugin-babel', babelOptions: { es2015: true }, meta: { '*.css': { loader: 'css' }, '*.vue': { loader: 'vue-loader' } }, 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-vue': 'npm:@grapecity/spread-sheets-vue/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'css': 'npm:systemjs-plugin-css/css.js', 'vue': 'npm:vue/dist/vue.min.js', 'vue-loader': 'npm:systemjs-vue-browser/index.js', 'tiny-emitter': 'npm:tiny-emitter/index.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: 'js' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' } } }); })(this);