Create Pivot Tables in Excel and Sort or Hide Data using C# .NET

C# Library to Create and Sort PivotTable in Excel

In this article, you’ll learn how to create Pivot Tables in Excel and sort or hide the Pivot Table data or items programmatically in C# using Aspose.Cells for .NET – .NET API to create and manipulate Excel spreadsheets without requiring Microsoft Excel.

Things have gone digital and the automated solutions for the generation and manipulation of Excel spreadsheets are widely used. The Pivot Tables in Excel are extensively used to summarize and analyze the data. Whereas sorting the data in the Pivot Tables is very useful for critical inspection of the huge data in Excel spreadsheets. The sorting of data in Pivot Tables can be applied for arranging the items alphabetically (A-Z or Z-A) for text values or from the highest to lowest or lowest to highest values in case of numbers.

Based on the importance of Excel Pivot Tables, this article aims to show you how to:

  • create a Pivot Table in Excel,
  • sort Pivot Table by row field values,
  • sort Pivot Table by column field values,
  • and hide Pivot Table rows.

For the demonstration, the following sample Excel spreadsheet will be used throughout the examples.

Create Pivot Table in Excel in C#

Create a Pivot Table in Excel using C#

Let’s first check out how to create an Excel Pivot Table in C# using Aspose.Cells for .NET. Once we have created the Pivot Table, we’ll hide the rows and sort the data based on its column or the row fields. The following code sample shows how to create an Excel Pivot Table.

Output

Pivot Table in Excel in C#

Sort Pivot Table by Row Field Values in C#

In the previous section, we simply created the Pivot Table using the data in the Excel spreadsheet. Now, we’ll create another Pivot Table and apply the sorting on the data. The following code sample creates and sorts the PivotTable by “SeaFood” row field values.

Output

Sort Pivot Table in Excel in C#

Sort Pivot Table by Column Field Values in C#

You can also sort the data in a Pivot Table by the column field values. The following C# code sample applies the sorting on field values of the “28/07/2000” column.

Output

Sort and Hide Data in Pivot Table in Excel

Hide PivotTable Rows in C#

You can hide the rows in the Excel Pivot Table based on certain conditions you want to apply. The following code sample shows how to hide particular rows in the Pivot Table using C#.

Have a look at the documentation of Aspose.Cells for .NET for more details on how to work with Pivot Tables in Excel using C# in any .NET based application. Reach us at our forum in case of any questions or queries.