Refresh Pivot Tables - No Data Update happens

Aspose.Cells Product Family
1

Just wondering if this feature works in the Aspose Library. Trying to refresh two pivot tables after a cell update and it seems that the refresh does not update the calculated values. Will this not work…


public void RefreshPivotTables(string worksheet, string pivotTableName)
{
PivotTable table = _Workbook.Worksheets[worksheet].PivotTables[pivotTableName];
table.RefreshDataFlag = true;
table.RefreshData();
table.CalculateData();
table.RefreshDataFlag = false;
}


…of course the names I am providing are actual names of worksheets. This is effectively doing nothing though. Anyone have any examples of working code or a way to prove to me that the code works and there is something wrong with me?

Stephen
2

Okay…an update. If I perform the operations twice.


  • Set a cell value on the worksheet outside of the pivot table
  • Refresh Pivot data
  • Set the same cell
  • Refresh Pivot data

…then it works. What the heck is going on? Nothing short of that works. Any ideas from that? I always have to have the feature no one uses. (Sigh)

As a final update…I found that if I:

  • Run calculation on entire workbook (WorkbookName.CalculateFormula())
  • Locate the pivot table and refresh data
  • Locate the pivot table and calculate data
…the updated method code…

public void RefreshPivotTables(string worksheet, string pivotTableName)
{
PivotTable table = _Workbook.Worksheets[worksheet].PivotTables[pivotTableName];

_Workbook.CalculateFormula();
table.RefreshData();
table.CalculateData();
}

…this seemed to get me the updated values. Not sure if that is the way to do it, or why this combination is required. Any additional light on the subject would be welcome. Thanks!

Stephen
3

Hi Stephen,

Thanks for sharing us the issue with good description and code snippets and using Aspose.Cells.

Your last approach is the best approach and recommended approach, you must first calculate the formulas then you can refresh your pivot table and call calculate data method. Let us know if you encounter any other issue, we will be glad to look into it and help you asap.

4

Ha! Nothing fails the good old process-of-elimination approach eh? Thanks for the confirmation! Cheers!


Stephen
5

Thanks Stephen. Cheers :slight_smile: