Pivot Tables

The pivot tables extension for Jspreadsheet enables users to create dynamic pivot tables for summarizing and analyzing large datasets. With support for multiple rows, columns, aggregation methods, filtering, and sorting, this extension provides a powerful tool for data exploration directly within your spreadsheet.

Key Features

Create pivot tables from any data range in your spreadsheet

Configure multiple row and column groupings

Support for various aggregation methods (SUM, COUNT, AVERAGE, etc.)

Filter data using validation rules

Sort by values or names in ascending or descending order

Collapse and expand grouped rows or columns

Documentation

Pivot Table Settings

Property	Description
id: String	Unique identifier for the pivot table (UUID)
anchor: String	Cell reference where the pivot table will be rendered (e.g., 'A1')
source: String	Data range reference (e.g., 'SHEET1!B3:E53')
rows?: Array	Array of row configuration objects
columns?: Array	Array of column configuration objects
cells?: Array	Array of cell/value configuration objects for aggregation
filters?: Array	Array of filter configuration objects

Row and Column Configuration

Property	Description
columnIndex: Number	Index of the source column to use for grouping
sortBy: String	Sort by 'name' or by a cell ID for value-based sorting
ascendingOrder: Boolean	Sort in ascending order (true) or descending (false)
collapsed?: Array	Array of values to show collapsed by default

Cell/Value Configuration

Property	Description
id: String	Unique identifier for the cell aggregation (UUID)
columnIndex: Number	Index of the source column containing the values to aggregate
method: String	Aggregation method: SUM, COUNTA, COUNT, COUNTUNIQUE, AVERAGE, MAX, MIN, MEDIAN, PRODUCT, STDEV, STDEVP, VAR, VARP

Filter Configuration

Property	Description
columnIndex: Number	Index of the source column to filter
filter?: Array	Array of specific values to include
validation?: Object	Validation object for conditional filtering

Validation Object

The validation object allows you to define conditional rules for filtering data in pivot tables. It uses the type, criteria, and value properties from the Data Validations documentation.

Note: The validation types list and formula are not available for pivot table filters.

Property	Description
type: String	The data type to validate against (e.g., `number`, `text`, `date`).
criteria?: String	The comparison operator to use (e.g., `>`, `between`, `contains`).
value?: Array	Comparison values used by the validation rule (e.g., `[1, 10]`, `[5]`).

Examples

// Filter rows where Units (column 3) is greater than 500
filters: [
    {
        columnIndex: 3,
        validation: {
            type: 'number',
            criteria: '>',
            value: [500],
        },
    },
]

// Filter rows where Units is between 100 and 800
filters: [
    {
        columnIndex: 3,
        validation: {
            type: 'number',
            criteria: 'between',
            value: [100, 800],
        },
    },
]

// Filter rows where Region (column 1) contains "North"
filters: [
    {
        columnIndex: 1,
        validation: {
            type: 'text',
            criteria: 'contains',
            value: ['North'],
        },
    },
]

// Filter rows where Seller name starts with "M"
filters: [
    {
        columnIndex: 0,
        validation: {
            type: 'text',
            criteria: 'begins with',
            value: ['M'],
        },
    },
]

Installation

Choose one of the following options:

Using NPM

npm install @jspreadsheet/pivot@1.0.5

Using a CDN

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot@1.0.5/dist/index.min.js"></script>

Examples

Basic Pivot Table

Create a spreadsheet with sales data and a pivot table that summarizes units sold by seller, region, and project.

<html>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot@1.0.5/dist/index.css" type="text/css" />

<script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot@1.0.5/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    tabs: true,
    tableOverflow: true,
    tableWidth: '1000px',
    tableHeight: '600px',
    worksheets: [
        {
            worksheetName: 'Data',
            minDimensions: [6, 55],
            data: [
                [],
                [],
                [undefined, 'Seller', 'Region', 'Project', 'Units'],
                [undefined, 'Mary', 'South', 'Project A', 796],
                [undefined, 'John', 'North', 'Project C', 748],
                [undefined, 'John', 'West', 'Project A', 561],
                [undefined, 'Mary', 'South', 'Project A', 861],
                [undefined, 'Mary', 'South', 'Project C', 247],
                [undefined, 'John', 'North', 'Project B', 788],
                [undefined, 'John', 'West', 'Project A', 328],
                [undefined, 'Mary', 'West', 'Project A', 847],
                [undefined, 'John', 'North', 'Project C', 367],
                [undefined, 'Mary', 'South', 'Project C', 85],
                [undefined, 'John', 'East', 'Project C', 238],
                [undefined, 'John', 'East', 'Project B', 332],
                [undefined, 'John', 'South', 'Project A', 292],
                [undefined, 'Mary', 'South', 'Project A', 407],
                [undefined, 'Mary', 'East', 'Project B', 187],
                [undefined, 'John', 'East', 'Project C', 289],
                [undefined, 'John', 'West', 'Project A', 407],
                [undefined, 'John', 'West', 'Project C', 782],
                [undefined, 'John', 'East', 'Project B', 774],
                [undefined, 'John', 'South', 'Project B', 221],
                [undefined, 'John', 'North', 'Project B', 552],
                [undefined, 'John', 'East', 'Project C', 552],
                [undefined, 'Mary', 'North', 'Project B', 579],
                [undefined, 'John', 'North', 'Project B', 780],
                [undefined, 'Mary', 'North', 'Project B', 345],
                [undefined, 'Mary', 'East', 'Project B', 354],
                [undefined, 'Mary', 'West', 'Project A', 519],
                [undefined, 'John', 'West', 'Project C', 920],
                [undefined, 'Mary', 'East', 'Project A', 394],
                [undefined, 'Mary', 'North', 'Project A', 461],
                [undefined, 'Mary', 'West', 'Project C', 83],
                [undefined, 'Mary', 'South', 'Project B', 620],
                [undefined, 'John', 'North', 'Project C', 337],
                [undefined, 'John', 'East', 'Project A', 280],
                [undefined, 'John', 'South', 'Project A', 172],
                [undefined, 'Mary', 'North', 'Project A', 206],
                [undefined, 'John', 'South', 'Project B', 950],
                [undefined, 'Mary', 'North', 'Project C', 124],
                [undefined, 'Mary', 'North', 'Project A', 232],
                [undefined, 'John', 'South', 'Project A', 791],
                [undefined, 'John', 'West', 'Project C', 234],
                [undefined, 'John', 'East', 'Project C', 33],
                [undefined, 'Mary', 'East', 'Project C', 423],
                [undefined, 'Mary', 'West', 'Project C', 519],
                [undefined, 'Mary', 'South', 'Project A', 367],
                [undefined, 'John', 'West', 'Project A', 930],
                [undefined, 'John', 'South', 'Project A', 974],
                [undefined, 'Mary', 'North', 'Project C', 897],
                [undefined, 'Mary', 'West', 'Project A', 318],
                [undefined, 'Mary', 'East', 'Project C', 25],
            ],
        },
        {
            worksheetName: 'Pivot',
            minDimensions: [10, 20],
            pivotTables: [
                {
                    anchor: 'A1',
                    source: 'Data!B3:E53',
                    rows: [
                        {
                            columnIndex: 0,
                            sortBy: 'name',
                            ascendingOrder: true,
                        },
                        {
                            columnIndex: 1,
                            sortBy: 'name',
                            ascendingOrder: true,
                        },
                    ],
                    columns: [
                        {
                            columnIndex: 2,
                            sortBy: 'name',
                            ascendingOrder: true,
                        },
                    ],
                    cells: [
                        {
                            columnIndex: 3,
                            method: 'SUM',
                        },
                    ],
                },
            ],
        },
    ],
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

// Sales data
const salesData = [
    [],
    [],
    [undefined, 'Seller', 'Region', 'Project', 'Units'],
    [undefined, 'Mary', 'South', 'Project A', 796],
    [undefined, 'John', 'North', 'Project C', 748],
    [undefined, 'John', 'West', 'Project A', 561],
    [undefined, 'Mary', 'South', 'Project A', 861],
    [undefined, 'Mary', 'South', 'Project C', 247],
    [undefined, 'John', 'North', 'Project B', 788],
    [undefined, 'John', 'West', 'Project A', 328],
    [undefined, 'Mary', 'West', 'Project A', 847],
    [undefined, 'John', 'North', 'Project C', 367],
    [undefined, 'Mary', 'South', 'Project C', 85],
    [undefined, 'John', 'East', 'Project C', 238],
    [undefined, 'John', 'East', 'Project B', 332],
    [undefined, 'John', 'South', 'Project A', 292],
    [undefined, 'Mary', 'South', 'Project A', 407],
    [undefined, 'Mary', 'East', 'Project B', 187],
    [undefined, 'John', 'East', 'Project C', 289],
    [undefined, 'John', 'West', 'Project A', 407],
    [undefined, 'John', 'West', 'Project C', 782],
    [undefined, 'John', 'East', 'Project B', 774],
    [undefined, 'John', 'South', 'Project B', 221],
    [undefined, 'John', 'North', 'Project B', 552],
    [undefined, 'John', 'East', 'Project C', 552],
    [undefined, 'Mary', 'North', 'Project B', 579],
    [undefined, 'John', 'North', 'Project B', 780],
    [undefined, 'Mary', 'North', 'Project B', 345],
    [undefined, 'Mary', 'East', 'Project B', 354],
    [undefined, 'Mary', 'West', 'Project A', 519],
    [undefined, 'John', 'West', 'Project C', 920],
    [undefined, 'Mary', 'East', 'Project A', 394],
    [undefined, 'Mary', 'North', 'Project A', 461],
    [undefined, 'Mary', 'West', 'Project C', 83],
    [undefined, 'Mary', 'South', 'Project B', 620],
    [undefined, 'John', 'North', 'Project C', 337],
    [undefined, 'John', 'East', 'Project A', 280],
    [undefined, 'John', 'South', 'Project A', 172],
    [undefined, 'Mary', 'North', 'Project A', 206],
    [undefined, 'John', 'South', 'Project B', 950],
    [undefined, 'Mary', 'North', 'Project C', 124],
    [undefined, 'Mary', 'North', 'Project A', 232],
    [undefined, 'John', 'South', 'Project A', 791],
    [undefined, 'John', 'West', 'Project C', 234],
    [undefined, 'John', 'East', 'Project C', 33],
    [undefined, 'Mary', 'East', 'Project C', 423],
    [undefined, 'Mary', 'West', 'Project C', 519],
    [undefined, 'Mary', 'South', 'Project A', 367],
    [undefined, 'John', 'West', 'Project A', 930],
    [undefined, 'John', 'South', 'Project A', 974],
    [undefined, 'Mary', 'North', 'Project C', 897],
    [undefined, 'Mary', 'West', 'Project A', 318],
    [undefined, 'Mary', 'East', 'Project C', 25],
];

// Pivot table configuration
const pivotTables = [
    {
        anchor: 'A1',
        source: 'Data!B3:E53',
        rows: [
            {
                columnIndex: 0,
                sortBy: 'name',
                ascendingOrder: true,
            },
            {
                columnIndex: 1,
                sortBy: 'name',
                ascendingOrder: true,
            },
        ],
        columns: [
            {
                columnIndex: 2,
                sortBy: 'name',
                ascendingOrder: true,
            },
        ],
        cells: [
            {
                columnIndex: 3,
                method: 'SUM',
            },
        ],
    },
];

export default function App() {
    // Spreadsheet ref
    const spreadsheet = useRef();

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} tabs toolbar tableOverflow tableWidth="1000px" tableHeight="600px">
            <Worksheet worksheetName="Data" data={salesData} minDimensions={[6, 55]} />
            <Worksheet worksheetName="Pivot" pivotTables={pivotTables} minDimensions={[10, 20]} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :tabs="true" :toolbar="true" :tableOverflow="true" tableWidth="1000px" tableHeight="600px">
        <Worksheet worksheetName="Data" :data="salesData" :minDimensions="[6, 55]" />
        <Worksheet worksheetName="Pivot" :pivotTables="pivotTables" :minDimensions="[10, 20]" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    setup() {
        // Sales data
        const salesData = [
            [],
            [],
            [undefined, 'Seller', 'Region', 'Project', 'Units'],
            [undefined, 'Mary', 'South', 'Project A', 796],
            [undefined, 'John', 'North', 'Project C', 748],
            [undefined, 'John', 'West', 'Project A', 561],
            [undefined, 'Mary', 'South', 'Project A', 861],
            [undefined, 'Mary', 'South', 'Project C', 247],
            [undefined, 'John', 'North', 'Project B', 788],
            [undefined, 'John', 'West', 'Project A', 328],
            [undefined, 'Mary', 'West', 'Project A', 847],
            [undefined, 'John', 'North', 'Project C', 367],
            [undefined, 'Mary', 'South', 'Project C', 85],
            [undefined, 'John', 'East', 'Project C', 238],
            [undefined, 'John', 'East', 'Project B', 332],
            [undefined, 'John', 'South', 'Project A', 292],
            [undefined, 'Mary', 'South', 'Project A', 407],
            [undefined, 'Mary', 'East', 'Project B', 187],
            [undefined, 'John', 'East', 'Project C', 289],
            [undefined, 'John', 'West', 'Project A', 407],
            [undefined, 'John', 'West', 'Project C', 782],
            [undefined, 'John', 'East', 'Project B', 774],
            [undefined, 'John', 'South', 'Project B', 221],
            [undefined, 'John', 'North', 'Project B', 552],
            [undefined, 'John', 'East', 'Project C', 552],
            [undefined, 'Mary', 'North', 'Project B', 579],
            [undefined, 'John', 'North', 'Project B', 780],
            [undefined, 'Mary', 'North', 'Project B', 345],
            [undefined, 'Mary', 'East', 'Project B', 354],
            [undefined, 'Mary', 'West', 'Project A', 519],
            [undefined, 'John', 'West', 'Project C', 920],
            [undefined, 'Mary', 'East', 'Project A', 394],
            [undefined, 'Mary', 'North', 'Project A', 461],
            [undefined, 'Mary', 'West', 'Project C', 83],
            [undefined, 'Mary', 'South', 'Project B', 620],
            [undefined, 'John', 'North', 'Project C', 337],
            [undefined, 'John', 'East', 'Project A', 280],
            [undefined, 'John', 'South', 'Project A', 172],
            [undefined, 'Mary', 'North', 'Project A', 206],
            [undefined, 'John', 'South', 'Project B', 950],
            [undefined, 'Mary', 'North', 'Project C', 124],
            [undefined, 'Mary', 'North', 'Project A', 232],
            [undefined, 'John', 'South', 'Project A', 791],
            [undefined, 'John', 'West', 'Project C', 234],
            [undefined, 'John', 'East', 'Project C', 33],
            [undefined, 'Mary', 'East', 'Project C', 423],
            [undefined, 'Mary', 'West', 'Project C', 519],
            [undefined, 'Mary', 'South', 'Project A', 367],
            [undefined, 'John', 'West', 'Project A', 930],
            [undefined, 'John', 'South', 'Project A', 974],
            [undefined, 'Mary', 'North', 'Project C', 897],
            [undefined, 'Mary', 'West', 'Project A', 318],
            [undefined, 'Mary', 'East', 'Project C', 25],
        ];

        // Pivot table configuration
        const pivotTables = [
            {
                anchor: 'A1',
                source: 'Data!B3:E53',
                rows: [
                    {
                        columnIndex: 0,
                        sortBy: 'name',
                        ascendingOrder: true,
                    },
                    {
                        columnIndex: 1,
                        sortBy: 'name',
                        ascendingOrder: true,
                    },
                ],
                columns: [
                    {
                        columnIndex: 2,
                        sortBy: 'name',
                        ascendingOrder: true,
                    },
                ],
                cells: [
                    {
                        columnIndex: 3,
                        method: 'SUM',
                    },
                ],
            },
        ];

        return {
            salesData,
            pivotTables,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            toolbar: true,
            tabs: true,
            tableOverflow: true,
            tableWidth: '1000px',
            tableHeight: '600px',
            worksheets: [
                {
                    worksheetName: 'Data',
                    minDimensions: [6, 55],
                    data: [
                        [],
                        [],
                        [undefined, 'Seller', 'Region', 'Project', 'Units'],
                        [undefined, 'Mary', 'South', 'Project A', 796],
                        [undefined, 'John', 'North', 'Project C', 748],
                        [undefined, 'John', 'West', 'Project A', 561],
                        [undefined, 'Mary', 'South', 'Project A', 861],
                        [undefined, 'Mary', 'South', 'Project C', 247],
                        [undefined, 'John', 'North', 'Project B', 788],
                        [undefined, 'John', 'West', 'Project A', 328],
                        [undefined, 'Mary', 'West', 'Project A', 847],
                        [undefined, 'John', 'North', 'Project C', 367],
                        [undefined, 'Mary', 'South', 'Project C', 85],
                        [undefined, 'John', 'East', 'Project C', 238],
                        [undefined, 'John', 'East', 'Project B', 332],
                        [undefined, 'John', 'South', 'Project A', 292],
                        [undefined, 'Mary', 'South', 'Project A', 407],
                        [undefined, 'Mary', 'East', 'Project B', 187],
                        [undefined, 'John', 'East', 'Project C', 289],
                        [undefined, 'John', 'West', 'Project A', 407],
                        [undefined, 'John', 'West', 'Project C', 782],
                        [undefined, 'John', 'East', 'Project B', 774],
                        [undefined, 'John', 'South', 'Project B', 221],
                        [undefined, 'John', 'North', 'Project B', 552],
                        [undefined, 'John', 'East', 'Project C', 552],
                        [undefined, 'Mary', 'North', 'Project B', 579],
                        [undefined, 'John', 'North', 'Project B', 780],
                        [undefined, 'Mary', 'North', 'Project B', 345],
                        [undefined, 'Mary', 'East', 'Project B', 354],
                        [undefined, 'Mary', 'West', 'Project A', 519],
                        [undefined, 'John', 'West', 'Project C', 920],
                        [undefined, 'Mary', 'East', 'Project A', 394],
                        [undefined, 'Mary', 'North', 'Project A', 461],
                        [undefined, 'Mary', 'West', 'Project C', 83],
                        [undefined, 'Mary', 'South', 'Project B', 620],
                        [undefined, 'John', 'North', 'Project C', 337],
                        [undefined, 'John', 'East', 'Project A', 280],
                        [undefined, 'John', 'South', 'Project A', 172],
                        [undefined, 'Mary', 'North', 'Project A', 206],
                        [undefined, 'John', 'South', 'Project B', 950],
                        [undefined, 'Mary', 'North', 'Project C', 124],
                        [undefined, 'Mary', 'North', 'Project A', 232],
                        [undefined, 'John', 'South', 'Project A', 791],
                        [undefined, 'John', 'West', 'Project C', 234],
                        [undefined, 'John', 'East', 'Project C', 33],
                        [undefined, 'Mary', 'East', 'Project C', 423],
                        [undefined, 'Mary', 'West', 'Project C', 519],
                        [undefined, 'Mary', 'South', 'Project A', 367],
                        [undefined, 'John', 'West', 'Project A', 930],
                        [undefined, 'John', 'South', 'Project A', 974],
                        [undefined, 'Mary', 'North', 'Project C', 897],
                        [undefined, 'Mary', 'West', 'Project A', 318],
                        [undefined, 'Mary', 'East', 'Project C', 25],
                    ],
                },
                {
                    worksheetName: 'Pivot',
                    minDimensions: [10, 20],
                    pivotTables: [
                        {
                            anchor: 'A1',
                            source: 'Data!B3:E53',
                            rows: [
                                {
                                    columnIndex: 0,
                                    sortBy: 'name',
                                    ascendingOrder: true,
                                },
                                {
                                    columnIndex: 1,
                                    sortBy: 'name',
                                    ascendingOrder: true,
                                },
                            ],
                            columns: [
                                {
                                    columnIndex: 2,
                                    sortBy: 'name',
                                    ascendingOrder: true,
                                },
                            ],
                            cells: [
                                {
                                    columnIndex: 3,
                                    method: 'SUM',
                                },
                            ],
                        },
                    ],
                },
            ],
        });
    }
}

Pivot Table with Filters and Collapsed Rows

This example demonstrates a pivot table with data filtering and collapsed row groups using product inventory data.

<html>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot@1.0.5/dist/index.css" type="text/css" />

<script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot@1.0.5/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    tabs: true,
    tableOverflow: true,
    tableWidth: '1000px',
    tableHeight: '600px',
    worksheets: [
        {
            worksheetName: 'Products',
            minDimensions: [6, 35],
            data: [
                ['Category', 'Product', 'Store', 'Revenue', 'Units'],
                ['Electronics', 'Laptop', 'Downtown', 4500, 15],
                ['Electronics', 'Smartphone', 'Mall', 3200, 20],
                ['Electronics', 'Laptop', 'Mall', 5100, 17],
                ['Electronics', 'Tablet', 'Downtown', 2800, 14],
                ['Electronics', 'Smartphone', 'Airport', 1900, 10],
                ['Electronics', 'Laptop', 'Airport', 3700, 12],
                ['Clothing', 'Shirt', 'Downtown', 1200, 40],
                ['Clothing', 'Pants', 'Mall', 980, 35],
                ['Clothing', 'Shirt', 'Mall', 1450, 48],
                ['Clothing', 'Jacket', 'Downtown', 670, 22],
                ['Clothing', 'Pants', 'Airport', 520, 18],
                ['Clothing', 'Shirt', 'Airport', 890, 30],
                ['Furniture', 'Sofa', 'Downtown', 3400, 8],
                ['Furniture', 'Table', 'Mall', 2900, 6],
                ['Furniture', 'Sofa', 'Mall', 4100, 10],
                ['Furniture', 'Chair', 'Downtown', 1800, 4],
                ['Furniture', 'Table', 'Airport', 950, 2],
                ['Furniture', 'Sofa', 'Airport', 2200, 5],
                ['Electronics', 'Tablet', 'Mall', 3100, 16],
                ['Electronics', 'Smartphone', 'Downtown', 2600, 13],
                ['Clothing', 'Jacket', 'Mall', 780, 26],
                ['Clothing', 'Pants', 'Downtown', 1100, 38],
                ['Furniture', 'Chair', 'Mall', 2100, 5],
                ['Furniture', 'Table', 'Downtown', 3200, 7],
                ['Electronics', 'Laptop', 'Downtown', 4800, 16],
                ['Electronics', 'Tablet', 'Airport', 1500, 8],
                ['Clothing', 'Shirt', 'Downtown', 1350, 45],
                ['Clothing', 'Jacket', 'Airport', 420, 14],
                ['Furniture', 'Sofa', 'Downtown', 3800, 9],
                ['Furniture', 'Chair', 'Airport', 1200, 3],
            ],
        },
        {
            worksheetName: 'Pivot',
            minDimensions: [10, 20],
            pivotTables: [
                {
                    id: 'd13442b8-a1c8-4055-97f3-1455cd3f88a9',
                    anchor: 'A1',
                    source: 'Products!A1:E31',
                    rows: [
                        {
                            columnIndex: 0,
                            sortBy: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                            ascendingOrder: false,
                            collapsed: ['Clothing'],
                        },
                        {
                            columnIndex: 1,
                            sortBy: 'name',
                            ascendingOrder: true,
                        },
                    ],
                    columns: [
                        {
                            columnIndex: 2,
                            sortBy: 'name',
                            ascendingOrder: true,
                        },
                    ],
                    cells: [
                        {
                            id: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                            columnIndex: 3,
                            method: 'SUM',
                        },
                        {
                            id: '3a5f8c2d-b9e1-4a7c-8d6e-9f2b1c4d5e6a',
                            columnIndex: 4,
                            method: 'SUM',
                        },
                    ],
                    filters: [
                        {
                            columnIndex: 4,
                            validation: {
                                type: 'number',
                                criteria: '>=',
                                value: [10],
                            },
                        },
                    ],
                },
            ],
        },
    ],
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

// Product inventory data
const productData = [
    ['Category', 'Product', 'Store', 'Revenue', 'Units'],
    ['Electronics', 'Laptop', 'Downtown', 4500, 15],
    ['Electronics', 'Smartphone', 'Mall', 3200, 20],
    ['Electronics', 'Laptop', 'Mall', 5100, 17],
    ['Electronics', 'Tablet', 'Downtown', 2800, 14],
    ['Electronics', 'Smartphone', 'Airport', 1900, 10],
    ['Electronics', 'Laptop', 'Airport', 3700, 12],
    ['Clothing', 'Shirt', 'Downtown', 1200, 40],
    ['Clothing', 'Pants', 'Mall', 980, 35],
    ['Clothing', 'Shirt', 'Mall', 1450, 48],
    ['Clothing', 'Jacket', 'Downtown', 670, 22],
    ['Clothing', 'Pants', 'Airport', 520, 18],
    ['Clothing', 'Shirt', 'Airport', 890, 30],
    ['Furniture', 'Sofa', 'Downtown', 3400, 8],
    ['Furniture', 'Table', 'Mall', 2900, 6],
    ['Furniture', 'Sofa', 'Mall', 4100, 10],
    ['Furniture', 'Chair', 'Downtown', 1800, 4],
    ['Furniture', 'Table', 'Airport', 950, 2],
    ['Furniture', 'Sofa', 'Airport', 2200, 5],
    ['Electronics', 'Tablet', 'Mall', 3100, 16],
    ['Electronics', 'Smartphone', 'Downtown', 2600, 13],
    ['Clothing', 'Jacket', 'Mall', 780, 26],
    ['Clothing', 'Pants', 'Downtown', 1100, 38],
    ['Furniture', 'Chair', 'Mall', 2100, 5],
    ['Furniture', 'Table', 'Downtown', 3200, 7],
    ['Electronics', 'Laptop', 'Downtown', 4800, 16],
    ['Electronics', 'Tablet', 'Airport', 1500, 8],
    ['Clothing', 'Shirt', 'Downtown', 1350, 45],
    ['Clothing', 'Jacket', 'Airport', 420, 14],
    ['Furniture', 'Sofa', 'Downtown', 3800, 9],
    ['Furniture', 'Chair', 'Airport', 1200, 3],
];

// Pivot table configuration with filters and collapsed rows
const pivotTables = [
    {
        id: 'd13442b8-a1c8-4055-97f3-1455cd3f88a9',
        anchor: 'A1',
        source: 'Products!A1:E31',
        rows: [
            {
                columnIndex: 0,
                sortBy: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                ascendingOrder: false,
                collapsed: ['Clothing'],
            },
            {
                columnIndex: 1,
                sortBy: 'name',
                ascendingOrder: true,
            },
        ],
        columns: [
            {
                columnIndex: 2,
                sortBy: 'name',
                ascendingOrder: true,
            },
        ],
        cells: [
            {
                id: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                columnIndex: 3,
                method: 'SUM',
            },
            {
                id: '3a5f8c2d-b9e1-4a7c-8d6e-9f2b1c4d5e6a',
                columnIndex: 4,
                method: 'SUM',
            },
        ],
        filters: [
            {
                columnIndex: 4,
                validation: {
                    type: 'number',
                    criteria: '>=',
                    value: [10],
                },
            },
        ],
    },
];

export default function App() {
    // Spreadsheet ref
    const spreadsheet = useRef();

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} tabs toolbar tableOverflow tableWidth="1000px" tableHeight="600px">
            <Worksheet worksheetName="Products" data={productData} minDimensions={[6, 35]} />
            <Worksheet worksheetName="Pivot" pivotTables={pivotTables} minDimensions={[10, 20]} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :tabs="true" :toolbar="true" :tableOverflow="true" tableWidth="1000px" tableHeight="600px">
        <Worksheet worksheetName="Products" :data="productData" :minDimensions="[6, 35]" />
        <Worksheet worksheetName="Pivot" :pivotTables="pivotTables" :minDimensions="[10, 20]" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    setup() {
        // Product inventory data
        const productData = [
            ['Category', 'Product', 'Store', 'Revenue', 'Units'],
            ['Electronics', 'Laptop', 'Downtown', 4500, 15],
            ['Electronics', 'Smartphone', 'Mall', 3200, 20],
            ['Electronics', 'Laptop', 'Mall', 5100, 17],
            ['Electronics', 'Tablet', 'Downtown', 2800, 14],
            ['Electronics', 'Smartphone', 'Airport', 1900, 10],
            ['Electronics', 'Laptop', 'Airport', 3700, 12],
            ['Clothing', 'Shirt', 'Downtown', 1200, 40],
            ['Clothing', 'Pants', 'Mall', 980, 35],
            ['Clothing', 'Shirt', 'Mall', 1450, 48],
            ['Clothing', 'Jacket', 'Downtown', 670, 22],
            ['Clothing', 'Pants', 'Airport', 520, 18],
            ['Clothing', 'Shirt', 'Airport', 890, 30],
            ['Furniture', 'Sofa', 'Downtown', 3400, 8],
            ['Furniture', 'Table', 'Mall', 2900, 6],
            ['Furniture', 'Sofa', 'Mall', 4100, 10],
            ['Furniture', 'Chair', 'Downtown', 1800, 4],
            ['Furniture', 'Table', 'Airport', 950, 2],
            ['Furniture', 'Sofa', 'Airport', 2200, 5],
            ['Electronics', 'Tablet', 'Mall', 3100, 16],
            ['Electronics', 'Smartphone', 'Downtown', 2600, 13],
            ['Clothing', 'Jacket', 'Mall', 780, 26],
            ['Clothing', 'Pants', 'Downtown', 1100, 38],
            ['Furniture', 'Chair', 'Mall', 2100, 5],
            ['Furniture', 'Table', 'Downtown', 3200, 7],
            ['Electronics', 'Laptop', 'Downtown', 4800, 16],
            ['Electronics', 'Tablet', 'Airport', 1500, 8],
            ['Clothing', 'Shirt', 'Downtown', 1350, 45],
            ['Clothing', 'Jacket', 'Airport', 420, 14],
            ['Furniture', 'Sofa', 'Downtown', 3800, 9],
            ['Furniture', 'Chair', 'Airport', 1200, 3],
        ];

        // Pivot table configuration with filters and collapsed rows
        const pivotTables = [
            {
                id: 'd13442b8-a1c8-4055-97f3-1455cd3f88a9',
                anchor: 'A1',
                source: 'Products!A1:E31',
                rows: [
                    {
                        columnIndex: 0,
                        sortBy: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                        ascendingOrder: false,
                        collapsed: ['Clothing'],
                    },
                    {
                        columnIndex: 1,
                        sortBy: 'name',
                        ascendingOrder: true,
                    },
                ],
                columns: [
                    {
                        columnIndex: 2,
                        sortBy: 'name',
                        ascendingOrder: true,
                    },
                ],
                cells: [
                    {
                        id: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                        columnIndex: 3,
                        method: 'SUM',
                    },
                    {
                        id: '3a5f8c2d-b9e1-4a7c-8d6e-9f2b1c4d5e6a',
                        columnIndex: 4,
                        method: 'SUM',
                    },
                ],
                filters: [
                    {
                        columnIndex: 4,
                        validation: {
                            type: 'number',
                            criteria: '>=',
                            value: [10],
                        },
                    },
                ],
            },
        ];

        return {
            productData,
            pivotTables,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import pivot from "@jspreadsheet/pivot";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/pivot/dist/index.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNlNDY3NjdmMTJmNDcwYmU4YWNiZDQ4OTU5MzNiYzUxOWI4YTM5NDJlNDk0MjkwMzk5YTFkNzEyYmMyZWJkYjZmODdjMjFkZDJmOGRhYzg5ZTVmZjkyNjMzYjZlZTQ0YjE5YTI3YmZlODU4NzNhYzJhMTQyMDk3NGIzNWQxNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZNE5UUTRNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula, pivot });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            toolbar: true,
            tabs: true,
            tableOverflow: true,
            tableWidth: '1000px',
            tableHeight: '600px',
            worksheets: [
                {
                    worksheetName: 'Products',
                    minDimensions: [6, 35],
                    data: [
                        ['Category', 'Product', 'Store', 'Revenue', 'Units'],
                        ['Electronics', 'Laptop', 'Downtown', 4500, 15],
                        ['Electronics', 'Smartphone', 'Mall', 3200, 20],
                        ['Electronics', 'Laptop', 'Mall', 5100, 17],
                        ['Electronics', 'Tablet', 'Downtown', 2800, 14],
                        ['Electronics', 'Smartphone', 'Airport', 1900, 10],
                        ['Electronics', 'Laptop', 'Airport', 3700, 12],
                        ['Clothing', 'Shirt', 'Downtown', 1200, 40],
                        ['Clothing', 'Pants', 'Mall', 980, 35],
                        ['Clothing', 'Shirt', 'Mall', 1450, 48],
                        ['Clothing', 'Jacket', 'Downtown', 670, 22],
                        ['Clothing', 'Pants', 'Airport', 520, 18],
                        ['Clothing', 'Shirt', 'Airport', 890, 30],
                        ['Furniture', 'Sofa', 'Downtown', 3400, 8],
                        ['Furniture', 'Table', 'Mall', 2900, 6],
                        ['Furniture', 'Sofa', 'Mall', 4100, 10],
                        ['Furniture', 'Chair', 'Downtown', 1800, 4],
                        ['Furniture', 'Table', 'Airport', 950, 2],
                        ['Furniture', 'Sofa', 'Airport', 2200, 5],
                        ['Electronics', 'Tablet', 'Mall', 3100, 16],
                        ['Electronics', 'Smartphone', 'Downtown', 2600, 13],
                        ['Clothing', 'Jacket', 'Mall', 780, 26],
                        ['Clothing', 'Pants', 'Downtown', 1100, 38],
                        ['Furniture', 'Chair', 'Mall', 2100, 5],
                        ['Furniture', 'Table', 'Downtown', 3200, 7],
                        ['Electronics', 'Laptop', 'Downtown', 4800, 16],
                        ['Electronics', 'Tablet', 'Airport', 1500, 8],
                        ['Clothing', 'Shirt', 'Downtown', 1350, 45],
                        ['Clothing', 'Jacket', 'Airport', 420, 14],
                        ['Furniture', 'Sofa', 'Downtown', 3800, 9],
                        ['Furniture', 'Chair', 'Airport', 1200, 3],
                    ],
                },
                {
                    worksheetName: 'Pivot',
                    minDimensions: [10, 20],
                    pivotTables: [
                        {
                            id: 'd13442b8-a1c8-4055-97f3-1455cd3f88a9',
                            anchor: 'A1',
                            source: 'Products!A1:E31',
                            rows: [
                                {
                                    columnIndex: 0,
                                    sortBy: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                                    ascendingOrder: false,
                                    collapsed: ['Clothing'],
                                },
                                {
                                    columnIndex: 1,
                                    sortBy: 'name',
                                    ascendingOrder: true,
                                },
                            ],
                            columns: [
                                {
                                    columnIndex: 2,
                                    sortBy: 'name',
                                    ascendingOrder: true,
                                },
                            ],
                            cells: [
                                {
                                    id: '2100eb1e-5a27-4db7-9774-53da9dba83da',
                                    columnIndex: 3,
                                    method: 'SUM',
                                },
                                {
                                    id: '3a5f8c2d-b9e1-4a7c-8d6e-9f2b1c4d5e6a',
                                    columnIndex: 4,
                                    method: 'SUM',
                                },
                            ],
                            filters: [
                                {
                                    columnIndex: 4,
                                    validation: {
                                        type: 'number',
                                        criteria: '>=',
                                        value: [10],
                                    },
                                },
                            ],
                        },
                    ],
                },
            ],
        });
    }
}

Edit this page on GitHub