In the VBA / C# API for Excel, there is the Range.PivotField property available. Is there an equivalent in the JavaScript API, or more specifically, can I find the PivotField that is bound to the active cell?
I found that there is PivotField in the JS API and these fields are contained in pivot table hierarchies, but the link to range seems to be missing. Alternatively, if there isn't Range.PivotField, could there a "PivotField.Range", that is, could I scan the pivot tables in a worksheet and create a cell-pivot field mapping?
Update
I've managed to map the pivot table areas (rows, columns, body and filters separately) like this:
var ws = context.workbook.worksheets.getItem(event.worksheetId);
return context.sync().then(function () {
ws.load('pivotTables');
})
.then(context.sync)
.then(function () {
ws.pivotTables.items.forEach(function (value, index) {
value.load('layout');
value.load('name');
value.load('rowHierarchies');
value.load('columnHierarchies');
value.load('dataHierarchies');
value.load('filterHierarchies');
//value.context.sync().then(function () {
sheetPivotTables[event.worksheetId][index] = {
Layout: value.layout,
Name: value.name,
RowHierarchies: value.rowHierarchies,
ColumnHierarchies: value.columnHierarchies,
DataHierarchies: value.dataHierarchies,
FilterHierarchies: value.filterHierarchies,
RowRange: null,
ColumnRange: null,
DataRange: null,
FilterRange: null,
Range: null
};
});
})
.then(context.sync)
.then(function () {
sheetPivotTables[event.worksheetId].forEach(function (item, index) {
var rowHierarchyCount = 0;
if (item.RowHierarchies != undefined) {
// empty row hierarchy count = 0!
rowHierarchyCount = item.RowHierarchies.getCount();
}
item.RowHierarchyCount = rowHierarchyCount;
var columnHierarchyCount = 0;
if (item.ColumnHierarchies != undefined) {
// empty column hiearchy count = 1!
columnHierarchyCount = item.ColumnHierarchies.getCount();
}
item.ColumnHierarchyCount = columnHierarchyCount;
var dataHierarchyCount = 0;
if (item.DataHierarchies != undefined) {
// empty data hiearchy count = 0!
dataHierarchyCount = item.DataHierarchies.getCount();
}
item.DataHierarchyCount = dataHierarchyCount;
var filterHierarchyCount = 0;
if (item.FilterHierarchies != undefined) {
// empty filter hiearchy count = 0!
filterHierarchyCount = item.FilterHierarchies.getCount();
}
item.FilterHierarchyCount = filterHierarchyCount;
});
})
.then(context.sync)
.then(function () {
sheetPivotTables[event.worksheetId].forEach(function (item, index) {
item.Range = item.Layout.getRange();
item.Range.load('rowCount');
item.Range.load('columnCount');
item.Range.load('rowIndex');
item.Range.load('columnIndex');
if (item.RowHierarchyCount.m_value > 0) {
// empty row hierarchy count = 0!
item.RowRange = item.Layout.getRowLabelRange();
item.RowRange.load('rowCount');
item.RowRange.load('columnCount');
item.RowRange.load('rowIndex');
item.RowRange.load('columnIndex');
}
if (item.ColumnHierarchyCount.m_value > 1) {
// empty column hiearchy count = 1!
item.ColumnRange = item.Layout.getColumnLabelRange();
item.ColumnRange.load('rowCount');
item.ColumnRange.load('columnCount');
item.ColumnRange.load('rowIndex');
item.ColumnRange.load('columnIndex');
}
if (item.DataHierarchyCount.m_value > 0) {
// empty data hiearchy count = 0!
item.DataRange = item.Layout.getDataBodyRange();
item.DataRange.load('rowCount');
item.DataRange.load('columnCount');
item.DataRange.load('rowIndex');
item.DataRange.load('columnIndex');
}
if (item.FilterHierarchyCount.m_value > 0) {
// empty filter hiearchy count = 0!
item.FilterRange = item.Layout.getFilterAxisRange();
item.FilterRange.load('rowCount');
item.FilterRange.load('columnCount');
item.FilterRange.load('rowIndex');
item.FilterRange.load('columnIndex');
}
});
})
.then(context.sync);
It is then possible to find the selected pivot table and the selected data item in the table body (see getDataHierarchy):
selectedDataItem = selectedPivotTable.layout.getDataHierarchy(selectedRange);
The axes remain the issue. getPivotItems should work here, but it only seems to return a correct empty array when there are no items on the corresponding axis. Otherwise it stops the execution of the event handler at the nearest context.sync() call:
...
selectedPivotItems = selectedPivotTable.layout.getPivotItems("column", selectedRange);
})
.then(context.sync)
...
I reported this and it is an open bug now: https://github.com/OfficeDev/office-js/issues/569
