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.

todo:image_alt_text

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