PivotTable Timeline Slicer | Enhanced Add-Ons

The Pivot Table Timeline Slicer is managed by SlicerCollection(WorkSheet.slicers), same as the Table Slicers. Timeline Slicers can only be added to date field. The changes made on Timeline Slicers are the same as using the condition filter, which means "condition" in the label filter. Add Timeline If we want to add a Pivot Table Timeline Slicer, create a PivotTable named "pt" (The specific implementation of initPivotTable can be found at the end of the article.): then add an Timeline slicer to the "date" field. Using Timeline Then we can control the timeline_date: For example, if you don't need to show the horizontal scrollbar: If you want to scroll the timeline to some specific day: If you want to filter by year: A Sample to Create a Pivot Table Custom Timeline Slicer Themes SpreadJS supports customize the theme of the pivot table timeline slicer. Using code such as the following:

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 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 { useState, useEffect } = React;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const [ptName, setPTName] = useState('');
const initSpread = (spread) => {
setSpread(spread);
spread.setSheetCount(2);
initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
initPivotTable(pivotLayoutSheet);
}
const initSheets = (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.name("PivotLayout");
spread.resumePaint();
}
const initPivotTable = (sheet) => {
sheet.setRowCount(1000);
var option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true
};
var pivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8.name(), option);
pivotTable.suspendLayout();
var groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.years
},
{
by: GC.Pivot.DateGroupType.quarters
},
{
by: GC.Pivot.DateGroupType.months
},
]
};
pivotTable.group(groupInfo);
pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Quarters", "Quarters", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("date", "Months", GC.Spread.Pivot.PivotTableFieldType.rowField);
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();
setPTName(pivotTable.name());
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
<div class="options-container">
<PivotTableItemSlicer spread={spread} ptName={ptName}></PivotTableItemSlicer>
</div>
</div>
);
}
function PivotTableItemSlicer({ spread, ptName }) {
const [state, setState] = useState({
showHeader: true,
showHorizontalScrollbar: true,
showSelectionLabel: true,
showTimeLevel: true,
styleName: "",
});
const [slicerCount, setSlicerCount] = useState(0);
useEffect(() => {
if (spread) {
initCustomThemes(spread);
const sheet = spread.getSheet(0);
initSlicers(sheet);
bindEvents(sheet);
}
}, [spread])
const initSlicers = (sheet) => {
sheet.suspendPaint();
var timeline_year = sheet.slicers.add("timeline_year", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark6().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_year.position(new GC.Spread.Sheets.Point(355, 20));
timeline_year.level(GC.Spread.Sheets.Slicers.TimelineLevel.years);
timeline_year.showSelectionLabel(false);
timeline_year.showTimeLevel(false);
timeline_year.showHorizontalScrollbar(false);
timeline_year.height(100);
timeline_year.captionName("Years");
var timeline_quarter = sheet.slicers.add("timeline_quarter", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_quarter.position(new GC.Spread.Sheets.Point(355, 130));
timeline_quarter.level(GC.Spread.Sheets.Slicers.TimelineLevel.quarters);
timeline_quarter.captionName("Quarters");
timeline_quarter.showSelectionLabel(false);
var timeline_month = sheet.slicers.add("timeline_month", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.light4().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_month.position(new GC.Spread.Sheets.Point(355, 290));
timeline_month.captionName("Months");
timeline_month.showTimeLevel(false);
sheet.resumePaint();
}
const initCustomThemes = (spread) => {
const theme1 = new GC.Spread.Sheets.Slicers.TimelineStyle();
theme1.fromJSON(GC.Spread.Sheets.Slicers.TimelineStyles.light1().toJSON());
theme1.name('custom1');
theme1.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('Accent 5 80'));
const theme2 = new GC.Spread.Sheets.Slicers.TimelineStyle();
theme2.fromJSON(GC.Spread.Sheets.Slicers.TimelineStyles.light2().toJSON());
theme2.name('custom2');
theme2.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('Accent 2 80'));
spread.customTimelineThemes.add(theme1);
spread.customTimelineThemes.add(theme2);
}
const bindEvents = (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()) {
updateSlicerInfo(slicers[i]);
break;
}
}
});
}
const getActiveSlicer = () => {
if (!spread) {
return null;
}
const sheet = spread.getSheet(0);
const slicers = sheet.slicers.all();
for (let i = 0; i < slicers.length; i++) {
if (slicers[i].isSelected()) {
return slicers[i];
}
}
return null;
}
const updateSlicerInfo = (activeSlicer) => {
let slicer = activeSlicer;
const slicerStyleName = slicer.style().name().toLowerCase();
const styleName = slicerStyleName.includes('custom') ? slicerStyleName : slicerStyleName.substr(15);
setState({
...state,
showHeader: slicer.showHeader(),
showHorizontalScrollbar: slicer.showHorizontalScrollbar(),
showSelectionLabel: slicer.showSelectionLabel(),
showTimeLevel: slicer.showTimeLevel(),
styleName: styleName
});
}
const changeProperty = (prop, v) => {
const activeSlicer = getActiveSlicer();
if (!activeSlicer) {
return;
}
if (v !== null && v !== undefined) {
activeSlicer[prop](v);
}
}
const setStyle = () => {
const activeSlicer = getActiveSlicer();
if (!activeSlicer) {
return;
}
activeSlicer.style(state.styleName);
}
const addSlicer = () => {
let sheet = spread.getSheet(0);
sheet.slicers.add("timeline_" + slicerCount, ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
setSlicerCount(slicerCount + 1);
}
return (
<div class="options-container">
<div class="block slicer-infos">
<div>Current Selected Timeline Info:</div><br />
<div class="slicer-info">
<input type="checkbox" id="showHeader" checked={state.showHeader} onChange={e => { setState({ ...state, showHeader: e.target.checked }); changeProperty("showHeader", e.target.checked) }} />
<label for="showHeader">Show Header</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHorizontalScrollbar" checked={state.showHorizontalScrollbar} onChange={e => { setState({ ...state, showHorizontalScrollbar: e.target.checked }); changeProperty("showHorizontalScrollbar", e.target.checked) }} />
<label for="showHorizontalScrollbar">Show Horizontal Scrollbar</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showSelectionLabel" checked={state.showSelectionLabel} onChange={e => { setState({ ...state, showSelectionLabel: e.target.checked }); changeProperty("showSelectionLabel", e.target.checked) }} />
<label for="showSelectionLabel">Show Selection Label</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showTimeLevel" checked={state.showTimeLevel} onChange={e => { setState({ ...state, showTimeLevel: e.target.checked }); changeProperty("showTimeLevel", e.target.checked) }} />
<label for="showTimeLevel">Show Time Level</label>
</div>
</div>
<div class="block">
<div>Add Timeline</div><br />
<button id="addSlicerBtn" onClick={addSlicer}>Add Timeline Slicer</button>
</div>
<div class="block">
<div>Change Current Timeline Style</div><br />
<div class="slicerStyle">
<select class="select-list" name="slicerStyle" id="slicerStyle" value={state.styleName} onChange={e => { setState({ ...state, 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="custom1">custom1</option>
<option value="custom2">custom2</option>
</select>
<button class="select-button" id="changeStyle" onClick={setStyle}>Change</button>
</div>
</div>
</div>
);
}

import * as React from 'react';
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;
export 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();
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();
}
initPivotTable(sheet) {
sheet.setRowCount(1000);
var option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true
};
var pivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8.name(), option);
pivotTable.suspendLayout();
var groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.years
},
{
by: GC.Pivot.DateGroupType.quarters
},
{
by: GC.Pivot.DateGroupType.months
},
]
};
pivotTable.group(groupInfo);
pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Quarters", "Quarters", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("date", "Months", GC.Spread.Pivot.PivotTableFieldType.rowField);
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 = {
showHeader: true,
showHorizontalScrollbar: true,
showSelectionLabel: true,
showTimeLevel: true,
styleName: "",
}
this.slicerCount = 0;
this.activeSlicer = null;
this.initCustomThemes(this.props.spread);
this.sheet = this.props.spread.getSheet(0);
this.initSlicers();
this.bindEvents();
}
initSlicers () {
let sheet = this.sheet;
let ptName = this.props.ptName;
sheet.suspendPaint();
var timeline_year = sheet.slicers.add("timeline_year", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark6().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_year.position(new GC.Spread.Sheets.Point(355, 20));
timeline_year.level(GC.Spread.Sheets.Slicers.TimelineLevel.years);
timeline_year.showSelectionLabel(false);
timeline_year.showTimeLevel(false);
timeline_year.showHorizontalScrollbar(false);
timeline_year.height(100);
timeline_year.captionName("Years");
var timeline_quarter = sheet.slicers.add("timeline_quarter", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_quarter.position(new GC.Spread.Sheets.Point(355, 130));
timeline_quarter.level(GC.Spread.Sheets.Slicers.TimelineLevel.quarters);
timeline_quarter.captionName("Quarters");
timeline_quarter.showSelectionLabel(false);
var timeline_month = sheet.slicers.add("timeline_month", ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.light4().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_month.position(new GC.Spread.Sheets.Point(355, 290));
timeline_month.captionName("Months");
timeline_month.showTimeLevel(false);
sheet.resumePaint();
}
initCustomThemes (spread) {
const theme1 = new GC.Spread.Sheets.Slicers.TimelineStyle();
theme1.fromJSON(GC.Spread.Sheets.Slicers.TimelineStyles.light1().toJSON());
theme1.name('custom1');
theme1.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('Accent 5 80'));
const theme2 = new GC.Spread.Sheets.Slicers.TimelineStyle();
theme2.fromJSON(GC.Spread.Sheets.Slicers.TimelineStyles.light2().toJSON());
theme2.name('custom2');
theme2.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('Accent 2 80'));
spread.customTimelineThemes.add(theme1);
spread.customTimelineThemes.add(theme2);
}
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;
const slicerStyleName = slicer.style().name().toLowerCase();
const styleName = slicerStyleName.includes('custom') ? slicerStyleName : slicerStyleName.substr(15);
this.setState({
showHeader: slicer.showHeader(),
showHorizontalScrollbar: slicer.showHorizontalScrollbar(),
showSelectionLabel: slicer.showSelectionLabel(),
showTimeLevel: slicer.showTimeLevel(),
styleName: styleName
});
}
changeProperty(prop, v) {
if (!this.activeSlicer) {
return;
}
if (v !== null && v !== undefined) {
this.activeSlicer[prop](v);
}
}
setStyle () {
if (!this.activeSlicer) {
return;
}
this.activeSlicer.style(this.state.styleName);
}
addSlicer () {
let sheet = this.sheet;
sheet.slicers.add("timeline_" + this.slicerCount, this.props.ptName, "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
this.slicerCount += 1;
}
render() {
return (
<div class="options-container">
<div class="block slicer-infos">
<div>Current Selected Timeline Info:</div><br/>
<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">Show Header</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHorizontalScrollbar" checked={this.state.showHorizontalScrollbar} onChange={e => {this.setState({showHorizontalScrollbar: e.target.checked}); this.changeProperty("showHorizontalScrollbar", e.target.checked)}} />
<label for="showHorizontalScrollbar">Show Horizontal Scrollbar</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showSelectionLabel" checked={this.state.showSelectionLabel} onChange={e => {this.setState({showSelectionLabel: e.target.checked}); this.changeProperty("showSelectionLabel", e.target.checked)}} />
<label for="showSelectionLabel">Show Selection Label</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showTimeLevel" checked={this.state.showTimeLevel} onChange={e => {this.setState({showTimeLevel: e.target.checked}); this.changeProperty("showTimeLevel", e.target.checked)}} />
<label for="showTimeLevel">Show Time Level</label>
</div>
</div>
<div class="block">
<div>Add Timeline</div><br/>
<button id="addSlicerBtn" onClick={this.addSlicer.bind(this)}>Add Timeline Slicer</button>
</div>
<div class="block">
<div>Change Current Timeline 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="custom1">custom1</option>
<option value="custom2">custom2</option>
</select>
<button class="select-button" id="changeStyle" onClick={this.setStyle.bind(this)}>Change</button>
</div>
</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">
<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);