PivotTable Timeline Slicer

SpreadJS PivotTables supports timeline slicers, which can carry out date slicing operation 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 Slicer can only be added to date field. The changes made on Timeline Slicer are equal to 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 "name" field. Using Timeline Then we can control the timeline_date: For example, if you don't need to show horizontal scrollbar. If you want to scroll the timeline to some day. If you want to do filter by year. A Sample to Create a Pivot Table
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), {sheetCount: 2}); initSpread(spread); var pivotLayoutSheet = spread.getSheet(0); var pt = initPivotTable(pivotLayoutSheet); initSlicer(pivotLayoutSheet, pt); bindEvent(pivotLayoutSheet, pt); }; function initSpread(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(); } function 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, 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(); return pivotTable; } function initSlicer(sheet, pt) { sheet.suspendPaint(); var timeline_year = sheet.slicers.add("timeline_year", pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark6(), 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", pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.dark1(), 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", pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.light4(), 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(); } function bindEvent(sheet, pt) { var slicer; var slicerCount = 0; sheet.bind(GC.Spread.Sheets.Events.SlicerChanged, function () { var slicers = sheet.slicers.all(); for (var i = 0; i < slicers.length; i++) { if (slicers[i].isSelected()) { slicer = slicers[i]; updateSlicerInfo(slicer, pt); break; } } }); _getElementById("showHeader").addEventListener("change", function (e) { var checked = e.target.checked; if (!_isNullOrUndefined(checked) && slicer) { slicer.showHeader(checked); } }); _getElementById("showHorizontalScrollbar").addEventListener("change", function (e) { var checked = e.target.checked; if (!_isNullOrUndefined(checked) && slicer) { slicer.showHorizontalScrollbar(checked); } }); _getElementById("showSelectionLabel").addEventListener("change", function (e) { var checked = e.target.checked; if (!_isNullOrUndefined(checked) && slicer) { slicer.showSelectionLabel(checked); } }); _getElementById("showTimeLevel").addEventListener("change", function (e) { var checked = e.target.checked; if (!_isNullOrUndefined(checked) && slicer) { slicer.showTimeLevel(checked); } }); _getElementById("addSlicerBtn").addEventListener("click", function () { sheet.slicers.add("timeline_" + slicerCount++, pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline); }); _getElementById("changeStyle").addEventListener("click", function () { var styleName = _getElementById("slicerStyle").value; if (styleName && slicer) { var style = GC.Spread.Sheets.Slicers.TimelineStyles[styleName](); slicer.style(style); } }); } function updateSlicerInfo(slicer, pt) { _getElementById("showHeader").checked = slicer.showHeader(); _getElementById("showHorizontalScrollbar").checked = slicer.showHorizontalScrollbar(); _getElementById("showSelectionLabel").checked = slicer.showSelectionLabel(); _getElementById("showTimeLevel").checked = slicer.showTimeLevel(); _getElementById("slicerStyle").value = slicer.style().name().substr(15).toLowerCase(); } function _getElementById(id) { return document.getElementById(id); } function _isNullOrUndefined(o) { return o === null || o === undefined; }
<!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/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <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"> <label for="showHeader">Show Header</label> </div> <div class="slicer-info"> <input type="checkbox" id="showHorizontalScrollbar"> <label for="showHorizontalScrollbar">Show Horizontal Scrollbar</label> </div> <div class="slicer-info"> <input type="checkbox" id="showSelectionLabel"> <label for="showSelectionLabel">Show Selection Label</label> </div> <div class="slicer-info"> <input type="checkbox" id="showTimeLevel"> <label for="showTimeLevel">Show Time Level</label> </div> </div> <div class="block"> <div>Add Timeline</div> <br> <button id="addSlicerBtn">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"> <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> </select> <button class="select-button" id="changeStyle">Change</button> </div> </div> </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; }