Introduction
SpreadJS PivotTable supports two types of slicers:
Item slicerTimeline slicer
Same with table slicer, pivot slicer is also managed through slicer collection.
SpreadJS defined SlicerType to distinguish which type of slicer will be added.
Pivot Slicers work with 3 plugins: shapes, slicers, pivot-addon.
Item Slicers can be added to any field (excluding the Calc field).
The changes made on an Item Slicer are equal to using the manual filter, which means "textItems" in the label filter.
Only Timeline Slicer can be added to the Date type field.
The changes made on Timeline Slicer are equal to using the label condition filter, which means "condition" in the label filter.
For example, if we want to add a Pivot Table Item Slicer.
Assuming that we have created a PivotTable named "pt".
If want to add an item slicer to the "name" field.
Also, assuming that pivot table "pt" has a date field named "birthday",
and we want to add a Pivot Table Timeline Slicer.
You may have a question:
It is obvious that item slicer and timeline slicer can be added to a date field at the same time.
Can they work at the same time?
By default, no.
They all work through labelFilter. Setting textItems will cause the condition filter to be lost.
You can solve this problem with an option in PivotTable: allowMultipleFiltersPerField.
Try to Filter by clicking and dragging items in the Slicer.
With Item Slicer, you can also try pressing the shift and ctrl keys (for Windows) / command key (for Mac) when clicking or dragging.
Shape Base
Pivot Slicer is based on Shape, so you can use some Shape API to control Pivot Slicer
For example
Connect With PivotTable
Pivot Slicer supports connection management, including connecting or disconnecting the PivotTable.
If disconnecting from a PivotTable, the slicer filter action will not affect the PivotTable.
PivotTable filter actions will also not affect a Slicer.
For example, through this capability, you can use a slicer to manage multiple PivotTables from the same source.
Assuming that there are two PivotTables "pt1" and "pt2", both of them are created from a table called "table1", so they have same data source and fields.
Now you can use slicer_name to control the filters of two PivotTables.
<template>
<div class="sample-tutorial">
<gc-spread-sheets
class="sample-spreadsheets"
@workbookInitialized="initSpread"
>
<gc-worksheet> </gc-worksheet>
<gc-worksheet> </gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="block slicer-infos">
<div>Current Selected Slicer Info:</div><br>
<div class="slicer-info">
<label class="info-label">Slicer Name:</label>
<input class="info-input" id="name" v-model="name" @change="changeProperty('name')"/>
</div>
<div class="slicer-info">
<label class="info-label">Caption Name:</label>
<input class="info-input" id="captionName" v-model="captionName" @change="changeProperty('captionName')"/>
</div>
<div class="slicer-info">
<label class="info-label">X:</label>
<input type="number" class="info-input" name="x" id="x" min="0" max="1000" step="1" v-model="x" @change="changeProperty('x')"/>
</div>
<div class="slicer-info">
<label class="info-label">Y:</label>
<input type="number" class="info-input" name="y" id="y" min="0" max="1000" step="1" v-model="y" @change="changeProperty('y')"/>
</div>
<div class="slicer-info">
<label class="info-label">Width:</label>
<input type="number" class="info-input" name="width" id="width" min="0" max="1000" step="1" v-model="width" @change="changeProperty('width')"/>
</div>
<div class="slicer-info">
<label class="info-label">Height:</label>
<input type="number" class="info-input" name="height" id="height" min="0" max="1000" step="1" v-model="height" @change="changeProperty('height')"/>
</div>
</div>
<div class="block">
<div>Slicer Connection</div><br/>
<div class="Connection">
<div class="slicer-info">
<input type="checkbox" id="pt1" v-model="connectPT1" @change="changeConnection(1)"/>
<label for="pt1">Connect Pivot Table 1</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="pt2" v-model="connectPT2" @change="changeConnection(2)"/>
<label for="pt2">Connect Pivot Table 2</label>
</div>
</div>
</div>
</div>
</div>
</template>
<script>
import Vue from "vue";
import "@mescius/spread-sheets-vue";
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-slicers";
import "@mescius/spread-sheets-pivot-addon";
import "./styles.css";
let App = Vue.extend({
name: "app",
data: function () {
return {
name: "",
captionName: "",
x: 0,
y: 0,
width: 0,
height: 0,
connectPT1: false,
connectPT2: false,
};
},
methods: {
initSpread: function (spread) {
this.initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
this.initPivotTable(pivotLayoutSheet);
this.initSlicer(pivotLayoutSheet);
this.bindEvents(pivotLayoutSheet);
},
initSheets: function (spread) {
spread.suspendPaint();
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.setValue(0, 0, "Pivot Table 1");
sheet0.setValue(0, 10, "Pivot Table 2");
sheet0.name("PivotLayout");
spread.resumePaint();
},
initPivotTable: function (sheet) {
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, {by: GC.Pivot.DateGroupType.years}, {by: GC.Pivot.DateGroupType.months}] };
let pt1 = sheet.pivotTables.add("pt1", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
pt1.suspendLayout();
pt1.options.showRowHeader = true;
pt1.options.showColumnHeader = true;
pt1.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.top;
pt1.layoutType(GC.Spread.Pivot.PivotTableLayoutType.compact);
pt1.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("country", "country", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("city", "city", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt1.add("amount", "amounts", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt1.resumeLayout();
pt1.autoFitColumn();
let pt2 = sheet.pivotTables.add("pt2", "tableSales", 1, 10, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium23);
pt2.suspendLayout();
pt2.options.showRowHeader = true;
pt2.options.showColumnHeader = true;
pt2.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.top;
pt2.layoutType(GC.Spread.Pivot.PivotTableLayoutType.compact);
pt2.group(groupInfo);
pt2.add("Years (date)", "Years (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("Months (date)", "Months (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt2.add("amount", "Amounts", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt2.resumeLayout();
pt2.autoFitColumn();
},
initSlicer(sheet) {
sheet.suspendPaint();
let yearSlicer = sheet.slicers.add("Years", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light5(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
let monthSlicer = sheet.slicers.add("Months", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
this.setTimelineProp(yearSlicer, 967, 20, 310, 160, 1, "Years");
this.setTimelineProp(monthSlicer, 967, 160, 310, 160, 3, "Months");
let regionSlicer = sheet.slicers.add("region", 'pt1', "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
let countrySlicer = sheet.slicers.add("country", 'pt1', "country", GC.Spread.Sheets.Slicers.SlicerStyles.dark2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
let citySlicer = sheet.slicers.add("city", 'pt1', "city", GC.Spread.Sheets.Slicers.SlicerStyles.dark3(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
this.setSlicerProp(regionSlicer, 285, 20, 140, 200, 1, true, true);
this.setSlicerProp(countrySlicer, 425, 20, 140, 200, 1, true, true);
this.setSlicerProp(citySlicer, 565, 20, 140, 200, 1, true, true);
sheet.resumePaint();
},
setSlicerProp(slicer, x, y, width, height, columnCount, showHeader, showNoDataItemsInLast) {
slicer.position({x, y});
slicer.width(width);
slicer.height(height);
if (columnCount) {
slicer.columnCount(columnCount);
}
slicer.showHeader(!!showHeader);
slicer.showNoDataItemsInLast(!!showNoDataItemsInLast);
slicer.allowMove(false);
slicer.allowResize(false);
},
setTimelineProp (timeline, x, y, width, height, level, caption) {
timeline.position({x, y});
timeline.width(width);
timeline.height(height);
timeline.level(level);
timeline.captionName(caption);
timeline.allowMove(false);
timeline.allowResize(false);
},
bindEvents(sheet) {
let self = this;
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;
}
}
});
},
changeProperty(prop, v) {
if (!this.activeSlicer) {
return;
}
v = v || this[prop];
if (v !== null && v !== undefined && v !== '') {
this.activeSlicer[prop](v);
}
},
updateSlicerInfo() {
if (!this.activeSlicer) {
return;
}
let slicer = this.activeSlicer;
this.name = slicer.name();
this.captionName = slicer.captionName();
this.x = slicer.x();
this.y = slicer.y();
this.width = slicer.width();
this.height = slicer.height();
this.connectPT1 = slicer.isConnectedPivotTable('pt1');
this.connectPT2 = slicer.isConnectedPivotTable('pt2');
},
changeConnection (id) {
let connect = id === 1 ? this.connectPT1 : this.connectPT2;
let ptName = id === 1 ? 'pt1' : 'pt2';
let slicer = this.activeSlicer;
if(slicer) {
if (connect) {
slicer.connectPivotTable(ptName);
} else {
slicer.disconnectPivotTable(ptName);
}
}
}
},
computed: {
dataSource() {
return getData();
},
},
});
new Vue({ render: (h) => h(App) }).$mount("#app");
</script>
<!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/vue/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/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/en/lib/vue/license.js');
</script>
</head>
<body>
<div id="app"></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;
}
.info-label {
width: 35%;
display: inline-block;
}
.info-input {
width: 58%;
display: inline-block;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
meta: {
'*.css': { loader: 'css' },
'*.vue': { loader: 'vue-loader' }
},
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-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'jszip': 'npm:jszip/dist/jszip.js',
'css': 'npm:systemjs-plugin-css/css.js',
'vue': 'npm:vue/dist/vue.min.js',
'vue-loader': 'npm:systemjs-vue-browser/index.js',
'tiny-emitter': 'npm:tiny-emitter/index.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: 'js'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
}
}
});
})(this);
