Pivoting and Grouping

Create pivot tables with row/column grouping and aggregations

Rowsncolumns Spreadsheet supports advanced pivot table functionality powered by DuckDB, allowing you to create dynamic data summaries with row and column grouping, aggregations, and filtering.

Features

Server-side pivot operations using DuckDB for high performance
Row and column grouping with multiple levels of nesting
Dynamic filtering with multiple filter types
Sorting by any field
Aggregation functions: sum, count, avg, min, max, var, stddev, median, product
React hook (usePivot) for easy integration
Multiple pivot tables support with pivotId-based API
Interactive pivot editor UI component

Installation

The pivot functionality is available as a separate package:

npm install @rowsncolumns/pivot

Basic Usage

Setting up the Pivot Hook

import { usePivot } from "@rowsncolumns/pivot";
import type { PivotTable, SheetRange } from "@rowsncolumns/spreadsheet";
import { useState } from "react";

function MySpreadsheet() {
  const [pivotTables, setPivotTables] = useState<PivotTable[]>([]);
  const [sheetData, setSheetData] = useState<SheetData>();

  const {
    addRowPivot,
    addColumnPivot,
    addValue,
    executePivot,
    onRequestCreatePivotTable,
    onCreatePivotTable,
    isInitializing,
  } = usePivot({
    locale: 'en-US',
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    onChangeSheets,
    onChangePivotTables: setPivotTables,
    onChangeSheetData: setSheetData,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    getGridValues: (range: SheetRange) => {
      // Extract data from your spreadsheet
      return {
        headers: ["Product", "Region", "Sales", "Quantity"],
        rows: [
          ["Laptop", "North", 1000, 5],
          ["Phone", "South", 500, 10],
          ["Tablet", "East", 750, 7],
          // ...more data
        ],
      };
    },
  });

  // Check if database is initializing
  if (isInitializing) {
    return <div>Initializing pivot database...</div>;
  }

  return (
    // Your spreadsheet UI
  );
}

Creating a Pivot Table

You can create pivot tables programmatically or through the UI:

// Programmatic creation
await onCreatePivotTable({
  pivotId: crypto.randomUUID(), // Generate unique ID
  source: {
    sheetId: 1,
    startRowIndex: 1,
    endRowIndex: 100,
    startColumnIndex: 1,
    endColumnIndex: 10,
  },
  targetPosition: {
    rowIndex: 1,
    columnIndex: 12,
    sheetId: 1,
  },
  rows: [
    { field: "Region" }
  ],
  columns: [
    { field: "Product" }
  ],
  values: [
    { field: "Sales", aggFunc: "sum" }
  ],
});

Adding Pivot Fields

// Add a row grouping
await addRowPivot(pivotId, {
  field: "Region",
  displayName: "Region",
});

// Add a column grouping
await addColumnPivot(pivotId, {
  field: "Product",
  displayName: "Product",
});

// Add a value with aggregation
await addValue(pivotId, {
  field: "Sales",
  displayName: "Total Sales",
  aggFunc: "sum",
});

// Execute the pivot to update the spreadsheet
const result = await executePivot(pivotId);

Understanding `getGridValues`

The getGridValues function is critical - it extracts data from your spreadsheet and formats it for pivoting:

const getGridValues = useCallback(
  (range: SheetRange) => {
    let headers: (string | number | boolean)[] = [];
    const rows = [];
    
    for (
      let rowIndex = range.startRowIndex;
      rowIndex <= range.endRowIndex;
      rowIndex++
    ) {
      const row = [];
      const isHeader = rowIndex === range.startRowIndex;
      
      for (
        let columnIndex = range.startColumnIndex;
        columnIndex <= range.endColumnIndex;
        columnIndex++
      ) {
        const value = getEffectiveValue(range.sheetId, rowIndex, columnIndex) ?? "";
        row.push(value);
      }
      
      if (isHeader) {
        headers = row;
      } else {
        rows.push(row);
      }
    }

    return { headers, rows };
  },
  [getEffectiveValue]
);

Using the Pivot Editor Component

The package includes a UI component for managing pivot tables:

import { PivotEditor } from "@rowsncolumns/pivot";

// In your component
const activePivotTable = pivotTables.find(
  (table) => table.pivotId === activePivotId
);

return (
  <div>
    {activePivotTable ? (
      <PivotEditor
        pivotTable={activePivotTable}
        getGridValues={getGridValues}
        addColumnPivot={addColumnPivot}
        addRowPivot={addRowPivot}
        removePivotField={removePivotField}
        changeFieldOrder={changeFieldOrder}
        addValue={addValue}
        setAggregationFunction={setAggregationFunction}
        onRequestClose={onClosePivotSettings}
        updatePivotSourceRange={updatePivotSourceRange}
      />
    ) : null}
  </div>
);

API Reference

Core Operations

Function

Description

Parameters

executePivot

Execute pivot and update the spreadsheet

(pivotId: string)

refreshPivot

Recalculate an existing pivot

(pivotId: string)

getPivotState

Get current pivot configuration

(pivotId: string)

getPivotManager

Get the pivot manager instance

(pivotId: string)

Field Management

Function

Description

Parameters

addRowPivot

Add a field to the rows area

(pivotId, field: PivotField)

addColumnPivot

Add a field to the columns area

(pivotId, field: PivotField)

addValue

Add a field to the values area

(pivotId, field: PivotField)

removePivotField

Remove a field from rows/columns/values

(pivotId, field, area)

changeFieldOrder

Reorder fields within an area

(pivotId, area, fromIndex, toIndex)

expandRowPivot

Expand a row group to show details

(pivotId, groupKeys: string[])

Filtering and Aggregation

Function

Description

Parameters

addFilter

Add a filter to the pivot

(pivotId, field, filterValue)

removeFilter

Remove a filter

(pivotId, field)

setAggregationFunction

Set aggregation for a value field

(pivotId, field, aggFunc)

UI Integration

Function

Description

Parameters

onRequestCreatePivotTable

Trigger pivot creation dialog

()

onCreatePivotTable

Create a new pivot table

(config: PivotTableConfig)

onRequestEditPivotTable

Open pivot editor for a table

(pivotId: string)

onRequestDeletePivotTable

Trigger delete confirmation

(pivotId: string)

onClosePivotSettings

Close the pivot editor

()

updatePivotSourceRange

Update the source data range

(pivotId, range: SheetRange)

Transform Functions

Function

Description

Return Type

transformPivotResults

Convert pivot results to 2D array for display

{ data: any[][], metadata: PivotMetadata }

transformChildRows

Get child rows for drill-down

PivotRow[]

Aggregation Functions

The following aggregation functions are supported:

sum - Sum of values
count - Count of values
avg - Average of values
min - Minimum value
max - Maximum value
var - Variance
stddev - Standard deviation
median - Median value
product - Product of values

Complete Example

import React, { useState, useCallback } from 'react';
import {
  SpreadsheetProvider,
  CanvasGrid,
  PivotTable,
} from "@rowsncolumns/spreadsheet";
import {
  usePivot,
  PivotEditor,
  NewPivotTableDialog,
  NewPivotTableEditor,
} from "@rowsncolumns/pivot";
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";

function SpreadsheetWithPivot() {
  const [pivotTables, setPivotTables] = useState<PivotTable[]>([]);
  
  const {
    activeCell,
    activeSheetId,
    sheets,
    sheetData,
    onChangeSheets,
    onChangeSheetData,
    onChangeBatch,
    getEffectiveValue,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    cellXfsRegistry,
  } = useSpreadsheetState({
    sheets: [],
    sheetData: {},
    onChangePivotTables: setPivotTables,
  });

  const getGridValues = useCallback(
    (range: SheetRange) => {
      let headers: (string | number | boolean)[] = [];
      const rows = [];
      
      for (
        let rowIndex = range.startRowIndex;
        rowIndex <= range.endRowIndex;
        rowIndex++
      ) {
        const row = [];
        for (
          let columnIndex = range.startColumnIndex;
          columnIndex <= range.endColumnIndex;
          columnIndex++
        ) {
          row.push(
            getEffectiveValue(range.sheetId, rowIndex, columnIndex) ?? ""
          );
        }
        
        if (rowIndex === range.startRowIndex) {
          headers = row;
        } else {
          rows.push(row);
        }
      }

      return { headers, rows };
    },
    [getEffectiveValue]
  );

  const {
    addRowPivot,
    addColumnPivot,
    addValue,
    executePivot,
    onRequestCreatePivotTable,
    onCreatePivotTable,
    onRequestEditPivotTable,
    activePivotId,
    onClosePivotSettings,
    removePivotField,
    changeFieldOrder,
    setAggregationFunction,
    updatePivotSourceRange,
  } = usePivot({
    locale: 'en-US',
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    onChangeSheets,
    onChangePivotTables: setPivotTables,
    onChangeSheetData,
    getGridValues,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
  });

  const activePivotTable = pivotTables.find(
    (table) => table.pivotId === activePivotId
  );

  return (
    <SpreadsheetProvider>
      <CanvasGrid
        pivotTables={pivotTables}
        onRequestEditPivotTable={onRequestEditPivotTable}
        // ...other props
      />
      
      {/* Pivot Editor Sidebar */}
      {activePivotTable ? (
        <div className="pivot-editor-sidebar">
          <PivotEditor
            pivotTable={activePivotTable}
            getGridValues={getGridValues}
            addColumnPivot={addColumnPivot}
            addRowPivot={addRowPivot}
            removePivotField={removePivotField}
            changeFieldOrder={changeFieldOrder}
            addValue={addValue}
            setAggregationFunction={setAggregationFunction}
            onRequestClose={onClosePivotSettings}
            updatePivotSourceRange={updatePivotSourceRange}
          />
        </div>
      ) : null}

      {/* New Pivot Table Dialog */}
      <NewPivotTableDialog>
        <NewPivotTableEditor
          sheetId={activeSheetId}
          onSubmit={onCreatePivotTable}
        />
      </NewPivotTableDialog>
    </SpreadsheetProvider>
  );
}

Advanced Features

Formatting Pivot Results

You can apply custom formatting to pivot table results:

import { createPivotTableFormats } from "@rowsncolumns/pivot";

const result = await executePivot(pivotId);

if (result) {
  const { data, metadata } = transformPivotResults(pivotId, result);
  
  // Create formats for the pivot table
  const formats = createPivotTableFormats(data, metadata, {
    headerBackgroundColor: { theme: 4, tint: 0.8 },
    headerTextColor: { theme: 1 },
  });

  // Apply the data and formats to the sheet
  onChangeBatch(activeSheetId, pivotRange, data, formats);
}

Drill-Down Support

Expand row groups to see detailed data:

// Expand a specific row group
const childRows = await expandRowPivot(pivotId, ["North", "Laptop"]);

// Transform child rows for display
const transformedChildren = transformChildRows(
  pivotId, 
  childRows, 
  ["North", "Laptop"]
);

Database Initialization

The usePivot hook handles DuckDB initialization automatically:

Auto-initialization (default): The hook initializes DuckDB on first use
Manual initialization: Optionally provide a pre-initialized database
Singleton pattern: The default database is shared across pivot instances

// Auto-initialization (recommended)
const { ... } = usePivot({
  pivotTables,
  // No database prop needed
});

// Manual initialization
import { initializeDatabase } from "@rowsncolumns/pivot";

const db = await initializeDatabase();
const { ... } = usePivot({
  pivotTables,
  database: db,
});

Check initialization status:

const { isInitializing } = usePivot({ ... });

if (isInitializing) {
  return <LoadingIndicator />;
}

PivotTable Type Structure

interface PivotTable {
  pivotId: string;              // Unique identifier
  source: SheetRange;           // Source data range
  targetPosition: {             // Where to place results
    rowIndex: number;
    columnIndex: number;
    sheetId: number;
  };
  rows: PivotGroup[];           // Row groupings
  columns: PivotGroup[];        // Column groupings
  values: PivotValue[];         // Aggregation values
  filters?: Record<string, any>;  // Filter configuration
  sortModel?: Array<{           // Sort configuration
    field: string;
    sort: "asc" | "desc";
  }>;
}

Performance Considerations

Pivot operations run in DuckDB for optimal performance
Large datasets are handled efficiently through SQL aggregation
Results are cached and only recalculated when needed
Use refreshPivot to manually trigger recalculation when source data changes

Troubleshooting

Pivot table not updating

Make sure you call executePivot after making configuration changes:

await addRowPivot(pivotId, { field: "Region" });
await addValue(pivotId, { field: "Sales", aggFunc: "sum" });
await executePivot(pivotId); // Don't forget this!

Database initialization issues

Check the isInitializing state before using pivot features:

const { isInitializing } = usePivot({ ... });

if (isInitializing) {
  // Show loading state
}

Source data not updating

Ensure your getGridValues function is using the latest spreadsheet data and is properly memoized with dependencies.

PreviousDrag and Drop NextTokenizer

Last updated 22 days ago

Was this helpful?

Features

Installation

Basic Usage

Setting up the Pivot Hook

Creating a Pivot Table

Adding Pivot Fields

Understanding getGridValues

Using the Pivot Editor Component

API Reference

Core Operations

Field Management

Filtering and Aggregation

UI Integration

Transform Functions

Aggregation Functions

Complete Example

Advanced Features

Formatting Pivot Results

Drill-Down Support

Database Initialization

PivotTable Type Structure

Performance Considerations

Troubleshooting

Pivot table not updating

Database initialization issues

Source data not updating

Understanding `getGridValues`