We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Platform: WinForms |
Control: XlsIO |
Published Date: May 29, 2020 |
Last Revised Date: May 29, 2020

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can sort pivot table 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 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.

C#

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.

C#

//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.

C#

//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.

See Also:

Read Excel from Code in C#, VB.NET

Create Excel file in Azure platform

Create Excel file in WPF

Create Excel from DataTable in C#, VB.NET

How to check if a column exists in Excel table using XlsIO?

Create Pivot Table in C#, VB.NET

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering Syncfusion license key in your application to use the components without trial message.

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile