Interface IField

A field of a pivot table: a field of its cache, as the table uses it.

Remarks

Changing what a field shows - collapsing items, hiding them, choosing a page item - changes the table's definition and lays the table out again from its cache, as one step that can be undone. The source is not read again. A table that grows over cells that hold anything shows #SPILL!, as a refresh does.

A field is a view of the table: it reads the table as it is now.

interface IField {
    isIPivotField: true;
    applyFilter(
        filters: FilterInput,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    clearAllFilters(options?: ITransaction.OperationOptions): Promise<void>;
    clearFilter(
        type: FilterType,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    getAxis(): IPivotTable.Axis | null;
    getBlankRowAfter(): boolean;
    getCurrentPage(): IItem | null;
    getDataType(): DataType;
    getFilters(): Filters;
    getForm(): "compact" | PivotForm;
    getItem(name: string): IItem | null;
    getItems(): readonly IItem[];
    getName(): string;
    getNumberFormat(): string;
    getPivotTable(): IPivotTable;
    getPosition(): number | null;
    getRepeatLabels(): boolean;
    getShowAllItems(): boolean;
    getSort(): IPivotTable.IField.Sort | null;
    getSubtotals(): readonly Subtotal[];
    getSubtotalsOnTop(): boolean;
    isFiltered(type?: FilterType): boolean;
    setAxis(
        axis: IPivotTable.Axis | null,
        position?: number,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setBlankRowAfter(
        blank: boolean,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setCurrentPage(
        name: string | null,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setExpanded(
        expanded: boolean,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setForm(
        form: "compact" | PivotForm,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setName(
        name: string,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setNumberFormat(
        format: string | number,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setPosition(
        position: number,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setRepeatLabels(
        repeat: boolean,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setShowAllItems(
        showAllItems: boolean,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setSubtotals(
        subtotals: readonly Subtotal[],
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setSubtotalsOnTop(
        top: boolean,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    setVisibleItems(
        names: readonly string[] | null,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    sortByLabels(
        order: "ascending" | "descending",
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
    sortByValues(
        order: "ascending" | "descending",
        dataField: string,
        scope?: readonly string[] | null,
        options?: ITransaction.OperationOptions,
    ): Promise<void>;
}

Properties

`Readonly`isIPivotField

isIPivotField: true

For runtime type checking.

Methods

applyFilter

applyFilter(
filters: FilterInput,
options?: ITransaction.OperationOptions,
): Promise<void>
Sets the field's filters of the kinds given and leaves its others, as one step that can be undone.
Parameters
- filters: FilterInput
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>
Remarks
A field takes one label-or-date filter and one value-or-top filter, as Excel's does; one given replaces the field's filter of its kind. Items hidden by hand stand beside them.
- label tests each item's caption as its cell shows it; given no selector, it takes the Display selector.
- date tests each item's date.
- value tests each item's total of a data field within each item of the fields outside it; a top N ranks the items under one outer item together.
- manual names the items shown, every item for null; on a page field, its selection.
A condition no Excel pivot filter type spells is kept and laid out, and is left out of an xlsx file with a warning.
Example
```
const region = table.getField('Region')!;
await region.applyFilter({ label: { selector: 'display', operator: 'textStartsWith', param: 'N' } });
await region.applyFilter({ value: { condition: { operator: 'greaterThan', param: 100 }, dataField: 'Sum of Sales' } });
```
Throws
ExpectedError When a label, date or value filter is given for a field that is not a row or column field, both a label and a date filter are given, a top N names no data field, a data field or item is not the table's, or no item would show.

clearAllFilters

clearAllFilters(options?: ITransaction.OperationOptions): Promise<void>
Clears every filter of the field and shows every item, as one step that can be undone; a page field shows all its items again.
Parameters
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

clearFilter

clearFilter(
type: FilterType,
options?: ITransaction.OperationOptions,
): Promise<void>
Clears the field's filter of type, as one step that can be undone; manual shows every item.
Parameters
- type: FilterType
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

getAxis

getAxis(): IPivotTable.Axis | null

Returns the axis the field is on, or null when it is on none.

Returns IPivotTable.Axis | null

getBlankRowAfter

getBlankRowAfter(): boolean

Returns whether the field leaves a blank line after each of its items.

Returns boolean

getCurrentPage

getCurrentPage(): IItem | null

Returns the item a page field shows, or null when it shows every item or more than one.

Returns IItem | null

getDataType

getDataType(): DataType

Returns what the field's items are: numbers, dates, text (logicals and errors among it), a mix of those, or blank when it holds nothing. A calculated field is numbers.

Returns DataType

Remarks
The rule IPivotTable.addDataField follows for a value's function: number sums, any other counts. A field list places a field by it, numbers in the values and the rest on the rows.

getFilters

getFilters(): Filters

Returns the field's filters: its label or date filter, its value or top filter, and the items it shows when some are hidden by hand. Empty when the field is not filtered.

Returns Filters

getForm

getForm(): "compact" | PivotForm

Returns how the field's items are laid out.

Returns "compact" | PivotForm

getItem

getItem(name: string): IItem | null
Returns the item with a caption, matched without regard to case, or null when there is none.
Parameters
- name: string
Returns IItem | null

Remarks
A renamed item is found by its new caption only, as in Excel.

getItems

getItems(): readonly IItem[]

Returns the field's items in the table's order; empty for a field the table lists none for.

Returns readonly IItem[]

getName

getName(): string

Returns the field's caption: its own name in the table, else its cache field's.

Returns string

getNumberFormat

getNumberFormat(): string

Returns the format code of the field's item labels: its own, else its source column's, else General; empty for a field holding anything but numbers and dates, which takes none.

Returns string

getPivotTable

getPivotTable(): IPivotTable

Returns the table the field belongs to.

Returns IPivotTable

getPosition

getPosition(): number | null

Returns the field's place on its axis, counted from 1, the values counting where they sit among the fields; null off the table.

Returns number | null

getRepeatLabels

getRepeatLabels(): boolean

Returns whether the field repeats its item's label on each line the item spans.

Returns boolean

getShowAllItems

getShowAllItems(): boolean

Returns whether the field shows every item, those with no data included.

Returns boolean

getSort

getSort(): IPivotTable.IField.Sort | null

Returns how the field's items are sorted, or null when they are in the order listed.

Returns IPivotTable.IField.Sort | null

getSubtotals

getSubtotals(): readonly Subtotal[]

Returns the subtotals the field shows, in the order they show: automatic, or its functions; empty for none.

Returns readonly Subtotal[]

getSubtotalsOnTop

getSubtotalsOnTop(): boolean

Returns whether the field's subtotals show on its items' first line rather than after them.

Returns boolean

isFiltered

isFiltered(type?: FilterType): boolean
Returns whether the field is filtered: by a filter of type, or by any when omitted.
Parameters
- Optionaltype: FilterType
Returns boolean

setAxis

setAxis(
    axis: IPivotTable.Axis | null,
    position?: number,
    options?: ITransaction.OperationOptions,
): Promise<void>
Puts the field on an axis, or takes it off the table for null, as one step that can be undone.
Parameters
- axis: IPivotTable.Axis | null
  Where it goes; null is nothing for a field already off the table.
- Optionalposition: number
  Its place on the axis, counted from 1, the values counting where they sit among the fields; absent, after the last. Absent is nothing for a field already on axis.
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>
Remarks
The table is laid out again from its cache. Where a value is in the way it shows #SPILL!, and a page field with no room above moves the table down, where it stays once the field has gone.

What the field holds goes with it, off the table and back too: hidden items, filters, sort and names. On the page axis it loses its filters, and items hidden there are a multiple selection; off the page it loses its pick. A field off the table filters nothing. Taking it off also drops the formats on its labels and on the values under them, for good; a move between axes keeps them. A sort by one line of the other axis ends when that line's fields are no longer that axis's outermost.

A field stays with its data fields either way: one source can be on an axis and summarized at once. To remove a value, delete its IDataField.
Example
```
await pivot.getField('Year').setAxis('column');
await pivot.getField('Product').setAxis('row', 1);
await pivot.getField('Region').setAxis(null);
```
Throws
ExpectedError For values, for a position not on the axis, and for a calculated field on any axis or a field with calculated items on the page axis.

setBlankRowAfter

setBlankRowAfter(
blank: boolean,
options?: ITransaction.OperationOptions,
): Promise<void>
Sets whether the field leaves a blank line after each of its items, as one step that can be undone.
Parameters
- blank: boolean
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
It shows only on a row field with another row field inside it; a field inside it that also leaves one shares that line.

setCurrentPage

setCurrentPage(
name: string | null,
options?: ITransaction.OperationOptions,
): Promise<void>
Shows one item of a page field, or every item for null, as one step that can be undone.
Parameters
- name: string | null
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
The field selects a single item: any items hidden by setVisibleItems are shown again.

Throws
When the field is not a page field or the name is not one of its items.

setExpanded

setExpanded(
expanded: boolean,
options?: ITransaction.OperationOptions,
): Promise<void>
Shows or hides the details of every item of the field, as one step that can be undone.
Parameters
- expanded: boolean
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Throws
When the field is not a row or column field with another field inside it.

setForm

setForm(
form: "compact" | PivotForm,
options?: ITransaction.OperationOptions,
): Promise<void>
Lays out the field's items in a form, as one step that can be undone.
Parameters
- form: "compact" | PivotForm
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
Fields of different forms share an axis: a compact field shares its label column with the field inside it. IPivotTable.setForm sets every field at once.

Throws
ExpectedError For a form that is none.

setName

setName(name: string, options?: ITransaction.OperationOptions): Promise<void>
Renames the field in the table, as one step that can be undone; its source field's name restores the original.
Parameters
- name: string
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Throws
ExpectedError When another field or data field of the table has the name.

setNumberFormat

setNumberFormat(
format: string | number,
options?: ITransaction.OperationOptions,
): Promise<void>
Sets the number format of the field's item labels, as one step that can be undone.
Parameters
- format: string | number
  A format code, or a built-in format's id.
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
GETPIVOTDATA still finds items by their values, and by the text they show. It does not reach a value that summarizes the field: see IDataField.setNumberFormat.

Throws
ExpectedError For a format that does not parse, and on a field holding anything but numbers and dates.

setPosition

setPosition(
position: number,
options?: ITransaction.OperationOptions,
): Promise<void>
Moves the field along its axis, as one step that can be undone.
Parameters
- position: number
  Its place, counted from 1 as getPosition counts.
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Throws
ExpectedError When the field is off the table or the position is not on its axis.

setRepeatLabels

setRepeatLabels(
repeat: boolean,
options?: ITransaction.OperationOptions,
): Promise<void>
Sets whether the field repeats its item's label on each line the item spans, as one step that can be undone.
Parameters
- repeat: boolean
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
It shows only where the field has a label column or row of its own, and has a field inside it.

setShowAllItems

setShowAllItems(
showAllItems: boolean,
options?: ITransaction.OperationOptions,
): Promise<void>
Shows every item of the field, those with no data included, or only those with data, as one step that can be undone.
Parameters
- showAllItems: boolean
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

setSubtotals

setSubtotals(
subtotals: readonly Subtotal[],
options?: ITransaction.OperationOptions,
): Promise<void>
Sets the subtotals the field shows, as one step that can be undone.
Parameters
- subtotals: readonly Subtotal[]
  Empty for none.
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>
Remarks
The automatic subtotal takes the place of any function asked for with it, and functions show in one order whatever the order asked. One function alone sits where the automatic subtotal does; two or more always show on lines after the items. With none, an item's own line holds no value in compact and outline forms. The setting holds for a field off the table, and shows when it is an outer field of its axis.
Example
```
await pivot.getField('Region').setSubtotals([]);
await pivot.getField('Region').setSubtotals(['average', 'max']);
```
Throws
ExpectedError For a subtotal that is none, on a calculated field, and for functions on a field with calculated items.

setSubtotalsOnTop

setSubtotalsOnTop(
top: boolean,
options?: ITransaction.OperationOptions,
): Promise<void>
Sets whether the field's subtotals show on its items' first line rather than after them, as one step that can be undone.
Parameters
- top: boolean
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
Tabular form always shows them after, as do two or more functions.

setVisibleItems

setVisibleItems(
names: readonly string[] | null,
options?: ITransaction.OperationOptions,
): Promise<void>
Shows only the named items, or every item for null, as one step that can be undone.
Parameters
- names: readonly string[] | null
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
On a page field the items shown are its selection: the field selects more than one item, and its page cell shows the item when one is left, else (Multiple Items).

Throws
When a name is not an item of the field, or no item would show.

sortByLabels

sortByLabels(
order: "ascending" | "descending",
options?: ITransaction.OperationOptions,
): Promise<void>
Sorts the field's items by their labels, as one step that can be undone: numbers, then text without regard to case, logicals, errors, and blanks last; descending is the exact reverse. A sort by value is replaced.
Parameters
- order: "ascending" | "descending"
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Throws
ExpectedError When the field is not a row or column field.

sortByValues

sortByValues(
    order: "ascending" | "descending",
    dataField: string,
    scope?: readonly string[] | null,
    options?: ITransaction.OperationOptions,
): Promise<void>
Sorts the field's items under each outer item by a data field's value, as one step that can be undone: their totals, or their values in one line of the other axis.
Parameters
- order: "ascending" | "descending"
- dataField: string
  The data field, by name.
- Optionalscope: readonly string[] | null
  The line of the other axis to read: its items' names, outermost field first. Fewer names than the axis has fields name a subtotal line (['2023'] over Year and Quarter is the 2023 total). Absent or empty, the grand total.
- Optionaloptions: ITransaction.OperationOptions
Returns Promise<void>

Remarks
An empty value sorts as 0. Hiding an item of the scope ends the sort, as in Excel.

Throws
ExpectedError When the field is not a row or column field, the data field is not the table's, or the scope names no line of the other axis.

Interface IField

Remarks

Index

Properties

Methods

Properties

ReadonlyisIPivotField

Methods

applyFilter

Parameters

Returns Promise<void>

Remarks

Example

Throws

clearAllFilters

Parameters

Returns Promise<void>

clearFilter

Parameters

Returns Promise<void>

getAxis

Returns IPivotTable.Axis | null

getBlankRowAfter

Returns boolean

getCurrentPage

Returns IItem | null

getDataType

Returns DataType

Remarks

getFilters

Returns Filters

getForm

Returns "compact" | PivotForm

getItem

Parameters

Returns IItem | null

Remarks

getItems

Returns readonly IItem[]

getName

Returns string

getNumberFormat

Returns string

getPivotTable

Returns IPivotTable

getPosition

Returns number | null

getRepeatLabels

Returns boolean

getShowAllItems

Returns boolean

getSort

Returns IPivotTable.IField.Sort | null

getSubtotals

Returns readonly Subtotal[]

getSubtotalsOnTop

Returns boolean

isFiltered

Parameters

Returns boolean

setAxis

Parameters

Returns Promise<void>

Remarks

Example

Throws

setBlankRowAfter

Parameters

Returns Promise<void>

Remarks

setCurrentPage

Parameters

Returns Promise<void>

Remarks

Throws

setExpanded

Parameters

Returns Promise<void>

Throws

setForm

`Readonly`isIPivotField