USING IRONXL

How to Create Pivot Tables in Excel with C# Using IronXL

Updated:February 27, 2026

Generating pivot tables programmatically in Excel requires either C# Interop with its Office dependencies or a modern library like IronXL that works independently -- this tutorial demonstrates both approaches and shows you exactly why the modern path is the better choice.

Building pivot-style reports in server-side or cross-platform .NET code has historically been painful. The traditional COM Interop route ties you to a Windows machine with a full Office installation, generates memory leaks if you miss a single COM cleanup call, and falls apart the moment you try to deploy to Linux or a Docker container. The modern alternative -- writing aggregation logic with IronXL and LINQ -- runs anywhere .NET runs, requires no Office license, and gives you clean, readable code.

This guide walks through both techniques in detail. You will see the raw Interop approach, understand exactly what makes it fragile, and then build the same pivot-style summary sheet using IronXL in C#. You will also see how to use Excel formulas for live, auto-updating summaries that behave like a real pivot table refresh.

What Is an Excel Pivot Table?

A pivot table is one of the most powerful analytical tools in spreadsheet software. It summarises large data sets by grouping rows, aggregating values, and projecting results into a cross-tabulation layout -- all without requiring you to write a single formula manually. Microsoft's official pivot table documentation provides a thorough overview of what the feature does inside Excel itself.

Pivot tables appear in Microsoft Excel, Google Sheets, Apple Numbers, and most other spreadsheet tools. The core concept is always the same: you define row fields, column fields, and value fields, and the tool builds a summary matrix for you. When the underlying data changes, you refresh the pivot table and the summary updates automatically.

In C# server-side code, you have two broad options:

C# Interop -- automates a running Excel process through COM to create a genuine native pivot table object inside an XLSX file
IronXL with LINQ aggregation -- reads the workbook into memory, computes the same summary in managed .NET code, and writes the result to a new worksheet

Both options produce a useful output. But only one of them works reliably in modern deployment environments.

How Do You Create a Pivot Table Using C# Interop?

C# Excel Interop gives you direct access to Excel's native pivot table functionality through COM automation. You create an Excel.Application object, open a workbook, define a pivot cache pointing at a data range, and then configure row fields, column fields, and data fields.

How to Set Up the Interop Pivot Table Code

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;

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

// 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;

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

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

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

' Create Excel application instance
Dim xlApp As New Excel.Application()
Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Open("C:\Data\SalesData.xlsx")
Dim xlSheet As Excel.Worksheet = CType(xlWorkbook.Sheets(1), Excel.Worksheet)
Dim xlPivotSheet As Excel.Worksheet = CType(xlWorkbook.Sheets.Add(), Excel.Worksheet)

' Define data range for pivot table
Dim dataRange As Excel.Range = xlSheet.UsedRange

' Create pivot cache and pivot table
Dim pivotCache As Excel.PivotCache = xlWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange, Type.Missing)
Dim pivotTable As Excel.PivotTable = pivotCache.CreatePivotTable(xlPivotSheet.Cells(3, 1), "SalesPivot", Type.Missing, Type.Missing)

' Configure pivot table fields
Dim productField As Excel.PivotField = CType(pivotTable.PivotFields("Product"), Excel.PivotField)
productField.Orientation = Excel.XlPivotFieldOrientation.xlRowField
productField.Position = 1

Dim regionField As Excel.PivotField = CType(pivotTable.PivotFields("Region"), Excel.PivotField)
regionField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
regionField.Position = 1

Dim salesField As Excel.PivotField = CType(pivotTable.PivotFields("Sales"), Excel.PivotField)
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 it does produce a real pivot table object inside the XLSX file, it requires Microsoft Office to be installed and demands careful COM object management. Miss a single Marshal.ReleaseComObject call and you will find stale Excel processes accumulating in Task Manager.

How to Install IronXL Before Writing Any Code

Before jumping to the IronXL approach, install the library through the NuGet Package Manager:

Install-Package IronXL.Excel
dotnet add package IronXL.Excel

Install-Package IronXL.Excel
dotnet add package IronXL.Excel

SHELL

No Office installation is required on the server, container, or development machine. IronXL reads and writes XLSX, XLS, and CSV files entirely in managed .NET memory.

What Problems Does C# Interop Create?

The Interop approach presents several significant challenges that compound quickly in real-world deployment. Stack Overflow and other programming resources continue to recommend it because many threads were written in the early 2000s and have since been locked -- so the advice is frozen in time.

Deployment dependencies -- every machine that runs your code must have a licensed copy of Microsoft Office installed, including production servers and CI/CD build agents. This adds both licensing cost and deployment complexity that is entirely avoidable with modern alternatives.

Memory management burden -- COM objects must be explicitly released using Marshal.ReleaseComObject(). Missing even one object causes Excel processes to hang in memory indefinitely, as extensively documented on Stack Overflow. In a long-running service or an ASP.NET web application, this becomes a critical resource leak.

Platform restrictions -- Interop only works on Windows with Office installed. You cannot run it on Linux, macOS, in Docker containers, or on serverless platforms like Azure Functions or AWS Lambda. This blocks you from modern cloud-native architectures entirely.

Performance bottlenecks -- starting an Excel application instance is slow and resource-intensive. For server-side batch processing where you might need to generate dozens or hundreds of reports, this startup overhead becomes a serious throughput constraint.

Version compatibility fragility -- different Office versions expose slightly different COM interfaces. Code that works against Office 2019 may behave differently against Office 2016 or Microsoft 365, and you have no way to pin the version in a deployment. The Microsoft documentation on Office Interop assemblies notes these versioning constraints as a known limitation.

CI/CD incompatibility -- most continuous integration environments do not have Office installed. Testing your pivot table generation code requires either mocking the entire COM layer or maintaining a special Windows agent with a licensed Office installation.

For any new .NET application targeting .NET 6 or later -- including .NET 10 -- these constraints make Interop an impractical choice.

How Does IronXL Create Pivot Tables Without Interop?

IronXL approaches pivot table creation differently. Rather than controlling an external Excel process through COM, IronXL reads your workbook into managed .NET memory, giving you direct access to cell values, formulas, and worksheet structure. You then build pivot-style aggregations using standard LINQ queries and write the results back to a new worksheet.

How to Build a Cross-Tabulation Summary with IronXL and LINQ

The following example loads a sales data workbook, computes a product-by-region cross-tabulation, and writes the summary to a new worksheet -- all without any Office dependency:

using IronXL;
using System.Linq;
using System.Data;

// Load Excel file -- no Office installation required
WorkBook workbook = WorkBook.Load("SalesData.xlsx");
WorkSheet dataSheet = workbook.WorkSheets[0];

// Convert to DataTable for flexible LINQ manipulation
var dataTable = dataSheet.ToDataTable(true); // true = first row as column headers

// Build pivot-style aggregation using LINQ grouping
var pivotData = dataTable.AsEnumerable()
    .GroupBy(row => new {
        Product = row["Product"].ToString(),
        Region = row["Region"].ToString()
    })
    .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 the pivot report worksheet
WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");

// Get distinct row and column values
var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p).ToList();
var regions  = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r).ToList();

// Write column headers
pivotSheet["A1"].Value = "Product / Region";
for (int c = 0; c < regions.Count; c++)
{
    pivotSheet[$"{(char)('B' + c)}1"].Value = regions[c];
}

// Populate data rows
for (int r = 0; r < products.Count; r++)
{
    pivotSheet[$"A{r + 2}"].Value = products[r];
    for (int c = 0; c < regions.Count; c++)
    {
        var sales = pivotData
            .Where(p => p.Product == products[r] && p.Region == regions[c])
            .Select(p => p.TotalSales)
            .FirstOrDefault();
        pivotSheet[$"{(char)('B' + c)}{r + 2}"].Value = sales;
    }
}

// Add a totals row using Excel SUM formulas
int totalRow = products.Count + 2;
pivotSheet[$"A{totalRow}"].Value = "Total";
for (int c = 0; c < regions.Count; c++)
{
    char col = (char)('B' + c);
    pivotSheet[$"{col}{totalRow}"].Formula = $"=SUM({col}2:{col}{totalRow - 1})";
}

// Apply currency formatting to the data range
var dataRange = pivotSheet[$"B2:{(char)('B' + regions.Count - 1)}{totalRow}"];
dataRange.FormatString = "$#,##0.00";

workbook.SaveAs("PivotReport.xlsx");

using IronXL;
using System.Linq;
using System.Data;

// Load Excel file -- no Office installation required
WorkBook workbook = WorkBook.Load("SalesData.xlsx");
WorkSheet dataSheet = workbook.WorkSheets[0];

// Convert to DataTable for flexible LINQ manipulation
var dataTable = dataSheet.ToDataTable(true); // true = first row as column headers

// Build pivot-style aggregation using LINQ grouping
var pivotData = dataTable.AsEnumerable()
    .GroupBy(row => new {
        Product = row["Product"].ToString(),
        Region = row["Region"].ToString()
    })
    .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 the pivot report worksheet
WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");

// Get distinct row and column values
var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p).ToList();
var regions  = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r).ToList();

// Write column headers
pivotSheet["A1"].Value = "Product / Region";
for (int c = 0; c < regions.Count; c++)
{
    pivotSheet[$"{(char)('B' + c)}1"].Value = regions[c];
}

// Populate data rows
for (int r = 0; r < products.Count; r++)
{
    pivotSheet[$"A{r + 2}"].Value = products[r];
    for (int c = 0; c < regions.Count; c++)
    {
        var sales = pivotData
            .Where(p => p.Product == products[r] && p.Region == regions[c])
            .Select(p => p.TotalSales)
            .FirstOrDefault();
        pivotSheet[$"{(char)('B' + c)}{r + 2}"].Value = sales;
    }
}

// Add a totals row using Excel SUM formulas
int totalRow = products.Count + 2;
pivotSheet[$"A{totalRow}"].Value = "Total";
for (int c = 0; c < regions.Count; c++)
{
    char col = (char)('B' + c);
    pivotSheet[$"{col}{totalRow}"].Formula = $"=SUM({col}2:{col}{totalRow - 1})";
}

// Apply currency formatting to the data range
var dataRange = pivotSheet[$"B2:{(char)('B' + regions.Count - 1)}{totalRow}"];
dataRange.FormatString = "$#,##0.00";

workbook.SaveAs("PivotReport.xlsx");

Imports IronXL
Imports System.Linq
Imports System.Data

' Load Excel file -- no Office installation required
Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)

' Convert to DataTable for flexible LINQ manipulation
Dim dataTable As DataTable = dataSheet.ToDataTable(True) ' True = first row as column headers

' Build pivot-style aggregation using LINQ grouping
Dim pivotData = dataTable.AsEnumerable() _
    .GroupBy(Function(row) New With {
        Key .Product = row("Product").ToString(),
        Key .Region = row("Region").ToString()
    }) _
    .Select(Function(g) New With {
        Key .Product = g.Key.Product,
        Key .Region = g.Key.Region,
        Key .TotalSales = g.Sum(Function(row) Convert.ToDecimal(row("Sales"))),
        Key .AverageSale = g.Average(Function(row) Convert.ToDecimal(row("Sales"))),
        Key .Count = g.Count()
    })

' Create the pivot report worksheet
Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")

' Get distinct row and column values
Dim products = pivotData.Select(Function(p) p.Product).Distinct().OrderBy(Function(p) p).ToList()
Dim regions = pivotData.Select(Function(p) p.Region).Distinct().OrderBy(Function(r) r).ToList()

' Write column headers
pivotSheet("A1").Value = "Product / Region"
For c As Integer = 0 To regions.Count - 1
    pivotSheet($"{ChrW(AscW("B"c) + c)}1").Value = regions(c)
Next

' Populate data rows
For r As Integer = 0 To products.Count - 1
    pivotSheet($"A{r + 2}").Value = products(r)
    For c As Integer = 0 To regions.Count - 1
        Dim sales = pivotData _
            .Where(Function(p) p.Product = products(r) AndAlso p.Region = regions(c)) _
            .Select(Function(p) p.TotalSales) _
            .FirstOrDefault()
        pivotSheet($"{ChrW(AscW("B"c) + c)}{r + 2}").Value = sales
    Next
Next

' Add a totals row using Excel SUM formulas
Dim totalRow As Integer = products.Count + 2
pivotSheet($"A{totalRow}").Value = "Total"
For c As Integer = 0 To regions.Count - 1
    Dim col As Char = ChrW(AscW("B"c) + c)
    pivotSheet($"{col}{totalRow}").Formula = $"=SUM({col}2:{col}{totalRow - 1})"
Next

' Apply currency formatting to the data range
Dim dataRange = pivotSheet($"B2:{ChrW(AscW("B"c) + regions.Count - 1)}{totalRow}")
dataRange.FormatString = "$#,##0.00"

workbook.SaveAs("PivotReport.xlsx")

$vbLabelText $csharpLabel

This produces the same cross-tabulated summary you would get from a native Excel pivot table. You have full programmatic control over every cell, formula, and format string -- and no COM objects to clean up.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 1 - The original Excel file vs. the Pivot table created showing the cells.

Get stated with IronXL now.

How Do You Create Dynamic Summaries Using Excel Formulas?

For scenarios where you want the summary sheet to stay live -- automatically recalculating whenever the source data changes -- IronXL lets you write Excel formula strings directly into cells. This gives you behaviour similar to a pivot table's auto-refresh, without any Interop dependency.

The key functions here are SUMIFS and COUNTIFS. SUMIFS sums a range based on multiple criteria column conditions; COUNTIFS counts matching rows. Both accept references to named worksheets, so you can point your summary sheet directly at the source data sheet by name.

How to Write Formula-Based Aggregations with IronXL

using IronXL;
using System.Data;

string inputPath  = "SalesData.xlsx";
string outputPath = "DynamicSummary.xlsx";

WorkBook workbook  = WorkBook.Load(inputPath);
WorkSheet dataSheet = workbook.WorkSheets[0];

// Name the data sheet so formula references are stable
dataSheet.Name = "DataSheet";

// Convert to DataTable to enumerate unique product/region combinations
DataTable dataTable = dataSheet.ToDataTable(true);

WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");

// Get unique product-region pairs
var uniqueCombos = dataTable.AsEnumerable()
    .Select(row => new {
        Product = row["Product"].ToString(),
        Region  = row["Region"].ToString()
    })
    .Distinct()
    .OrderBy(x => x.Product)
    .ThenBy(x => x.Region)
    .ToList();

// Header row
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";

// Populate rows with live SUMIFS / COUNTIFS formulas
for (int i = 0; i < uniqueCombos.Count; i++)
{
    int rowIndex = i + 2;
    var combo = uniqueCombos[i];

    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;

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

// Grand total row
int totalRow = uniqueCombos.Count + 2;
summarySheet[$"A{totalRow}"].Value = "Total";
summarySheet[$"C{totalRow}"].Formula = $"=SUM(C2:C{totalRow - 1})";
summarySheet[$"D{totalRow}"].Formula = $"=SUM(D2:D{totalRow - 1})";

workbook.SaveAs(outputPath);

using IronXL;
using System.Data;

string inputPath  = "SalesData.xlsx";
string outputPath = "DynamicSummary.xlsx";

WorkBook workbook  = WorkBook.Load(inputPath);
WorkSheet dataSheet = workbook.WorkSheets[0];

// Name the data sheet so formula references are stable
dataSheet.Name = "DataSheet";

// Convert to DataTable to enumerate unique product/region combinations
DataTable dataTable = dataSheet.ToDataTable(true);

WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");

// Get unique product-region pairs
var uniqueCombos = dataTable.AsEnumerable()
    .Select(row => new {
        Product = row["Product"].ToString(),
        Region  = row["Region"].ToString()
    })
    .Distinct()
    .OrderBy(x => x.Product)
    .ThenBy(x => x.Region)
    .ToList();

// Header row
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";

// Populate rows with live SUMIFS / COUNTIFS formulas
for (int i = 0; i < uniqueCombos.Count; i++)
{
    int rowIndex = i + 2;
    var combo = uniqueCombos[i];

    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;

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

// Grand total row
int totalRow = uniqueCombos.Count + 2;
summarySheet[$"A{totalRow}"].Value = "Total";
summarySheet[$"C{totalRow}"].Formula = $"=SUM(C2:C{totalRow - 1})";
summarySheet[$"D{totalRow}"].Formula = $"=SUM(D2:D{totalRow - 1})";

workbook.SaveAs(outputPath);

Imports IronXL
Imports System.Data
Imports System.Linq

Dim inputPath As String = "SalesData.xlsx"
Dim outputPath As String = "DynamicSummary.xlsx"

Dim workbook As WorkBook = WorkBook.Load(inputPath)
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)

' Name the data sheet so formula references are stable
dataSheet.Name = "DataSheet"

' Convert to DataTable to enumerate unique product/region combinations
Dim dataTable As DataTable = dataSheet.ToDataTable(True)

Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")

' Get unique product-region pairs
Dim uniqueCombos = dataTable.AsEnumerable() _
    .Select(Function(row) New With {
        .Product = row("Product").ToString(),
        .Region = row("Region").ToString()
    }) _
    .Distinct() _
    .OrderBy(Function(x) x.Product) _
    .ThenBy(Function(x) x.Region) _
    .ToList()

' Header row
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Region"
summarySheet("C1").Value = "Total Sales"
summarySheet("D1").Value = "Count"

' Populate rows with live SUMIFS / COUNTIFS formulas
For i As Integer = 0 To uniqueCombos.Count - 1
    Dim rowIndex As Integer = i + 2
    Dim combo = uniqueCombos(i)

    summarySheet($"A{rowIndex}").Value = combo.Product
    summarySheet($"B{rowIndex}").Value = combo.Region

    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}"")"
Next

' Grand total row
Dim totalRow As Integer = uniqueCombos.Count + 2
summarySheet($"A{totalRow}").Value = "Total"
summarySheet($"C{totalRow}").Formula = $"=SUM(C2:C{totalRow - 1})"
summarySheet($"D{totalRow}").Formula = $"=SUM(D2:D{totalRow - 1})"

workbook.SaveAs(outputPath)

$vbLabelText $csharpLabel

These formulas maintain live connections to the source data. When someone updates a value in DataSheet, Excel recalculates the summary automatically on the next open or refresh -- giving you the same behaviour as a native pivot table's refresh cycle, without requiring COM automation.

When you apply this to the same sales data workbook used in the previous example, the output looks like this:

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 2 - C# Dynamic summaries output shown in context.

This formula-based approach also gives you the ability to add conditional formatting, data bars, or icon sets to the summary cells using IronXL's cell formatting API, making your reports visually clear without any manual work in the Excel UI.

How Do the Two Approaches Compare?

Before choosing an approach, it helps to see the trade-offs side by side. The table below covers the most important dimensions for production .NET development:

C# Interop vs IronXL for pivot table generation
Aspect	C# Interop	IronXL
Office required	Yes -- full installation on every machine	No -- standalone NuGet package
Platform support	Windows only	Windows, Linux, macOS, Docker
Memory management	Manual COM cleanup required	Automatic .NET garbage collection
Deployment complexity	High -- Office licensing + installation	Low -- single DLL reference
Performance	Slow -- Excel process startup overhead	Fast -- in-memory calculations
Cloud compatible	No -- blocked on Azure Functions, AWS Lambda	Yes -- runs on any cloud platform
Native pivot table object	Yes -- full Excel pivot table	No -- aggregation-based equivalent
Development speed	Slow -- COM complexity	Fast -- fluent managed API
.NET 10 support	Limited -- COM binding issues	Full -- targets modern .NET

The only scenario where Interop has a clear advantage is when you specifically need the native Excel pivot table object embedded in the XLSX file -- for example, if downstream users must interact with it using Excel's built-in pivot table UI (drilling down, filtering, changing aggregation functions interactively). In every other scenario, IronXL's approach is faster to write, easier to deploy, and far more portable.

Which Approach Should You Choose?

The right choice depends on your deployment environment and your users' needs.

Choose C# Interop only when:

Your users require native Excel pivot table objects that they can manipulate interactively inside the Excel UI
You are targeting a closed Windows desktop environment where Office is guaranteed to be installed on every machine
You are maintaining legacy .NET Framework code that already depends on Interop and a rewrite is not currently justified

Choose IronXL when:

You are deploying to a server, a container, or any cloud environment (Azure, AWS, GCP)
You need cross-platform support for Linux, macOS, or Docker-based builds
You want clean, maintainable code without COM lifecycle management
You are targeting .NET 5, 6, 7, 8, 9, or 10
You want to avoid Microsoft Office licensing fees on server infrastructure
You need fast batch processing of many workbooks without per-file Excel process startup

For the vast majority of modern .NET applications, IronXL is the practical choice. The aggregation-based output meets all real reporting requirements, and you gain full portability in return.

You can explore further capabilities -- including cell formatting, formula evaluation, data validation, and chart generation -- in the IronXL documentation and the IronXL examples library.

How Do You Get Started with IronXL Today?

The IronXL library is available on NuGet and takes under a minute to add to any .NET project:

Install-Package IronXL.Excel
dotnet add package IronXL.Excel

Install-Package IronXL.Excel
dotnet add package IronXL.Excel

SHELL

Once installed, you can load an existing workbook or create a new one, read and write cell values, apply formulas, set format strings, and save to XLSX -- all with a clean, well-documented API. There is no COM, no Office dependency, and no special server configuration required.

For full API documentation, see the IronXL getting started guide, the C# Excel Interop migration guide, and the IronXL code examples. You can also compare IronXL against other Excel libraries in the IronXL comparison articles.

A free trial license lets you test full functionality in your own project before committing. When you are ready to deploy to production, a commercial IronXL license removes the trial watermark and includes priority support. Start with the free trial and see how much simpler cross-platform Excel automation can be.

Frequently Asked Questions

How can I create pivot tables in Excel using C# without Interop?

You can create pivot tables in Excel using C# without Interop by using IronXL, which offers powerful data manipulation capabilities independently of Office dependencies.

What are the advantages of using IronXL for generating pivot tables?

IronXL allows developers to generate pivot tables without relying on Excel Interop, eliminating the need for Office installations and reducing complexity in deployment.

Is IronXL compatible with .NET applications?

Yes, IronXL is fully compatible with .NET applications, providing an easy-to-use API for Excel operations, including creating pivot tables.

Does IronXL require Excel to be installed on the server?

No, IronXL does not require Excel to be installed on the server. It functions independently, allowing for seamless integration into server-side applications.

Can I manipulate data in Excel using IronXL?

Yes, IronXL offers robust data manipulation features, enabling developers to create, modify, and analyze Excel data, including creating pivot tables.

Why might developers prefer IronXL over traditional Interop methods?

Developers might prefer IronXL over traditional Interop methods due to its lack of Office dependencies, simpler deployment, and comprehensive functionality for Excel operations.

What features does IronXL provide for Excel data manipulation?

IronXL provides features such as reading and writing Excel files, creating and editing spreadsheets, and generating pivot tables, all without needing Excel Interop.

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 ...

Updated March 1, 2026

Download Excel File in ASP .NET C#: Export Data to XLSX, CSV & More

Download Excel file in ASP.NET Core using C# and IronXL. Export data to XLSX, CSV, and XML from MVC controllers with MemoryStream and File() return. Code examples included.

Updated March 1, 2026

How to Export Excel Files in Blazor Using IronXL

Learn how to export data to Excel in Blazor Server applications using IronXL. This guide covers project setup, service design, conditional formatting, multi-sheet reports, and error handling with complete C# code examples.

Updated February 27, 2026

How to Read Excel Files in C# Instead of Using StreamReader

Learn why StreamReader cannot read Excel files and how to use IronXL to load XLSX and XLS workbooks from disk or memory streams in C# .NET.

How to Write CSV Files in .NET with IronXL

How to Export GridView to Excel XLS...

Customer Highlight:

Developer Spotlight:

Webinars:

Start Free 30 Day Trial

How to Create Pivot Tables in Excel with C# Using IronXL

What Is an Excel Pivot Table?

How Do You Create a Pivot Table Using C# Interop?

How to Set Up the Interop Pivot Table Code

How to Install IronXL Before Writing Any Code

What Problems Does C# Interop Create?

How Does IronXL Create Pivot Tables Without Interop?

How to Build a Cross-Tabulation Summary with IronXL and LINQ

How Do You Create Dynamic Summaries Using Excel Formulas?

How to Write Formula-Based Aggregations with IronXL

How Do the Two Approaches Compare?

Which Approach Should You Choose?

How Do You Get Started with IronXL Today?

Frequently Asked Questions

How can I create pivot tables in Excel using C# without Interop?

What are the advantages of using IronXL for generating pivot tables?

Is IronXL compatible with .NET applications?

Does IronXL require Excel to be installed on the server?

Can I manipulate data in Excel using IronXL?

Why might developers prefer IronXL over traditional Interop methods?

What features does IronXL provide for Excel data manipulation?

Iron Support Team

Start Free 30 Day Trial

How to Create Pivot Tables in Excel with C# Using IronXL

What Is an Excel Pivot Table?

How Do You Create a Pivot Table Using C# Interop?

How to Set Up the Interop Pivot Table Code

How to Install IronXL Before Writing Any Code

What Problems Does C# Interop Create?

How Does IronXL Create Pivot Tables Without Interop?

How to Build a Cross-Tabulation Summary with IronXL and LINQ

How Do You Create Dynamic Summaries Using Excel Formulas?

How to Write Formula-Based Aggregations with IronXL

How Do the Two Approaches Compare?

Which Approach Should You Choose?

How Do You Get Started with IronXL Today?

Frequently Asked Questions

How can I create pivot tables in Excel using C# without Interop?

What are the advantages of using IronXL for generating pivot tables?

Is IronXL compatible with .NET applications?

Does IronXL require Excel to be installed on the server?

Can I manipulate data in Excel using IronXL?

Why might developers prefer IronXL over traditional Interop methods?

What features does IronXL provide for Excel data manipulation?

Related Articles

Download Excel File in ASP .NET C#: Export Data to XLSX, CSV & More

How to Export Excel Files in Blazor Using IronXL

How to Read Excel Files in C# Instead of Using StreamReader

Next step: Start free 30-day Trial

Next step: Start free 30-day Trial

Trusted by Millions of Engineers Worldwide

Iron Support Team