Message icon
Skip to footer content
  1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. Create Pivot Table In Excel Using C# Interop
USING IRONXL

How to Create Pivot Tables in Excel with C# Without Interop

Jordi Bardia
Jordi Bardia
Updated:

Creating a pivot table in Excel with C# is straightforward when you choose the right library -- Excel Interop requires Office on every machine, while IronXL works anywhere .NET runs. This guide walks through both approaches with complete code examples, covering setup, data aggregation, field configuration, and deployment considerations so you can make the right call for your project.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 1 - IronXL

What Is the Difference Between Excel Interop and IronXL?

Before writing a single line of code, it helps to understand what each approach does under the hood. Excel Interop uses COM (Component Object Model) to drive Microsoft Excel directly from C#. The .NET process communicates with a running Excel instance, which means Excel itself must be installed on the machine. Every object you manipulate -- workbooks, worksheets, ranges, pivot caches -- is a COM wrapper, and each one must be explicitly released to avoid process leaks.

IronXL takes a fundamentally different path. It reads and writes the Open XML file format directly, without launching Excel at all. The result is a standard .NET library with familiar object patterns, garbage-collected memory, and no dependency on Office installation or licensing. That architecture makes IronXL suitable for server-side workloads, Docker containers, Linux hosts, and any .NET 6, .NET 8, or .NET 10 environment.

Excel Interop vs IronXL -- at a glance
Criteria Excel Interop IronXL
Office required Yes No
Windows only Yes No -- cross-platform
Memory management Manual COM release Automatic (.NET GC)
Server deployment Difficult Straightforward
Native pivot table Full Excel pivot API Data aggregation via LINQ
License needed Office license IronXL license only

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 2 - Cross Platform

How Do You Install Each Library?

Setting Up Excel Interop

Excel Interop ships as a NuGet package. Run either of the following in your project:

Install-Package Microsoft.Office.Interop.Excel
dotnet add package Microsoft.Office.Interop.Excel
Install-Package Microsoft.Office.Interop.Excel
dotnet add package Microsoft.Office.Interop.Excel
SHELL

Note that the package alone is not enough -- the target machine must have a matching version of Microsoft Excel installed and properly licensed. That dependency rules out most server, container, and cloud scenarios.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 3 - Excel Interop Installation

Setting Up IronXL

Install IronXL through NuGet with no additional system requirements:

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

After installation, you can start reading and writing Excel files immediately on any platform that supports .NET. No Office license, no COM registration, no server configuration. See the IronXL installation guide for additional options, including offline installation and project reference setup.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 4 - Installation

Get stated with IronXL now.
green arrow pointer

How Do You Create an Excel Pivot Table with C# Interop?

The Interop pivot table workflow follows a strict sequence: create a pivot cache from a source range, then build a PivotTable object on a separate worksheet, then configure field orientations. The example below uses top-level C# statements compatible with .NET 10:

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

// Launch Excel and set up workbooks
var excelApp = new Excel.Application();
excelApp.Visible = false;
var workbook = excelApp.Workbooks.Add();
var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
dataSheet.Name = "SalesData";
var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
pivotSheet.Name = "Pivot";

// Populate header row
dataSheet.Cells[1, 1] = "Product";
dataSheet.Cells[1, 2] = "Region";
dataSheet.Cells[1, 3] = "Sales";

// Add sample data rows
object[,] rows = {
    { "Laptop",   "North", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "North",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "North",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet.Cells[i + 2, 1] = rows[i, 0];
    dataSheet.Cells[i + 2, 2] = rows[i, 1];
    dataSheet.Cells[i + 2, 3] = rows[i, 2];
}

// Build pivot cache from source range
Excel.Range dataRange = dataSheet.Range["A1:C10"];
Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase, dataRange);

// Create the PivotTable on the pivot sheet
Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
Excel.PivotTable pivotTable = pivotTables.Add(
    pivotCache, pivotSheet.Range["A3"], "SalesPivot");

// Assign field orientations
((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;

// Enable grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

// Save and close
workbook.SaveAs(@"C:\output\pivot_interop.xlsx");
workbook.Close(false);
excelApp.Quit();

// Release every COM object -- skipping any of these causes Excel to stay in memory
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotTables);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(dataRange);
Marshal.ReleaseComObject(pivotSheet);
Marshal.ReleaseComObject(dataSheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

// Launch Excel and set up workbooks
var excelApp = new Excel.Application();
excelApp.Visible = false;
var workbook = excelApp.Workbooks.Add();
var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
dataSheet.Name = "SalesData";
var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
pivotSheet.Name = "Pivot";

// Populate header row
dataSheet.Cells[1, 1] = "Product";
dataSheet.Cells[1, 2] = "Region";
dataSheet.Cells[1, 3] = "Sales";

// Add sample data rows
object[,] rows = {
    { "Laptop",   "North", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "North",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "North",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet.Cells[i + 2, 1] = rows[i, 0];
    dataSheet.Cells[i + 2, 2] = rows[i, 1];
    dataSheet.Cells[i + 2, 3] = rows[i, 2];
}

// Build pivot cache from source range
Excel.Range dataRange = dataSheet.Range["A1:C10"];
Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase, dataRange);

// Create the PivotTable on the pivot sheet
Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
Excel.PivotTable pivotTable = pivotTables.Add(
    pivotCache, pivotSheet.Range["A3"], "SalesPivot");

// Assign field orientations
((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;

// Enable grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

// Save and close
workbook.SaveAs(@"C:\output\pivot_interop.xlsx");
workbook.Close(false);
excelApp.Quit();

// Release every COM object -- skipping any of these causes Excel to stay in memory
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotTables);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(dataRange);
Marshal.ReleaseComObject(pivotSheet);
Marshal.ReleaseComObject(dataSheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);
$vbLabelText   $csharpLabel

Every COM wrapper requires a matching Marshal.ReleaseComObject call. Missing even one reference causes the Excel process to persist in the background, consuming memory and file handles until the host process ends. This cleanup burden scales with the complexity of your spreadsheet operations.

How Do You Achieve the Same Result with IronXL?

IronXL does not expose a native pivot table API in the same way Interop does -- the Open XML pivot format has hundreds of XML attributes, and most business requirements are met more reliably with explicit LINQ aggregation written in plain C#. The output is a clean summary sheet that recalculates correctly when the file opens, without depending on Excel to refresh a pivot cache.

using IronXL;
using System.Data;

// Create workbook and populate source data
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet dataSheet = workbook.CreateWorkSheet("SalesData");

dataSheet["A1"].Value = "Product";
dataSheet["B1"].Value = "Region";
dataSheet["C1"].Value = "Sales";

object[,] rows = {
    { "Laptop",   "North", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "North",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "North",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet[$"A{i + 2}"].Value = rows[i, 0];
    dataSheet[$"B{i + 2}"].Value = rows[i, 1];
    dataSheet[$"C{i + 2}"].Value = rows[i, 2];
}

// Aggregate data with LINQ -- equivalent to a pivot table row/column/value layout
DataTable table = dataSheet["A1:C10"].ToDataTable(true);
var summary = table.AsEnumerable()
    .GroupBy(row => row.Field<string>("Product"))
    .Select((group, idx) => new
    {
        Product    = group.Key,
        TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
        RegionCount = group.Select(r => r.Field<string>("Region")).Distinct().Count(),
        RowIndex   = idx + 2
    })
    .OrderByDescending(x => x.TotalSales);

// Write the summary sheet
WorkSheet summarySheet = workbook.CreateWorkSheet("Summary");
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Total Sales";
summarySheet["C1"].Value = "Regions";

foreach (var item in summary)
{
    summarySheet[$"A{item.RowIndex}"].Value = item.Product;
    summarySheet[$"B{item.RowIndex}"].Value = (double)item.TotalSales;
    summarySheet[$"C{item.RowIndex}"].Value = item.RegionCount;
}

// Apply currency format to the sales column
summarySheet["B:B"].FormatString = "$#,##0.00";

// Bold the header row
summarySheet["A1:C1"].Style.Font.Bold = true;

// Save -- no COM cleanup needed
workbook.SaveAs(@"C:\output\analysis_ironxl.xlsx");
using IronXL;
using System.Data;

// Create workbook and populate source data
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet dataSheet = workbook.CreateWorkSheet("SalesData");

dataSheet["A1"].Value = "Product";
dataSheet["B1"].Value = "Region";
dataSheet["C1"].Value = "Sales";

object[,] rows = {
    { "Laptop",   "North", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "North",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "North",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet[$"A{i + 2}"].Value = rows[i, 0];
    dataSheet[$"B{i + 2}"].Value = rows[i, 1];
    dataSheet[$"C{i + 2}"].Value = rows[i, 2];
}

// Aggregate data with LINQ -- equivalent to a pivot table row/column/value layout
DataTable table = dataSheet["A1:C10"].ToDataTable(true);
var summary = table.AsEnumerable()
    .GroupBy(row => row.Field<string>("Product"))
    .Select((group, idx) => new
    {
        Product    = group.Key,
        TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
        RegionCount = group.Select(r => r.Field<string>("Region")).Distinct().Count(),
        RowIndex   = idx + 2
    })
    .OrderByDescending(x => x.TotalSales);

// Write the summary sheet
WorkSheet summarySheet = workbook.CreateWorkSheet("Summary");
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Total Sales";
summarySheet["C1"].Value = "Regions";

foreach (var item in summary)
{
    summarySheet[$"A{item.RowIndex}"].Value = item.Product;
    summarySheet[$"B{item.RowIndex}"].Value = (double)item.TotalSales;
    summarySheet[$"C{item.RowIndex}"].Value = item.RegionCount;
}

// Apply currency format to the sales column
summarySheet["B:B"].FormatString = "$#,##0.00";

// Bold the header row
summarySheet["A1:C1"].Style.Font.Bold = true;

// Save -- no COM cleanup needed
workbook.SaveAs(@"C:\output\analysis_ironxl.xlsx");
$vbLabelText   $csharpLabel

There are no COM objects to release, no Excel process to terminate, and no Office license to manage. The same code compiles and runs on Linux, macOS, and Windows without modification. For more details on the aggregation API, visit the IronXL WorkSheet documentation.

Output

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 6 - IronXL Output

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 7 - Summary Output

What Are the Key Differences in Deployment and Maintenance?

Deployment Requirements

Deploying an Interop-based application involves verifying that the target environment runs Windows, has the correct Office version installed, and has COM automation permissions configured on the server. Cloud-hosted environments and containerized workloads typically cannot meet these requirements without adding virtualized Windows desktop infrastructure, which significantly increases cost and operational complexity.

IronXL has no such constraints. Add the NuGet package, set your target framework to .NET 6 or later, and the application deploys to any host -- including Linux containers, Azure App Service on Linux, AWS Lambda, and on-premises Windows servers. The IronXL system requirements page lists the full compatibility matrix.

Error Handling and Debugging

Interop failures surface as COM exceptions (COMException) that are difficult to map to user-visible messages. Version mismatches between the installed Office version and the Interop assembly add another failure mode. Debugging these issues usually requires reproducing the exact Office version on a development machine.

IronXL throws standard System.Exception subclasses with descriptive messages. You can wrap file operations in a try-catch block and present meaningful feedback without understanding COM error codes. The IronXL troubleshooting guide covers common exceptions and their resolutions.

Memory and Performance

COM objects hold unmanaged memory. If your code processes many worksheets or runs in a loop, failing to release each reference correctly causes memory growth over time -- a problem that is difficult to detect until it causes a production incident. Interop is also inherently single-threaded because it drives a single Excel window.

IronXL uses managed objects backed by the .NET garbage collector. Memory reclaims automatically when objects go out of scope. Processing multiple workbooks in parallel is straightforward because there is no shared COM state to protect with locks.

How Do You Choose Between the Two Approaches?

The right tool depends on two constraints: where the code runs, and whether native pivot table XML is required.

Choose Excel Interop when:

  • The workload runs only on Windows desktop machines where Office is already installed
  • Exact native pivot table formatting (slicers, grouped date fields, calculated items) is required in the output file
  • You maintain an existing Interop codebase and a full rewrite is not justified

Choose IronXL when:

  • The application runs on a server, container, or cloud function
  • Cross-platform or Linux deployment is required
  • You want data aggregation logic that is testable with unit tests, not dependent on an Excel process
  • You need to process Excel files at scale or in parallel

For most new .NET 10 projects, IronXL is the practical default. The IronXL library also covers reading Excel files in C#, creating charts, applying cell styles, working with formulas, and exporting Excel data to DataTable -- reducing the need for multiple libraries in your stack.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 8 - Features

What Additional IronXL Features Support Data Analysis?

Beyond basic aggregation, IronXL provides several features that support richer data analysis workflows:

Sorting and Filtering Data

You can sort a range in ascending or descending order before writing a summary sheet. This ensures the output always presents data in a consistent order, making downstream processing more predictable. See the IronXL sort documentation for range-level sort options.

Applying Conditional Formatting

Highlight outliers or thresholds by applying conditional formatting rules programmatically. For example, you can color cells in the summary sheet red when sales fall below a target, providing an at-a-glance view without requiring end users to configure rules manually.

Reading Existing Excel Files

If the source data comes from an existing spreadsheet rather than a database, you can load it directly with WorkBook.Load:

using IronXL;

WorkBook existing = WorkBook.Load(@"C:\data\sales_report.xlsx");
WorkSheet sheet = existing.WorkSheets[0];

// Read column C starting at row 2
var salesValues = sheet["C2:C100"]
    .Where(cell => cell.Value != null && cell.Value.ToString() != string.Empty)
    .Select(cell => cell.DecimalValue)
    .ToList();

decimal total = salesValues.Sum();
Console.WriteLine($"Total sales from file: {total:C}");
using IronXL;

WorkBook existing = WorkBook.Load(@"C:\data\sales_report.xlsx");
WorkSheet sheet = existing.WorkSheets[0];

// Read column C starting at row 2
var salesValues = sheet["C2:C100"]
    .Where(cell => cell.Value != null && cell.Value.ToString() != string.Empty)
    .Select(cell => cell.DecimalValue)
    .ToList();

decimal total = salesValues.Sum();
Console.WriteLine($"Total sales from file: {total:C}");
$vbLabelText   $csharpLabel

This pattern works for .xlsx, .xls, .csv, and other formats supported by IronXL. Details on supported formats appear in the IronXL file format documentation.

Exporting to CSV

After producing a summary worksheet, you can export it to CSV for downstream systems that do not accept Excel:

workbook.SaveAsCsv(@"C:\output\summary.csv");
workbook.SaveAsCsv(@"C:\output\summary.csv");
$vbLabelText   $csharpLabel

This is particularly useful in ETL pipelines where the final consumer is a database import tool or a data warehouse loader.

How Do You Get Started with IronXL for Free?

IronXL is available under a free trial license that lets you evaluate the full feature set without committing to a subscription. The trial places a watermark on output files, which you remove when you apply a production license key.

To activate a license key in code, set it before any IronXL operation:

IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE";
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE";
$vbLabelText   $csharpLabel

You can also set the key via an environment variable (IRONXL_LICENSEKEY) or through appsettings.json in ASP.NET Core projects. The IronXL licensing page describes all available tiers, including team and organizational options.

For production deployments, review the IronXL deployment documentation and the broader IronXL tutorial library for patterns covering ASP.NET Core, Azure Functions, and Docker.

How to Create Pivot Table in Excel Using C# Interop vs IronXL: Image 9 - Licensing

How Do You Take the Next Step?

Excel pivot tables in C# do not have to mean Office dependency and COM complexity. With IronXL, you write plain .NET code that runs on any platform, handles memory automatically, and integrates naturally with unit testing frameworks and CI/CD pipelines.

Download the free IronXL trial and run the examples from this guide against your own data. If you run into questions, the IronXL community forum and support portal are available for both trial and licensed users. Once you are ready to deploy, review the licensing options to find the plan that fits your team size and usage volume.

Frequently Asked Questions

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

IronXL offers a more modern and efficient way to create pivot tables in Excel files compared to the traditional Excel Interop. It simplifies the process and reduces the amount of boilerplate code needed.

Can I create a pivot table in Excel using C# with IronXL?

Yes, IronXL provides a straightforward method to create pivot tables in Excel using C#. It allows developers to easily manipulate Excel files without needing to rely on Excel Interop.

Is IronXL compatible with .NET applications?

IronXL is fully compatible with .NET applications, making it an excellent choice for developers looking to integrate Excel functionalities into their C# projects.

Does IronXL require Excel to be installed on the system?

No, IronXL does not require Microsoft Excel to be installed on the system. It operates independently, which is a significant advantage over Excel Interop that requires Excel to be installed.

How does IronXL simplify the process of creating pivot tables?

IronXL simplifies the process by providing a user-friendly API that reduces the need for extensive boilerplate code, making the development process faster and more efficient.

What are the system requirements for using IronXL?

IronXL requires a .NET framework compatible environment but does not need Microsoft Excel to be installed, which simplifies deployment and reduces dependencies.

Can IronXL handle large Excel files efficiently?

Yes, IronXL is designed to handle large Excel files efficiently, making it suitable for business applications that require processing substantial amounts of data.

Is there a learning curve for using IronXL compared to Excel Interop?

IronXL is designed to be intuitive and easy to learn, with comprehensive documentation and examples, making it easier to adopt compared to the more complex Excel Interop.

What types of Excel operations can IronXL perform besides creating pivot tables?

IronXL can perform a wide range of Excel operations, including reading and writing Excel files, formatting cells, and applying formulas, among others.

Can IronXL export pivot tables to formats other than Excel?

While IronXL is primarily focused on Excel operations, it also supports exporting data to other formats such as CSV and PDF, depending on your project requirements.

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