Overview

SpreadJS will support PivotTable reports. The PivotTable Add-on is currently in beta and supports many functions, such as sorting, filtering, etc. It can analyze data according to different dimensions and supports multiple themes.

SpreadJS PivotTable supports two formats of data sources: table name: The PivotTable uses a table name to get source data. range formula: The PivotTable uses a range to get source data. The range formula must be an absolute path. To use the PivotTable, add the js file link into the document's head section: Using a table name to build the PivotTable: Using a range formula to build the PivotTable:
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 4}); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let sheet3 = spread.getSheet(2); let sheet4 = spread.getSheet(3); let tableName = getDataSource(sheet4, pivotSales); initPivotTable(sheet1, tableName); addCustom(sheet2, tableName); initBlankPivot(sheet3, tableName); autoFit(sheet1); autoFit(sheet2); autoFit(sheet4); 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 addCustom(sheet, tableName){ sheet.name("Custom PivotTable"); sheet.setRowCount(10000); sheet.setColumnWidth(0, 20); let pivotTableOptions = {bandRows:true,bandColumns:true}; let pivotTable = sheet.pivotTables.add("CustomPivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.dark3, pivotTableOptions); pivotTable.add("id", "id", GC.Spread.Pivot.PivotTableFieldType.filterField); pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField); 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); let itemList = ["North America", "Asia", "Australia", "Europe"]; pivotTable.labelFilter("region", { textItem: { list: itemList, isAll: false } }); pivotTable.sort("region", GC.Spread.Pivot.SortType.asc); } function initBlankPivot(sheet, source){ sheet.name("Blank PivotTable"); sheet.setColumnWidth(0, 20); sheet.pivotTables.add("BlankPivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2); } function initPivotTable(sheet, tableName){ sheet.name("Basic PivotTable"); sheet.setRowCount(10000); sheet.setColumnWidth(0, 20); let pivotTableOptions = {bandRows:true,bandColumns:true}; let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark2, pivotTableOptions); pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField); 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); } function autoFit(sheet){ let columnCount = sheet.getColumnCount(); for (let i = 1; i < columnCount; i++) { sheet.autoFitColumn(i); } }
<!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/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="optionContainer" class="optionContainer"> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }