Create a Pivot Table in an Excel Worksheet with the Syncfusion .NET Excel Library

Overview

The Syncfusion^® .NET Excel Library (XlsIO) enables you to create, read, and edit Excel documents programmatically without Microsoft Excel or interop dependencies. Using this library, you can create a pivot table in a worksheet using C#.

Watch this video to see how to create a pivot table in an Excel worksheet using the Syncfusion .NET Excel Library:

Watch the video

Create a pivot table in an Excel worksheet

Follow these steps to create a pivot table in an Excel worksheet using the Syncfusion .NET Excel Library.

Step 1: Create a new project

Start by creating a new C# Console Application project.

Step 2: Install the NuGet package

Add the Syncfusion.XlsIO.Net.Core package to your project from NuGet.org.

Step 3: Include namespaces

Add the following namespaces to your Program.cs file:

c#
using System.IO;
using Syncfusion.XlsIO;

Step 4: Add code to create a pivot table in the Excel worksheet

Use the following code in the Program.cs file to create a pivot table in the Excel worksheet.

Run

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/PivotData.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];
	IWorksheet pivotSheet = workbook.Worksheets[1];

	//Create pivot cache with the given data range.
	IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);

	//Create "PivotTable1" with the cache at the specified range.
	IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

	//Add Pivot table fields (Row and Column fields)
	pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
	pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
	pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

	//Add data field.
	IPivotField field = pivotTable.Fields[5];
	pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);

	#region Save
	//Saving the workbook.
	FileStream outputStream = new FileStream(Path.GetFullPath(@"Output/PivotTable.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams.
	outputStream.Dispose();
	inputStream.Dispose();
}