ReadonlyisFor runtime type checking.
ReadonlyisFor runtime type checking.
Summarizes a field in the table's values, after the last, as one step that can be undone.
The field, or its caption.
Optionalinput: Omit<IPivotTable.IDataField.Input, "field">
Its caption, function and number format; absent, as IPivotTable.IDataField.Input says.
Optionaloptions: ITransaction.OperationOptionsThe field comes off any row, column or page axis it is on, as in Excel, keeping what IPivotTable.IField.setAxis keeps. IPivotTable.IField.setAxis can then put it back, holding both. One source can be summarized any number of times.
With a second value the table gains its values field.
Add Listeners.
Optionaloptions: IListener.OptionsAdds a slicer that filters one of the table's fields, as one step that can be undone.
The field, or its name.
Optionaloptions: ISlicer.AddOptions
Where it goes and how it looks. Sized 192 × 280 pixels and centered in the
view unless bounds says otherwise; placed on the table's sheet unless sheet says otherwise.
The slicer, named for the field's name in the source (Region, then Region 1), unique
in the workbook, and captioned with the field's caption.
Similar to delete but cleans resources and can't be undone.
Deletes the anchored item.
OptionalgetReturns whether the row and column fields leave a blank line after each item, or null when
they differ.
Returns the cells that hold the table's values, its subtotals and grand totals included; null
while the table shows #SPILL!.
Optionaloptions: ICellRange.GetRangeOptionsReturns the fields across the columns, outermost first; the values, where they sit among them, are getValuesField.
Returns the header rows across the top, right of the label columns, from the row of their
caption; null when nothing is on the columns, or the table shows #SPILL!.
Optionaloptions: ICellRange.GetRangeOptionsReturns the value with a caption, matched without regard to case, or null when there is none.
Returns the table's values, in the order they show; empty for a table that is not modeled.
Returns what a crossing no record falls in shows: this text, empty by default for a blank cell,
or null when it shows 0.
Returns what an error value shows as: this text, or null, the default, when it shows the error.
Returns the field with a caption, matched without regard to case, or null when there is none.
Returns the table's fields, one per field of its cache, in the cache's order; empty for a table that is not modeled.
Returns how the table lays out its fields, or null when the row and column fields differ.
Returns the page fields above the table, in the order they are listed.
Returns the cells of the page fields above the table, each a caption and its pick; null when
the table has no page field, or shows #SPILL!.
Optionaloptions: ICellRange.GetRangeOptionsReturns the cells the table is laid out on: its location, without the page fields above it.
Optionaloptions: ICellRange.GetRangeOptionsThe anchor cell alone while the table shows #SPILL!. An insert or delete before the table
moves it, one taking every cell of it deletes it (IAnchored.isDeleted), and one that
would move part of it - a row deleted or inserted inside it or its page fields, a column cut
through it - is refused.
Returns the ranges this object is anchored to, in authored order.
Optionaloptions: ICellRange.GetRangeOptionsnull. An item whose last range was removed by a row/column delete returns an
invalid ranges — ask ICellRanges.isInvalid, the same contract as
ITable.getDataRange.getRange() for it, but data validations, conditional formats and protected ranges are
authored as several, and any single range can become several when a delete splits it.Returns whether the row and column fields repeat their labels, or null when they differ.
Returns the fields down the rows, outermost first; the values, where they sit among them, are getValuesField.
Returns the label columns down the side, from the row of their captions to the last line; null
when nothing is on the rows, or the table shows #SPILL!.
Optionaloptions: ICellRange.GetRangeOptionsReturns the cells the table summarizes, as IPivotTableCollection.add takes them: a table's
or defined name's name, or a sheet-qualified range; null when its cache names none, as one
from another workbook or a database may not.
Returns the table's pivot table style, or null when it names none or one the workbook does not have.
Returns the style the table gives the cell at a sheet position: its pivot table style, and the formats its layout sets, such as a row label set left and indented by its level.
The row on the sheet.
The column on the sheet.
Returns the name of the table's style and which of its parts show.
Returns whether the row and column fields show their subtotals on their items' first line, or
null when they differ.
Returns where the table's values sit among the fields of an axis; null below two values, when
there is nothing for them to be placed among.
Returns the sheet the table is laid out on.
Returns whether the table fits its columns to its cells each time it is laid out.
Returns if the model has been closed
Returns if the model has been deleted
Returns whether the expand/collapse buttons beside the items are hidden.
Returns whether the captions over the row and column fields, and their buttons, are hidden.
Returns whether a field's filters are allowed to combine.
Returns whether the formats set on the table's cells follow their items through a relayout.
If the anchor is unable to be modified.
Returns whether each column shows a grand total: the row of totals at the table's bottom.
Returns whether each row shows a grand total: the column of totals at the table's right.
Returns whether the cells the table summarizes have been edited since its cache last read them.
As in Excel, an edit to the source does not change the table; it shows what the cache held until it is refreshed from the source. Every table on the same cache is stale together.
An edit is a value or formula written or cleared in the source (an undo of one too), a row or column inserted or deleted inside it, or a resize of the table it names. Moving the source whole is not. Neither is a recalculation: a formula in the source that reads cells outside it can change value without the table reading as stale. A source this workbook does not hold, such as another workbook's, never reads as stale.
Moves the table on its sheet, as one step that can be undone.
Optionaloptions: MoveOptionscell takes the table's top-left cell, as IPivotTableCollection.add's location does:
page fields sit above it, a blank row between, and at the top of a sheet the table moves down
to make room for them. The cells it leaves are cleared. The cells it needs must hold no value,
unless IPivotTable.MoveOptions.discardValues clears them; another pivot table or an
array's cells there always refuse it. The new place may overlap the old.
Reads the table's source again and lays the table out from it, as one step that can be undone.
Optionaloptions: RefreshOptionsThe cache is read again from the cells its source names now, and every table on that cache is
laid out again with this one, as in Excel. A field follows its column by the header's text
(without regard to case): a column whose header changed, or that is gone, takes its field out
of every table. Each field keeps its items in their place, with what was hidden, collapsed or
renamed; an item no longer in the source is kept, missing, until it comes back, and a new one
is added after the others (in its place under a sort), hidden when the field hides items.
With source: false the table alone is laid out again from its cache as it is.
The table takes the cells its layout needs: its range grows or shrinks from its top-left
cell, and the cells it gives up are cleared. When a cell it needs holds anything, the table
shows #SPILL! in its top-left cell, as a dynamic array does, and is laid out again as soon
as the cells are clear.
Sets whether the table fits its columns to its cells each time it is laid out, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether every field leaves a blank line after each of its items, those off the table too, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets what a crossing no record falls in shows, as one step that can be undone.
The text; empty for a blank cell; null for 0. Text that reads as a number shows as
that number.
Optionaloptions: ITransaction.OperationOptionsSets what an error value shows as, as one step that can be undone.
The text; empty for a blank cell; null for the error itself.
Optionaloptions: ITransaction.OperationOptionsLays out every field of the table in a form, those off the table too, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether the expand/collapse buttons beside the items are hidden, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsThe items still expand and collapse through IPivotTable.IItem.setExpanded.
Sets whether the captions over the row and column fields, and their buttons, are hidden, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether a field's filters are allowed to combine, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsTurned off, a field holding more than one filter loses all of them, and the table is laid out again if any went; a field with one keeps it. The flag does not limit IPivotTable.IField.applyFilter.
Renames the table, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether the formats set on the table's cells follow their items through a relayout, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether every field repeats its item's label on each line the item spans, those off the table too, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether each column shows a grand total, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSets whether each row shows a grand total, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsSummarizes other cells, as one step that can be undone: the table reads source as a refresh
does and is laid out again.
The cells to summarize, as IPivotTableCollection.add takes them.
Optionaloptions: ITransaction.OperationOptionsFields are matched to the new columns by their header, without regard to case. A field whose
column is gone, or whose header changed, leaves the table; the rest keep their place, hidden
items, filters, captions and formats. Other tables that shared the cache keep the cells they
summarized. When the table grows over a value it shows #SPILL!, as after a refresh. A slicer
on a field the new source does not have is deleted with it.
Sets whether every field shows its subtotals on its items' first line rather than on a line after them, those off the table too, as one step that can be undone.
Optionaloptions: ITransaction.OperationOptionsOptionaltoResourceId: (resource: IResource) => number | nullChanges the table's style or which of its parts show, as one step that can be undone.
Merged with the current options; name: null removes the style and keeps the flags.
Optionaloperation: ITransaction.OperationOptions
A pivot table laid out on a sheet, anchored at the top-left cell of its location.
Remarks
Until it is refreshed, a table is written back as it was read, with the location restated. Where it sits follows structural edits; an edit that would move only part of it, its page fields included, is refused, as Excel refuses it, and one that takes every cell of it deletes it. While something is in the way of its cells, the table shows
#SPILL!at its anchor and covers that cell alone, as in Excel.