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.
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.
| 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 |
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.ExcelNote 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.
Install IronXL through NuGet with no additional system requirements:
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.
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);Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
' Launch Excel and set up workbooks
Dim excelApp As New Excel.Application()
excelApp.Visible = False
Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()
Dim dataSheet As Excel.Worksheet = CType(workbook.Worksheets(1), Excel.Worksheet)
dataSheet.Name = "SalesData"
Dim pivotSheet As Excel.Worksheet = CType(workbook.Worksheets.Add(), Excel.Worksheet)
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
Dim rows As Object(,) = {
{"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 i As Integer = 0 To rows.GetLength(0) - 1
dataSheet.Cells(i + 2, 1) = rows(i, 0)
dataSheet.Cells(i + 2, 2) = rows(i, 1)
dataSheet.Cells(i + 2, 3) = rows(i, 2)
Next
' Build pivot cache from source range
Dim dataRange As Excel.Range = dataSheet.Range("A1:C10")
Dim pivotCache As Excel.PivotCache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange)
' Create the PivotTable on the pivot sheet
Dim pivotTables As Excel.PivotTables = CType(pivotSheet.PivotTables(), Excel.PivotTables)
Dim pivotTable As Excel.PivotTable = pivotTables.Add(pivotCache, pivotSheet.Range("A3"), "SalesPivot")
' Assign field orientations
CType(pivotTable.PivotFields("Product"), Excel.PivotField).Orientation = Excel.XlPivotFieldOrientation.xlRowField
CType(pivotTable.PivotFields("Region"), Excel.PivotField).Orientation = Excel.XlPivotFieldOrientation.xlColumnField
CType(pivotTable.PivotFields("Sales"), Excel.PivotField).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)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.
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");Imports IronXL
Imports System.Data
Imports System.Linq
' Create workbook and populate source data
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim dataSheet As WorkSheet = workbook.CreateWorkSheet("SalesData")
dataSheet("A1").Value = "Product"
dataSheet("B1").Value = "Region"
dataSheet("C1").Value = "Sales"
Dim rows As Object(,) = {
{"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 i As Integer = 0 To rows.GetLength(0) - 1
dataSheet($"A{i + 2}").Value = rows(i, 0)
dataSheet($"B{i + 2}").Value = rows(i, 1)
dataSheet($"C{i + 2}").Value = rows(i, 2)
Next
' Aggregate data with LINQ -- equivalent to a pivot table row/column/value layout
Dim table As DataTable = dataSheet("A1:C10").ToDataTable(True)
Dim summary = table.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of String)("Product")) _
.Select(Function(group, idx) New With {
.Product = group.Key,
.TotalSales = group.Sum(Function(r) Convert.ToDecimal(r("Sales"))),
.RegionCount = group.Select(Function(r) r.Field(Of String)("Region")).Distinct().Count(),
.RowIndex = idx + 2
}) _
.OrderByDescending(Function(x) x.TotalSales)
' Write the summary sheet
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("Summary")
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Total Sales"
summarySheet("C1").Value = "Regions"
For Each item In summary
summarySheet($"A{item.RowIndex}").Value = item.Product
summarySheet($"B{item.RowIndex}").Value = CDbl(item.TotalSales)
summarySheet($"C{item.RowIndex}").Value = item.RegionCount
Next
' 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")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.
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.
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.
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.
The right tool depends on two constraints: where the code runs, and whether native pivot table XML is required.
Choose Excel Interop when:
Choose IronXL when:
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.
Beyond basic aggregation, IronXL provides several features that support richer data analysis workflows:
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.
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.
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}");Imports IronXL
Dim existing As WorkBook = WorkBook.Load("C:\data\sales_report.xlsx")
Dim sheet As WorkSheet = existing.WorkSheets(0)
' Read column C starting at row 2
Dim salesValues = sheet("C2:C100") _
.Where(Function(cell) cell.Value IsNot Nothing AndAlso cell.Value.ToString() <> String.Empty) _
.Select(Function(cell) cell.DecimalValue) _
.ToList()
Dim total As Decimal = salesValues.Sum()
Console.WriteLine($"Total sales from file: {total:C}")This pattern works for .xlsx, .xls, .csv, and other formats supported by IronXL. Details on supported formats appear in the IronXL file format documentation.
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")This is particularly useful in ETL pipelines where the final consumer is a database import tool or a data warehouse loader.
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";Imports IronXL
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE"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.
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.
Curtis Chau holds a Bachelor’s degree in Computer Science (Carleton University) and specializes in front-end development with expertise in Node.js, TypeScript, JavaScript, and React. Passionate about crafting intuitive and aesthetically pleasing user interfaces, Curtis enjoys working with modern frameworks and creating well-structured, visually appealing manuals.
IronXL is a part of IRONSUITE
10 .NET API products for your office documents
The Excel API you need, without the Office Interop hassle.
