Overview

Similar to filters, the Excel-like slicers offer an intuitive visual way to filter your spreadsheet data. Slicers also visually indicate the current filtered state. Available slicers include table slicer, item slicer, chart slicer, aggregation slicer, and cusotmer slicers such as those used on consumer websites.

Slicer relies on table, so you must add a table first if you want to create a slicer. You can add a slicer by using the add method, as shown in the following code: Slicer consists of slicer header and slicer body. Slicer header is composed of slicer caption and clear filter button. Slicer caption is used to show a caption. The clear filter button is used to unfilter the slicer and it is inactive by default. It is active only when a slicer item is filtered . Slicer body is composed of slicer items. There are four kinds of slicer items: No Data Items: The items have been filtered out by another slicer. Has Data Items: The items haven't been filtered out by another slicer. Selected Items: The items have been filtered out by themselves. Unselected Items: The items haven't been filtered out by themselves. The items are divided into No Data Items and Has Data Items when they are filtered out by another slicer, or divided into Selected Items and Unselected Items when they are filtered out by themselves.
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { FormsModule } from '@angular/forms'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular'; import GC from '@grapecity/spread-sheets'; import './styles.css'; const getSelectedSlicers = (sheet: GC.Spread.Sheets.Worksheet) => { if (!sheet) { return null; } let slicers = sheet.slicers.all(); if (!slicers || _isEmptyObject(slicers)) { return null; } let selectedSlicersList = []; for (let item in slicers) { let slicer = slicers[item]; if (slicer.isSelected()) { selectedSlicersList.push(slicer); } } return selectedSlicersList; } const _isEmptyObject = (obj: object) => { for (let name in obj) { return false; } return true; } @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; name = ''; captionName = ''; showHeader = true; showNoDataItems = false; showNoDataItemsInLast = false; visuallyNoDataItems = false; disableName = true; disableCaptionName = true; selectedSlicersList: any[] = []; hostStyle = { width: 'calc(100% - 280px)', height: '100%', overflow: 'hidden', float: 'left' }; updateName() { this.selectedSlicersList.forEach((slicer) => { slicer.name(this.name); }) } updateCaptionName() { this.selectedSlicersList.forEach((slicer) => { slicer.captionName(this.captionName); }) } updateShowHeader() { this.selectedSlicersList.forEach((slicer) => { slicer.showHeader(this.showHeader); }) } updateShowNoDataItems() { this.selectedSlicersList.forEach((slicer) => { slicer.showNoDataItems(this.showNoDataItems); }) } updateShowNoDataItemsInLast() { this.selectedSlicersList.forEach((slicer) => { slicer.showNoDataItemsInLast(this.showNoDataItemsInLast); }) } updateVisuallyNoDataItems() { this.selectedSlicersList.forEach((slicer) => { slicer.visuallyNoDataItems(this.visuallyNoDataItems); }) } initSpread($event: any) { let spread = this.spread = $event.spread; spread.suspendPaint(); let sheet = spread.getActiveSheet(); let dataColumns = ["Name", "City", "Birthday", "Sex", "Weight", "Height"]; let data = [ ["bob", "NewYork", "1968/6/8", "man", "80", "180"], ["Betty", "NewYork", "1972/7/3", "woman", "72", "168"], ["Cherry", "Washington", "1986/2/2", "woman", "58", "161"], ["Gary", "NewYork", "1964/3/2", "man", "71", "179"], ["Hunk", "Washington", "1972/8/8", "man", "80", "171"], ["Eva", "Washington", "1993/2/15", "woman", "71", "180"] ]; sheet.tables.addFromDataSource("table1", 1, 1, data); sheet.getRange(-1, 1, -1, 6).width(80); let table = sheet.tables.findByName("table1"); table.setColumnName(0, dataColumns[0]); table.setColumnName(1, dataColumns[1]); table.setColumnName(2, dataColumns[2]); table.setColumnName(3, dataColumns[3]); table.setColumnName(4, dataColumns[4]); table.setColumnName(5, dataColumns[5]); let slicer1 = sheet.slicers.add("slicer1", "table1", "Name"); slicer1.position(new GC.Spread.Sheets.Point(10, 170)); // show data items in original position slicer1.showNoDataItemsInLast(false); let slicer2 = sheet.slicers.add("slicer2", "table1", "City"); slicer2.position(new GC.Spread.Sheets.Point(220, 170)); let slicer3 = sheet.slicers.add("slicer3", "table1", "Height"); slicer3.position(new GC.Spread.Sheets.Point(430, 170)); // do not show filtered out items slicer3.showNoDataItems(false); spread.resumePaint(); this.bindEvent(spread); table.getSlicerData().doFilter("City", {exclusiveRowIndexes: [0]}); } bindEvent(spread: GC.Spread.Sheets.Workbook) { let self = this; spread.bind(GC.Spread.Sheets.Events.SlicerChanged, function(event: any, args: any) { let sheet = args.sheet; let slicer = args.slicer; if (!slicer) { return; } let propertyName = args.propertyName; if (propertyName === "isSelected") { if (slicer.isSelected()) { self.disableCaptionName = false; self.selectedSlicersList = getSelectedSlicers(sheet); if (self.selectedSlicersList && self.selectedSlicersList.length > 1) { self.disableName = true; self.name = ''; self.captionName = ''; self.showHeader = false; self.showNoDataItems = false; self.showNoDataItemsInLast = false; self.visuallyNoDataItems = false; } else if (self.selectedSlicersList.length === 1) { self.disableName = false; self.name = slicer.name(); self.captionName = slicer.captionName(); self.showHeader = slicer.showHeader(); self.showNoDataItems = slicer.showNoDataItems(); self.showNoDataItemsInLast = slicer.showNoDataItemsInLast(); self.visuallyNoDataItems = slicer.visuallyNoDataItems(); } } else { self.disableName = true; self.disableCaptionName = true; } } }); } } @NgModule({ imports: [BrowserModule, SpreadSheetsModule, FormsModule], 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-spread-sheets> <div class="options-container"> <p style="padding:2px 10px; background-color:lavender">Select the slicer and then change the options.</p> <div class="option-row"> <label for="slicer_name" style="display: inline-block;width: 150px">Name:</label> <input type="text" id="slicer_name" [disabled]="disableName" [(ngModel)]="name" (change)="updateName()" /> </div> <div class="option-row"> <label for="slicer_caption_name" style="display: inline-block;width: 150px">Caption Name:</label> <input type="text" id="slicer_caption_name" [disabled]="disableCaptionName" [(ngModel)]="captionName" (change)="updateCaptionName()" /> </div> <div class="option-row"> <input type="checkbox" id="show_header" [(ngModel)]="showHeader" (change)="updateShowHeader()"/> <label for="show_header">ShowHeader</label> </div> <div class="option-row"> <input type="checkbox" id="show_nodata_items" [(ngModel)]="showNoDataItems" (change)="updateShowNoDataItems()" /> <label for="show_nodata_items">ShowNoDataItems</label> </div> <div class="option-row"> <p class="desc">Check this box to show the unfiltered data items last in the slicer.</p> <input type="checkbox" id="show_nodata_items_last" [(ngModel)]="showNoDataItemsInLast" (change)="updateShowNoDataItemsInLast()" /> <label for="show_nodata_items_last">ShowNoDataItemsInLast</label> <p style="padding:2px 10px; background-color:lavender">Check this box to gray out items that aren’t present in the table.</p> <input type="checkbox" id="visually_nodata_items" [(ngModel)]="visuallyNoDataItems" (change)="updateVisuallyNoDataItems()" /> <label for="visually_nodata_items">VisuallyNoDataItems</label> </div> </div> </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: auto; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-group { margin-bottom: 6px; } label { display: inline-block; min-width: 90px; margin: 6px 0; } input { padding: 4px 6px; box-sizing: border-box; margin-bottom: 6px; } hr { border-color: #fff; opacity: .2; margin: 12px 0; } p { padding: 2px 10px; background-color: lavender; } 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/index.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);