Manipulate Pivot Table
Possible Usage Scenarios
Besides creating new pivot tables, you can manipulate the new and existing pivot tables. You can change the data in the source range of pivot table and then refresh and calculate it and attain the new values of pivot table cells. Please use PivotTable.RefreshData() and PivotTable.CalculateData() methods after you have changed the values in the source range of the pivot table to refresh the pivot table.
Manipulate Pivot Table
The following sample code loads the sample excel file and accesses the existing pivot table insides its first worksheet. It changes the value of cell B3 which is inside the source range of pivot table and then refreshes the pivot table. Before it refreshes the pivot table, it accesses the value of pivot table cell H8 which is 15 and after refreshing the pivot table, its value changes to 6. Please see the output excel file generated with this code and the screenshot showing the effect of sample code on the sample excel file. Please also see the console output below which shows the value of the pivot table cell H8 before and after refreshing the pivot table.
Sample Code
Aspose::Cells::Startup(); | |
//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C | |
//Source directory path | |
U16String dirPath(u"..\\Data\\PivotTables\\"); | |
//Output directory path | |
U16String outPath(u"..\\Data\\Output\\"); | |
//Path of input excel file | |
U16String sampleManipulatePivotTable = dirPath + u"sampleManipulatePivotTable.xlsx"; | |
//Path of output excel file | |
U16String outputManipulatePivotTable = outPath + u"outputManipulatePivotTable.xlsx"; | |
//Load the sample excel file | |
Workbook wb(sampleManipulatePivotTable); | |
//Access first worksheet | |
Worksheet ws = wb.GetWorksheets().Get(0); | |
//Change value of cell B3 which is inside the source data of pivot table | |
ws.GetCells().Get(u"B3").PutValue(u"Cup"); | |
//Get the value of cell H8 before refreshing pivot table | |
U16String val = ws.GetCells().Get(u"H8").GetStringValue(); | |
std::cout << "Before refreshing Pivot Table value of cell H8: " << val.ToUtf8() << std::endl; | |
//Access pivot table, refresh and calculate it | |
PivotTable pt = ws.GetPivotTables().Get(0); | |
pt.RefreshData(); | |
pt.CalculateData(); | |
//Get the value of cell H8 after refreshing pivot table | |
val = ws.GetCells().Get(u"H8").GetStringValue(); | |
std::cout << "After refreshing Pivot Table value of cell H8: " << val.ToUtf8() << std::endl; | |
//Save the output excel file | |
wb.Save(outputManipulatePivotTable); | |
Aspose::Cells::Cleanup(); |
Console Output
Below is the console output of the above sample code when executed with the provided sample excel file.
Before refreshing Pivot Table value of cell H8: 15
After refreshing Pivot Table value of cell H8: 6