We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback


Trusted by the world’s leading companies

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#

Here is an example of how to create a pivot table in Excel Library using 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#.

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);
        }
    }
}



Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion is proud to hold the following industry awards.

Scroll up icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon