Skip to footer content
  1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. Create an Pivot Table Excel in C#
USING IRONXL

How to Create an Excel Pivot Table in C#

Jordi Bardia
Jordi Bardia
Published:

Working with an Excel pivot table programmatically is a common requirement in business applications that need to analyze and calculate source data. While Microsoft's Excel Interop has been the traditional method to create a pivot table in an Excel file, modern solutions like IronXL offer significant advantages. This guide details both methods with practical examples to help you create pivot table in Excel using C# Interop or choose a better alternative.

Understanding the Two Approaches

What is Excel Interop?

Excel Interop uses COM (Component Object Model) to control Microsoft Excel directly through C#. It requires Office to be installed on the system and essentially automates Excel as if a user were interacting with the application. Each worksheet, workbook, and cell becomes an object you can manipulate through code.

What is IronXL?

IronXL is a standalone .NET library that can read, edit, and create Excel files without requiring Microsoft Office. It works across Windows, Linux, macOS, and Docker containers, making it ideal for modern deployment scenarios. You can open, save, and export data without the overhead of COM interop.

Setting Up Your Environment

For Excel Interop

Install-Package Microsoft.Office.Interop.Excel

For IronXL

Install-Package IronXL.Excel

Alternatively, use the NuGet Package Manager UI by searching for "IronXL.Excel" and clicking install. You can also install via the .NET CLI with command args or reference it directly from GitHub.

Both libraries are available through NuGet. Note that Excel Interop requires a full Microsoft Office installation, while IronXL operates independently. Before proceeding, ensure your system meets the requirements.

Creating an Excel Pivot Table Programmatically with C# Interop

Here's a complete example showing how to create a pivot table programmatically using the traditional Interop approach:

using Excel = Microsoft.Office.Interop.Excel;
class Program
{
    static void Main(string[] args)
    {
        // Create Excel application instance
        var excelApp = new Excel.Application();
        var workbook = excelApp.Workbooks.Add();
                    var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
            var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
            // Add header row and sample data
            dataSheet.Cells[1, 1] = "Product";
            dataSheet.Cells[1, 2] = "Region";
            dataSheet.Cells[1, 3] = "Sales";
            // ... populate data rows with values
            // Add sample data rows
            dataSheet.Cells[2, 1] = "Laptop";
            dataSheet.Cells[2, 2] = "North";
            dataSheet.Cells[2, 3] = 1200;
            dataSheet.Cells[3, 1] = "Laptop";
            dataSheet.Cells[3, 2] = "South";
            dataSheet.Cells[3, 3] = 1500;
            dataSheet.Cells[4, 1] = "Phone";
            dataSheet.Cells[4, 2] = "North";
            dataSheet.Cells[4, 3] = 800;
            dataSheet.Cells[5, 1] = "Phone";
            dataSheet.Cells[5, 2] = "South";
            dataSheet.Cells[5, 3] = 950;
            dataSheet.Cells[6, 1] = "Tablet";
            dataSheet.Cells[6, 2] = "East";
            dataSheet.Cells[6, 3] = 600;
            dataSheet.Cells[7, 1] = "Tablet";
            dataSheet.Cells[7, 2] = "West";
            dataSheet.Cells[7, 3] = 750;
            dataSheet.Cells[8, 1] = "Monitor";
            dataSheet.Cells[8, 2] = "North";
            dataSheet.Cells[8, 3] = 400;
            dataSheet.Cells[9, 1] = "Monitor";
            dataSheet.Cells[9, 2] = "South";
            dataSheet.Cells[9, 3] = 500;
            dataSheet.Cells[10, 1] = "Keyboard";
            dataSheet.Cells[10, 2] = "East";
            dataSheet.Cells[10, 3] = 300;
            // Create pivot cache from source data range
            Excel.Range dataRange = dataSheet.Range["A1:C10"];
            Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
                Excel.XlPivotTableSourceType.xlDatabase, dataRange);
            // Create PivotTable at specific location
            Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
            Excel.PivotTable pivotTable = pivotTables.Add(
                pivotCache, pivotSheet.Range["A3"], "SalesPivot");
            // Configure pivot table fields - row and column headers
            ((Excel.PivotField)pivotTable.PivotFields("Product")).Orientation =
                Excel.XlPivotFieldOrientation.xlRowField;
            ((Excel.PivotField)pivotTable.PivotFields("Region")).Orientation =
                Excel.XlPivotFieldOrientation.xlColumnField;
            ((Excel.PivotField)pivotTable.PivotFields("Sales")).Orientation =
                Excel.XlPivotFieldOrientation.xlDataField;
            // Configure grand totals and formatting
            pivotTable.RowGrand = true;
            pivotTable.ColumnGrand = true;
            // Save the Excel file
            workbook.SaveAs("pivot_interop.xlsx");
            workbook.Close();
            excelApp.Quit();
            // Critical: Release COM objects to avoid errors
            #if WINDOWS
            Marshal.ReleaseComObject(pivotTable);
            Marshal.ReleaseComObject(pivotSheet);
            Marshal.ReleaseComObject(dataSheet);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);
            #endif
    }
}
using Excel = Microsoft.Office.Interop.Excel;
class Program
{
    static void Main(string[] args)
    {
        // Create Excel application instance
        var excelApp = new Excel.Application();
        var workbook = excelApp.Workbooks.Add();
                    var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
            var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
            // Add header row and sample data
            dataSheet.Cells[1, 1] = "Product";
            dataSheet.Cells[1, 2] = "Region";
            dataSheet.Cells[1, 3] = "Sales";
            // ... populate data rows with values
            // Add sample data rows
            dataSheet.Cells[2, 1] = "Laptop";
            dataSheet.Cells[2, 2] = "North";
            dataSheet.Cells[2, 3] = 1200;
            dataSheet.Cells[3, 1] = "Laptop";
            dataSheet.Cells[3, 2] = "South";
            dataSheet.Cells[3, 3] = 1500;
            dataSheet.Cells[4, 1] = "Phone";
            dataSheet.Cells[4, 2] = "North";
            dataSheet.Cells[4, 3] = 800;
            dataSheet.Cells[5, 1] = "Phone";
            dataSheet.Cells[5, 2] = "South";
            dataSheet.Cells[5, 3] = 950;
            dataSheet.Cells[6, 1] = "Tablet";
            dataSheet.Cells[6, 2] = "East";
            dataSheet.Cells[6, 3] = 600;
            dataSheet.Cells[7, 1] = "Tablet";
            dataSheet.Cells[7, 2] = "West";
            dataSheet.Cells[7, 3] = 750;
            dataSheet.Cells[8, 1] = "Monitor";
            dataSheet.Cells[8, 2] = "North";
            dataSheet.Cells[8, 3] = 400;
            dataSheet.Cells[9, 1] = "Monitor";
            dataSheet.Cells[9, 2] = "South";
            dataSheet.Cells[9, 3] = 500;
            dataSheet.Cells[10, 1] = "Keyboard";
            dataSheet.Cells[10, 2] = "East";
            dataSheet.Cells[10, 3] = 300;
            // Create pivot cache from source data range
            Excel.Range dataRange = dataSheet.Range["A1:C10"];
            Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
                Excel.XlPivotTableSourceType.xlDatabase, dataRange);
            // Create PivotTable at specific location
            Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
            Excel.PivotTable pivotTable = pivotTables.Add(
                pivotCache, pivotSheet.Range["A3"], "SalesPivot");
            // Configure pivot table fields - row and column headers
            ((Excel.PivotField)pivotTable.PivotFields("Product")).Orientation =
                Excel.XlPivotFieldOrientation.xlRowField;
            ((Excel.PivotField)pivotTable.PivotFields("Region")).Orientation =
                Excel.XlPivotFieldOrientation.xlColumnField;
            ((Excel.PivotField)pivotTable.PivotFields("Sales")).Orientation =
                Excel.XlPivotFieldOrientation.xlDataField;
            // Configure grand totals and formatting
            pivotTable.RowGrand = true;
            pivotTable.ColumnGrand = true;
            // Save the Excel file
            workbook.SaveAs("pivot_interop.xlsx");
            workbook.Close();
            excelApp.Quit();
            // Critical: Release COM objects to avoid errors
            #if WINDOWS
            Marshal.ReleaseComObject(pivotTable);
            Marshal.ReleaseComObject(pivotSheet);
            Marshal.ReleaseComObject(dataSheet);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);
            #endif
    }
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

This code creates an Excel application, adds a worksheet with source data including a header row, establishes a pivot cache, builds the PivotTable object, and configures the field orientation. The cleanup section is critical - failure to release COM objects causes memory leaks. Each cell, range, and worksheet must be properly disposed of to avoid runtime errors.

The IronXL Alternative Approach

IronXL takes a different approach by working directly with the Excel file format. Here's how to achieve similar analysis results programmatically:

using IronXL;
using System.Linq;
class Program 
{
    static void Main(string[] args)
    {
        // Create workbook and add worksheet with data
        WorkBook workbook = WorkBook.Create();
        WorkSheet sheet = workbook.CreateWorkSheet("Data");
        // Add header row to define column structure
        sheet["A1"].Value = "Product";
        sheet["B1"].Value = "Region";
        sheet["C1"].Value = "Sales";
        // Add sample data to cells
        sheet["A2"].Value = "Widget";
        sheet["B2"].Value = "North";
        sheet["C2"].Value = 1500;
        // ... continue to add more data rows
        sheet["A3"].Value = "Laptop";
        sheet["B3"].Value = "South";
        sheet["C3"].Value = 1500;
        sheet["A4"].Value = "Phone";
        sheet["B4"].Value = "North";
        sheet["C4"].Value = 800;
        sheet["A5"].Value = "Phone";
        sheet["B5"].Value = "South";
        sheet["C5"].Value = 950;
        sheet["A6"].Value = "Tablet";
        sheet["B6"].Value = "East";
        sheet["C6"].Value = 600;
        sheet["A7"].Value = "Tablet";
        sheet["B7"].Value = "West";
        sheet["C7"].Value = 750;
        sheet["A8"].Value = "Monitor";
        sheet["B8"].Value = "North";
        sheet["C8"].Value = 400;
        sheet["A9"].Value = "Monitor";
        sheet["B9"].Value = "South";
        sheet["C9"].Value = 500;
        sheet["A10"].Value = "Keyboard";
        sheet["B10"].Value = "East";
        sheet["C10"].Value = 300;
        // Create summary analysis worksheet
        var summarySheet = workbook.CreateWorkSheet("Summary");
        // Group and calculate aggregated data
        var data = sheet["A1:C10"].ToDataTable(true);
        var productSummary = data.AsEnumerable()
            .GroupBy(row => row.Field<string>("Product"))
            .Select((group, index) => new {
                Product = group.Key,
                TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
                Count = group.Count(),
                RowIndex = index + 2
            });
        // Write column headers for summary
        summarySheet["A1"].Value = "Product Summary";
        summarySheet["A2"].Value = "Product";
        summarySheet["B2"].Value = "Total Sales";
        summarySheet["C2"].Value = "Count";
        // Export results to cells
        foreach (var item in productSummary)
        {
            summarySheet[$"A{item.RowIndex + 1}"].Value = item.Product;
            summarySheet[$"B{item.RowIndex + 1}"].Value = item.TotalSales;
            summarySheet[$"C{item.RowIndex + 1}"].Value = item.Count;
        }
        // Apply number formatting and style
        summarySheet["B:B"].FormatString = "$#,##0.00";
        // Save the xlsx file
        workbook.SaveAs("analysis_ironxl.xlsx");
    }
}
using IronXL;
using System.Linq;
class Program 
{
    static void Main(string[] args)
    {
        // Create workbook and add worksheet with data
        WorkBook workbook = WorkBook.Create();
        WorkSheet sheet = workbook.CreateWorkSheet("Data");
        // Add header row to define column structure
        sheet["A1"].Value = "Product";
        sheet["B1"].Value = "Region";
        sheet["C1"].Value = "Sales";
        // Add sample data to cells
        sheet["A2"].Value = "Widget";
        sheet["B2"].Value = "North";
        sheet["C2"].Value = 1500;
        // ... continue to add more data rows
        sheet["A3"].Value = "Laptop";
        sheet["B3"].Value = "South";
        sheet["C3"].Value = 1500;
        sheet["A4"].Value = "Phone";
        sheet["B4"].Value = "North";
        sheet["C4"].Value = 800;
        sheet["A5"].Value = "Phone";
        sheet["B5"].Value = "South";
        sheet["C5"].Value = 950;
        sheet["A6"].Value = "Tablet";
        sheet["B6"].Value = "East";
        sheet["C6"].Value = 600;
        sheet["A7"].Value = "Tablet";
        sheet["B7"].Value = "West";
        sheet["C7"].Value = 750;
        sheet["A8"].Value = "Monitor";
        sheet["B8"].Value = "North";
        sheet["C8"].Value = 400;
        sheet["A9"].Value = "Monitor";
        sheet["B9"].Value = "South";
        sheet["C9"].Value = 500;
        sheet["A10"].Value = "Keyboard";
        sheet["B10"].Value = "East";
        sheet["C10"].Value = 300;
        // Create summary analysis worksheet
        var summarySheet = workbook.CreateWorkSheet("Summary");
        // Group and calculate aggregated data
        var data = sheet["A1:C10"].ToDataTable(true);
        var productSummary = data.AsEnumerable()
            .GroupBy(row => row.Field<string>("Product"))
            .Select((group, index) => new {
                Product = group.Key,
                TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
                Count = group.Count(),
                RowIndex = index + 2
            });
        // Write column headers for summary
        summarySheet["A1"].Value = "Product Summary";
        summarySheet["A2"].Value = "Product";
        summarySheet["B2"].Value = "Total Sales";
        summarySheet["C2"].Value = "Count";
        // Export results to cells
        foreach (var item in productSummary)
        {
            summarySheet[$"A{item.RowIndex + 1}"].Value = item.Product;
            summarySheet[$"B{item.RowIndex + 1}"].Value = item.TotalSales;
            summarySheet[$"C{item.RowIndex + 1}"].Value = item.Count;
        }
        // Apply number formatting and style
        summarySheet["B:B"].FormatString = "$#,##0.00";
        // Save the xlsx file
        workbook.SaveAs("analysis_ironxl.xlsx");
    }
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

This IronXL example demonstrates how to create a workbook, add worksheets, populate cells with data, and perform aggregation analysis. The code groups data by product and calculates totals and counts, creating a summary report. No COM objects need management, and the methods are straightforward .NET collections that automatically handle memory.

Output

How to Create an Excel Pivot Table in C#: Figure 6 - IronXL Output

How to Create an Excel Pivot Table in C#: Figure 7 - Summary Output

Key Differences and Considerations

Deployment Requirements

Excel Interop requires:

  • Microsoft Excel installation with a valid license
  • Windows operating system
  • Proper COM permissions and settings

  • Server configuration for Office automation IronXL requires:

  • Only the IronXL library package
  • Works on any platform that supports .NET
  • No Office installation or license needed
  • Simplified deployment process

How to Create an Excel Pivot Table in C#: Figure 8 - Features

Code Quality and Maintenance

Interop involves managing COM objects carefully to avoid memory leaks and errors. Every Excel object created must be explicitly released using the correct methods. IronXL uses standard .NET objects with automatic garbage collection, reducing the risk of resource issues.

Error Handling

With Interop, errors often relate to Excel availability, version differences, or COM failures. IronXL errors are standard .NET exceptions, making debugging more straightforward. You can rely on familiar try-catch patterns without worrying about COM-specific issues.

Best Practices and Recommendations

Choose Excel Interop when:

  • You need exact Excel pivot table features with all formatting options
  • Excel is guaranteed to be available on the system
  • Working only on Windows desktop applications

  • Legacy code requirements Choose IronXL when:

  • Building server applications or web solutions
  • Requiring cross-platform compatibility
  • Needing reliable performance without COM overhead
  • Deploying to containers or cloud environments

Visit IronXL documentation to learn more details about implementation. For questions or support, contact the Iron Software team.

Conclusion

While C# Interop provides direct access to create pivot table functionality in Excel, it comes with deployment limitations and complexity. IronXL offers a modern alternative that simplifies Excel file manipulation while providing the flexibility to run anywhere .NET is supported.

For developers building new applications or modernizing existing solutions, IronXL's approach eliminates COM InterOp overhead while providing powerful data manipulation capabilities. Whether you need to read, edit, or export Excel data, IronXL delivers a cleaner solution.

Get started with IronXL's free trial to experience the difference, or explore tutorials to see more examples. Ready to deploy? View licensing options to choose the right package for your task.

How to Create an Excel Pivot Table in C#: Figure 9 - Licensing

Frequently Asked Questions

What is the advantage of using IronXL over Excel Interop for creating pivot tables?

IronXL offers significant advantages over Excel Interop, including ease of use, better performance, and the ability to create pivot tables without requiring Excel to be installed on the server.

Can I create an Excel pivot table in C# without using Excel Interop?

Yes, you can create an Excel pivot table in C# using IronXL, which provides a modern and efficient alternative to Excel Interop.

Is it necessary to have Microsoft Excel installed to use IronXL?

No, IronXL does not require Microsoft Excel to be installed on your system, making it a flexible solution for creating and managing Excel files.

What are the steps to create a pivot table in Excel using IronXL?

To create a pivot table using IronXL, first, load your Excel file, specify the data range, define your pivot table fields, and then generate the pivot table. IronXL's comprehensive API makes this process straightforward.

Does IronXL support other Excel functionalities besides pivot tables?

Yes, IronXL supports a wide range of Excel functionalities, including reading and writing Excel files, formatting cells, and performing calculations, among others.

How does IronXL handle large datasets when creating pivot tables?

IronXL is designed to efficiently handle large datasets, ensuring quick and reliable creation of pivot tables even with extensive data.

Can IronXL be used in cloud-based applications?

Yes, IronXL can be integrated into cloud-based applications, providing a seamless solution for managing Excel files in the cloud.

What programming languages are supported by IronXL for creating pivot tables?

IronXL primarily supports C#, making it easy to create pivot tables and perform other Excel operations within .NET applications.

Are there any tutorials available for learning how to use IronXL?

Yes, Iron Software provides comprehensive documentation and tutorials on their website to help users learn how to effectively use IronXL.

What are the licensing options available for IronXL?

IronXL offers various licensing options, including free and paid tiers, to accommodate different project needs and scales.

Jordi Bardia
Jordi Bardia
  Chat with engineering team now
Software Engineer
Jordi is most proficient in Python, C# and C++, when he isn’t leveraging his skills at Iron Software; he’s game programming. Sharing responsibilities for product testing, product development and research, Jordi adds immense value to continual product improvement. The varied experience keeps him challenged and engaged, and he ...
Read More

Related Articles

30-day trial → Full product. No limits. No card. Start Free Trial