PivotTable Timeline Slicer
SpreadJS PivotTables supports timeline slicers, which can carry out date-slicing operations conveniently.
Try clicking on slicers in the below demo to see all of the available properties.
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.):
var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
initPivotTable();
then add an Timeline slicer to the "date" field.
var timeline_date = sheet.slicers.add("timeline_date", pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
Using Timeline
Then we can control the timeline_date: For example, if you don't need to show the horizontal scrollbar:
timeline_date.showHorizontalScrollbar(false);
If you want to scroll the timeline to some specific day:
timeline_date.scrollPosition(new Date('2021-6-23'));
If you want to filter by year:
timeline_date.level(GC.Spread.Sheets.Slicers.TimelineLevel.years);
A Sample to Create a Pivot Table
function initPivotTable () {
var dataSource = [
[ "name", "product", "date", "amount", "price", "sales" ],
[ "chris", "desk", new Date("2020-10-08T16:00:00.000Z"), 5, 199, 995 ],
[ "radow", "pen", new Date("2020-09-15T16:00:00.000Z"), 2, 5, 10 ],
[ "peyton", "pencil", new Date("2021-06-22T16:00:00.000Z"), 6, 1.5, 9 ],
[ "johnson", "chair", new Date("2021-07-19T16:00:00.000Z"), 7, 68, 476 ],
[ "vic", "notebook", new Date("2021-01-13T16:00:00.000Z"), 7, 3.2, 22.4 ],
[ "lan", "desk", new Date("2021-03-12T16:00:00.000Z"), 9, 199, 1791 ],
[ "chris", "pen", new Date("2021-03-06T16:00:00.000Z"), 4, 5, 20 ],
[ "chris", "pencil", new Date("2020-09-02T16:00:00.000Z"), 10, 1.5, 15 ],
[ "radow", "chair", new Date("2020-08-09T16:00:00.000Z"), 3, 68, 204 ],
[ "peyton", "notebook", new Date("2021-02-08T16:00:00.000Z"), 9, 3.2, 28.8 ],
[ "johnson", "desk", new Date("2021-07-03T16:00:00.000Z"), 7, 199, 1393],
[ "vic", "pen", new Date("2021-06-27T16:00:00.000Z"), 8, 5, 40],
[ "lan", "pencil", new Date("2020-10-10T16:00:00.000Z"), 2, 1.5, 3],
[ "chris", "chair", new Date("2021-03-04T16:00:00.000Z"), 2, 68, 136],
[ "chris", "notebook", new Date("2021-02-21T16:00:00.000Z"), 11, 3.2, 35.2],
[ "radow", "desk", new Date("2021-06-03T16:00:00.000Z"), 6, 199, 1194]
];
var sourceSheet = spread.sheets[0];
sourceSheet.setArray(0, 0, dataSource);
sourceSheet.tables.add("table1", 0, 0, 17, 6);
spread.sheets[0].name("sourceSheet");
var pivotSheet = new GC.Spread.Sheets.Worksheet('pivotTable1');
pivotSheet.setRowCount(2000);
pivotSheet.setColumnCount(30);
spread.addSheet(1, pivotSheet);
var pt = pivotSheet.pivotTables.add("pivotTable1", "table1", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.compact, GC.Spread.Pivot.PivotTableThemes.medium1.name(), null);
pt.suspendLayout();
pt.add("name", "name", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("product", "product", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.group({
originFieldName: 'date',
dateGroups: [
{
by: GC.Pivot.DateGroupType.years
},
{
by: GC.Pivot.DateGroupType.quarters
},
{
by: GC.Pivot.DateGroupType.months
},
]
});
pt.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
pt.add("sales", "sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt.resumeLayout();
pt.autoFitColumn();
spread.setActiveSheet("pivotTable1");
}
Custom Timeline Slicer Themes
SpreadJS supports customize the theme of the pivot table timeline slicer. Using code such as the following:
const customTheme = new GC.Spread.Sheets.Slicers.TimelineStyle();
customTheme.name("custom0");
const wholeSlicerStyle = new GC.Spread.Sheets.Slicers.SlicerStyleInfo('red', 'white', '16px Calibri');
customTheme.wholeSlicerStyle(wholeSlicerStyle);
spread.customTimelineThemes.add(customTheme);
slicer.style("custom0");
slicer.getStyleName(); // "custom0"
// set default table theme
spread.defaultTimelineTheme('custom0');
Feedback
Submit and view feedback for