Pivot table and chart refresh error

Aspose.Cells Product Family
1

I have some sample data like this:

Member Team sum_total
Mckein, Steve Member Team 11
Turner, Bob Member Team 15
Conan, Dectective Member Team 171
Mobsy,Ted Member Team 514
Lee, Kia Member Team 360
Brenner, Tiffany Member Team 616

A pivot table is created from this data like so (see Test.xlsx attached):

Team Member Team


Member Sum of sum_total
Mckein, Steve 11
Turner, Bob 15
Conan, Dectective 171
Lee, Kia 360
Mobsy,Ted 514
Brenner, Tiffany 616

The pivot table is sorted by the "Sum of sum_total" column. I then run the following code to export the workbook to PDF.

Workbook book = new Workbook(@"Test.xlsx");
foreach (Worksheet ws in book.Worksheets)
{
foreach (PivotTable pt in ws.PivotTables)
{
pt.RefreshData();
pt.CalculateData();
}
}
book.Save(@"Test.pdf", SaveFormat.Pdf);

The resulting PDF shows the pivot table as unsorted (when it is sorted in Excel). Can you please have a look at why this happens?

Thanks.
Lawrence.



2

Another problem:

Creating a pivot chart from the pivot table in the previous post, the chart shows the sums in the sorted order (see Test.xlsx attached).

If I then export the workbook to PDF using the following code:

Workbook book = new Workbook(@“Test.xlsx”);
foreach (Worksheet ws in book.Worksheets)
{
int chartchount = ws.Charts.Count;
for (int i = 0; i < chartchount; i++)
{
if (!string.IsNullOrEmpty(ws.Charts[i].PivotSource))
ws.Charts[i].RefreshPivotData();
ws.Charts[i].Calculate();
}
}
book.Save(@“Test.pdf”, SaveFormat.Pdf);

The resulting chart in the PDF looks different from the chart shown in Excel. In particular, the sort order is wrong, and the first data item is missing its label. Help!

3

Hi Lawrence,


Thank you for contacting Aspose support.

We have evaluated your first scenario against the latest version of Aspose.Cells for .NET 8.3.2.3, and we are able to confirm the issue. The Pivot Field’s sorting is changed after rendering the spreadsheet to PDF. We have logged the issue in our bug tracking system under the ticket CELLSNET-43416 for further investigation & correction purposes. Please spare us little time to properly analyze the problem cause, and to provide the fix at earliest possible.

We are currently evaluating your presented scenario, and we will shortly get back to you with updates in this regard.
4
Hi,

llawryy:

Creating a pivot chart from the pivot table in the previous post, the chart shows the sums in the sorted order (see Test.xlsx attached).

If I then export the workbook to PDF using the following code:

Workbook book = new Workbook(@"Test.xlsx");
foreach (Worksheet ws in book.Worksheets)
{
int chartchount = ws.Charts.Count;
for (int i = 0; i < chartchount; i++)
{
if (!string.IsNullOrEmpty(ws.Charts[i].PivotSource))
ws.Charts[i].RefreshPivotData();
ws.Charts[i].Calculate();
}
}
book.Save(@"Test.pdf", SaveFormat.Pdf);

The resulting chart in the PDF looks different from the chart shown in Excel. In particular, the sort order is wrong, and the first data item is missing its label!

Thanks for the sample code and details.

After an initial test, I observed the issue as you mentioned. I found the sort order of PivotChart's series data points is wrong with missing first point label after refreshing the pivot chart and calculating its data in the output PDF file format.
e.g
Sample code:

Workbook book = new Workbook(@"e:\\test2\\Test.xlsx");
foreach (Worksheet ws in book.Worksheets)
{
int chartchount = ws.Charts.Count;
for (int i = 0; i < chartchount; i++)
{
if (!string.IsNullOrEmpty(ws.Charts[i].PivotSource))
ws.Charts[i].RefreshPivotData();
ws.Charts[i].Calculate();
}
}
book.Save(@"e:\\test2\\out1.pdf", SaveFormat.Pdf);

I have logged a ticket with an id "CELLSNET-43417" for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
5

Hi,

Thanks for your using Aspose.Cells.

We have fixed this issue.

  • CELLSNET-43417 - The sort order of PivotChart’s series data points is wrong with in output PDF file format

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

6

Hi,

I’ve just tested Aspose.Cells v8.4.0.3 and the relevant issue seems to be fixed.

However, I’m still waiting on the first issue

CELLSNET-43416

Any idea when it will be resolved? It has been 2 months already since it was lodged.

Cheers.

7

Hi,

Thanks for your posting and using Aspose.Cells.

It is good to know that one of your issue is resolved with the latest fix. Regarding your other issue, we got the following information from product team.

It is not supported that sorting the PivotField according to DataField or other PivotFields. We only support sorting the PivotField by itself, ascending or
descending.If you want to sort the PivotField according to DataField or
other PivotFields,when you call the method “PivotTable.CalculateData()”,
it may generate unexpected result.

8

The issues you have found earlier (filed as CELLSNET-43417) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier
9

@llawryy,

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

10

@llawryy,

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

Your issue “CELLSNET-43416” should be fixed in it.

Let us know your feedback.
Aspose.Cells19.9.1 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.9.1 For .Net4.0.Zip (4.9 MB)

11

The issues you have found earlier (filed as CELLSNET-43416) have been fixed in Aspose.Cells for .NET v19.10. This message was posted using Bugs notification tool by Amjad_Sahi