The Pivot Table Item Slicer is managed by SlicerCollection(WorkSheet.slicers), same as the Table Slicers.
Item Slicers can be added to any field (excluding the Calc field).
The changes made on Item Slicer are equal to using the manual filter, which means "textItems" in the label filter.
Add Slicer
If we want to add a PivotTable Item Slicer, we can create a PivotTable named "pt"
(The specific implementation of initPivotTable can be found at the end of the article.):
Then add an item slicer to the "name" field:
Item Status
We defined the item with two status:
selected: whether an item is chosen by filter.
noData: if an item has been filtered by other filter, which means it will not take effect whether it is selected or not, we defined it as noData.
These two statuses build an item status.
For example, "selected && noData" or "unselected && hasData"
Using Slicer
Then we can define the slicer_name:
For example, if you want to show items in two column:
If you want to define the item height:
If you don't want to see the items that have the noData status:
A Sample to Create a Pivot Table
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 "@mescius/spread-sheets-pivot-addon";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
const GCsheets = GC.Spread.Sheets;
function _getElementById(id) {
return document.getElementById(id);
}
class App extends Component {
constructor(props) {
super(props);
this.state = {
renderChild: false
}
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
{this.state.renderChild ?
(<div class="options-container">
<PivotTableItemSlicer spread={this.spread} ptName={this.ptName}></PivotTableItemSlicer>
</div>) : ""}
</div>
);
}
componentDidMount() {
this.setState(() => ({
renderChild: true
}))
}
initSpread(spread) {
this.spread = spread;
spread.setSheetCount(2);
this.initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
this.initPivotTable(pivotLayoutSheet);
}
initSheets (spread) {
spread.suspendPaint();
spread.setSheetCount(2);
let sheet = spread.getSheet(1);
sheet.name("DataSource");
sheet.setRowCount(650);
sheet.setColumnWidth(5, 120);
sheet.getCell(-1, 5).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,1).formatter("$ #,##0");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 0, 0, 637, 6);
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
let sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
spread.resumePaint();
}
getSource(sheet, tableSource) {
sheet.name("DataSource");
sheet.setRowCount(1984);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1, 4, 0, 2).formatter("$ #,##0");
let table = sheet.tables.add('table', 0, 0, 1984, 6);
for (let i = 2; i <= 1984; i++) {
sheet.setFormula(i - 1, 5, '=D' + i + '*E' + i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
sheet.setArray(0, 0, tableSource);
return table.name();
}
initPivotTable(sheet) {
sheet.setRowCount(1000);
let option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true
};
let pivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8, option);
pivotTable.suspendLayout();
pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("country", "countrys", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("city", "city", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
};
pivotTable.group(groupInfo);
pivotTable.add("amount", "amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.none;
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
this.ptName = pivotTable.name();
}
}
class PivotTableItemSlicer extends Component {
constructor(props) {
super(props);
this.state = {
columnCount: 0,
showHeader: true,
showNoDataItems: false,
visuallyNoDataItems: true,
showNoDataItemsInLast: true,
sortState: 1,
selectedField: "region",
styleName: ""
}
this.slicerCount = 0;
this.activeSlicer = null;
this.sheet = this.props.spread.getSheet(0);
this.initSlicers();
this.bindEvents();
}
initSlicers () {
let sheet = this.sheet;
let ptName = this.props.ptName;
let slicer_region = sheet.slicers.add("slicer_region", ptName, "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_region.position(new GC.Spread.Sheets.Point(491, 20));
slicer_region.height(210);
let slicer_country = sheet.slicers.add("slicer_country", ptName, "country", GC.Spread.Sheets.Slicers.SlicerStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
slicer_country.position(new GC.Spread.Sheets.Point(691, 20));
slicer_country.height(460);
slicer_country.showNoDataItems(false);
let slicer_city = sheet.slicers.add("slicer_city", ptName, "city", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_city.position(new GC.Spread.Sheets.Point(891, 20));
slicer_city.height(320);
slicer_city.columnCount(2);
}
bindEvents () {
let self = this;
let sheet = this.sheet;
sheet.bind(GC.Spread.Sheets.Events.SlicerChanged, function () {
let slicers = sheet.slicers.all();
for (let i = 0; i < slicers.length; i++) {
if (slicers[i].isSelected()) {
self.activeSlicer = slicers[i];
self.updateSlicerInfo();
break;
}
}
});
}
updateSlicerInfo () {
if (!this.activeSlicer) {
return;
}
let slicer = this.activeSlicer;
this.setState({
columnCount: slicer.columnCount(),
showHeader: slicer.showHeader(),
showNoDataItems: slicer.showNoDataItems(),
visuallyNoDataItems: slicer.visuallyNoDataItems(),
showNoDataItemsInLast: slicer.showNoDataItemsInLast(),
sortState: slicer.sortState(),
styleName: slicer.style().name().substr(11).toLowerCase()
});
}
setColumnCount (v) {
let numberV = parseInt(v, 10);
if (!isNaN(numberV)) {
this.setState({ columnCount: numberV });
this.changeProperty("columnCount", numberV);
} else {
this.setState({ columnCount: null });
}
}
changeProperty(prop, v) {
if (!this.activeSlicer) {
return;
}
if (v !== null && v !== undefined) {
this.activeSlicer[prop](v);
}
}
setStyle () {
if (!this.activeSlicer) {
return;
}
let style = GC.Spread.Sheets.Slicers.SlicerStyles[this.state.styleName]();
this.activeSlicer.style(style);
}
addSlicer () {
let sheet = this.sheet;
sheet.slicers.add(this.state.selectedField + "_" + this.slicerCount, this.props.ptName, this.state.selectedField, GC.Spread.Sheets.Slicers.SlicerStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
this.slicerCount += 1;
}
render() {
return (
<div class="options-container">
<div class="block slicer-infos">
<div>Current Selected Slicer Info:</div><br/>
<div class="slicer-info">
<label>Column Count:</label>
<input class="info-input" type="number" id="columnCount" value={this.state.columnCount} onChange={e => {this.setColumnCount(e.target.value)}}/>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHeader" checked={this.state.showHeader} onChange={e => {this.setState({showHeader: e.target.checked}); this.changeProperty("showHeader", e.target.checked)}} />
<label for="showHeader">Display Header</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItems" checked={!this.state.showNoDataItems} onChange={e => {this.setState({showNoDataItems: !e.target.checked}); this.changeProperty("showNoDataItems", !e.target.checked)}} />
<label for="showNoDataItems">Hide Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="visuallyNoDataItems" checked={this.state.visuallyNoDataItems} onChange={e => {this.setState({visuallyNoDataItems: e.target.checked}); this.changeProperty("visuallyNoDataItems", e.target.checked)}} />
<label for="visuallyNoDataItems">Visually Indicate Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItemsInLast" checked={this.state.showNoDataItemsInLast} onChange={e => {this.setState({showNoDataItemsInLast: e.target.checked}); this.changeProperty("showNoDataItemsInLast", e.target.checked)}} />
<label for="showNoDataItemsInLast">Show Items With No Data Last</label>
</div>
<div class="slicer-info">
<p>Sort State</p>
<input type="radio" id="ascending" name="sortState" checked={this.state.sortState === 1} onChange={e => {this.setState({sortState: e.target.checked ? 1 : 2}); this.changeProperty("sortState", 1)}} />
<label for="ascending">Ascending(A to Z)</label><br/>
<input type="radio" id="descending" name="sortState" checked={this.state.sortState === 2} onChange={e => {this.setState({sortState: e.target.checked ? 2 : 1}); this.changeProperty("sortState", 2)}}/>
<label for="descending">Descending(Z to A)</label><br/>
</div>
</div>
<div class="block">
<div>Add Slicer</div>
<br/>
<select class="select-list" name="slicerList" id="slicerList" value={this.state.selectedField} onChange={e => {this.setState({selectedField: e.target.value})}}>
<option value="region">region</option>
<option value="country">country</option>
<option value="city">city</option>
<option value="date">date</option>
<option value="amount">amount</option>
<option value="id">id</option>
</select>
<button class="select-button" id="addSlicerBtn" onClick={this.addSlicer.bind(this)}>Add</button>
</div>
<div class="block">
<div>Change Current Slicer Style</div>
<br/>
<div class="slicerStyle">
<select class="select-list" name="slicerStyle" id="slicerStyle" value={this.state.styleName} onChange={e => {this.setState({styleName: e.target.value})}}>
<option value="light1">light1</option>
<option value="light2">light2</option>
<option value="light3">light3</option>
<option value="light4">light4</option>
<option value="light5">light5</option>
<option value="light6">light6</option>
<option value="dark1">dark1</option>
<option value="dark2">dark2</option>
<option value="dark3">dark3</option>
<option value="dark4">dark4</option>
<option value="dark5">dark5</option>
<option value="dark6">dark6</option>
<option value="other1">other1</option>
<option value="other2">other2</option>
</select>
<button class="select-button" id="changeStyle" onClick={this.setStyle.bind(this)}>Change</button>
</div>
</div>
</div>
);
}
}
ReactDOM.render(<App />, _getElementById('app'));
<!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">
<script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script>
<!-- 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% - 330px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 330px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.slicer-info {
margin-top: 5px;
margin-bottom: 5px;
}
.block {
border: 1px solid gray;
padding-left: 5px;
padding-top: 10px;
padding-bottom: 10px;
margin-bottom: 1px;
}
.select-list {
width: 120px;
}
.select-button {
width: 80px;
}
(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-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/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);
