Pivot Slicer

SpreadJS PivotTable supports slicer, which is a convenient graphical interface for the label filter.

Introduction SpreadJS PivotTable supports two types of slicers: item slicer and timeline 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. Item Slicer 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. Timeline Slicer only 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? As default, No. They all work through labelFilter. set textItems will lead to condition filter lost. You can solve this problem by 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 based on Shape, so you can use many Shape API to control Pivot Slicer For example Connect With PivotTable Pivot Slicer supports connection management, add or cut the connection with PivotTable. if disconnect with PivotTable, the slicer filter action will not affect on PivotTable, also, PivotTable filter action will not affect on Slicer. For example, through this capability, you can use a slicer to manage multiple same source pivotTables. Assuming that there are two pivot table "pt1" and "pt2", both of them are created from table which named "table1", so they have same data source, same fields. OK, now you can use slicer_name controls the filters of two pivot tables.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 }); initSpread(spread); var pivotLayoutSheet = spread.getSheet(0); initPivotTable(pivotLayoutSheet); initSlicer(pivotLayoutSheet); bindEvent(spread.getSheet(0)); }; 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"); sheet0.setValue(0, 0, "Pivot Table 1"); sheet0.setValue(0, 11, "Pivot Table 2"); spread.resumePaint(); } function initPivotTable(sheet) { var groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, {by: GC.Pivot.DateGroupType.years}, {by: GC.Pivot.DateGroupType.months}] }; var 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(); var pt2 = sheet.pivotTables.add("pt2", "tableSales", 1, 11, 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", "Years", GC.Spread.Pivot.PivotTableFieldType.rowField); pt2.add("Quarters", "Quarters", GC.Spread.Pivot.PivotTableFieldType.rowField); pt2.add("date", "date", GC.Spread.Pivot.PivotTableFieldType.rowField); pt2.add("amount", "Amounts", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pt2.resumeLayout(); pt2.autoFitColumn(); } function initSlicer (sheet) { sheet.suspendPaint(); var yearSlicer = sheet.slicers.add("Years", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light5(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline); var monthSlicer = sheet.slicers.add("Months", 'pt2', "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline); setTimelineProp(yearSlicer, 377, 20, 310, 160, 1, "Years"); setTimelineProp(monthSlicer, 377, 160, 310, 160, 3, "Months"); var regionSlicer = sheet.slicers.add("region", 'pt1', "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable); var countrySlicer = sheet.slicers.add("country", 'pt1', "country", GC.Spread.Sheets.Slicers.SlicerStyles.dark2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable); var citySlicer = sheet.slicers.add("city", 'pt1', "city", GC.Spread.Sheets.Slicers.SlicerStyles.dark3(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable); setSlicerProp(regionSlicer, 318, 340, 124, 200, 1, true, true); setSlicerProp(countrySlicer, 442, 340, 160, 200, 1, true, true); setSlicerProp(citySlicer, 602, 340, 146, 200, 1, true, true); sheet.resumePaint(); } function 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); } function 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); } function bindEvent (sheet) { var slicer; 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); break; } } }); ['name', 'captionName'].forEach(prop => { _getElementById(prop).addEventListener("change", function (e) { var value = e.target.value; if (value && slicer) { slicer[prop](value); } }); }); ['x', 'y', 'width', 'height'].forEach(prop => { _getElementById(prop).addEventListener("change", function (e) { var value = +e.target.value; if (value && slicer) { slicer[prop](value); } }); }); ['pt1', 'pt2'].forEach(ptName => { _getElementById(ptName).addEventListener("change", function (e) { var checked = e.target.checked; if (!_isNullOrUndefined(checked) && slicer) { if (checked) { slicer.connectPivotTable(ptName); } else { slicer.disconnectPivotTable(ptName); } } }); }); } function updateSlicerInfo (slicer) { _getElementById("name").value = slicer.name(); _getElementById("captionName").value = slicer.captionName(); _getElementById("x").value = slicer.x(); _getElementById("y").value = slicer.y(); _getElementById("width").value = slicer.width(); _getElementById("height").value = slicer.height(); _getElementById("pt1").checked = slicer.isConnectedPivotTable('pt1'); _getElementById("pt2").checked = slicer.isConnectedPivotTable('pt2'); } 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/data/pivotSales.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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 Slicer Info:</div><br> <div class="slicer-info"> <label class="info-label">Slicer Name:</label> <input class="info-input" id="name"></input> </div> <div class="slicer-info"> <label class="info-label">Caption Name:</label> <input class="info-input" id="captionName"></input> </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"> </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"> </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"> </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"> </div> </div> <div class="block"> <div>Slicer Connection</div><br/> <div class="Connection"> <div class="slicer-info"> <input type="checkbox" id="pt1"> <label for="pt1">Connect Pivot Table 1</label> </div> <div class="slicer-info"> <input type="checkbox" id="pt2"> <label for="pt2">Connect Pivot Table 2</label> </div> </div> </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; } .info-label { width: 35%; display: inline-block; } .info-input { width: 58%; display: inline-block; }