You can use the filterButtonVisible method to get or set whether the filter buttons are displayed. For example:
If you want to know whether any row is filtered, use the isFiltered method. You can also use the isRowFilteredOut method to determine whether the specified row is filtered out. For example:
You can use getFilterItems to get the filters (Conditions Array) for the specified column. You can also use getFilteredItems to get all filtered conditions. For example:
The filtered row can be sorted. Use sortColumn to sort the specified column in the specified order, and use getSortState to get the current sort state. For example:
If you don't want the filters, you can remove some filters or reset all filters. 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 '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import './styles.css';
const spreadNS = GC.Spread.Sheets;
function getElementById(id: string): HTMLElement {
return document.getElementById(id);
}
function updateEnumType(conditionType: string, element: HTMLElement) {
let data: string[] = [];
switch (conditionType) {
case "2":
data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain'];
updateEnumTypeList(element, data);
break;
case "3":
data = ['BackgroundColor','ForegroundColor'];
updateEnumTypeList(element, data);
break;
case "4":
data = ['Empty','NonEmpty','Error','NonError','Formula'];
updateEnumTypeList(element, data);
break;
case "5":
data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo'];
updateEnumTypeList(element, data);
break;
case "6":
data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear'];
updateEnumTypeList(element, data);
break;
case "8":
data = ['Top','Bottom'];
updateEnumTypeList(element, data);
break;
default: // case "0", "1", "7" same items
data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo'];
updateEnumTypeList(element, data);
break;
}
}
function updateEnumTypeList(element: HTMLElement, data: string[]) {
element.innerHTML = "";
for (let i = 0; i < data.length; i++) {
let option = document.createElement("option");
let attribute = document.createAttribute("value");
attribute.nodeValue = i.toString();
option.setAttributeNode(attribute);
option.innerHTML = data[i];
element.appendChild(option);
}
}
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook;
factorial: any;
hostStyle = {
width: 'calc(100% - 280px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread;
spread.suspendPaint();
let 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"],[new Date('01/01/2017')],[new Date('02/01/2017')],[new Date('03/01/2017')],[new Date('04/01/2017')],[new Date('05/01/2017')],[new Date('06/01/2017')],[new Date('07/01/2017')],[new Date('08/01/2017')]
]);
sheet.setArray(1, 3, [
["String"],["Abby"],["Aimee"],["Alisa"],["Angelia"],["Anne"],["Bobe"],["Jack"],["Grace"]
]);
let backColorArray = ['yellow','red','green','blue','orange','purple','pink','grey'];
for (let i = 0; i < backColorArray.length; i++) {
sheet.getCell(2 + i, 4).backColor(backColorArray[i]);
}
spread.resumePaint();
}
onConditionChanged($event: any, type: number) {
let conditionType = (getElementById("Condition" + type) as HTMLInputElement).value;
let element = getElementById("optEnumType" + type);
updateEnumType(conditionType, element);
}
getCondition(conditionType: string, compareType: number, value: any) {
let sheet = this.spread.getActiveSheet();
let condition;
let formula;
if ((value != null) && (value[0] == "=")) {
formula = value;
value = null;
}
else {
formula = null;
if (!isNaN(value)) {
value = parseFloat(value);
}
}
switch (conditionType) {
case "1":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "2":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "3":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, {
compareType: compareType,
expected: value
});
break;
case "4":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, {
customValueType: compareType,
formula: formula
});
break;
case "5":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "6":
if (compareType < 10) {
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, {
expected: compareType,
});
}
else if (compareType == 10) {
condition = spreadNS.ConditionalFormatting.Condition.fromDay(value);
} else if (compareType == 11) {
condition = spreadNS.ConditionalFormatting.Condition.fromMonth(value);
} else if (compareType == 12) {
condition = spreadNS.ConditionalFormatting.Condition.fromQuarter(value);
} else if (compareType == 13) {
condition = spreadNS.ConditionalFormatting.Condition.fromWeek(value);
} else {
condition = spreadNS.ConditionalFormatting.Condition.fromYear(value);
}
break;
case "7":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "8":
let ranges = sheet.getSelections().slice(0);
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, {
type: compareType,
expected: value,
ranges: ranges
});
break;
default: // case "0" same
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
}
return condition;
}
getConditions() {
let radio1 = (getElementById("rdoAND") as HTMLInputElement).checked;
let radio2 = (getElementById("rdoOR") as HTMLInputElement).checked;
let conditionType1 = (getElementById("Condition1") as HTMLSelectElement).value;
let conditionType2 = (getElementById("Condition2") as HTMLSelectElement).value;
let compareType1 = parseInt((getElementById("optEnumType1") as HTMLSelectElement).value);
let compareType2 = parseInt((getElementById("optEnumType2") as HTMLSelectElement).value);
let value1 = (getElementById("txtFormulas1") as HTMLInputElement).value;
let value2 = (getElementById("txtFormulas2") as HTMLInputElement).value;
let con1 = this.getCondition(conditionType1, compareType1, value1);
let con2 = this.getCondition(conditionType2, compareType2, value2);
let 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;
}
applyFilter() {
let sheet = this.spread.getActiveSheet();
let selections = sheet.getSelections();
if (selections.length == 0) return;
let selection = selections[0];
// set filter
let hideRowFilter = new spreadNS.Filter.HideRowFilter(selection);
sheet.rowFilter(hideRowFilter);
let conditions = this.getConditions();
conditions.ignoreBlank((getElementById('chkIgnoreBlank') as HTMLInputElement).checked);
hideRowFilter.addFilterItem(sheet.getActiveColumnIndex(), conditions);
// filter
hideRowFilter.filter((selection.col >= 0) ? selection.col : 0);
sheet.invalidateLayout();
sheet.repaint();
}
clearFilter() {
let sheet = this.spread.getActiveSheet();
sheet.rowFilter(null);
sheet.invalidateLayout();
sheet.repaint();
}
}
@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/@mescius/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/fesm2015/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.import('@angular/compiler');
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">
<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" (change)="onConditionChanged($event, 1)">
<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" (change)="onConditionChanged($event, 2)">
<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" (click)="applyFilter()" />
<input type="button" value="Clear Filter" id="btnClearFilter" (click)="clearFilter()" />
</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: 10px;
}
p {
padding:2px 10px;
background-color:#F4F8EB;
}
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;
}
(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/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.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',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@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'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
