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:
- Installation - C# .NET library to create Excel Pivot Tables
- Pivot Tables in Excel
- Create a Pivot Table in Excel in C#
- Sort Excel Pivot Table by row field values
- Sort Excel Pivot Table by column field values
- Hide Excel Pivot Table rows
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.
The following code sample shows how to create an Excel Pivot Table in C#.
Output
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
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 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.
Conclusion
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.