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>

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.
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 a real refresh, 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. 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, and (unless skipRematerialize) every PT that references the cache has been re-materialized regardless.

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)