In this article, you’ll learn how to create Pivot Table in Excel in C#. Also, we will cover how to sort or hide Pivot Table data programmatically using Aspose.Cells for .NET - a .NET API to create and manipulate Excel spreadsheets without requiring Microsoft Excel.

Pivot Tables in Excel

Things have gone digital and 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 via C#,
  • 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 in 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

Create Pivot Table in Excel in ASP.NET

C# Create and Sort Excel Pivot Table by Row

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

Create and Sort Pivot Table in Excel

Sort Excel Pivot Table by Column 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

Hide Data in Pivot Table in Excel

Hide Pivot Table Rows in Excel 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.

Conclusion

In this article, you have learned how to create pivot tables in Excel files using C#. Furthermore, you have seen how to sort or hide data in pivot tables in Excel using C#. You can explore more about Aspose.Cells for .NET using documentation.

See also