Find and Refresh the Nested or Children Pivot Tables of Parent Pivot Table
Contents
[
Hide
]
Possible Usage Scenarios
Sometimes, one pivot table uses another pivot table as a data source, so it is called a child pivot table or nested pivot table. You can find the children pivot tables of a parent pivot table using the PivotTable.children method.
How to Find and Refresh the Nested or Children Pivot Tables of Parent Pivot Table
The following sample code loads the sample Excel file that contains three pivot tables. The bottom two pivot tables are the children of the above pivot table as shown in this screenshot. The code finds the children pivot table using the PivotTable.children method and then refreshes them one by one.
Sample Code
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Refresh Nested/Children Pivot Tables Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet
const ws = workbook.worksheets.get(0);
// Access third pivot table (index 2)
const ptParent = ws.pivotTables.get(2);
// Access the children of the parent pivot table
const ptChildren = ptParent.children;
// Refresh all the children pivot table
for (let pivot of ptChildren) {
pivot.refreshData();
pivot.calculateData();
}
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.sampleFindAndRefreshNestedOrChildrenPivotTables.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Pivot tables refreshed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>