Sort Excel pivot table data by row and column fields in C# using XlsIO

6 mins read

Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using this library, you can sort pivot tables based on value fields in C# and VB.NET.

While working with pivot tables in Excel, you can sort the pivot values based on value fields. This article explains how to perform top-to-bottom sorting with row fields and left-to-right sorting with column fields in the pivot table with an example.

Steps to sort pivot table with value field in Excel programmatically:

Step 1: Create a new C# Windows application project with two buttons TopToBottomSort and LeftToRightSort.

Create C# Windows application

Create a new C# Windows application

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Install NuGet Package

Install NuGet package

Step 3: Include the following namespaces in the Form1.cs file.

using Syncfusion.XlsIO;

VB.NET

Imports Syncfusion.XlsIO

Step 4: Add the following code snippet inside the button click of TopToBottomSort to sort pivot values from top to bottom with row fields using C# and VB.NET.

// Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
 
    IWorkbook workbook = application.Workbooks.Open("../../Data/PivotTable.xlsx");
    IWorksheet worksheet = workbook.Worksheets[1];
    IPivotTable pivotTable = worksheet.PivotTables[0];
 
    // Pivot Top to Bottom sorting of values in 4th column of the pivot table, (i.e.) 3rd data column
    IPivotField rowField = pivotTable.RowFields[1];
    rowField.AutoSort(PivotFieldSortType.Ascending, 3);
 
    string fileName = "TopToBottomSort.xlsx";
    workbook.SaveAs(fileName);
}

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
 
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2016
 
    Dim workbook As IWorkbook = application.Workbooks.Open("../../Data/PivotTable.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
 
    'Pivot Top to Bottom sorting of values in 4th column of the pivot table, (i.e.) 3rd data column
    Dim rowField As IPivotField = pivotTable.RowFields(1)
    rowField.AutoSort(PivotFieldSortType.Ascending, 3)
 
    Dim fileName As String = "TopToBottomSort.xlsx"
    workbook.SaveAs(fileName)
 
End Using

The following code snippet can be added inside the button click of LeftToRightSort to sort pivot values from left to right with column fields using C# and VB.NET.

// Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
 
    IWorkbook workbook = application.Workbooks.Open("../../Data/PivotTable.xlsx");
    IWorksheet worksheet = workbook.Worksheets[1];
    IPivotTable pivotTable = worksheet.PivotTables[0];
 
    //Pivot Left to Right sorting of values in 14th row of the pivot table, (i.e.) 12th data row
    IPivotField colField = pivotTable.ColumnFields[0];
    colField.AutoSort(PivotFieldSortType.Ascending, 12);
 
    string fileName = "LeftToRightSort.xlsx";
    workbook.SaveAs(fileName);
}

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
 
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2016
 
    Dim workbook As IWorkbook = application.Workbooks.Open("../../Data/PivotTable.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
 
    'Pivot Left to Right sorting of values in 14th row of the pivot table, (i.e.) 12th data row
    Dim colField As IPivotField = pivotTable.ColumnFields(0)
    colField.AutoSort(PivotFieldSortType.Ascending, 12)
 
    Dim fileName As String = "LeftToRightSort.xlsx"
    workbook.SaveAs(fileName)
 
End Using

A complete working example to sort pivot table with value fields in Excel along with the input file containing a sample pivot table can be downloaded from Sort pivot table with Value field.

By executing the program, you will get the output Excel files as shown below.

Output Excel file of top to bottom pivot sorting with value field

Output Excel document of Top-to-Bottom Sorting

Output Excel file of left to right pivot sorting with value field

Output Excel document of Left-to-Right Sorting

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features.

An online sample link to generate Excel file.

Sort Excel pivot table data by row and column fields in C# using XlsIO

Steps to sort pivot table with value field in Excel programmatically:

See Also:

Access denied