Free Support Forum - aspose.com

Sort pivot table data in ascending order alphabetically in Excel worksheet using C#.NET

Dear Support,
I want to set the rows of pivot table alphabetically. In excel there is an option of “Sort A to Z” for pivot table. How can i do this with aspose.cells?

Hi,


Please see the following sample code snippet to sort a row field (Pivot field) in ascending order.

Sample code:

//…

//Accessing the row fields.

PivotFieldCollection pivotFields = pivotTable.RowFields;

//Accessing the first row field in the row fields.

PivotField pivotField = pivotFields[0];

//Setting Subtotals.

pivotField.SetSubtotals(PivotFieldSubtotalType.Max, true);

//Setting the field auto sort.

pivotField.IsAutoSort = true;

//Setting the field sorting in ascending order.

**pivotField.IsAscendSort = true;**

//Setting the field auto sort using the field itself.

pivotField.AutoSortField = -1;

//Setting autoShow options.

//Setting the field auto show.

pivotField.IsAutoShow = true;

//Setting the field auto show ascend.

pivotField.IsAscendShow = false;

//Setting the auto show using field(data field).

pivotField.AutoShowField = 0;

//Saving the Excel file

workbook.Save(“e:\test\outputpvt_table3.xls”);

Thank you.

Thank you Amjad!
I wrote only one line ( pivotField.IsAutoSort = true;) and it seems to work perfectly. I have customer names in the pivot row field and date as column and value as margin. So after writing this line the customer are shown in alphabetic order (A-Z).
Just wanted to be sure as should i write these below lines too with the above line?
//Setting the field sorting in ascending order.
pivotField.IsAscendSort = true;
//Setting the field auto sort using the field itself.
pivotField.AutoSortField = -1;

Best regards,
Radha

Hi,


Yes, I think your code snippet is correct.

Thank you.