How to Refresh Nested PivotTable?

Aspose.Cells Product Family
1

I have read the Refresh and Calculate Pivot Table having Calculated Items at first, it is a great job!
But now I have come across with the problem of Refresh and Calculate Nested Pivot Table.

There is an Excel File attached: Nested PivotTable.zip (11.1 KB)
image.png (6.3 KB)

The Excel File including a Data Source, a Pivot Table and a Nested Pivot Table(Its data source is the previous pivot table).
And my code just like this:

var book = new Workbook(@“Data\Nested PivotTable.xlsx”);
var sheet = book.Worksheets[“Sheet1”];
sheet.Cells[“C8”].PutValue(28);
sheet.CalculateFormula(true, true, null);
foreach (PivotTable pt in sheet.PivotTables)
{
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
}
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
imgOptions.OnePagePerSheet = true;
imgOptions.Quality = 100;
imgOptions.PrintingPage = PrintingPageType.IgnoreBlank;
SheetRender sr = new SheetRender(sheet, imgOptions);
var result = sr.ToImage(0);

The result is:
image.png (2.3 KB)
In which The Nested PivotTable hasn’t been updated(Total Age should be 79).

So, How to update/refresh Nested PivotTable?

3

@stephseven,

Thanks for the template file, sample code and screenshots.

I have evaluated your template file and found your nested Pivot Table “数据透视表4” is at 0 indexed position, so when your foreach loop is processed, it tries to refresh and calculate second (nested Pivot Table) first which is dependent on first one. Since the first PivotTable is not refreshed/calculated yet, so the nested PivotTable is not refreshed/calculated fine. Please change your following lines of code:
i.e.,
foreach (PivotTable pt in sheet.PivotTables)
{
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
}

with:
for (int i = sheet.PivotTables.Count - 1; i >= 0; i–)
{
PivotTable pt = sheet.PivotTables[i];
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
}

It would work fine as I tested.

Let us know if you still have any issue.

4

Thanks for ur reply!

I’m so glad that you had read through my question and provided the solution. But can I believe that the index positions are representing the dependency of pivot table? If not, How can I bulid the dependency tree for all pivot table?

for the previous example, the dependency tree may looks like:
Nested PivotTable -> PivotTable -> DataSource
with this tree, I can decide which table should be refresh and calculate first, then one by one.

further more:
image.jpg (5.4 KB)
refresh sequence can be A B C D E F

5

@stephseven

We will look into your issue further and find if it is feasible for us to implement it. We have logged this requirement in our database. Once, there is some news for you, we will let you know asap.

  • CELLSNET-45740 - Find the Children Pivot Tables of Parent Pivot Table
6

@stephseven,

This is to inform you that we have fixed your issue “CELLSNET-45740” now. We will soon provide the fix after performing QA and including other enhancements and fixes.

7

@stephseven,

Please try our latest version/fix: Aspose.Cells for .NET v17.12.4 (.NET 4.0)

Your issue should be fixed in it.

We have added PivotTable.GetChildren() method which gets the children of Pivot Tables which you may use to set PivotTable data as data source.

The sample code in C# is as follows:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "Nested PivotTable.xlsx");
PivotTable[] tables = wb.Worksheets[0].PivotTables[0].GetChildren();

Let us know your feedback.

8

The issues you have found earlier (filed as CELLSNET-45740) have been fixed in this Aspose.Cells for .NET 18.1 update.

Please also check the following article: