Custom PivotTable

SpreadJS PivotTables provide the ability to modify the standard PivotTable presentation with specific parameters, which in this demo can be customized using the different settings below.

You can configure the PivotTable with different parameters: These parameters that can be used for PivotTable are as follows: allowMultipleFiltersPerField: whether multiple filters can be used in one field. insertBlankLineAfterEachItem: whether a blank row should be inserted at end of each item. grandTotalPosition: show the grandtotal in the row, column or both. subtotalsPosition: show subtotal top, bottom, or not. displayFieldsInPageFilterArea: display the page area fields first over then down or first down then over reportFilterFieldsPerColumn: the number of report filter fields per column bandRows: show banded rows or not bandColumns: show banded columns or not showRowHeader: show row header styles or not showColumnHeader: show column header styles or not
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let tableName = getDataSource(sheet2, pivotSales); let pivotTable = initPivotTable(sheet1, tableName); autoFit(sheet1); autoFit(sheet2); bindEvent(pivotTable); spread.resumePaint(); } function getDataSource(sheet, tableSource){ sheet.name("DataSource"); sheet.setRowCount(700); sheet.setColumnWidth(5, 150); let table = sheet.tables.add('table', 0, 0, 642, 6); sheet.setArray(0, 0, tableSource); return table.name(); } function initPivotTable(sheet, source){ sheet.name("PivotTable Theme"); sheet.setRowCount(10000); sheet.setColumnWidth(0, 20); let option = { showRowHeader:true, showColumnHeader:true, bandRows:true, bandColumns:true }; let pivotTable = sheet.pivotTables.add("pivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark4,option); pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("id", "id", GC.Spread.Pivot.PivotTableFieldType.filterField); pivotTable.add("country", "country", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("date", "date", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, { by: GC.Pivot.DateGroupType.years }] }; pivotTable.group(groupInfo); pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.columnField); pivotTable.add("amount", "Sum of amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); return pivotTable; } function autoFit(sheet){ let columnCount = sheet.getColumnCount(); for (let i = 1; i < columnCount; i++) { sheet.autoFitColumn(i); } } function bindEvent(pivotTable){ document.getElementById("set-option").addEventListener("click", function(event){ let radioOption = document.getElementsByClassName("select-option"); let option = pivotTable.options, item; for (let i = 0; i < radioOption.length; i++) { item = radioOption[i]; option[item.value] = item.checked; } let grandTotalPosition = document.getElementById("grandTotalPosition"); option["grandTotalPosition"] = GC.Spread.Pivot.GrandTotalPosition[grandTotalPosition.options[grandTotalPosition.selectedIndex].text]; let subtotalsPosition = document.getElementById("subtotalsPosition"); option["subTotalsPosition"] = GC.Spread.Pivot.SubtotalsPosition[subtotalsPosition.options[subtotalsPosition.selectedIndex].text]; let displayFields = document.getElementById("displayFieldsInPageFilterArea"); option["displayFieldsInPageFilterArea"] = GC.Spread.Pivot.DisplayFields[displayFields.options[displayFields.selectedIndex].text]; let reportFilterFieldsPerColumn = document.getElementById("reportFilterFieldsPerColumn"); option["reportFilterFieldsPerColumn"] = +reportFilterFieldsPerColumn.value; pivotTable.refresh(); }); }
<!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-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/data.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 id="container" class="options-container"> <div class="option-item"> <input type="checkbox" id="allowMultipleFiltersPerField" name="allowMultipleFiltersPerField" value="allowMultipleFiltersPerField" class="select-option"> <label for="allowMultipleFiltersPerField">Allow Multiple Filters Per Field</label> </div> <div class="option-item"> <input type="checkbox" id="insertBlankLineAfterEachItem" name="insertBlankLineAfterEachItem" value="insertBlankLineAfterEachItem" class="select-option"> <label for="insertBlankLineAfterEachItem">Insert Blank Line After Each Item</label> </div> <div class="option-item"> <input type="checkbox" id="bandRows" name="bandRows" value="bandRows" class="select-option" checked> <label for="bandRows">Band Rows</label> </div> <div class="option-item"> <input type="checkbox" id="bandColumns" name="bandColumns" value="bandColumns" class="select-option" checked> <label for="bandColumns">Band Columns</label> </div> <div class="option-item"> <input type="checkbox" id="showRowHeader" name="showRowHeader" value="showRowHeader" class="select-option" checked> <label for="showRowHeader">Show Row Header</label> </div> <div class="option-item"> <input type="checkbox" id="showColumnHeader" name="showColumnHeader" value="showColumnHeader" class="select-option" checked> <label for="showColumnHeader">Show Column Header</label> </div> <div> <div class = "select-option-class">Grand Total Position:</div> <select name="" id="grandTotalPosition" class="grandTotalPosition select-option-select"> <option value="both">both</option> <option value="none">none</option> <option value="row">row</option> <option value="col">col</option> </select> </div> <div> <div class = "select-option-class">Subtotals Position:</div> <select name="" id="subtotalsPosition" class="subtotalsPosition select-option-select"> <option value="bottom">bottom</option> <option value="none">none</option> <option value="top">top</option> </select> </div> <div> <div class = "select-option-class">Display Fields In Page Filter Area:</div> <select name="" id="displayFieldsInPageFilterArea" class="displayFieldsInPageFilterArea select-option-select"> <option value="overThenDown">overThenDown</option> <option value="downThenOver">downThenOver</option> </select> </div> <div> <label for="reportFilterFieldsPerColumn">Report Filter Fields Per Column:</label> <input type="number" value="1" id="reportFilterFieldsPerColumn" min="1"> </div> <input type="button" value="OK" class="set-option" id="set-option"> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 300px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 300px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .set-option { display: block; margin-top: 20px; width: 250px; } #reportFilterFieldsPerColumn { width: 28px; } .select-option-class{ display: block; margin-top: 20px; margin-bottom: 10px } .select-option-select{ width: 250px; display: block; margin-bottom: 20px; } .option-item{ height: 20px; margin-bottom: 10px; }