Pivot Options

SpreadJS provides many different options to customize the appearance and functionality of the PivotTables per your application's needs. Edit the different options below then scroll down and press the Apply button to see the results.

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 showDrill: show expand/collapse button or not showMissing: show missing caption or not showToolTip: show tool tip or not missingCaption: replace empty cell in content area to custom string or number fillDownLabels: show repeat label items or not repeatAllItemLabels: show repeat label items or not rowLabelIndent: set the indent of each level of title in compact layout mergeItem: merge and center cells with labels showHeaders: show row and column headers 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); bindEvent(pivotTable, spread); spread.resumePaint(); } function getDataSource(sheet, tableSource){ sheet.name("DataSource"); sheet.setRowCount(117); sheet.setColumnWidth(0, 120); sheet.getCell(-1, 0).formatter("YYYY-mm-DD"); sheet.getRange(-1,4,0,2).formatter("$ #,##0"); let table = sheet.tables.add('table', 0, 0, 117, 6); for(let i=2;i<=117;i++) { sheet.setFormula(i-1,5,'=D'+i+'*E'+i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, tableSource); return table.name(); } function initPivotTable(sheet, source){ sheet.name("PivotTable"); sheet.setRowCount(1000); 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.medium8,option); pivotTable.suspendLayout(); pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.filterField); pivotTable.add("price", "Price", GC.Spread.Pivot.PivotTableFieldType.filterField); pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; pivotTable.group(groupInfo); pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); let style = new GC.Spread.Sheets.Style(); style.formatter = "$ #,##0"; pivotTable.setStyle({dataOnly: true}, style); pivotTable.resumeLayout(); pivotTable.autoFitColumn(); return pivotTable; } function bindEvent(pivotTable){ document.getElementById("set-option").addEventListener("click", function(event){ pivotTable.suspendLayout(); 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; let missingCaption = document.getElementById("missingCaption"); if (!isNaN(parseFloat(missingCaption.value))) { option["missingCaption"] = parseFloat(missingCaption.value); } else { option["missingCaption"] = missingCaption.value; } let rowLabelIndent = document.getElementById('rowLabelIndent'); if (!isNaN(parseFloat(rowLabelIndent.value))) { option["rowLabelIndent"] = parseFloat(rowLabelIndent.value); } pivotTable.layoutType(+document.getElementById("pivotTableLayout").value); pivotTable.resumeLayout(); }); }
<!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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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/pivot-data.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"> <label><b>Pivot Options</b></label> </div> <hr> <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="fillDownLabels" name="fillDownLabels" value="fillDownLabels" class="select-option"> <label for="fillDownLabels">fillDownLabels</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 class="option-item"> <input type="checkbox" id="showDrill" name="showDrill" value="showDrill" class="select-option" checked> <label for="showDrill">Show Drill</label> </div> <div class="option-item"> <input type="checkbox" id="showFilter" name="showFilter" value="showFilter" class="select-option" checked> <label for="showFilter">Show Filter</label> </div> <div class="option-item"> <input type="checkbox" id="showMissing" name="showMissing" value="showMissing" class="select-option" checked> <label for="showMissing">Show Missing</label> </div> <div class="option-item"> <input type="checkbox" id="showToolTip" name="showToolTip" value="showToolTip" class="select-option" checked> <label for="showToolTip">Show Tooltip</label> </div> <div class="option-item"> <input type="checkbox" id="mergeItem" name="mergeItem" value="mergeItem" class="select-option"> <label for="mergeItem">Merge Item</label> </div> <div class="option-item"> <input type="checkbox" id="showHeaders" name="showHeaders" value="showHeaders" class="select-option" checked> <label for="showHeaders">showHeaders</label> </div> <hr> <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> <div class="select-option-class">PivotTable Layout:</div> <select id="pivotTableLayout" class="pivotTableLayout select-option-select"> <option value = 0>Compact</option> <option value = 1 selected>Outline</option> <option value = 2>Tabular</option> </select> </div> <div> <div class = "select-option-class">Show Empty Value In Content Area As:</div> <input type="text" name="" id="missingCaption" class="missingCaption select-option-select"/> </div> <div> <div class = "select-option-class">Set Row Label Indent In Compact Layout:</div> <input type="number" name="" id="rowLabelIndent" class="rowLabelIndent select-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="Apply" 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; }