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

npm install @rowsncolumns/pivot

yarn

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:

1. Auto-initialization (default): The hook initializes DuckDB on first use

2. Manual initialization: Optionally provide a pre-initialized database

3. 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.