Pivot Table | .NET Excel Library

Overview

The Syncfusion .NET Excel Library (XlsIO) supports the creation and manipulation of pivot tables. Use pivot tables to organize, summarize, or calculate data dynamically. These functionalities can be achieved using jsut a few lines of code.

How to create a pivot table in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the Excel document.
Create an IWorksheet instance to access the sheet in Excel Library.
Create a pivot cache with the given data range by using IPivotCache.
Create a pivot table with that cache by using IPivotTable.
Add data fields using IPivotField .
Save the workbook object to a FileStream object.

Here is an example of how to create a pivot table in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    using (FileStream inputStream = new FileStream("PivotData.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
 
        //Create a 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);
 
        //Saving the workbook as stream.
        using (FileStream outputStream = new FileStream("PivotTable.xlsx", FileMode.Create, FileAccess.ReadWrite))
        {
            workbook.SaveAs(outputStream);
        }
    }
}

Pivot table styles

Formatting a pivot table includes modifying its overall format and refining the format of individual pivot cells to enhance the visual presentation. Microsoft Excel provides 85 different styles for pivot tables under light, medium, and dark categories. The Excel Library supports all these built-in pivot tables.

Here is an example of how to format a pivot table in C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using (FileStream inputStream = new FileStream("PivotTable.xlsx", FileMode.Open,     FileAccess.Read))
    { 
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[1];
        IPivotTable pivotTable = worksheet.PivotTables[0];
 
        //Set BuiltInStyle.
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark12;
 
        //Saving the workbook as stream.
        using (FileStream outputStream = new FileStream("PivotTable_Style.xlsx", FileMode.Create, FileAccess.ReadWrite))
        {
            workbook.SaveAs(outputStream);
        }
    }
}