Sort Excel pivot table data by row and column fields in C# using XlsIO
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 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
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 document of Top to Bottom Sorting
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 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
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.
