@rowsncolumns/pivot

AG Grid-style pivoting library for spreadsheets using DuckDB, adapted from ag-grid-duckdb-datasource.

Features

• Server-side pivot operations using DuckDB
• Row and column grouping with aggregations
• Dynamic filtering with multiple filter types
• Sorting by any field
• Aggregation functions: sum, count, avg, min, max, var, stddev
• React hook for easy integration
• Multiple pivot tables support with pivotId-based API

Installation

npm install @rowsncolumns/pivot

Usage

Basic Example

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

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

  const {
    addRowPivot,
    addColumnPivot,
    addValue,
    addFilter,
    executePivot,
    getPivotState,
    isInitializing, // Shows if database is initializing
  } = usePivot({
    pivotTables, // Contains targetSheetId and targetPosition
    onChangePivotTables: setPivotTables, // Updates pivot config when actions occur
    onChangeSheetData: setSheetData,
    // database prop is optional - will auto-initialize if not provided
    getGridValues: (range: SheetRange) => {
      // Return structured data with headers and rows
      // Headers should be extracted from your grid (usually first row)
      // Rows should be the actual data (excluding headers)
      return {
        headers: ["Product", "Region", "Sales"],
        rows: [
          ["Laptop", "North", 1000],
          ["Phone", "South", 500],
          ["Tablet", "East", 750],
          ["Laptop", "South", 1200],
          // ...
        ],
      };
    },
  });

  // Add a row pivot
  await addRowPivot("pivot-1", {
    field: "Product",
    displayName: "Product",
  });

  // Add a column pivot
  await addColumnPivot("pivot-1", {
    field: "Region",
    displayName: "Region",
  });

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

  // Execute the pivot
  await executePivot("pivot-1");
}

Understanding getGridValues

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

getGridValues: (range: SheetRange) => {
  headers: string[];  // Column names - typically from the first row of your data
  rows: (string | number | boolean | null | undefined)[][];  // Data rows
}

Example Implementation:

const getGridValues = (range: SheetRange) => {
  const {
    sheetId,
    startRowIndex,
    startColumnIndex,
    endRowIndex,
    endColumnIndex,
  } = range;

  // Get the sheet data
  const sheet = getSheetById(sheetId);

  // Extract headers (first row)
  const headers: string[] = [];
  for (let col = startColumnIndex; col <= endColumnIndex; col++) {
    const cell = getCellValue(sheet, startRowIndex, col);
    headers.push(String(cell || `col${col}`));
  }

  // Extract data rows (excluding header row)
  const rows: any[][] = [];
  for (let row = startRowIndex + 1; row <= endRowIndex; row++) {
    const rowData: any[] = [];
    for (let col = startColumnIndex; col <= endColumnIndex; col++) {
      const cell = getCellValue(sheet, row, col);
      rowData.push(cell);
    }
    rows.push(rowData);
  }

  return { headers, rows };
};

API Reference

usePivot Hook

const {
  // State
  isInitializing,

  // Core operations
  executePivot,
  refreshPivot,
  getPivotState,

  // Row/Column/Value management
  addRowPivot,
  addColumnPivot,
  addValue,
  removePivotField,
  changeFieldOrder,

  // Filtering
  addFilter,
  removeFilter,

  // Aggregation
  setAggregationFunction,

  // Sorting
  sortPivotField,

  // Display options
  toggleSubtotals,
  toggleRowGrandTotals,
  toggleColumnGrandTotals,
  toggleGrandTotals,

  // Utilities
  clearPreviousResults,
} = usePivot(props);

Available Functions

| Function | Description | Parameters | | --------------------------- | ---------------------------------------- | ------------------------------------- | | addRowPivot | Add a field to the rows area | (pivotId, field: PivotField) | | expandRowPivot | Fetch data for an expanded row group | (pivotId, groupKeys: string[]) | | collapseRowPivot | Mark a row group as collapsed | (pivotId, groupKeys: string[]) | | addColumnPivot | Add a field to the columns area | (pivotId, field: PivotField) | | addValue | Add a field to the values area | (pivotId, field: PivotField) | | 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) | | removePivotField | Remove a field from an area | (pivotId, field, area) | | changeFieldOrder | Reorder fields | (pivotId, area, fromIndex, toIndex) | | sortPivotField | Sort by a field | (pivotId, field, direction) | | toggleSubtotals | Show/hide subtotals | (pivotId, show) | | toggleRowGrandTotals | Show/hide row grand totals | (pivotId, show) | | toggleColumnGrandTotals | Show/hide column grand totals | (pivotId, show) | | toggleGrandTotals | Show/hide both row & column grand totals | (pivotId, show) | | refreshPivot | Recalculate the pivot | (pivotId) | | executePivot | Execute pivot and update sheet | (pivotId) | | getPivotState | Get current pivot state | (pivotId) |

Aggregation Functions

• sum - Sum of values
• count - Count of values
• avg - Average of values
• min - Minimum value
• max - Maximum value
• var - Variance
• stddev - Standard deviation

PivotField Type

interface PivotField {
  field: string;
  displayName?: string;
  aggFunc?:
    | "sum"
    | "count"
    | "avg"
    | "min"
    | "max"
    | "var"
    | "stddev"
    | "median"
    | "product";
}

Architecture

The library is structured as follows:

• DuckDbDatasource: Main datasource class that interfaces with DuckDB
• QueryBuilder: Base class for query construction
  • SimpleQueryBuilder: Handles basic queries
  • GroupingQueryBuilder: Handles grouped queries
  • PivotQueryBuilder: Handles pivot queries
• PivotManager: Manages pivot state and operations
• usePivot: React hook for easy integration

Database Initialization

The usePivot hook handles DuckDB initialization automatically:

1. Auto-initialization: If you don't provide a database prop, the hook will automatically initialize DuckDB on first use
2. Manual initialization: You can optionally provide a pre-initialized database instance
3. Singleton pattern: The default database is shared across all pivot instances

// Option 1: Auto-initialization (recommended)
const { ... } = usePivot({
  pivotTables,
  // database prop omitted - will auto-initialize
});

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

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

Checking Initialization Status

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

if (isInitializing) {
  return <div>Loading DuckDB...</div>;
}

Implementation Details

The pivot implementation converts spreadsheet data to SQL using DuckDB's PIVOT operator. The flow is:

1. Grid values are converted to SQL VALUES clause
2. DuckDB processes the data with grouping, filtering, and aggregation
3. Results are returned and converted back to sheet format
4. Previous results are cleared and new results are placed at the target position

PivotTable Structure

Each PivotTable must include:

{
  pivotId: string;           // Unique identifier for the pivot
  source: SheetRange;        // Source data range
  targetSheetId: string;     // Where to place results
  targetPosition: {          // Position for results
    rowIndex: number;
    columnIndex: number;
  };
  rows: PivotGroup[];        // Row groupings
  columns: PivotGroup[];     // Column groupings
  values: PivotValue[];      // Aggregation values
  filters?: Record<string, any>;       // Persisted filter model
  sortModel?: Array<{ field: string; sort: "asc" | "desc" }>; // Persisted sort state
}

The hook reads targetSheetId and targetPosition directly from the PivotTable, so you don't need to pass them as separate props.

type PivotGroup = {
  field: string;
  displayName?: string;
  expandedGroups: string[][]; // Path slices (e.g., ["Door"], ["Door","D-01X"])
  sourceColumnOffset: number;
  sortOrder?: SortOrder | null;
};

type PivotValue = {
  field: string;
  displayName?: string;
  sourceColumnOffset: number;
  aggFunc: PivotValueSummarizeFunction;
};

License

UNLICENSED