PivotManager

Owns a workbook’s pivot tables and pivot caches and the operations spanning them: lookup, refresh, JSF (de)serialization, and structural-edit hooks that adjust output and source ranges when sheets change.

Constructors

Constructor

new PivotManager(workbook): PivotManager;

Parameters

workbook

Workbook

Returns

PivotManager

Methods

[iterator]()

[iterator](): IterableIterator<PivotTable>;

Iterate the workbook’s pivot tables in insertion order (live, unlike getAll).

Returns

IterableIterator<PivotTable>

add()

add(options, behavior?): PivotTable;

Create a pivot table over a worksheet source range, register it, write its output on options.sheetName and recalculate the model. The source’s header row names the fields. The table shares an existing cache over the same source range and headers, and otherwise gets one of its own, refreshed from the source. A failure leaves nothing behind: the table, its cells and an unshared cache are removed again.

Parameters

options

PivotAddOptions

behavior?

skipRecalc?

boolean

skip the recalculation call; the output region is still marked changed

Returns

PivotTable

Throws

options.sheetName is not a sheet of this workbook

Throws

a pivot table named options.name already exists on that sheet

Throws

the source does not resolve to a worksheet range

Throws

two source headers are equal (case-insensitive)

Throws

the output would overlap populated cells or another pivot table

Throws

the anchor does not parse or includes a sheet prefix, or a field list names a header the source lacks or puts one header on both axes

delete()

delete(pt, options?): void;

Remove a registered pivot table, clearing its cells (values, styles, number formats) and dropping a cache no other pivot table references.

Parameters

pt

PivotTable

options?

skipRecalc?

boolean

skip the recalculation call; the vacated region is still marked changed

Returns

void

Throws

pt is not registered

findAt()

findAt(
   sheetName,
   row,
   col
): PivotTable | null;

Find the pivot table whose protected footprint (PivotTable.getProtectedBounds) contains the given cell, or null if no pivot table covers that cell.

Parameters

sheetName

string

row

number

col

number

Returns

PivotTable | null

findIntersecting()

findIntersecting(
   sheetName,
   top,
   left,
   bottom,
   right
): PivotTable | null;

Find the first pivot table on sheetName whose protected footprint intersects the inclusive cell range [top..bottom] x [left..right], or null if none. Used by GETPIVOTDATA, whose pivot_table argument may be a multi-cell range that overlaps but does not anchor at the pivot’s top-left (Microsoft documents the arg as “a reference to any cell, range of cells, or named range of cells in a PivotTable”).

Iteration order matches findAt’s (insertion order within sheet), so the first registered pivot wins on ambiguous overlaps.

Parameters

sheetName

string

top

number

left

number

bottom

number

right

number

Returns

PivotTable | null

get()

get(name, sheetName?): PivotTable | null;

Look up a pivot table by name. Excel allows same-name pivot tables on different sheets, so callers that can name a sheet should pass it to disambiguate; without a sheet, the first match in insertion order wins.

Parameters

name

string

sheetName?

string

Returns

PivotTable | null

getAll()

getAll(): PivotTable[];

All pivot tables in the workbook, in insertion order, as a snapshot copy: later additions and removals do not affect the returned array. Iterate the manager itself for the live sequence.

Returns

PivotTable[]

refreshCache()

refreshCache(cacheIndex, options?): boolean;

Refresh a pivot cache from its worksheet source. Reads the source range, reconciles field changes into the cache and every pivot table that references it, rebuilds the cache’s records, writes each affected pivot table’s updated output cells onto its sheet, and triggers recalculation (unless skipRecalc is set) so dependents of the changed pivot output pick up new values.

Field-level changes in source headers are reconciled against the cache (verified empirically in excel-test/pivot-refresh-semantics/):

REORDERED columns: remap by name; pivot keeps the same logical fields.
RENAMED or DELETED (header cleared): the cache field is removed and every PT reference (data, row/col, page) is silently dropped; the column comes back as a new field, named by its new header or, when the header was cleared, Field<n> by its position (see sourceHeaderNames). So after a delete of the whole source range (which keeps the ref), the fields become Field1..Field<k> and the pivot table loses every field reference, except to a field already named Field<n> for its column.
DUPLICATE column names (case-insensitive): throw PivotDuplicateSourceHeadersError by default. Stricter than Excel, which silently picks the first match by position and produces wrong values; the throw preempts that corruption. (Excel throws “PivotTable field name is not valid” at pivot creation time.) Callers that need Excel’s silent-mangle for bug-for-bug compatibility can opt in with allowDuplicateHeaders: 'firstMatch'.

Derived fields — calculated (formula) and grouped (fieldGroup) — are preserved across a refresh, and reconciled against the new columns when the source structure changes (reorder / rename / add / delete): see _computeDerivedFieldPreservingRemap. Re-materializing throws PivotOutputOverlapsDataError if the refresh would grow a pivot’s output footprint into populated user cells (mirroring Excel’s “this action will overlap…” dialog).

On any change — rebuilt records OR a field-only remap (rename / reorder / drop / add of a source column, which changes captions and dependent results while records stay identical) — marks each affected PT’s pre- and post-refresh output ranges as changed in RecalcState so GETPIVOTDATA formulas (whose dependence on individual pivot output cells is not expressed in the dep graph) get triggered through normal range-overlap propagation, and recalculates. The range marks happen even with skipRecalc: true so a later caller-driven recalculate() picks up the same fanout.

Parameters

cacheIndex

number

the 0-based index of the pivot cache to refresh

options?

allowDuplicateHeaders?

"firstMatch"

if 'firstMatch', mirror Excel’s silent-accept behaviour: duplicate (case-insensitive) headers resolve by first-occurrence position, producing values from the wrong column when an existing field’s name matches multiple new headers. Default is to throw, which is stricter than Excel but preempts the corruption.

skipRecalc?

boolean

if true, skip the post-refresh recalculation call (the range marks are still recorded for a later recalc to pick up)

skipRematerialize?

boolean

if true, return right after the records are rebuilt, skipping the per-PT re-materialize, the GETPIVOTDATA range marks, and the recalc. For the load path (_materializePivotOnLoad), which materializes each pivot itself — once, with the collision check skipped over its own saved output.

Returns

boolean

true when the cache’s records were actually rebuilt from the source, false when the cache doesn’t exist, isn’t a worksheet source, or the freshly-read records (including their date-formatting encoding) matched the existing records cell-for-cell. Note that false does NOT mean “no work happened”: in the records-matched case, header reconciliation may already have permuted or dropped fields (which also recalculates dependents), and (unless skipRematerialize) every PT that references the cache is re-materialized even when nothing changed at all.

Throws

when the new source headers contain duplicates (unless allowDuplicateHeaders is set)

Throws

when re-materializing would overlap populated user cells (unless skipRematerialize is set)

update()

update(
   pt,
   mutate,
   options?
): void;

Edit a registered pivot table in place. mutate runs with the table’s mutators unlocked (rename, setLayoutForm, the field, item and measure handles, …); then the definition is normalized, the output rewritten and the model recalculated. If mutate throws, or the new output does not fit, the definition is restored, the cells are left as they were and the error propagates. While mutate runs, structural edits to the pivot’s sheet (inserting, deleting or moving rows, columns or cells; renaming, copying or removing the sheet) and a refresh of its cache are refused.

Parameters

pt

PivotTable

mutate

(pt) => void

options?

skipRecalc?

boolean

skip the recalculation call; the changed output regions are still marked changed

Returns

void

Throws

a rename took a name another pivot table on the sheet has

Throws

the edited output would overlap populated cells or another pivot table

Throws

pt is not registered, or is already being updated