Overview

The SpreadJS PivotTable Add-on supports many of Excel's PivotTable features and options, such as sorting, filtering, and more. It can analyze complex data 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: Using a TableSheet name to build the PivotTable: If the data source used by PivotTable changes, and the PivotTable needs to update the data source, you can update it in the following way. PivotTable update data source:
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity/spread-sheets'; import "@grapecity/spread-sheets-shapes"; import "@grapecity/spread-sheets-pivot-addon"; import { SpreadSheets, Worksheet } from '@grapecity/spread-sheets-react'; import './styles.css'; const Component = React.Component; function _getElementById(id) { return document.getElementById(id); } class App extends Component { constructor(props) { super(props); } render() { return ( <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}> </SpreadSheets> </div> </div> ); } initSpread(spread) { spread.suspendPaint(); spread.setSheetCount(4); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let sheet3 = spread.getSheet(2); let sheet4 = spread.getSheet(3); let tableName = this.getDataSource(sheet4, pivotSales); this.initPivotTable(sheet1, tableName); this.addCustom(sheet2, tableName); this.initBlankPivot(sheet3, tableName); this.columnFit(sheet4,0,6,100); spread.resumePaint(); } 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(); } addCustom(sheet, tableName){ sheet.name("Custom PivotTable"); sheet.setRowCount(1000); let pivotTableOptions = {bandRows:true,bandColumns:true}; let pivotTable = sheet.pivotTables.add("CustomPivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.light8, pivotTableOptions); pivotTable.suspendLayout(); 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); pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); let itemList = ["Alan","John", "Tess"]; pivotTable.labelFilter("Salesperson", { textItem: { list: itemList, isAll: false } }); pivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc }); let carList = ["Audi","BMW","Mercedes"]; pivotTable.labelFilter("Cars", { textItem: { list: carList, isAll: false } }); pivotTable.sort("Cars", { sortType: GC.Pivot.SortType.asc }); let style = new GC.Spread.Sheets.Style(); style.formatter = "$ #,##0"; pivotTable.setStyle({dataOnly: true}, style); pivotTable.resumeLayout(); pivotTable.autoFitColumn(); } 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); } initPivotTable(sheet, tableName){ sheet.name("Basic PivotTable"); sheet.setRowCount(1000); let pivotTableOptions = {bandRows:true,bandColumns:true}; let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium1, pivotTableOptions); pivotTable.suspendLayout(); 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(); } columnFit(sheet,start,end,width){ for (let i = start; i < end; i++) { sheet.setColumnWidth(i, width); } } } ReactDOM.render(<App />, _getElementById('app'));
<!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/react/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/en/lib/react/license.js').then(function () { System.import('./src/app'); }); </script> </head> <body> <div id="app" style="height: 100%;"></div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-shapes': 'npm:@grapecity/spread-sheets-shapes/index.js', '@grapecity/spread-sheets-pivot-addon': 'npm:@grapecity/spread-sheets-pivot-addon/index.js', '@grapecity/spread-sheets-react': 'npm:@grapecity/spread-sheets-react/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);