Sort on count column in pivot table

Hi,



Would like to sort a pivottable on a “count(col2)” column.



See the attached file for my current test code.



I tried with f.AutoSort=true or things that like that but I’m unable to get the result I want (so sorting on count(col2) by descending order.



Thanks in advance.

Hi Patrice,


Thank you for contacting Aspose support.

I am afraid, the current implementation of Aspose.Cells APIs do not support ordering/sorting of the DataField in a PivotTable. In this scenario, the field (Count of Col2) is actually a DataField therefore it cannot be sorted dynamically at the moment. We have logged this incident in our bug tracking system as CELLSNET-44237 for product team’s review. Please spare us little time to schedule the case for detailed analysis and get back with updates in this regard.

@Pat17,

Please try our latest version/fix: Aspose.Cells for .NET v19.12.3:
Aspose.Cells19.12.3 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells19.12.3 For .Net4.0.Zip (5.0 MB)

Your issue should be fixed in it. About this issue, There are some tips for you:

MS-Excel also can’t support sorting the DataField directly. You can get the same result according to sorting PivotField via DataField. So you can use the following code to get the results:
e.g
Sample code:

var wbk = new Workbook();
var wks = wbk.Worksheets[0];
wks.Cells[0, 0].PutValue("Col1");
wks.Cells[0, 1].PutValue("Col2");
for (int row = 1; row <= 10; row++)
{
    wks.Cells[row, 0].PutValue(row / 2 == 0 ? "A" : "B");
    wks.Cells[row, 1].PutValue(5 + row / 2);
}

var index = wks.PivotTables.Add("A1:B11", "I1", "MyTable");
var p = wks.PivotTables[index];
p.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "Col1");
index = p.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "Col2");
var f = p.DataFields[index];
f.Function = ConsolidationFunction.Count;

//p.DataFields[0].IsAutoSort = false;
////Setting the field auto show ascend. 
//p.DataFields[0].IsAscendShow = false;

////Setting the auto show using field(data field). 
//p.DataFields[0].AutoShowField = 0;

p.RowFields[0].IsAutoSort = true;
//Setting the field auto show ascend. 
p.RowFields[0].IsAscendSort = false;

//Setting the auto show using field(data field). 
p.RowFields[0].AutoShowField = 0;
wbk.Save(filePath + "out.xlsx");

Let us know your feedback.

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