This brief tutorial guides on how to create pivot table in Excel using C#. To create pivot table programmatically C# code and detailed description are provided such that at the end you will get an XLSX (or XLS) file having a pivot table in it without using any other third-party tool. It also contains steps that guide adding different fields to different areas in the pivot table.
Steps to Create pivot Table in C#
- Establish the environment to add Aspose.Cells for .NET from the NuGet package manager for creating pivot table
- Create or load an existing workbook having data for the pivot table
- Get access to the target worksheet where the pivot table is to be added
- Create a pivot table and get its instance for further processing
- Configure the new pivot table and add different fields to the column, row, and data area
- Save the resultant workbook having a pivot table in it
After establishing the environment for C# Excel pivot table generation is described here such that we create a new workbook here having the hardcoded data however you can load an existing Excel file also having target data in it. In the next steps, it further describes the process of creation of a pivotTable and then its configuration. In the final steps, different fields are added to different areas in the pivot table like the column, row, and data.
Code to Create pivot Table in Excel using C#
| using System.IO; | |
| using System.Text; | |
| using Aspose.Cells; | |
| namespace CreatePivotTableInExcelUsingCSharp | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) // Main function to set width of cell in CSharp | |
| { | |
| // Instantiate the license to remove trial version watermark in the output Excel file | |
| Aspose.Cells.License licForCells= new Aspose.Cells.License(); | |
| licForCells.SetLicense("Aspose.Cells.lic"); | |
| // Set the hard-coded data. You may use an existing Excel file also if required | |
| byte[] SrcDataByteArray = Encoding.ASCII.GetBytes( | |
| $@"City,Product,Sales | |
| Paris,Cream,2300 | |
| Paris,Lotion,1600 | |
| Tunis,Cream,900 | |
| Tunis,Lotion,1400 | |
| Tunis,Cream,3090 | |
| Tunis,Lotion,6000 | |
| Paris,Cream,4320" ); | |
| // Create a memory stream from the source data | |
| MemoryStream dataStream = new MemoryStream( SrcDataByteArray ); | |
| // Create LoadOptions class object to load the comma-separated data given above | |
| LoadOptions loadOptions = new LoadOptions(LoadFormat.Csv); | |
| // Instantiate a workbook class object having above mentioned data | |
| Workbook wbCSV = new Workbook(dataStream, loadOptions); | |
| // Get access to the first worksheet in the collection | |
| Worksheet targetSheet = wbCSV.Worksheets[0]; | |
| // Get collection of pivot tables in the target worksheet | |
| Aspose.Cells.Pivot.PivotTableCollection pvTablesCollection = targetSheet.PivotTables; | |
| // Get pivot table index after adding a new pivot table by provding source data range and destination cell | |
| int iNewPivotTable = pvTablesCollection.Add("=A1:C8", "F3", "MyPivotTable"); | |
| // Get the instance of newly added pivot table for further processing | |
| Aspose.Cells.Pivot.PivotTable newPivotTable = pvTablesCollection[iNewPivotTable]; | |
| // Hide the grand total for rows in the output Excel file | |
| newPivotTable.RowGrand = false; | |
| // Add the first field to the column area | |
| newPivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 0); | |
| // Add the second field to the row area | |
| newPivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1); | |
| // Add the third field to the data area | |
| newPivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2); | |
| // Saving the output Excel file with pivot table | |
| wbCSV.Save("OutputPivotTable.xlsx"); | |
| System.Console.WriteLine("Done"); | |
| } | |
| } | |
| } |
This sample code demonstrates how to create a pivot table in C# by providing the data range, and destination cell where the pivot table is to be placed along with the pivot table name. The pivotTable class has a function AddFieldToArea() that is used to drag different fields to different areas using pivotFieldType like Column, Row, or Data along with the field number in the selected data range. You can also use another pivotFieldType Page if required.
In this article, we have learned to add Excel pivot table in C#. If you want to learn to export data in a list to Excel, refer to the article on how to export list data to Excel in C#.
