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()
source is a range, a table or an address such as “Data!A1:E13”; destination its top-left cell.
PivotTableCollection.getCount()
PivotTableCollection.getItem()
PivotTableCollection.getItemOrNullObject()
PivotTableCollection.refreshAll()
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()
SourceData): Change Data Source. Fields keep their place by name; a table on
a shared cache gets a cache of its own.
ExcelPivotTable.clearAllFilters()
ClearAllFilters): every field shows all its items again.
ExcelPivotTable.clearTable()
ClearTable): removes every field, filter and format; the table stays.
ExcelPivotTable.delete()
ExcelPivotTable.getDataSourceString()
Sheet1!A1:D13, or the name or table it reads.
ExcelPivotTable.getDataSourceType()
ExcelPivotTable.move()
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()
ExcelPivotTable.set()
PivotHierarchy
A source field, or one placed on an axis (RowColumnPivotHierarchy, FilterPivotHierarchy).
Import from @reductoai/xlsx/officejs.
PivotHierarchy properties
PivotHierarchy.set()
RowColumnPivotHierarchy
A field on the row or column axis.
Import from @reductoai/xlsx/officejs. Extends PivotHierarchy.
RowColumnPivotHierarchy properties
RowColumnPivotHierarchy.set()
RowColumnPivotHierarchy.setToDefault()
DataPivotHierarchy
Excel.DataPivotHierarchy: a value field with its caption, source field and summary.
Import from @reductoai/xlsx/officejs.
DataPivotHierarchy properties
DataPivotHierarchy.set()
DataPivotHierarchy.setToDefault()
PivotField
Excel.PivotField: sorts and filters the items of a row, column or filter field.
Import from @reductoai/xlsx/officejs.
PivotField properties
PivotField.applyFilter()
PivotField.clearAllFilters()
PivotField.clearFilter()
PivotField.getFilters()
PivotField.getGrouping()
PivotField.group()
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()
PivotField.sortByLabels()
PivotField.sortByValues()
valuesHierarchy on the grand total line, or on the line of the other
axis whose items pivotItemScope names, outermost first.
PivotField.ungroup()
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()
PivotLayout.getAxisFieldName()
cell shows, as Excel’s Active Field reads it.
PivotLayout.getColumnLabelRange()
PivotLayout.getDataBodyRange()
PivotLayout.getDataHierarchy()
PivotLayout.getFilterAxisRange()
PivotLayout.getPivotItems()
PivotLayout.getPivotStyle()
getPivotStyle returns the style object;
read it with workbook.pivotTableStyles.getItem(name).
PivotLayout.getRange()
PivotLayout.getRowLabelRange()
PivotLayout.repeatAllItemLabels()
PivotLayout.set()
PivotLayout.setAutoSortOnCell()
cell: a label sorts its field, a value sorts by that column.
PivotLayout.setStyle()
setStyle: a built-in or custom pivot style by name, or null for None.