PivotTables

A PivotTable has two separate parts: a pivot cache holding the source fields and records, and a pivot table that places and aggregates those fields on a worksheet. Create the cache first, give it a worksheet source, then create and configure the pivot table.

Formulon projects the configured cache into cells with pivotLayout(). It does not rebuild a pivot cache from a worksheet or run external connections. Populate a newly authored cache explicitly, and keep its declared worksheet source aligned with the source data.

Pivot item grouping, labels, and order use the workbook's active Excel compatibility profile. Mac profiles use their measured locale collation; Windows profiles use the Windows word sort measured on Windows Excel 365 ja-JP. The profile is runtime state and is not saved in the workbook. See Text collation.

Caption filters (LabelContains, LabelBeginsWith, and authored begins / ends / contains predicates with their negations) use SEARCH-style wildcard matching under the active profile's compare collation. Case, width, and kana fold one character at a time; * matches a run, ? matches one character, and ~ escapes the next wildcard. A loaded pivot matches the stored wildcard pattern when it refreshes, and API label filters use the same rule.

Create a simple summary

This example creates a Region / Product / Sales cache and configures a row hierarchy with a sum anchored at E1. The worksheet source and cache records contain the same header and data rows, so an Excel refresh has a consistent source. pivotLayout() returns the projected cells for host rendering; it does not write those values into worksheet cells. PivotAxis.Row is 0, PivotAxis.Value is 2, and PivotAggregation.Sum is 0 when a host uses the C ABI directly.

WASM

import createFormulon, {
  PivotAggregation, PivotAxis, ValueKind,
} from '@libraz/formulon'

const Module = await createFormulon()
const wb = Module.Workbook.createDefault()
type StatusLike = { ok: boolean; message: string }
const requireOk = <T extends StatusLike | { status: StatusLike }>(result: T): T => {
  const status: StatusLike = 'ok' in result ? result as StatusLike : result.status
  if (!status.ok) throw new Error(status.message)
  return result
}
try {
  const cacheId = requireOk(wb.pivotCacheCreate(0)).index
  requireOk(wb.pivotCacheSetWorksheetSource(cacheId, { present: true, ref: 'A1:C3', sheet: 'Sheet1' }))
  requireOk(wb.setText(0, 0, 0, 'Region'))
  requireOk(wb.setText(0, 0, 1, 'Product'))
  requireOk(wb.setText(0, 0, 2, 'Sales'))
  requireOk(wb.setText(0, 1, 0, 'East'))
  requireOk(wb.setText(0, 1, 1, 'Widget'))
  requireOk(wb.setNumber(0, 1, 2, 10))
  requireOk(wb.setText(0, 2, 0, 'West'))
  requireOk(wb.setText(0, 2, 1, 'Gadget'))
  requireOk(wb.setNumber(0, 2, 2, 30))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Region'))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Product'))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Sales'))

  const rows: Array<[string, string, number]> = [
    ['East', 'Widget', 10], ['West', 'Gadget', 30],
  ]
  for (const [region, product, sales] of rows) {
    const record = requireOk(wb.pivotCacheRecordAdd(cacheId)).index
    requireOk(wb.pivotCacheRecordSetText(cacheId, record, 0, region))
    requireOk(wb.pivotCacheRecordSetText(cacheId, record, 1, product))
    requireOk(wb.pivotCacheRecordSetNumber(cacheId, record, 2, sales))
  }

  const pivot = requireOk(wb.pivotCreate(0, 'SalesSummary', cacheId, 0, 4))
  const regionField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Region', axis: PivotAxis.Row }))
  const productField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Product', axis: PivotAxis.Row }))
  const salesField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Sales', axis: PivotAxis.Value }))
  requireOk(wb.pivotSetRowFieldOrder(
    0, pivot.index, [regionField.index, productField.index],
  ))
  const fmt = requireOk(wb.addNumFmt('#,##0.00'))
  requireOk(wb.pivotDataFieldAdd(0, pivot.index, {
    name: 'Sum of Sales', fieldIndex: salesField.index, aggregation: PivotAggregation.Sum,
    numberFormat: String(fmt.numFmtId)
  }))

  requireOk(wb.pivotFieldSetNumberFormat(0, pivot.index, salesField.index, String(fmt.numFmtId)))
  const layout = requireOk(wb.pivotLayout(0, pivot.index))
  const labels = layout.cells.map((cell) => cell.value.text).filter(Boolean)
  if (!labels.includes('East') || !labels.includes('West')) throw new Error('Pivot row labels are missing')
  const anchor = wb.getValue(0, 0, 4)
  if (!anchor.status.ok || anchor.value.kind !== ValueKind.Blank) throw new Error('pivotLayout wrote E1')
  console.log(labels)
} finally {
  wb.delete()
}

Native Node

import {
  PivotAggregation, PivotAxis, ValueKind, Workbook,
} from '@libraz/formulon-native'

const wb = Workbook.createDefault()
type StatusLike = { ok: boolean; message: string }
const requireOk = <T extends StatusLike | { status: StatusLike }>(result: T): T => {
  const status: StatusLike = 'ok' in result ? result as StatusLike : result.status
  if (!status.ok) throw new Error(status.message)
  return result
}
try {
  const cacheId = requireOk(wb.pivotCacheCreate(0)).index
  requireOk(wb.pivotCacheSetWorksheetSource(cacheId, { present: true, ref: 'A1:C3', sheet: 'Sheet1' }))
  requireOk(wb.setText(0, 0, 0, 'Region'))
  requireOk(wb.setText(0, 0, 1, 'Product'))
  requireOk(wb.setText(0, 0, 2, 'Sales'))
  requireOk(wb.setText(0, 1, 0, 'East'))
  requireOk(wb.setText(0, 1, 1, 'Widget'))
  requireOk(wb.setNumber(0, 1, 2, 10))
  requireOk(wb.setText(0, 2, 0, 'West'))
  requireOk(wb.setText(0, 2, 1, 'Gadget'))
  requireOk(wb.setNumber(0, 2, 2, 30))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Region'))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Product'))
  requireOk(wb.pivotCacheFieldAdd(cacheId, 'Sales'))

  const rows: Array<[string, string, number]> = [
    ['East', 'Widget', 10], ['West', 'Gadget', 30],
  ]
  for (const [region, product, sales] of rows) {
    const record = requireOk(wb.pivotCacheRecordAdd(cacheId)).index
    requireOk(wb.pivotCacheRecordSetText(cacheId, record, 0, region))
    requireOk(wb.pivotCacheRecordSetText(cacheId, record, 1, product))
    requireOk(wb.pivotCacheRecordSetNumber(cacheId, record, 2, sales))
  }

  const pivot = requireOk(wb.pivotCreate(0, 'SalesSummary', cacheId, 0, 4))
  const regionField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Region', axis: PivotAxis.Row }))
  const productField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Product', axis: PivotAxis.Row }))
  const salesField = requireOk(wb.pivotFieldAdd(0, pivot.index, { sourceName: 'Sales', axis: PivotAxis.Value }))
  requireOk(wb.pivotSetRowFieldOrder(0, pivot.index, [regionField.index, productField.index]))
  const fmt = requireOk(wb.addNumFmt('#,##0.00'))
  requireOk(wb.pivotDataFieldAdd(0, pivot.index, {
    name: 'Sum of Sales', fieldIndex: salesField.index, aggregation: PivotAggregation.Sum,
    numberFormat: String(fmt.numFmtId),
  }))
  requireOk(wb.pivotFieldSetNumberFormat(0, pivot.index, salesField.index, String(fmt.numFmtId)))

  const layout = requireOk(wb.pivotLayout(0, pivot.index))
  const labels = layout.cells.map((cell) => cell.value.text).filter(Boolean)
  if (!labels.includes('East') || !labels.includes('West')) throw new Error('Pivot row labels are missing')
  const anchor = wb.getValue(0, 0, 4)
  if (!anchor.status.ok || anchor.value.kind !== ValueKind.Blank) throw new Error('pivotLayout wrote E1')
  console.log(labels)
} finally {
  wb.dispose()
}

Python

from formulon import (
    PivotAggregation, PivotAxis, PivotDataFieldSpec, PivotFieldSpec,
    PivotWorksheetSource, ValueKind,
    Workbook,
)

with Workbook.create_default() as wb:
    cache_id = wb.pivot_cache_create()
    wb.set_pivot_cache_worksheet_source(cache_id, PivotWorksheetSource(ref='A1:C3', sheet='Sheet1'))
    wb.set_text(0, 0, 0, 'Region')
    wb.set_text(0, 0, 1, 'Product')
    wb.set_text(0, 0, 2, 'Sales')
    wb.set_text(0, 1, 0, 'East')
    wb.set_text(0, 1, 1, 'Widget')
    wb.set_number(0, 1, 2, 10)
    wb.set_text(0, 2, 0, 'West')
    wb.set_text(0, 2, 1, 'Gadget')
    wb.set_number(0, 2, 2, 30)
    wb.pivot_cache_field_add(cache_id, 'Region')
    wb.pivot_cache_field_add(cache_id, 'Product')
    wb.pivot_cache_field_add(cache_id, 'Sales')

    for region, product, sales in [('East', 'Widget', 10), ('West', 'Gadget', 30)]:
        record = wb.pivot_cache_record_add(cache_id)
        wb.pivot_cache_record_set_text(cache_id, record, 0, region)
        wb.pivot_cache_record_set_text(cache_id, record, 1, product)
        wb.pivot_cache_record_set_number(cache_id, record, 2, sales)

    pivot = wb.pivot_create(0, 'SalesSummary', cache_id, 0, 4)
    region_field = wb.pivot_field_add(0, pivot, PivotFieldSpec(source_name='Region', axis=PivotAxis.ROW))
    product_field = wb.pivot_field_add(0, pivot, PivotFieldSpec(source_name='Product', axis=PivotAxis.ROW))
    sales_field = wb.pivot_field_add(0, pivot, PivotFieldSpec(source_name='Sales', axis=PivotAxis.VALUE))
    wb.pivot_set_row_field_order(0, pivot, [region_field, product_field])
    fmt_id = wb.add_num_fmt('#,##0.00')
    wb.pivot_data_field_add(0, pivot, PivotDataFieldSpec(
        name='Sum of Sales', field_index=sales_field, aggregation=PivotAggregation.SUM,
        number_format=str(fmt_id)
    ))

    wb.pivot_field_set_number_format(0, pivot, sales_field, str(fmt_id))
    layout = wb.pivot_layout(0, pivot)
    labels = [cell.value.text for cell in layout.cells if cell.value.text]
    if 'East' not in labels or 'West' not in labels:
        raise RuntimeError('Pivot row labels are missing')
    if wb.get_value(0, 0, 4).kind is not ValueKind.BLANK:
        raise RuntimeError('pivot_layout wrote E1')
    print(labels)

All workbook coordinates are zero-based, so the pivot anchor (0, 4) is E1. The cache source is required before saving a newly authored pivot. A cache without it would produce a package Excel offers to repair, so save() fails instead. The anchor locates the projected result; it does not materialize the returned layout into E1 or the surrounding worksheet cells.

The focused snippets below are continuations. Insert them before the finally / with cleanup in a full example above, or repeat the setup in a new live workbook; they cannot run after that workbook has been cleaned up.

Address a cache item by index

Use pivotFieldAddItemAt() / pivot_field_add_item_at() when a manual filter must bind to a cache shared-item index. This is the form that can express the blank member: an empty label passed to pivotFieldAddItem() is text matching and cannot identify the blank item. The index is the same zero-based space as the OOXML pivot item x attribute. An index that does not resolve yet is accepted and filters nothing, so populate the cache before evaluating the pivot when the item must match records.

wb.pivotFieldAddItemAt(0, pivot.index, /*fieldIdx*/ 0, /*cacheIndex*/ 2, false)

wb.pivot_field_add_item_at(0, pivot, 0, 2, False)  # field_idx=0, cache_index=2

Try it

Pivot a small sales table below. The source rows sit in a read-only formulon-cell grid and the same rows are loaded into a pivot cache; choosing a row field, an optional column field and an aggregation rebuilds the pivot and renders the cells that pivotLayout() returns. The result is drawn by the page, not written into the sheet.

PivotTable projection

The same rows as the source table go into a pivot cache. Each change of row field, column field or aggregation rebuilds the pivot and renders the result of pivotLayout().

Powered by the real Formulon engine (WASM) — it runs entirely in your browser, nothing is uploaded.

Inspect the projected result

pivotLayout() / pivot_layout() returns the projected rectangle and cells. It is a projection for host rendering and does not write ordinary worksheet values. Saving preserves the PivotTable definition and cache for Excel to render; a static report must write the returned cells explicitly with cell setters.

The layout snippet follows the same live-workbook rule: run it before cleanup, or repeat the setup in a new workbook.

WASM / Native Node

const layout = wb.pivotLayout(0, pivot.index)
if (!layout.status.ok) throw new Error(layout.status.message)
for (const cell of layout.cells) console.log(cell.row, cell.col, cell.value)

Python

layout = wb.pivot_layout(0, pivot)
for cell in layout.cells:
    print(cell.row, cell.col, cell.value)

Configure an existing pivot

Each setting is its own call on (sheet, pivotIndex, …). Python uses the snake_case name unless the table shows otherwise.

Task	WASM / Native Node
Compact, tabular, or outline form	pivotSetLayout, pivotGetLayout (Python: set_pivot_report_layout)
Grand totals, name, anchor	pivotSetGrandTotals, pivotSetName, pivotSetAnchor
Field placement and order	pivotFieldSetAxis, pivotSetRowFieldOrder, pivotSetColFieldOrder
Sort	pivotFieldSetSort
Subtotals	pivotFieldAddSubtotalFn, pivotFieldClearSubtotalFns, pivotFieldSetSubtotalTop
Show or hide items	pivotFieldAddItem, pivotFieldAddItemAt, pivotFieldSetItemVisible, pivotFieldClearItems
Value fields	pivotDataFieldAdd, pivotDataFieldSet, pivotDataFieldClear
Label and value filters	pivotFilterAdd, pivotFilterAt, pivotFilterRemoveAt, pivotFilterClear
Date grouping	pivotFieldSetDateGroup, pivotFieldClearDateGroup

Read a definition back

A pivot's definition can be read back whether it was built in the session or loaded from a file. Each getter takes (sheet, pivotIdx); Python uses snake_case with pivot_index.

Read	WASM / Native Node	Python
Count on a sheet, name, cache id	pivotCount, pivotName, pivotCacheId	pivot_count, pivot_name, pivot_cache_id
Grand totals	pivotGetGrandTotals (rows, cols)	pivot_grand_totals (tuple)
Table style	pivotGetStyleInfo	pivot_style_info
Axis order	pivotRowFieldOrder, pivotColFieldOrder	pivot_row_field_order, pivot_col_field_order
Fields and items	pivotFieldCount, pivotFieldAt, pivotFieldItemCount, pivotFieldItemAt	pivot_field_count, pivot_field_at, pivot_field_item_count, pivot_field_item_at
Data fields	pivotDataFieldCount, pivotDataFieldAt	pivot_data_field_count, pivot_data_field_at

pivotFieldAt() reports the field's axis (-1 when it sits on none), sort, number format, date grouping and subtotals. defaultSubtotal / default_subtotal says whether the automatic subtotal shows and is false whenever custom functions are set; subtotalFns / subtotal_fns lists those functions in ascending PivotAggregation order. pivotDataFieldAt() returns the same members as PivotDataFieldSpec, with showAsBaseField / showAsBaseItem at -1 when unset.

pivotSetStyleInfo() / pivot_set_style_info() set the table style name and the row-header, column-header, row-stripe, column-stripe and last-column toggles; omitted flags are false. A new pivot starts as PivotStyleLight16 with row headers, column headers and the last column on. pivotGetStyleInfo() reports present: false when the definition has no style element.

A data field added or replaced with no name is named from the profile's pivot wording, such as Sum of Sales under an English profile and 合計 / Sales under ja_JP.

pivotFieldSetSort() / pivot_field_set_sort() / fm_workbook_pivot_field_set_sort take a PivotSortOrder: Descending (0), Ascending (1), or Manual (2) — PivotSortOrder.DESCENDING / ASCENDING / MANUAL in Python and FM_PIVOT_SORT_DESCENDING / _ASCENDING / _MANUAL in C; any other value is rejected. Manual keeps the field's items in the order they were added with pivotFieldAddItem() and takes no by-field key; it is saved as sortType="manual". For the other orders an empty by-field key sorts by label. An item added by label binds to the cache's shared item of that label, so its saved <item x> matches the cache.

pivotFieldAdd() and pivotFieldSetAxis() keep the row and column field orders in step with the field's axis, so a field added on the row or column axis appears without a separate order call. pivotSetRowFieldOrder() / pivotSetColFieldOrder() set the axis's whole membership: fields left out of the list leave the axis, and a duplicate index is rejected. pivotFieldClear() empties both orders.

subtotalTop defaults to true in the Python, Node, and WASM bindings, as in Excel: a group's subtotal sits on its own heading row. With false, compact and outline layouts head the group with a label-only row and close it with an "<item> Total" row in the locale's wording. A field with custom subtotal functions labels each subtotal row with the function name in the UI locale's wording (for example East Max) in Excel's fixed function order, and two or more functions place the subtotals below the group whatever subtotalTop says.

PivotDataFieldSpec.showAs / show_as also supports PercentOf, PercentOfRunningTotal, RankAscending, and RankDescending. Set showAsBaseField / show_as_base_field and showAsBaseItem / show_as_base_item for modes that need a base item. Percent of Grand Total and Index divide by the whole-table aggregate when the grand totals are hidden. Loaded x14 pivotShowAs values and subtotalHiddenItems are preserved on save.

Filter authoring and round-tripping

A filter added with pivotFilterAdd() affects pivotLayout() and GETPIVOTDATA only while the workbook handle lives; save() does not write it. Loaded <filters> blocks are preserved on save, including valueEqual, valueNotEqual, valueGreaterThan, valueGreaterThanOrEqual, valueLessThan, valueLessThanOrEqual, valueBetween, and valueNotBetween; they do not appear in pivotFilterCount(). Item visibility is the filter state that persists when authored through the API.

Group dates by an explicit day interval

PivotDateGrouping.Days / PivotDateGrouping.DAYS is Excel's interval-based date grouping. The snippets below assume a separate pivot whose cache includes an OrderDate field; dateField is the index returned when that field is added. There is no separate week granularity: use intervalDays: 7 / interval_days=7 for a seven-day bucket. The date-grouping call takes startYear and endYear (use -1 except for a year-truncation bound), then the day interval and the optional Start/End window as Excel serials. The last three arguments are optional on both JavaScript packages and in Python: intervalDays defaults to 1, and startSerial / endSerial to -1. Passing -1 for either serial lets the engine use the field's data minimum as Start and the data maximum plus one day as End. Labels use the bucket's date range; values below or above an explicit window are labelled <start or >end. A zero interval or a Start after End is rejected.

The date field index is the index / returned integer from pivotFieldAdd() / pivot_field_add():

WASM / Native Node

const dateStatus = wb.pivotFieldSetDateGroup(
  0, pivot.index, dateField.index, PivotDateGrouping.Days, PivotCalendar.Gregorian,
  -1, -1, 7, -1, -1
)
if (!dateStatus.ok) throw new Error(dateStatus.message)

Python

wb.pivot_field_set_date_group(
    0, pivot, date_field, PivotDateGrouping.DAYS, PivotCalendar.GREGORIAN,
    start_year=-1, end_year=-1, interval_days=7,
    start_serial=-1.0, end_serial=-1.0,
)

Pivot field and data-field number formats use a decimal numFmtId string, not a format code. Use a built-in ID or register a code with addNumFmt() / add_num_fmt() first, as in the examples above; any other non-empty string is rejected and the ID is written to the pivot part.

Field names, grid bounds, and recalculation

Field names must resolve to one distinct pivot field. If a name is shared by distinct fields, filter authoring rejects the ambiguous name without mutating the existing filter; value filters also reject an unknown field name at authoring. GETPIVOTDATA returns #REF! when a field name is ambiguous.

Pivot anchors and their rendered spans must fit within the worksheet grid, so a new out-of-grid anchor is rejected. If a row or column insertion pushes an entire PivotTable off the grid, the engine removes that pivot. A GETPIVOTDATA formula that reads the removed pivot then returns #REF! after recalculation.

Pivot authoring edits — including cache-record, field, layout, filter, data-field, add, and remove operations — dirty formula cells that depend on pivot results and invalidate derived layout. After recalc(), GETPIVOTDATA observes the edited result. Recreating a removed pivot at its anchor wakes a dependent formula so it can recover on the next recalculation.

Boundaries

• A new cache is not automatically populated from its declared worksheet range. Add its fields and records explicitly.
• The source range is metadata required for a valid saved workbook; it does not schedule a cache refresh.
• External connections and PivotCache recalculation are outside Formulon's local calculation model.
• Existing PivotTables can be read, updated, projected, and preserved. XLSB pivotCacheDefinition, pivotCacheRecords, and pivot-table parts are evaluated when their record encoding is supported; an unmeasured encoding is skipped rather than guessed. Keep source-workbook compatibility checks in place when files contain features outside the documented model.

Read next

• Workbook operations — the broader workbook editing surface.
• File formats — PivotTable and PivotCache preservation boundaries.
• Compatibility non-goals — external connections and local-engine scope.