Aspose.Cells to PDF does not generate charts based on successive pivot tables

Aspose.Cells Product Family
1

The chart is based on 4 successive pivot tables (see tabs).
In Excel, it is necessary to click twice on the button “Refresh All” in the tab “Data” of Excel so that all the computations are carried out (apparently excel do not do more than 2 levels of computations at the same execution).

But to my knowledge this is not possible in Aspose …

In attachments, you can find an example of xlsx (in the zip file) and 2 screenshots to compare the desired result in Microsoft Excels and that obtained by Aspose.

ASPOSE.png (18.5 KB)
Sample.zip (287.7 KB)
Excel.png (67.7 KB)

Our version of Aspose.Cells is 18.8.0

2

@gle,

Thanks for the template file and screenshots.

I have evaluated your issue a bit using your template file. I found the pivot tables (in different sheets) are dependent on each other (some pivot tables are parent tables and have child table(s) while at the same time they itself are child to some other pivot table(s)). You need to refresh/calculate Pivot tables in sequence and finally refresh the pivot chart before rendering to PDF file format. See the following sample code that works fine and output PDF file is ok.
e.g
Sample code:

var workbook = new Workbook("e:\\test2\\2018_11_27__test_pivot_SVA_template (2).xlsx");

            //Refresh pivot tables
            workbook.Worksheets["Calculs4"].PivotTables[0].RefreshData();
            workbook.Worksheets["Calculs4"].PivotTables[0].CalculateData();

            //Refresh pivot tables
            workbook.Worksheets["Calculs3"].PivotTables[0].RefreshData();
            workbook.Worksheets["Calculs3"].PivotTables[0].CalculateData();

            //Refresh pivot tables
            workbook.Worksheets["Calculs2"].PivotTables[0].RefreshData();
            workbook.Worksheets["Calculs2"].PivotTables[0].CalculateData();

            //Refresh pivot tables
            workbook.Worksheets["Calculs1"].PivotTables[0].RefreshData();
            workbook.Worksheets["Calculs1"].PivotTables[0].CalculateData();

            workbook.Worksheets["Chart"].Charts[0].RefreshPivotData();

            workbook.Save("e:\\test2\\out1.pdf");

Hope, this helps a bit.

3

Thank you for your quick reply.
Your sample code working well but pivot tables dependency are not alway ordered.

Is there a way to detect pivot tables dependencies to refresh in the right order?

4

@gle,

Well, you may try to use PivotTable.GetChildren method for the purpose which returns an array of child pivot tables.

5

@Amjad_Sahi
GetChildren() seems to be broken.
It work on the worksheet “Calculs4” but return an Exception (“Invalid cell name”) on all other worksheet.

6

@gle,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46472 - PivotTable.GetChildren() raises exception "Invalid Cell Name"
7

@gle,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46472”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

8

@gle,

Please try our latest version/fix: Aspose.Cells for .NET v18.11.6 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells18.11.6 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells18.11.6 For .Net4.0.Zip (4.7 MB)

9

@Amjad_Sahi ,

Thank you for the fix.
I confirm that it corrects my problem.
Can you give me an estimate of the date of the next stable release?

10

@gle,

Good to know that your issue is sorted out by the new fix/version. You may use this fix as an official release on production server. This fix includes all the functionality (full fledged) of official release Aspose.Cells for .NET v18.11 till this fix. We do provide hotfixes in the forums so the users do not have to wait much, the hotfixes are fully tested and pass our QA modules (internally), so it is safe to use the hot fixes. However, if you want to go for official release of the product, well, you can expect the next official version, i.e., Aspose.Cells v18.12 in the third or fourth week of December 2018.

11

The issues you have found earlier (filed as ) have been fixed in Aspose.Cells for .NET v18.12. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi