Cannot find API to identify and refresh a common cache once if it's shared by multiple PivotTables

Aspose.Cells Product Family
1

How to efficiently refresh multiple pivot tables that are configured to share one cache and use data from a table on a separate worksheet in the same workbook, after I programmatically update data in the table, before saving to PDF.



Currently, I do:

foreach (var ws in workbook.Worksheets) {
foreach (var pt in ws.PivotTables) {
pt.RefreshData();
pt.CalculateDate();
}
foreach (var ch in ws.Charts) {
ch.RefreshPivotData();
ch.Calculate();
}
}

workbook.Save("report.pdf", new PdfSaveOptions {CalculateFormula=true});

If I understand correctly, RefreshData and RefreshPivotData update the cache with current data from the table, and since there is only one share cache, the cache is unnecessary refreshed multiple times.

I didn’t find API to access the cache directly. How can I identify and update the cache only once using Aspose.Cells API?

Thanks

2

Hi,



Well, yes, if multiple PivotTables share one cache, RefreshData method can be called only once but CalculateData must be called multiple times because every PivotTable has different layouts. But if multiple PivotTables contain diverse formats, such as, cell formattings, conditional formatting etc., every PivotTable must call the method named “RefreshData” accordingly. If the PivotTable is in contact with some other objects, such as, slicer, the PivotTable should call the method “RefreshData” too.

Hope, it helps a bit.

Thank you.
3

Thanks for your reply.

Does it mean that in the generic case when specifics of a particular report are unknown, my code above is correct and I cannot reliably optimize it better? (the assumption is that the data source table is large, ~10k rows and ~50 columns).

4

Hi,


Well, yes, your understanding is correct, your code is fine tuned for your scenario.

Thank you.