How to Create an Excel Pivot Table in C#
Generating pivot tables programmatically in Excel 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.
In this article, we will learn how to edit, create, design and calculate pivot tables and groups, With automatic analysis and error handling.
What's is an Excel pivot table?
A pivot type is one of the most powerful tools you can add in Excel. It's an easy and simple way to summarize large data sets. Consequently, pivot tables allow you to easily display, understand, and analyze numerical data. Pivot tables are available not only in Excel but also in other programs such as Google Sheets, Apple Numbers and CSV Exports. It provides a solution to seeing the data in overview - Acting as a as a data console to let people see their information in a meaningful way - linking to reality.
Let's make a pivot table the wrong way and then learn to make it a pivot table the right way in C#:
How to Create a Pivot Table in Excel Tables Using C# Interop?
C# Excel Interop provides direct access to the Excel's pivot table functionality through COM automation. Here's the traditional approach that many developers find when searching for a tool to use to generate a pivot table in C#: (Deprecated)
How to create pivot tables with the old-fashioned way 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);
IRON VB CONVERTER ERROR developers@ironsoftware.com
This Interop example creates a native XL 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. Refer to Microsoft documentation on why not use this approach is modern.
What Problems Does C# Interop Create ?
The Interop approach presents several significant challenges:
Sadly, Stack Overflow and other programming websites continue to recommend it because they are locked in time with threads locked to Ideas 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 Limitation Details : This old-fashioned solution only works on Windows with Office installed. And can be incredibly slow - confusing for the user and can lead to memory leaks. No support for Linux, macOS, Docker containers, or cloud platforms like Azure Functions.
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 ProgrammaticallyWithout 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:
How to create an XLSX ore XLS pivot table programmatically the modern way:
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");
}
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
So that, in 2025, is how you create pivot tables.
Output
How to Create Dynamic Summaries with IronXL Formulas?
For scenarios requiring dynamic updates similar to create a pivot table, calculate pivot table refresh functionality, IronXL can leverage Excel's built-in formulas to get your answers. Generally, this is preferable to the preceding answer - Your record is being handled in a much more modern and elegant way, With code, a person can understand easily. Easy to set up, Without the need to contact support or read a manual.
// 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
IRON VB CONVERTER ERROR developers@ironsoftware.com
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.
If we apply this code to the example Excel file (XLS / XLSX) from the previous example, we would get this output of fields To the page :
Comparing 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 |
Which Approach Should You Choose?
Choose C# Interop when:
- Native Excel pivot table objects are absolutely required
- Working exclusively on Windows with Office installed on every target machine
- Only deploying to desktop systems that you administer
- Existing legacy code depends on Interop
- Using Legacy .NET Framework Version
Choose IronXL when:
- Deploying to servers or cloud environments and services (Azure, AWS ...)
- Building cross-platform applications
- Requiring better performance and reliability
- Avoiding Office licensing costs
- Needing simpler, maintainable code
- Supporting Mac, iOS, Android and/or Linux System
- Working in modern .NET Framework Core and .NET 5,6,7,8,9,10
- You want full access in your development console / IDE to to configure the settings for each field in the pivot table programmatically from your data ranges.
Conclusion
While C# Excel Interop can develop native pivot tables, its deployment restrictions and complexity make it increasingly impractical for modern applications. IronXL provides powerful alternatives through data aggregation and formula-based summaries, eliminating Office dependencies while maintaining analytical capabilities. Please vote, comment or subscribe If this has been helpful.
For developers searching for and alternative option for 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.
Ready to modernize your Excel automation and create pivot table code of your own in modern C#?
IronXL can by implemented in your C# applications in seconds through the NuGet Package Manager. Try the free trial to eliminate Interop dependencies in your production applications.