Order and show only top 5 records in Pivot Table

Aspose.Cells Product Family
1

Hi,
I have a pivot table that has dozen of rows, how can I apply Order descending to one of its columns and only show Top 5 rows?

Thank you!

2

@rmacero,

You may use PivotField.IsAscendSort attribute to set to “false” to implement descending order for the pivot field. Moreover, see the documents on how to sort pivot fields data in pivot table report for your complete reference:

3

I tried using what you recommended, but it does not apply any sorting on the column that I want.

image.png (6.0 KB)
This is the sample data I have, and I want to sort the column highlighted in yellow.
This is the code I use to produce that excel file.

    string dataDir = "C:\\AsposeTest\\";
        //Instantiating an Workbook object
        Workbook workbook = new Workbook();
        //Obtaining the reference of the newly added worksheet
        Worksheet ws = workbook.Worksheets[0];
        Aspose.Cells.Cells cells = ws.Cells;

        //Setting the value to the cells
        Aspose.Cells.Cell cell = cells["A1"];
        cell.PutValue("Fruit");
        cell = cells["B1"];
        cell.PutValue("Count");

        cell = cells["A2"];
        cell.PutValue("Apple");
        cell = cells["A3"];
        cell.PutValue("Mango");
        cell = cells["A4"];
        cell.PutValue("Mango");
        cell = cells["A5"];
        cell.PutValue("Apple");
        cell = cells["A6"];
        cell.PutValue("Guava");
        cell = cells["A7"];
        cell.PutValue("Apple");
        cell = cells["A8"];
        cell.PutValue("Banana");
        cell = cells["B2"];
        cell.PutValue(5);
        cell = cells["B3"];
        cell.PutValue(3);
        cell = cells["B4"];
        cell.PutValue(6);
        cell = cells["B5"];
        cell.PutValue(4);
        cell = cells["B6"];
        cell.PutValue(5);
        cell = cells["B7"];
        cell.PutValue(2);
        cell = cells["B8"];
        cell.PutValue(20);
       
        
        //Adding a PivotTable to the worksheet           
        int i = ws.PivotTables.Add("=A1:B8", "D10", "PivotTable1");
        //Accessing the instance of the newly added PivotTable
        PivotTable pivotTable = ws.PivotTables[i];
        pivotTable.AddFieldToArea(PivotFieldType.Row, 0);       

        pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "Count");
        pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
        pivotTable.DataFields[0].DragToColumn = false;
        pivotTable.DataFields[0].DragToRow = true;
        pivotTable.DataFields[0].IsAutoSort = true;
        pivotTable.DataFields[0].IsAscendSort = false;
        pivotTable.DataFields[0].AutoSortField = 0;
          
        workbook.Save(dataDir + "Sorting.xlsx");
4

@rmacero,

You need to sort values in data field with respect to row pivot field. Please try the following updated sample code, it works as expected:
e.g.
Sample code:

            string dataDir = "e:\\test2\\";
            //Instantiating an Workbook object
            Workbook workbook = new Workbook();
            //Obtaining the reference of the newly added worksheet
            Worksheet ws = workbook.Worksheets[0];
            Aspose.Cells.Cells cells = ws.Cells;

            //Setting the value to the cells
            Aspose.Cells.Cell cell = cells["A1"];
            cell.PutValue("Fruit");
            cell = cells["B1"];
            cell.PutValue("Count");

            cell = cells["A2"];
            cell.PutValue("Apple");
            cell = cells["A3"];
            cell.PutValue("Mango");
            cell = cells["A4"];
            cell.PutValue("Mango");
            cell = cells["A5"];
            cell.PutValue("Apple");
            cell = cells["A6"];
            cell.PutValue("Guava");
            cell = cells["A7"];
            cell.PutValue("Apple");
            cell = cells["A8"];
            cell.PutValue("Banana");
            cell = cells["B2"];
            cell.PutValue(5);
            cell = cells["B3"];
            cell.PutValue(3);
            cell = cells["B4"];
            cell.PutValue(6);
            cell = cells["B5"];
            cell.PutValue(4);
            cell = cells["B6"];
            cell.PutValue(5);
            cell = cells["B7"];
            cell.PutValue(2);
            cell = cells["B8"];
            cell.PutValue(20);


            //Adding a PivotTable to the worksheet           
            int i = ws.PivotTables.Add("=A1:B8", "D10", "PivotTable1");
            //Accessing the instance of the newly added PivotTable
            PivotTable pivotTable = ws.PivotTables[i];
            pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "Count");
            pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;
            pivotTable.DataFields[0].DragToColumn = false;
            pivotTable.DataFields[0].DragToRow = true;

            PivotField field = pivotTable.RowFields[0];
            field.IsAutoSort = true;
            field.IsAscendSort = false;
            field.AutoSortField = 0;


            pivotTable.RefreshData();
            pivotTable.CalculateData();

            workbook.Save(dataDir + "Sorting1.xlsx");

Hope, this helps a bit.

5

@rmacero,
You need to add filter on row field in PivotTable. You can use the following code to test it. Please try it.

//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet ws = workbook.Worksheets[0];
Aspose.Cells.Cells cells = ws.Cells;

//Setting the value to the cells
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue("Fruit");
cell = cells["B1"];
cell.PutValue("Count");

cell = cells["A2"];
cell.PutValue("Apple");
cell = cells["A3"];
cell.PutValue("Mango");
cell = cells["A4"];
cell.PutValue("Blackberry");
cell = cells["A5"];
cell.PutValue("Cherry");
cell = cells["A6"];
cell.PutValue("Guava");
cell = cells["A7"];
cell.PutValue("Carambola");
cell = cells["A8"];
cell.PutValue("Banana");
cell = cells["B2"];
cell.PutValue(5);
cell = cells["B3"];
cell.PutValue(3);
cell = cells["B4"];
cell.PutValue(6);
cell = cells["B5"];
cell.PutValue(4);
cell = cells["B6"];
cell.PutValue(5);
cell = cells["B7"];
cell.PutValue(2);
cell = cells["B8"];
cell.PutValue(20);


//Adding a PivotTable to the worksheet           
int i = ws.PivotTables.Add("=A1:B8", "D10", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = ws.PivotTables[i];
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, "Count");
pivotTable.DataFields[0].Function = ConsolidationFunction.Sum;            

PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 0;

//Add top10 filter
int index = pivotTable.PivotFilters.Add(0, PivotFilterType.Count);
PivotFilter filter = pivotTable.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, true, false, 5);


pivotTable.RefreshData();
pivotTable.CalculateData();

workbook.Save("out.xlsx");
6

@Amjad_Sahi, @John.He Thank you for the code sample.

So now I have a chart from that pivot table, and even the value of the hidden rows are still included in that chart.
Is there an easy way not to include the value of the hidden rows in the chart?

7

@rmacero,

Could you please zip and attach your current output Excel file by Aspose.Cells and your expected output Excel file (containing the pivot table and pivot chart intact). Also, share your sample code (runnable) that you are trying using Aspose.Cells APIs for your task. We will check and help you through accordingly.