.NET Aspose: PDF export from Excel Pivot does not respect Sort saved in Pivot (Pivot and sort is created inside excel template)

Aspose.Cells Product Family
1

I am using Aspose to export my reports to Excel and PDF using excel template.

Created a excel template with Pivot table. Saved Custom sort (sort using different column) on one of the column.
When I export my report to Excel, the sort remains as per template but when I export to pdf, the column does not respect the custom sort rather it sorts alphabetically.
No programmatic sorting used. Everything is saved inside the Excel Template.

Let me know if you need any more details.
2

Hi Sandip,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v7.7.2.5 it should fix your issue.

If your problem still occurs, then please provide us your template Excel file which you are converting to PDF. We will look into this issue and update you asap.

3
The above dll didn't solve the issue.
I have attached the excel template which has pivot and raw data.
The symbol column is sorted by SecurityTypeOrder and you could see the correct order of Symbols in Symbol column.
A1
A1 12345
A1 4567
123456
987654

When I use the same template to convert to pdf using Aspose, the symbol column does not respect the above sort rather it gets sorted alphabetically i.e.
123456
987654
A1
A1 4567
A1 12345
4

Hi Sandip,

Thanks for your using Aspose.Cells.

I have tested this issue with the following code using the latest version and it generated the correct pdf.

I have attached the output pdf file for your reference. Kindly provide us your sample code which you are using to convert the file to pdf.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Report_ForAsposePDFIssue.xlsx”;


Workbook workbook = new Workbook(filePath);

workbook.Save(filePath + “.out.pdf”);

5

Hi,

The below code is used to populate the excel template details tab and then pivot gets refreshed with that data on details tab.
I have attached the template used in the below code. The last attachment I added is the result of this code if “isExcel” is true. The issue if with pdf output. i.e. “isExcel” is false.

DataTable reportDetails = new DataTable();
var data = da.GetDailyPositionsReport(input, ref reportDetails, true);

Assembly assembly = Assembly.GetExecutingAssembly();
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);

var fi = assembly.GetManifestResourceStream(“PositionsTemplate.xlsx”);

var wb = new WorkbookDesigner();
wb.Workbook = new Workbook(fi);

wb.SetDataSource(“details”, data.DailyPositionsModelData.ToList());
wb.Process(false);
Cells cells;
Cell cell;
PivotTable pt;
pt = wb.Workbook.Worksheets[“ByIssuer”].PivotTables[0];
pt.HideBlankRows();
pt.RefreshData();
pt.CalculateData();
if (!isExcel)
wb.Workbook.Worksheets[“Details”].IsVisible = false;

wb.Workbook.CalculateFormula();
wb.Workbook.Save(ms, isExcel ? SaveFormat.Xlsx : SaveFormat.Pdf);
6

Hi Sandip,

Thanks for using Aspose.Cells.

This is not the issue with PDF but actually the sorting is not respected after pivot table refresh. The pivot table inside the template (Report_ForAsposePDFIssue.xlsx) is sorted by the Symbol column with respect to SecurityTypeOrder, but when the pivot table is refreshed, this sorting is changed. The Symbol column gets sorted alphabetically.

We have tested this issue with the following code using the latest version. I have also attached the output xlsx file for a reference. We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42479.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Report_ForAsposePDFIssue.xlsx”;


Workbook workbook = new Workbook(filePath);


PivotTable pt = workbook.Worksheets[“ByIssuer”].PivotTables[0];

pt.RefreshData();

pt.CalculateData();


workbook.Save(filePath + “.out.xlsx”);

7

Hi Sandip,

Thanks for your posting and using Aspose.Cells.

Could you provide us some specification about SecurityTypeOrder? It will help us sort out this issue at our end.

8

SecurityTypeOrder is of type integer and has values like 1, 2,3, 4


9

Hi Sandip,

Thanks for providing us more information about SecurityTypeOrder.

We have logged your provided information in our database against this issue. Once, there is some fix or other update for you, we will let you know asap.

10

Hi Sandip,

Thanks for using Aspose.Cells.

SecurityTypeOrder is of type integer and has values like 1, 2,3, 4, but what is the rule?

In the example file, the values are A1, A1 12345 and so on, how to calculate the integer type?

Could you provide us more detail?

11

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan

12

@spatil-1,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.