Use the Filter or Table object's filterButtonVisible method to get or set whether the filter buttons are displayed. This allows you to control what you want to allow users to filter without completely getting rid of filters, such as when you have user identification in your application, and only certain users can filter specific columns or rows.
The filterButtonVisible method's get and set data depends on the following arguments:
No arguments: Get whether filter buttons are displayed. True if one is visible; otherwise, false.
1 argument: If it's a number, then use it as a column index that returns whether the corresponding column's filter button is displayed. If it's a boolean, set all filter buttons to the specified value.
2 arguments: The first argument is the column index and the second one is the value. Sets whether the corresponding column's filter button is displayed.
The filter dialog container contains sortByValue, sortByColor, filterByColor, filterByValue and listFilterArea. The filterDialogVisibleInfo method is used to get or set whether these options are displayed.
Enhanced functionality: Adding Current Selection To Filter
Let's say you've filtered a selection once, and part of the selection has been filtered out. If you check "add current selection to filter" during your second filter, your second filter will be displayed at the same time as your first filter, instead of clearing the first filter to show only the second filter.
The filter dialog supports resizing by clicking and dragging the indicator in the lower right corner.
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 { data } from './app.data';
let { salesData, outlineColumnData } = data;
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;
filter: GC.Spread.Sheets.Filter.HideRowFilter;
tableColumnsContainer: HTMLElement;
checkBoxes: HTMLInputElement[];
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.options.allowCellOverflow = true;
sheet.name("FilterDialog");
sheet.setArray(1, 1, salesData);
let filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(2, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
this.filter = filter;
this.prepareFilterItems(sheet, salesData[0] as string[]);
sheet.defaults.rowHeight = 28;
sheet.setColumnWidth(1, 110);
sheet.setColumnWidth(2, 80);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 80);
sheet.setColumnWidth(5, 80);
sheet.setColumnWidth(6, 80);
sheet.getRange(2, 2, 10, 1).formatter("yyyy/mm/dd");
let ComparisonOperators = spreadNS.ConditionalFormatting.ComparisonOperators;
let equalsTo = ComparisonOperators.equalsTo;
let range = sheet.getRange(1, 1, 11, 6);
range.setBorder(new spreadNS.LineBorder("gray", spreadNS.LineStyle.thin), {all: true});
let ranges = [new spreadNS.Range(2, 3, 10, 1)];
let style1 = new spreadNS.Style();
style1.foreColor = "Accent 2";
let rule1 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, equalsTo, "West", "");
sheet.conditionalFormats.addRule(rule1);
let style2 = new spreadNS.Style();
style2.foreColor = "Accent 3";
let rule2 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, equalsTo, "East", "");
sheet.conditionalFormats.addRule(rule2);
let style3 = new spreadNS.Style();
style3.foreColor = "Accent 6";
let rule3 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, equalsTo, "North", "");
sheet.conditionalFormats.addRule(rule3);
let style4 = new spreadNS.Style();
style4.foreColor = "Accent 1";
let rule4 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style4, equalsTo, "South", "");
sheet.conditionalFormats.addRule(rule4);
ranges = [new spreadNS.Range(2, 2, 10, 1)];
style1 = new spreadNS.Style();
style1.backColor = "rgb(241, 135, 102)";
rule1 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, ComparisonOperators.lessThan, "1990/01/01", "");
sheet.conditionalFormats.addRule(rule1);
style2 = new spreadNS.Style();
style2.backColor = "lightGreen";
rule2 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, ComparisonOperators.between, "1990/01/01", "2000/01/01");
sheet.conditionalFormats.addRule(rule2);
style3 = new spreadNS.Style();
style3.backColor = "deepSkyBlue";
rule3 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, ComparisonOperators.greaterThan, "2000/01/01", "");
sheet.conditionalFormats.addRule(rule3);
sheet.resumePaint();
let sheet2 = spread.sheets[1];
this.initOutlineColumnFilter(sheet2);
sheet2.name("OutlineColumnFilter");
}
setAllFilterButtonsVisible($event: any, visible: boolean) {
let filter = this.filter;
if (filter) {
filter.filterButtonVisible(visible);
this.checkBoxes.forEach(function(item) {
item.checked = visible;
});
}
}
setMenuItem($event: any, key: string) {
let options = {};
options[key] = $event.target.checked;
this.filter.filterDialogVisibleInfo(options);
}
prepareFilterItems(sheet: GC.Spread.Sheets.Worksheet, headers: string[]) {
let items = [];
let filter = sheet.rowFilter(),
range = filter.range,
startColumn = range.col;
for (let c = 0, length = headers.length; c < length; c++) {
let name = headers[c];
items.push('<div><label><input type="checkbox" checked data-index="' + (startColumn + c) + '">'+ name + '</label></div>');
}
this.tableColumnsContainer = document.getElementById("tableColumnsContainer");
this.tableColumnsContainer.innerHTML = items.join("");
let nodeList = this.tableColumnsContainer.querySelectorAll("input[type='checkbox']");
this.checkBoxes = [];
let checkBoxes = this.checkBoxes;
for (let i = 0, count = nodeList.length; i < count; i++) {
let element = nodeList[i] as HTMLInputElement;
checkBoxes.push(element);
element.addEventListener('change', function () {
let index = +this.dataset.index; // +this.getAttribute("data-index");
if (filter) {
filter.filterButtonVisible(index, this.checked);
}
});
}
}
initOutlineColumnFilter(sheet: GC.Spread.Sheets.Worksheet) {
sheet.setColumnWidth(2, 120);
sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(-1, 0, -1, 1)));
sheet.suspendPaint();
sheet.setColumnWidth(0, 200);
sheet.setRowCount(12);
sheet.outlineColumn.options({
columnIndex: 0,
showIndicator: true,
});
let sourceData = outlineColumnData;
sheet.setDataSource(sourceData);
sheet.bindColumn(0, "name");
sheet.setColumnCount(3);
sheet.setColumnWidth(0, 300);
for (let r = 0; r < sourceData.length; r++) {
let level = sourceData[r].level;
sheet.getCell(r, 0).textIndent(level);
}
sheet.showRowOutline(false);
sheet.outlineColumn.refresh();
sheet.resumePaint();
}
}
@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-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="option-group">
<input type="button" value="Show All" title="Show all filter buttons of the table" (click)="setAllFilterButtonsVisible($event, true)" />
<input type="button" value="Hide All" title="Hide all filter buttons of the table" (click)="setAllFilterButtonsVisible($event, false)" />
</div>
<div class="option-group">
<h4>Show filter buttons:</h4>
<div id="tableColumnsContainer"></div>
<h4>Filter dialog visible info:</h4>
<div>
<div><label><input type="checkbox" checked (change)="setMenuItem($event, 'sortByValue')">ShowSortByValue</label></div>
<div><label><input type="checkbox" checked (change)="setMenuItem($event, 'sortByColor')">ShowSortByColor</label></div>
<div><label><input type="checkbox" checked (change)="setMenuItem($event, 'filterByColor')">ShowFilterByColor</label></div>
<div><label><input type="checkbox" checked (change)="setMenuItem($event, 'filterByValue')">ShowFilterByValue</label></div>
<div><label><input type="checkbox" checked (change)="setMenuItem($event, 'listFilterArea')">ShowListFilterArea</label></div>
</div>
</div>
</div>
</div>
export const data = {
salesData: [
["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"],
["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26],
["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99],
["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141],
["Erich", new Date("1994/05/23"), "West", 410, 0.12, 49.2],
["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120],
["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135],
["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110],
["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2],
["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76],
["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35]
],
outlineColumnData: [
{name: 'Preface', chapter: '1', page: 1, level: 0},
{name: 'Java SE5 and SE6', chapter: '1.1', page: 2, level: 1},
{name: 'Java SE6', chapter: '1.1.1', page: 2, level: 2},
{name: 'The 4th edition', chapter: '1.2', page: 2, level: 1},
{name: 'Changes', chapter: '1.2.1', page: 3, level: 2},
{name: 'Note on the cover design', chapter: '1.3', page: 4, level: 1},
{name: 'Acknowledgements', chapter: '1.4', page: 4, level: 1},
{name: 'Introduction', chapter: '2', page: 9, level: 0},
{name: 'Prerequisites', chapter: '2.1', page: 9, level: 1},
{name: 'Learning Java', chapter: '2.2', page: 10, level: 1},
{name: 'Goals', chapter: '2.3', page: 10, level: 1},
{name: 'Teaching from this book', chapter: '2.4', page: 11, level: 1},
{name: 'JDK HTML documentation', chapter: '2.5', page: 11, level: 1},
{name: 'Exercises', chapter: '2.6', page: 12, level: 1},
{name: 'Foundations for Java', chapter: '2.7', page: 12, level: 1},
{name: 'Source code', chapter: '2.8', page: 12, level: 1},
{name: 'Coding standards', chapter: '2.8.1', page: 14, level: 2},
{name: 'Errors', chapter: '2.9', page: 14, level: 1},
{name: 'Introduction to Objects', chapter: '3', page: 15, level: 0},
{name: 'The progress of abstraction', chapter: '3.1', page: 15, level: 1},
{name: 'An object has an interface', chapter: '3.2', page: 17, level: 1},
{name: 'An object provides services', chapter: '3.3', page: 18, level: 1},
{name: 'The hidden implementation', chapter: '3.4', page: 19, level: 1},
{name: 'Reusing the implementation', chapter: '3.5', page: 20, level: 1},
{name: 'Inheritance', chapter: '3.6', page: 21, level: 1},
{name: 'Is-a vs. is-like-a relationships', chapter: '3.6.1', page: 24, level: 2},
{name: 'Interchangeable objects with polymorphism', chapter: '3.7', page: 25, level: 1},
{name: 'The singly rooted hierarchy', chapter: '3.8', page: 28, level: 1},
{name: 'Containers', chapter: '3.9', page: 28, level: 1},
{name: 'Parameterized types (Generics)', chapter: '3.10', page: 29, level: 1},
{name: 'Object creation & lifetime', chapter: '3.11', page: 30, level: 1},
{name: 'Exception handling: dealing with errors', chapter: '3.12', page: 31, level: 1},
{name: 'Concurrent programming', chapter: '3.13', page: 32, level: 1},
{name: 'Java and the Internet', chapter: '3.14', page: 33, level: 1},
{name: 'What is the Web?', chapter: '3.14.1', page: 33, level: 2},
{name: 'Client-side programming', chapter: '3.14.2', page: 34, level: 2},
{name: 'Server-side programming', chapter: '3.14.3', page: 38, level: 2}
]
}
.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-group {
margin-bottom: 6px;
}
.option-group input[type="checkbox"] {
margin-right: 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);
