Skip to main content

PivotTableCollection

A range, a table or an address such as “Data!A1:E13”; a bare address is on sheet. Import from @reductoai/xlsx/officejs.

PivotTableCollection properties

PivotTableCollection.add()

An empty pivot table, as Excel’s Insert PivotTable makes before any field is placed. source is a range, a table or an address such as “Data!A1:E13”; destination its top-left cell.

PivotTableCollection.getCount()

PivotTableCollection.getItem()

By name, case-insensitive.

PivotTableCollection.getItemOrNullObject()

PivotTableCollection.refreshAll()

Refreshes every pivot table in the collection, as ExcelPivotTable.refresh.

ExcelPivotTable

Excel.PivotTable, read from its part: fields, layout and the ranges it covers. Import from @reductoai/xlsx/officejs.

ExcelPivotTable properties

ExcelPivotTable.changeDataSource()

Extension (VBA SourceData): Change Data Source. Fields keep their place by name; a table on a shared cache gets a cache of its own.

ExcelPivotTable.clearAllFilters()

Extension (VBA ClearAllFilters): every field shows all its items again.

ExcelPivotTable.clearTable()

Extension (VBA ClearTable): removes every field, filter and format; the table stays.

ExcelPivotTable.delete()

Removes the pivot table and its cells.

ExcelPivotTable.getDataSourceString()

The source as Excel writes it: Sheet1!A1:D13, or the name or table it reads.

ExcelPivotTable.getDataSourceType()

ExcelPivotTable.move()

Extension (VBA Location): Move PivotTable. The table’s first cell goes to destination, lower when its filters need the rows above; cells there are overwritten.

ExcelPivotTable.refresh()

Rebuilds the cells from the source as Excel’s Refresh does. Throws for a layout we cannot rebuild yet (grouping, dates, sorted fields, values shown on rows) or a table that would grow over other cells.

ExcelPivotTable.set()

Sets several properties at once; a navigation member takes a nested batch.

PivotHierarchy

A source field, or one placed on an axis (RowColumnPivotHierarchy, FilterPivotHierarchy). Import from @reductoai/xlsx/officejs.

PivotHierarchy properties

PivotHierarchy.set()

Sets several properties at once; a navigation member takes a nested batch.

RowColumnPivotHierarchy

A field on the row or column axis. Import from @reductoai/xlsx/officejs. Extends PivotHierarchy.

RowColumnPivotHierarchy properties

RowColumnPivotHierarchy.set()

Sets several properties at once; a navigation member takes a nested batch.

RowColumnPivotHierarchy.setToDefault()

DataPivotHierarchy

Excel.DataPivotHierarchy: a value field with its caption, source field and summary. Import from @reductoai/xlsx/officejs.

DataPivotHierarchy properties

DataPivotHierarchy.set()

Sets several properties at once; a navigation member takes a nested batch.

DataPivotHierarchy.setToDefault()

The summary Excel picks for the field, its caption and the General format.

PivotField

Excel.PivotField: sorts and filters the items of a row, column or filter field. Import from @reductoai/xlsx/officejs.

PivotField properties

PivotField.applyFilter()

Manual, label and value filters; a date filter throws.

PivotField.clearAllFilters()

PivotField.clearFilter()

PivotField.getFilters()

The manual, label and value filters; date filters are not read.

PivotField.getGrouping()

Extension: the field’s grouping as Excel’s Grouping window shows it; null for text.

PivotField.group()

Extension (VBA Range.Group): numbers in steps of by from start to end, or dates by periods. A date field gives way to one new field per period, named like “Months (Date)”. With items, those labels become one group (“Group1”) in a new field outside, like “Region2”.

PivotField.isFiltered()

PivotField.set()

Sets several properties at once; a navigation member takes a nested batch.

PivotField.sortByLabels()

PivotField.sortByValues()

Orders the items by valuesHierarchy on the grand total line, or on the line of the other axis whose items pivotItemScope names, outermost first.

PivotField.ungroup()

Extension (VBA Range.Ungroup): on a date’s period field, every period goes and the date comes back. items: only those groups of an item-group field split up.

PivotLayout

Excel.PivotLayout. Import from @reductoai/xlsx/officejs.

PivotLayout properties

PivotLayout.displayBlankLineAfterEachItem()

Ends each row item’s group with an empty line.

PivotLayout.getAxisFieldName()

Extension: the row or column field whose label cell shows, as Excel’s Active Field reads it.

PivotLayout.getColumnLabelRange()

PivotLayout.getDataBodyRange()

PivotLayout.getDataHierarchy()

The value field a body cell shows.

PivotLayout.getFilterAxisRange()

The report filter cells above the table: one row per filter field, caption and value.

PivotLayout.getPivotItems()

The items of the row or column fields that make up the cell’s value; a grand total has none.

PivotLayout.getPivotStyle()

The pivot style’s name, or null for None. Office.js getPivotStyle returns the style object; read it with workbook.pivotTableStyles.getItem(name).

PivotLayout.getRange()

PivotLayout.getRowLabelRange()

PivotLayout.repeatAllItemLabels()

Repeats each row item’s label on every line of its group, in outline and tabular form.

PivotLayout.set()

Sets several properties at once; a navigation member takes a nested batch.

PivotLayout.setAutoSortOnCell()

Sorts as Excel’s ribbon does from cell: a label sorts its field, a value sorts by that column.

PivotLayout.setStyle()

Office.js setStyle: a built-in or custom pivot style by name, or null for None.