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

How to Create an Excel Pivot Table in C#?

Jordi Bardia
Jordi Bardia
Updated:

Creating Excel pivot tables in C# works with either Office Interop (requiring Microsoft Office installation) or modern libraries like IronXL that work independently, with IronXL offering superior deployment flexibility and cross-platform support for DevOps environments.

Generating pivot tables programmatically requires either C# Interop with its Office dependencies or modern libraries like IronXL that work independently. This tutorial demonstrates both approaches, highlighting why developers increasingly choose IronXL over traditional Interop methods, especially when deploying to Docker containers or cloud environments like Azure and AWS.

In this article, we'll learn how to edit, create, design and calculate pivot tables and groups with automatic analysis and error handling - all while maintaining deployment simplicity that DevOps engineers require.

What Is an Excel Pivot Table?

A pivot table is one of Excel's most powerful tools. It's an easy way to summarize large data sets, making it invaluable for data analysis in .NET applications. Pivot tables allow you to easily display, understand, and analyze numerical data. They're available not only in Excel but also in other programs such as Google Sheets, Apple Numbers and CSV Exports. They provide a solution for seeing data in overview - acting as a data console to let people see their information in a meaningful way.

For containerized applications, creating pivot tables programmatically eliminates the need for Excel installations in your Docker images, significantly reducing container size and deployment complexity. This approach aligns perfectly with modern CI/CD pipelines and container deployment strategies.

Let's explore the wrong way to make a pivot table, then learn the right way in C#:

How to Create a Pivot Table in Excel Tables Using C# Interop?

C# Excel Interop provides direct access to Excel's pivot table functionality through COM automation. Here's the traditional approach that many developers find when searching for tools to generate pivot tables in C#:

Why Is This Approach Considered Outdated in .NET?

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
// Create Excel application instance
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Data\SalesData.xlsx");
Excel.Worksheet xlSheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Worksheet xlPivotSheet = (Excel.Worksheet)xlWorkbook.Sheets.Add();
// Define data range for pivot table
Excel.Range dataRange = xlSheet.UsedRange;
// Row area and column area 
// Create pivot cache and pivot table
Excel.PivotCache pivotCache = xlWorkbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase,
    dataRange,
    Type.Missing);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(
    xlPivotSheet.Cells[3, 1],
    "SalesPivot",
    Type.Missing,
    Type.Missing); // fields by field
// Configure pivot table fields
Excel.PivotField productField = (Excel.PivotField)pivotTable.PivotFields("Product");
productField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
productField.Position = 1;
Excel.PivotField regionField = (Excel.PivotField)pivotTable.PivotFields("Region");
regionField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
regionField.Position = 1;
Excel.PivotField salesField = (Excel.PivotField)pivotTable.PivotFields("Sales");
pivotTable.AddDataField(salesField, "Sum of Sales", Excel.XlConsolidationFunction.xlSum);
// Save and cleanup
xlWorkbook.SaveAs(@"C:\Data\PivotReport.xlsx");
xlWorkbook.Close();
xlApp.Quit();
// Release COM objects to prevent memory leaks
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(xlPivotSheet);
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
// Create Excel application instance
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Data\SalesData.xlsx");
Excel.Worksheet xlSheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Worksheet xlPivotSheet = (Excel.Worksheet)xlWorkbook.Sheets.Add();
// Define data range for pivot table
Excel.Range dataRange = xlSheet.UsedRange;
// Row area and column area 
// Create pivot cache and pivot table
Excel.PivotCache pivotCache = xlWorkbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase,
    dataRange,
    Type.Missing);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(
    xlPivotSheet.Cells[3, 1],
    "SalesPivot",
    Type.Missing,
    Type.Missing); // fields by field
// Configure pivot table fields
Excel.PivotField productField = (Excel.PivotField)pivotTable.PivotFields("Product");
productField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
productField.Position = 1;
Excel.PivotField regionField = (Excel.PivotField)pivotTable.PivotFields("Region");
regionField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
regionField.Position = 1;
Excel.PivotField salesField = (Excel.PivotField)pivotTable.PivotFields("Sales");
pivotTable.AddDataField(salesField, "Sum of Sales", Excel.XlConsolidationFunction.xlSum);
// Save and cleanup
xlWorkbook.SaveAs(@"C:\Data\PivotReport.xlsx");
xlWorkbook.Close();
xlApp.Quit();
// Release COM objects to prevent memory leaks
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(xlPivotSheet);
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
$vbLabelText   $csharpLabel

This Interop example creates a native Excel pivot table with Product as rows, Region as columns, and Sales summed in the data area. While functional, this approach requires Microsoft Office installation and careful COM object management. Microsoft documentation explains why this approach isn't modern. From a DevOps perspective, this approach is particularly problematic as it cannot be containerized effectively - you cannot install Microsoft Office in a Linux Docker container or deploy to serverless environments.

What Problems Does C# Interop Create?

The Interop approach presents several significant challenges that make it unsuitable for modern DevOps practices and cloud-native deployments.

Unfortunately, Stack Overflow and other programming websites continue to recommend it because they're locked in time with threads from the early 2000s.

Deployment Dependencies: Requires Microsoft Office installation on every machine running the sourcecode, including production servers. This adds licensing costs and deployment complexity.

Memory Management: COM objects must be explicitly released using Marshal.ReleaseComObject(). Missing even one object causes Excel processes to hang in memory, as extensively documented on Stack Overflow. Consider the pivot cache.

Platform Limitations: This solution only works on Windows with Office installed. It can be incredibly slow and lead to memory leaks. No support for Linux, macOS, Docker containers, or cloud platforms like Azure Functions. This severely limits deployment options and prevents the use of modern container orchestration platforms.

Performance Issues: Starting Excel application instances is slow and resource-intensive, especially for server-side processing.

Version Compatibility: Different Office versions may have varying COM interfaces, causing compatibility issues across environments.

How Does IronXL Create a Pivot Table Programmatically Without Interop?

IronXL approaches pivot table creation differently, using managed code without COM dependencies. While it doesn't create native Excel pivot tables, it provides powerful aggregation capabilities perfect for containerized deployments and cloud-native architectures. The library's performance optimizations include a 40x speed increase and memory usage reduction from 19.5 GB to under 1 GB, making it ideal for resource-constrained container environments.

What Makes This Approach Modern for XLSX or XLS Files?

using IronXL;
using System.Linq;
using System.Data; // Keep this namespace
using static System.Data.DataTableExtensions; // Use 'using static' for DataTableExtensions
class Program
{
    static void Main(string[] args)
    {
        // Load Excel file - no Office required
        WorkBook workbook = WorkBook.Load("SalesData.xlsx");
        WorkSheet dataSheet = workbook.WorkSheets[0];
        // Convert to DataTable for powerful manipulation
        var dataTable = dataSheet.ToDataTable(true); // true = use first row as column headers
        // Create pivot-style aggregation using LINQ
        var pivotData = dataTable.AsEnumerable()
            .GroupBy(row => new {
                Product = row["Product"].ToString(),
                Region = row["Region"].ToString()
            }) //range
            .Select(g => new {
                Product = g.Key.Product,
                Region = g.Key.Region,
                TotalSales = g.Sum(row => Convert.ToDecimal(row["Sales"])),
                AverageSale = g.Average(row => Convert.ToDecimal(row["Sales"])),
                Count = g.Count()
            }); 
        // Create pivot report worksheet
        WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");
        // Build cross-tabulation structure
        var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p);
        var regions = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r);
        // Create headers
        pivotSheet["A1"].Value = "Product/Region";
        int col = 2;
        foreach (var region in regions)
        {
            pivotSheet[$"{(char)('A' + col - 1)}1"].Value = region; // string  
            col++;
        }
        // Populate pivot data
        int row = 2;
        foreach (var product in products)
        {
            pivotSheet[$"A{row}"].Value = product;
            col = 2;
            foreach (var region in regions)
            {
                var sales = pivotData
                    .Where(p => p.Product == product && p.Region == region)
                    .Select(p => p.TotalSales)
                    .FirstOrDefault();
                pivotSheet[$"{(char)('A' + col - 1)}{row}"].Value = sales;
                col++;
            }
            row++;
        }
        // Add totals using Excel formulas
        pivotSheet[$"A{row}"].Value = "Total"; // grand totals
        for (int c = 2; c <= regions.Count() + 1; c++)
        {
            pivotSheet[$"{(char)('A' + c - 1)}{row}"].Formula = $"=SUM({(char)('A' + c - 1)}2:{(char)('A' + c - 1)}{row - 1})";
        }
        // Proceeding to apply formatting
        var dataRange = pivotSheet[$"B2:{(char)('A' + regions.Count())}{row}"];
        dataRange.FormatString = "$#,##0.00";
        workbook.SaveAs("PivotReport.xlsx");
    }
}
using IronXL;
using System.Linq;
using System.Data; // Keep this namespace
using static System.Data.DataTableExtensions; // Use 'using static' for DataTableExtensions
class Program
{
    static void Main(string[] args)
    {
        // Load Excel file - no Office required
        WorkBook workbook = WorkBook.Load("SalesData.xlsx");
        WorkSheet dataSheet = workbook.WorkSheets[0];
        // Convert to DataTable for powerful manipulation
        var dataTable = dataSheet.ToDataTable(true); // true = use first row as column headers
        // Create pivot-style aggregation using LINQ
        var pivotData = dataTable.AsEnumerable()
            .GroupBy(row => new {
                Product = row["Product"].ToString(),
                Region = row["Region"].ToString()
            }) //range
            .Select(g => new {
                Product = g.Key.Product,
                Region = g.Key.Region,
                TotalSales = g.Sum(row => Convert.ToDecimal(row["Sales"])),
                AverageSale = g.Average(row => Convert.ToDecimal(row["Sales"])),
                Count = g.Count()
            }); 
        // Create pivot report worksheet
        WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");
        // Build cross-tabulation structure
        var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p);
        var regions = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r);
        // Create headers
        pivotSheet["A1"].Value = "Product/Region";
        int col = 2;
        foreach (var region in regions)
        {
            pivotSheet[$"{(char)('A' + col - 1)}1"].Value = region; // string  
            col++;
        }
        // Populate pivot data
        int row = 2;
        foreach (var product in products)
        {
            pivotSheet[$"A{row}"].Value = product;
            col = 2;
            foreach (var region in regions)
            {
                var sales = pivotData
                    .Where(p => p.Product == product && p.Region == region)
                    .Select(p => p.TotalSales)
                    .FirstOrDefault();
                pivotSheet[$"{(char)('A' + col - 1)}{row}"].Value = sales;
                col++;
            }
            row++;
        }
        // Add totals using Excel formulas
        pivotSheet[$"A{row}"].Value = "Total"; // grand totals
        for (int c = 2; c <= regions.Count() + 1; c++)
        {
            pivotSheet[$"{(char)('A' + c - 1)}{row}"].Formula = $"=SUM({(char)('A' + c - 1)}2:{(char)('A' + c - 1)}{row - 1})";
        }
        // Proceeding to apply formatting
        var dataRange = pivotSheet[$"B2:{(char)('A' + regions.Count())}{row}"];
        dataRange.FormatString = "$#,##0.00";
        workbook.SaveAs("PivotReport.xlsx");
    }
}
$vbLabelText   $csharpLabel

This is how you create pivot tables in a container-friendly way. This approach works seamlessly in Docker containers, Kubernetes pods, and serverless functions without any external dependencies. The entire application can be packaged into a lightweight container image that runs anywhere .NET is supported.

What Does the Pivot Table Output Look Like?

Comparison of original Excel sales data and generated pivot table showing product sales aggregated by region with totals

The output demonstrates how IronXL transforms raw sales data into a structured pivot report without requiring Excel installation, making it perfect for automated reporting in CI/CD pipelines.

How to Create Dynamic Summaries with IronXL Formulas?

For scenarios requiring dynamic updates similar to pivot table refresh functionality, IronXL can leverage Excel's built-in formulas. This approach is preferable - your data is handled in a much more modern and elegant way. The code is easy to understand and set up without needing to contact support or read manuals. This approach is particularly valuable in containerized environments where you need formula-based calculations that update automatically.

How Do Formula-Based Summaries Update Automatically?

// Load the workbook
WorkBook workbook = WorkBook.Load(inputPath);
// Rename the first worksheet so formulas reference correctly
WorkSheet dataSheet = workbook.WorkSheets[0];
dataSheet.Name = "DataSheet";
// Convert worksheet to DataTable
DataTable dataTable = dataSheet.ToDataTable(true);
// Create new summary worksheet
WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");
// Get unique product-region combinations
var uniqueCombos = dataTable.AsEnumerable()
    .Select(row => new {
        Product = row["Product"].ToString(),
        Region = row["Region"].ToString()
    })
    .Distinct()
    .OrderBy(x => x.Product)
    .ThenBy(x => x.Region);
// Add header row
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";
// Populate rows with formulas
int rowIndex = 2;
foreach (var combo in uniqueCombos)
{
    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;
    // Adjust column references if your Sales column is C (not D)
    summarySheet[$"C{rowIndex}"].Formula =
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    summarySheet[$"D{rowIndex}"].Formula =
        $"=COUNTIFS(DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    rowIndex++;
}
// Optional: add total row
summarySheet[$"A{rowIndex}"].Value = "Total";
summarySheet[$"C{rowIndex}"].Formula = $"=SUM(C2:C{rowIndex - 1})";
summarySheet[$"D{rowIndex}"].Formula = $"=SUM(D2:D{rowIndex - 1})";
// Save output file
workbook.SaveAs(outputPath);  //filename
// Load the workbook
WorkBook workbook = WorkBook.Load(inputPath);
// Rename the first worksheet so formulas reference correctly
WorkSheet dataSheet = workbook.WorkSheets[0];
dataSheet.Name = "DataSheet";
// Convert worksheet to DataTable
DataTable dataTable = dataSheet.ToDataTable(true);
// Create new summary worksheet
WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");
// Get unique product-region combinations
var uniqueCombos = dataTable.AsEnumerable()
    .Select(row => new {
        Product = row["Product"].ToString(),
        Region = row["Region"].ToString()
    })
    .Distinct()
    .OrderBy(x => x.Product)
    .ThenBy(x => x.Region);
// Add header row
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";
// Populate rows with formulas
int rowIndex = 2;
foreach (var combo in uniqueCombos)
{
    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;
    // Adjust column references if your Sales column is C (not D)
    summarySheet[$"C{rowIndex}"].Formula =
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    summarySheet[$"D{rowIndex}"].Formula =
        $"=COUNTIFS(DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    rowIndex++;
}
// Optional: add total row
summarySheet[$"A{rowIndex}"].Value = "Total";
summarySheet[$"C{rowIndex}"].Formula = $"=SUM(C2:C{rowIndex - 1})";
summarySheet[$"D{rowIndex}"].Formula = $"=SUM(D2:D{rowIndex - 1})";
// Save output file
workbook.SaveAs(outputPath);  //filename
$vbLabelText   $csharpLabel

These formulas maintain live connections to source data, automatically updating when the data sheet changes - similar to pivot table refresh behavior but without Interop dependencies. This approach is ideal for containerized microservices that need to generate dynamic reports without external dependencies.

What Results Can You Expect from Dynamic Summaries?

If we apply this code to the example Excel file from the previous example, we'd get this output:

Excel spreadsheet displaying product sales data with dynamic summary formulas showing products (Laptop, Phone, Tablet) across regions with calculated totals and counts

The dynamic summary approach provides real-time calculations that update automatically when source data changes, making it perfect for automated reporting pipelines in containerized environments. This eliminates the need for scheduled pivot table refreshes and works seamlessly in .NET MAUI and Blazor applications.

How Should You Compare C# Interop vs IronXL for Pivot Tables?

Aspect

C# Interop

IronXL

Office Required

Yes - Full installation

No - Standalone library

Platform Support

Windows only

Windows, Linux, macOS, Docker

Memory Management

Manual COM cleanup required

Automatic .NET garbage collection

Deployment

Complex - Office licensing

Simple - Single DLL

Performance

Slow - Excel process startup

Fast - In-memory calculations

Cloud Compatible

No - Azure limitations

Yes - Azure Functions support

Native Pivot Tables

Yes

No - Aggregation alternatives

Development Speed

Slow - COM complexity

Fast - Intuitive API

Container Support

No - Cannot containerize Office

Yes - Docker-ready

Health Checks

Difficult - COM process monitoring

Easy - Standard .NET monitoring

From a DevOps perspective, IronXL's architecture provides significant advantages for modern deployment scenarios. The library's ability to run in containers without external dependencies means you can create lightweight Docker images that deploy quickly and scale efficiently. Health checks can be implemented using standard .NET patterns, and the library's security features include DigiCert certification and no COM interfaces, reducing attack vectors.

Which Approach Should You Choose?

When Should You Use C# Interop?

Choose C# Interop when:

  • Native Excel pivot table objects are absolutely required
  • Working exclusively on Windows with Office installed everywhere
  • Only deploying to desktop systems you administer
  • Existing legacy code depends on Interop
  • Using legacy .NET Framework versions
  • You have no plans to containerize or move to cloud

When Does IronXL Provide Better Results?

Choose IronXL when:

  • Deploying to servers or cloud environments (Azure, AWS)
  • Building cross-platform applications that run in containers
  • Requiring better performance with 40x speed improvements
  • Avoiding Office licensing costs and deployment complexity
  • Needing simpler code with automatic license key management
  • Supporting Mac, iOS, Android, and Linux systems
  • Working in modern .NET Core and .NET 5-10
  • Configuring pivot table fields programmatically
  • Building microservices that export to various formats
  • Implementing automated reporting in CI/CD pipelines
  • Creating health check endpoints for container orchestration
  • Converting between different spreadsheet formats

What Have We Learned About Creating Pivot Tables in C#?

While C# Excel Interop can create native pivot tables, its deployment restrictions and complexity make it increasingly impractical for modern applications, especially in containerized environments. IronXL provides powerful alternatives through data aggregation and formula-based summaries, eliminating Office dependencies while maintaining analytical capabilities.

For developers and DevOps engineers searching for alternatives to developing pivot tables without Interop, IronXL offers a superior path that avoids COM complications, works across all platforms, and simplifies deployment. The trade-off of not having native pivot objects is offset by greater flexibility, better performance, and elimination of Office licensing requirements. Most importantly for DevOps teams, IronXL enables true infrastructure as code with containerized deployments, automated scaling, and seamless integration with modern CI/CD pipelines.

The library's comprehensive feature set includes conditional formatting, cell styling, formula support, and data validation, making it a complete solution for Excel automation in modern .NET applications. Whether you're working with CSV files, managing worksheets, or implementing complex data transformations, IronXL provides a consistent, deployment-friendly API.

Get stated with IronXL now.
green arrow pointer

Ready to modernize your Excel automation and create pivot table code in modern C#?

IronXL can be implemented in your C# applications in seconds through the NuGet Package Manager. Try the free trial to eliminate Interop dependencies in your production applications and simplify your container deployments.

Frequently Asked Questions

What is a pivot table in Excel?

A pivot table in Excel is a powerful tool used to summarize, analyze, explore, and present data. It allows users to transform columns into rows and vice versa, enabling dynamic data analysis.

Why use IronXL for creating Excel pivot tables in C#?

IronXL allows developers to create Excel pivot tables in C# without relying on Office Interop, eliminating the need for Excel installations and reducing dependencies, making it a modern and efficient choice.

How does IronXL compare to C# Interop for Excel operations?

IronXL offers a more streamlined and independent approach compared to C# Interop, which requires Office installations. IronXL simplifies the creation of pivot tables and other Excel operations without the complications of Interop.

Can I generate pivot tables without Excel installed?

Yes, using IronXL, you can generate pivot tables in your C# applications without having Excel installed, as it operates independently of Microsoft Office.

Is IronXL suitable for large datasets?

IronXL is designed to handle large datasets efficiently, making it suitable for applications that require robust data manipulation and pivot table generation.

What are the advantages of using IronXL over traditional methods?

IronXL provides a modern, dependency-free alternative to traditional methods like C# Interop, offering ease of use, flexibility, and support for complex data operations without the need for Excel installations.

Do I need to learn VBA to use IronXL for pivot tables?

No, IronXL allows developers to work directly within C# to create and manage pivot tables, eliminating the need to learn VBA or other Excel-specific programming languages.

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