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 * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity/spread-sheets'; import Panel from './panel'; import { SpreadSheets, Worksheet } from '@grapecity/spread-sheets-react'; import './styles.css'; const { useState, useEffect } = React; let selectedSlicersList = []; let disableNameValue = false; let nameValue = ''; let captionNameValue = ''; let showHeaderValue = false; let showNoDataItemsValue = false; let showNoDataItemsInLastValue = false; let visuallyNoDataItemsValue = false; let disableCaptionNameValue = false; const App = () => { const [name, setName] = useState(nameValue); const [captionName, setCaptionName] = useState(captionNameValue); const [showHeader, setShowHeader] = useState(showHeaderValue); const [showNoDataItems, setShowNoDataItems] = useState(showNoDataItemsValue); const [showNoDataItemsInLast, setShowNoDataItemsInLast] = useState(showNoDataItemsInLastValue); const [visuallyNoDataItems, setVisuallyNoDataItems] = useState(visuallyNoDataItemsValue); const [disableName, setDisableName] = useState(disableNameValue); const [disableCaptionName, setDisableCaptionName] = useState(disableCaptionNameValue); useEffect(() => { setNameToSpread(name); }, [name]); useEffect(() => { setCaptionNameToSpread(captionName); }, [captionName]); useEffect(() => { setShowHeaderToSpread(showHeader); }, [showHeader]); useEffect(() => { setShowNoDataItemsToSpread(showNoDataItems); }, [showNoDataItems]); useEffect(() => { setShowNoDataItemsInLastToSpread(showNoDataItemsInLast); }, [showNoDataItemsInLast]); useEffect(() => { setVisuallyNoDataItemsToSpread(visuallyNoDataItems); }, [visuallyNoDataItems]); return ( <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread => initSpread(spread, setName, setCaptionName, setShowHeader, setShowNoDataItems, setShowNoDataItemsInLast, setVisuallyNoDataItems, setDisableName, setDisableCaptionName)}> <Worksheet> </Worksheet> </SpreadSheets> </div> <Panel name={name} captionName={captionName} showHeader={showHeader} showNoDataItems={showNoDataItems} showNoDataItemsInLast={showNoDataItemsInLast} visuallyNoDataItems={visuallyNoDataItems} setName={setName} disableName={disableName} setDisableName={setDisableName} disableCaptionName={disableCaptionName} setDisableCaptionName={setDisableCaptionName} setCaptionName={setCaptionName} setShowHeader={setShowHeader} setShowNoDataItems={setShowNoDataItems} setShowNoDataItemsInLast={setShowNoDataItemsInLast} setVisuallyNoDataItems={setVisuallyNoDataItems} ></Panel> </div> ) } const setNameToSpread = (value) => { nameValue = value; selectedSlicersList.forEach((slicer) => { slicer.name(value); }) } const setCaptionNameToSpread = (value) => { captionNameValue = value; selectedSlicersList.forEach((slicer) => { slicer.captionName(value); }) } const setShowHeaderToSpread = (value) => { showHeaderValue = value; selectedSlicersList.forEach((slicer) => { slicer.showHeader(value); }) } const setShowNoDataItemsToSpread = (value) => { showNoDataItemsValue = value; selectedSlicersList.forEach((slicer) => { slicer.showNoDataItems(value); }) } const setShowNoDataItemsInLastToSpread = (value) => { showNoDataItemsInLastValue = value; selectedSlicersList.forEach((slicer) => { slicer.showNoDataItemsInLast(value); }) } const setVisuallyNoDataItemsToSpread = (value) => { visuallyNoDataItemsValue = value; selectedSlicersList.forEach((slicer) => { slicer.visuallyNoDataItems(value); }) } const bindEvent = (spread, ...fns) => { spread.bind(GC.Spread.Sheets.Events.SlicerChanged, function (event, args) { let sheet = args.sheet; let slicer = args.slicer; if (!slicer) { return; } let propertyName = args.propertyName; if (propertyName === "isSelected") { if (slicer.isSelected()) { disableCaptionNameValue = false; selectedSlicersList = getSelectedSlicers(sheet); if (selectedSlicersList && selectedSlicersList.length > 1) { disableNameValue = true; nameValue = ''; captionNameValue = ''; showHeaderValue = false; showNoDataItemsValue = false; showNoDataItemsInLastValue = false; visuallyNoDataItemsValue = false; } else if (selectedSlicersList.length === 1) { nameValue = slicer.name(); captionNameValue = slicer.captionName(); showHeaderValue = slicer.showHeader(); showNoDataItemsValue = slicer.showNoDataItems(); showNoDataItemsInLastValue = slicer.showNoDataItemsInLast(); visuallyNoDataItemsValue = slicer.visuallyNoDataItems(); } } else { disableNameValue = true; disableCaptionNameValue = true; } } const [setName, setCaptionName, setShowHeader, setShowNoDataItems, setShowNoDataItemsInLast, setVisuallyNoDataItems, setDisableName, setDisableCaptionName] = fns; setName(nameValue); setDisableName(disableNameValue); setDisableCaptionName(disableCaptionNameValue); setCaptionName(captionNameValue); setShowHeader(showHeaderValue); setShowNoDataItems(showNoDataItemsValue); setShowNoDataItemsInLast(showNoDataItemsInLastValue); setVisuallyNoDataItems(visuallyNoDataItemsValue); }); } const getSelectedSlicers = (sheet) => { 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) => { for (let name in obj) { return false; } return true; } const initSpread = (spread, ...args) => { 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(); bindEvent(spread, ...args); table.getSlicerData().doFilter("City", {exclusiveRowIndexes: [0]}); } ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react'; export default function Panel(props) { const { name, captionName, showHeader, showNoDataItems, showNoDataItemsInLast, visuallyNoDataItems, setName, setCaptionName, setShowHeader, setShowNoDataItems, setShowNoDataItemsInLast, setVisuallyNoDataItems, disableName, disableCaptionName, } = props; return ( <div class="options-container"> <p style={{ padding: '2px 10px', backgroundColor: '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} value={name} onChange={(e) => { setName(e.target.value) }} /> </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} value={captionName} onChange={(e) => { setCaptionName(e.target.value) }} /> </div> <div class="option-row"> <input type="checkbox" id="show_header" checked={showHeader} onChange={(e) => { setShowHeader(e.target.checked) }} /> <label for="show_header">ShowHeader</label> </div> <div class="option-row"> <input type="checkbox" id="show_nodata_items" checked={showNoDataItems} onChange={(e) => { setShowNoDataItems(e.target.checked) }} /> <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" checked={showNoDataItemsInLast} onChange={(e) => { setShowNoDataItemsInLast(e.target.checked) }} /> <label for="show_nodata_items_last">ShowNoDataItemsInLast</label> <p style={{ padding: '2px 10px', backgroundColor: 'lavender' }}>Check this box to gray out items that aren’t present in the table.</p> <input type="checkbox" id="visually_nodata_items" checked={visuallyNoDataItems} onChange={(e) => { setVisuallyNoDataItems(e.target.checked) }} /> <label for="visually_nodata_items">VisuallyNoDataItems</label> </div> </div> ); }
<!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" style="height: 100%;"></div> </body> </html>
.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: '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);