Create Pivot Table in Excel C#

Pivot tables are a powerful tool in Excel for summarizing and analyzing data. They allow you to quickly transform a large dataset into a more manageable and meaningful format. 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.

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

C# Library to Create Excel Pivot Tables

To create Pivot Tables in Excel files, first, we will install Aspose.Cells for .NET. You can either download its DLL or install it from NuGet.

PM> NuGet\Install-Package Aspose.Cells 

Pivot Tables in Excel

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.

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. For the demonstration, the following sample Excel spreadsheet is used throughout the examples.

Create Pivot Table in Excel in C#

The following code sample shows how to create an Excel Pivot Table in C#.


Create Pivot Table in Excel in ASP.NET

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.


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.


Hide Data in Pivot Table in Excel

Hide Pivot Table Rows in Excel

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

Use Aspose.Cells for Free

You can get a free temporary license to use Aspose.Cells for .NET without any evaluation limitations.

Explore C# .NET Excel Library

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.


Creating pivot tables in Excel sheets using C# and Aspose.Cells for .NET is a powerful way to automate data analysis tasks. This library simplifies the process and allows you to work with Excel files programmatically, making it easier to manage and manipulate data.

By following the steps outlined in this blog post, you can get started with creating pivot tables in your C# applications, making data analysis and reporting more efficient and effective. Furthermore, you can sort or hide data in pivot tables in Excel sheets seamlessly.

See also