The Table 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 a slicer header and a slicer body.
The slicer header is composed of slicer caption, multiSelect and clear filter button. The slicer caption is used to show a caption. The clear filter button is used to un-filter the slicer and it is inactive by default. It is active only when a slicer item is filtered .
The 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 './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-slicers";
import Panel from './panel';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const { useState, useEffect } = React;
let selectedSlicersList = [];
let disableNameValue = true;
let nameValue = '';
let captionNameValue = '';
let showHeaderValue = true;
let showNoDataItemsValue = true;
let showNoDataItemsInLastValue = false;
let visuallyNoDataItemsValue = true;
let disableCaptionNameValue = true;
export const AppFunc = () => {
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) => {
if (!value) {
return;
}
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) {
disableNameValue = false;
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]});
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
import './styles.css';
import Panel from './panel';
const Component = React.Component;
const GCsheets = GC.Spread.Sheets;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
// this.selectedSlicersList = [];
this.state = {
selectedSlicersList: [],
disableNameValue: true,
nameValue: '',
captionNameValue: '',
showHeaderValue: true,
showNoDataItemsValue: true,
showNoDataItemsInLastValue: false,
visuallyNoDataItemsValue: true,
disableCaptionNameValue: true
};
this.bindEvent = this.bindEvent.bind(this);
this.setName = this.setName.bind(this);
this.setDisableName = this.setDisableName.bind(this);
this.setDisableCaptionName = this.setDisableCaptionName.bind(this);
this.setCaptionName = this.setCaptionName.bind(this);
this.setShowHeader = this.setShowHeader.bind(this);
this.setShowNoDataItems = this.setShowNoDataItems.bind(this);
this.setShowNoDataItemsInLast = this.setShowNoDataItemsInLast.bind(this);
this.setVisuallyNoDataItems = this.setVisuallyNoDataItems.bind(this);
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
name={this.state.nameValue}
captionName={this.state.captionNameValue}
showHeader={this.state.showHeaderValue}
showNoDataItems={this.state.showNoDataItemsValue}
showNoDataItemsInLast={this.state.showNoDataItemsInLastValue}
visuallyNoDataItems={this.state.visuallyNoDataItemsValue}
setName={this.setName}
disableName={this.state.disableNameValue}
setDisableName={this.setDisableName}
disableCaptionName={this.state.disableCaptionNameValue}
setDisableCaptionName={this.setDisableCaptionName}
setCaptionName={this.setCaptionName}
setShowHeader={this.setShowHeader}
setShowNoDataItems={this.setShowNoDataItems}
setShowNoDataItemsInLast={this.setShowNoDataItemsInLast}
setVisuallyNoDataItems={this.setVisuallyNoDataItems}
></Panel>
</div>
);
}
_isEmptyObject(obj) {
for (let name in obj) {
return false;
}
return true;
}
getSelectedSlicers(sheet) {
if (!sheet) {
return null;
}
let slicers = sheet.slicers.all();
if (!slicers || this._isEmptyObject(slicers)) {
return null;
}
let selectedSlicersList = [];
for (let item in slicers) {
let slicer = slicers[item];
if (slicer.isSelected()) {
selectedSlicersList.push(slicer);
}
}
return selectedSlicersList;
}
bindEvent(spread) {
let self = this;
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()) {
// self.setState({ disableCaptionNameValue: false });
self.state.disableCaptionNameValue = false;
self.state.selectedSlicersList = self.getSelectedSlicers(sheet);
if (self.state.selectedSlicersList && self.state.selectedSlicersList.length > 1) {
self.setState({
disableNameValue: true,
nameValue: '',
captionNameValue: '',
showHeaderValue: false,
showNoDataItemsValue: false,
showNoDataItemsInLastValue: false,
visuallyNoDataItemsValue: false,
});
}
else if (self.state.selectedSlicersList.length === 1) {
self.setState({
disableNameValue: false,
nameValue: slicer.name(),
disableChangeSlicerOptions: false,
captionNameValue: slicer.captionName(),
showHeaderValue: slicer.showHeader(),
showNoDataItemsValue: slicer.showNoDataItems(),
showNoDataItemsInLastValue: slicer.showNoDataItemsInLast(),
visuallyNoDataItemsValue: slicer.visuallyNoDataItems(),
});
}
}
else {
self.setState({
disableNameValue: true,
disableCaptionNameValue: true
});
}
}
});
}
initSpread(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] });
}
setName(nameValue) {
if (!nameValue) {
return;
}
this.state.selectedSlicersList.forEach((slicer) => {
slicer.name(nameValue);
})
this.setState({ nameValue: nameValue });
}
setDisableName(disableNameValue) {
this.setState({ disableNameValue: disableNameValue });
}
setDisableCaptionName(disableCaptionNameValue) {
this.setState({ disableCaptionNameValue: disableCaptionNameValue });
}
setCaptionName(captionNameValue) {
this.state.selectedSlicersList.forEach((slicer) => {
slicer.captionName(captionNameValue);
})
this.setState({ captionNameValue: captionNameValue });
}
setShowHeader(showHeaderValue) {
this.state.selectedSlicersList.forEach((slicer) => {
slicer.showHeader(showHeaderValue);
})
this.setState({ showHeaderValue: showHeaderValue });
}
setShowNoDataItems(showNoDataItemsValue) {
this.state.selectedSlicersList.forEach((slicer) => {
slicer.showNoDataItems(showNoDataItemsValue);
})
this.setState({ showNoDataItemsValue: showNoDataItemsValue });
}
setShowNoDataItemsInLast(showNoDataItemsInLastValue) {
this.state.selectedSlicersList.forEach((slicer) => {
slicer.showNoDataItemsInLast(showNoDataItemsInLastValue);
})
this.setState({ showNoDataItemsInLastValue: showNoDataItemsInLastValue });
}
setVisuallyNoDataItems(visuallyNoDataItemsValue) {
this.state.selectedSlicersList.forEach((slicer) => {
slicer.visuallyNoDataItems(visuallyNoDataItemsValue);
})
this.setState({ visuallyNoDataItemsValue: visuallyNoDataItemsValue });
}
}
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: '#F4F8EB' }}>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" disabled={disableCaptionName} 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" disabled={disableCaptionName} 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" disabled={disableCaptionName} checked={showNoDataItemsInLast} onChange={(e) => { setShowNoDataItemsInLast(e.target.checked) }} />
<label for="show_nodata_items_last">ShowNoDataItemsInLast</label>
<p style={{ padding: '2px 10px', backgroundColor: '#F4F8EB' }}>Check this box to gray out items that aren’t present in the table.</p>
<input type="checkbox" id="visually_nodata_items" disabled={disableCaptionName} 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/@mescius/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: #F4F8EB;
}
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: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-slicers': 'npm:@mescius/spread-sheets-slicers/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/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);
