SpreadJS 14
Features / Pivot Table / Create Pivot Table
In This Topic
    Create Pivot Table
    In This Topic

    This topic explains the creation of a pivot table in a spreadsheet by taking a use case scenario, as explained below.

    Use Case Scenario

    Let's say there is a food sales company which sells food and beverage products across different countries. The company maintains sales data of products on a daily basis. By using this data, a pivot table is created which can present meaningful insights, like:

    Usage of GETPIVOTDATA

    A pivot table can easily categorize the sales data and calculate the subtotals and grand totals automatically. In order to create a pivot table, follow the below steps:

    1. Add data source to the project. 
    2. Create a pivot table using add method of the PivotTables method.
    3. Use PivotTableFieldType enumeration to display data into various fields.

    The following example code creates the pivot table and adds fields to the field area.

    C#
    Copy Code
    $(document).ready(function () {
        // initializing Spread
        var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
        spread.suspendPaint();
        // get sheets
        var pivotLayoutSheet = spread.getSheet(0);
        var dataSourceSheet = spread.getSheet(1);
        // set sheet name
        pivotLayoutSheet.name("PivotLayout");
        dataSourceSheet.name("DataSource");
        // set row count
        dataSourceSheet.setRowCount(245);
        // set datasource
        dataSourceSheet.setArray(0, 0, pivotDB_UseCase);
        // add table to dataSourceSheet
        dataSourceSheet.tables.add('tableSales', 0, 0, 245, 8);
        spread.resumePaint();
        // initialize pivottable
        initPivotTable(pivotLayoutSheet);
        // auto fit columns in both the sheets
        autoFit(pivotLayoutSheet);
        autoFit(dataSourceSheet);
         });
    function initPivotTable(sheet) {
         // add pivottable
         var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 0, 0, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.dark3);
         myPivotTable.suspendLayout();
         // show rowHeader and columnHeader for PivotTable
         myPivotTable.options.showRowHeader = true;
         myPivotTable.options.showColumnHeader = true;
         // add column fields
         myPivotTable.add("Category", "Category", GC.Spread.Pivot.PivotTableFieldType.columnField);
         myPivotTable.add("Product", "Product", GC.Spread.Pivot.PivotTableFieldType.columnField);
         // add row fields
         myPivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField);
         myPivotTable.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.rowField);
         // add value field with SubtotalType Sum
         myPivotTable.add("Quantity", "Sum of quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
         // add filter field
         myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
         myPivotTable.resumeLayout();
         return myPivotTable;
            }
    function autoFit(sheet) {
         // auto fit columns
         let columnCount = sheet.getColumnCount();
         for (let i = 0; i < columnCount; i++) {
             sheet.autoFitColumn(i);
         }
       }
    

    The pivot table can also use the data from the data source to present the following insights: