Working with an Excel pivot table programmatically is a common requirement in business applications that need to analyze and calculate source data. While Microsoft's Excel Interop has been the traditional method to create a pivot table in an Excel file, modern solutions like IronXL offer significant advantages. This guide details both methods with practical examples to help you create a pivot table in Excel using C# Interop or choose a better alternative.
Excel Interop uses COM (Component Object Model) to control Microsoft Excel directly through C#. It requires Office to be installed on the system and essentially automates Excel as if a user were interacting with the application. Each worksheet, workbook, and cell becomes an object you can manipulate through code.
IronXL is a standalone .NET library that can read, edit, and create Excel files without requiring Microsoft Office. It works across Windows, Linux, macOS, and Docker containers, making it ideal for modern deployment scenarios. You can open, save, and export data without the overhead of COM interop.
Alternatively, use the NuGet Package Manager UI by searching for "IronXL.Excel" and clicking install. You can also install via the .NET CLI with command args or reference it directly from GitHub.
Both libraries are available through NuGet. Note that Excel Interop requires a full Microsoft Office installation, while IronXL operates independently. Before proceeding, ensure your system meets the requirements.
Here's a complete example showing how to create a pivot table programmatically using the traditional Interop approach:
using Excel = Microsoft.Office.Interop.Excel;
class Program
{
static void Main(string[] args)
{
// Create Excel application instance
var excelApp = new Excel.Application();
var workbook = excelApp.Workbooks.Add();
var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
// Add header row and sample data
dataSheet.Cells[1, 1] = "Product";
dataSheet.Cells[1, 2] = "Region";
dataSheet.Cells[1, 3] = "Sales";
// ... populate data rows with values
// Add sample data rows
dataSheet.Cells[2, 1] = "Laptop";
dataSheet.Cells[2, 2] = "North";
dataSheet.Cells[2, 3] = 1200;
dataSheet.Cells[3, 1] = "Laptop";
dataSheet.Cells[3, 2] = "South";
dataSheet.Cells[3, 3] = 1500;
dataSheet.Cells[4, 1] = "Phone";
dataSheet.Cells[4, 2] = "North";
dataSheet.Cells[4, 3] = 800;
dataSheet.Cells[5, 1] = "Phone";
dataSheet.Cells[5, 2] = "South";
dataSheet.Cells[5, 3] = 950;
dataSheet.Cells[6, 1] = "Tablet";
dataSheet.Cells[6, 2] = "East";
dataSheet.Cells[6, 3] = 600;
dataSheet.Cells[7, 1] = "Tablet";
dataSheet.Cells[7, 2] = "West";
dataSheet.Cells[7, 3] = 750;
dataSheet.Cells[8, 1] = "Monitor";
dataSheet.Cells[8, 2] = "North";
dataSheet.Cells[8, 3] = 400;
dataSheet.Cells[9, 1] = "Monitor";
dataSheet.Cells[9, 2] = "South";
dataSheet.Cells[9, 3] = 500;
dataSheet.Cells[10, 1] = "Keyboard";
dataSheet.Cells[10, 2] = "East";
dataSheet.Cells[10, 3] = 300;
// Create pivot cache from source data range
Excel.Range dataRange = dataSheet.Range["A1:C10"];
Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
Excel.XlPivotTableSourceType.xlDatabase, dataRange);
// Create PivotTable at specific location
Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
Excel.PivotTable pivotTable = pivotTables.Add(
pivotCache, pivotSheet.Range["A3"], "SalesPivot");
// Configure pivot table fields - row and column headers
((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;
// Configure grand totals and formatting
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
// Save the Excel file
workbook.SaveAs("pivot_interop.xlsx");
workbook.Close();
excelApp.Quit();
// Critical: Release COM objects to avoid errors
#if WINDOWS
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotSheet);
Marshal.ReleaseComObject(dataSheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);
#endif
}
}Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Class Program
Shared Sub Main(ByVal args() As String)
' Create Excel application instance
Dim excelApp As New Excel.Application()
Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()
Dim dataSheet As Excel.Worksheet = CType(workbook.Worksheets(1), Excel.Worksheet)
Dim pivotSheet As Excel.Worksheet = CType(workbook.Worksheets.Add(), Excel.Worksheet)
' Add header row and sample data
dataSheet.Cells(1, 1) = "Product"
dataSheet.Cells(1, 2) = "Region"
dataSheet.Cells(1, 3) = "Sales"
' ... populate data rows with values
' Add sample data rows
dataSheet.Cells(2, 1) = "Laptop"
dataSheet.Cells(2, 2) = "North"
dataSheet.Cells(2, 3) = 1200
dataSheet.Cells(3, 1) = "Laptop"
dataSheet.Cells(3, 2) = "South"
dataSheet.Cells(3, 3) = 1500
dataSheet.Cells(4, 1) = "Phone"
dataSheet.Cells(4, 2) = "North"
dataSheet.Cells(4, 3) = 800
dataSheet.Cells(5, 1) = "Phone"
dataSheet.Cells(5, 2) = "South"
dataSheet.Cells(5, 3) = 950
dataSheet.Cells(6, 1) = "Tablet"
dataSheet.Cells(6, 2) = "East"
dataSheet.Cells(6, 3) = 600
dataSheet.Cells(7, 1) = "Tablet"
dataSheet.Cells(7, 2) = "West"
dataSheet.Cells(7, 3) = 750
dataSheet.Cells(8, 1) = "Monitor"
dataSheet.Cells(8, 2) = "North"
dataSheet.Cells(8, 3) = 400
dataSheet.Cells(9, 1) = "Monitor"
dataSheet.Cells(9, 2) = "South"
dataSheet.Cells(9, 3) = 500
dataSheet.Cells(10, 1) = "Keyboard"
dataSheet.Cells(10, 2) = "East"
dataSheet.Cells(10, 3) = 300
' Create pivot cache from source data range
Dim dataRange As Excel.Range = dataSheet.Range("A1:C10")
Dim pivotCache As Excel.PivotCache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange)
' Create PivotTable at specific location
Dim pivotTables As Excel.PivotTables = CType(pivotSheet.PivotTables(), Excel.PivotTables)
Dim pivotTable As Excel.PivotTable = pivotTables.Add(pivotCache, pivotSheet.Range("A3"), "SalesPivot")
' Configure pivot table fields - row and column headers
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
' Configure grand totals and formatting
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True
' Save the Excel file
workbook.SaveAs("pivot_interop.xlsx")
workbook.Close()
excelApp.Quit()
' Critical: Release COM objects to avoid errors
#If WINDOWS Then
Marshal.ReleaseComObject(pivotTable)
Marshal.ReleaseComObject(pivotSheet)
Marshal.ReleaseComObject(dataSheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(excelApp)
#End If
End Sub
End ClassThis code creates an Excel application, adds a worksheet with source data including a header row, establishes a pivot cache, builds the PivotTable object, and configures the field orientation. The cleanup section is critical - failure to release COM objects causes memory leaks. Each cell, range, and worksheet must be properly disposed of to avoid runtime errors.
IronXL takes a different approach by working directly with the Excel file format. Here's how to achieve similar analysis results programmatically:
using IronXL;
using System.Linq;
class Program
{
static void Main(string[] args)
{
// Create workbook and add worksheet with data
WorkBook workbook = WorkBook.Create();
WorkSheet sheet = workbook.CreateWorkSheet("Data");
// Add header row to define column structure
sheet["A1"].Value = "Product";
sheet["B1"].Value = "Region";
sheet["C1"].Value = "Sales";
// Add sample data to cells
sheet["A2"].Value = "Widget";
sheet["B2"].Value = "North";
sheet["C2"].Value = 1500;
// ... continue to add more data rows
sheet["A3"].Value = "Laptop";
sheet["B3"].Value = "South";
sheet["C3"].Value = 1500;
sheet["A4"].Value = "Phone";
sheet["B4"].Value = "North";
sheet["C4"].Value = 800;
sheet["A5"].Value = "Phone";
sheet["B5"].Value = "South";
sheet["C5"].Value = 950;
sheet["A6"].Value = "Tablet";
sheet["B6"].Value = "East";
sheet["C6"].Value = 600;
sheet["A7"].Value = "Tablet";
sheet["B7"].Value = "West";
sheet["C7"].Value = 750;
sheet["A8"].Value = "Monitor";
sheet["B8"].Value = "North";
sheet["C8"].Value = 400;
sheet["A9"].Value = "Monitor";
sheet["B9"].Value = "South";
sheet["C9"].Value = 500;
sheet["A10"].Value = "Keyboard";
sheet["B10"].Value = "East";
sheet["C10"].Value = 300;
// Create summary analysis worksheet
var summarySheet = workbook.CreateWorkSheet("Summary");
// Group and calculate aggregated data
var data = sheet["A1:C10"].ToDataTable(true);
var productSummary = data.AsEnumerable()
.GroupBy(row => row.Field<string>("Product"))
.Select((group, index) => new {
Product = group.Key,
TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
Count = group.Count(),
RowIndex = index + 2
});
// Write column headers for summary
summarySheet["A1"].Value = "Product Summary";
summarySheet["A2"].Value = "Product";
summarySheet["B2"].Value = "Total Sales";
summarySheet["C2"].Value = "Count";
// Export results to cells
foreach (var item in productSummary)
{
summarySheet[$"A{item.RowIndex + 1}"].Value = item.Product;
summarySheet[$"B{item.RowIndex + 1}"].Value = item.TotalSales;
summarySheet[$"C{item.RowIndex + 1}"].Value = item.Count;
}
// Apply number formatting and style
summarySheet["B:B"].FormatString = "$#,##0.00";
// Save the xlsx file
workbook.SaveAs("analysis_ironxl.xlsx");
}
}Imports IronXL
Imports System.Linq
Class Program
Shared Sub Main(args As String())
' Create workbook and add worksheet with data
Dim workbook As WorkBook = WorkBook.Create()
Dim sheet As WorkSheet = workbook.CreateWorkSheet("Data")
' Add header row to define column structure
sheet("A1").Value = "Product"
sheet("B1").Value = "Region"
sheet("C1").Value = "Sales"
' Add sample data to cells
sheet("A2").Value = "Widget"
sheet("B2").Value = "North"
sheet("C2").Value = 1500
' ... continue to add more data rows
sheet("A3").Value = "Laptop"
sheet("B3").Value = "South"
sheet("C3").Value = 1500
sheet("A4").Value = "Phone"
sheet("B4").Value = "North"
sheet("C4").Value = 800
sheet("A5").Value = "Phone"
sheet("B5").Value = "South"
sheet("C5").Value = 950
sheet("A6").Value = "Tablet"
sheet("B6").Value = "East"
sheet("C6").Value = 600
sheet("A7").Value = "Tablet"
sheet("B7").Value = "West"
sheet("C7").Value = 750
sheet("A8").Value = "Monitor"
sheet("B8").Value = "North"
sheet("C8").Value = 400
sheet("A9").Value = "Monitor"
sheet("B9").Value = "South"
sheet("C9").Value = 500
sheet("A10").Value = "Keyboard"
sheet("B10").Value = "East"
sheet("C10").Value = 300
' Create summary analysis worksheet
Dim summarySheet = workbook.CreateWorkSheet("Summary")
' Group and calculate aggregated data
Dim data = sheet("A1:C10").ToDataTable(True)
Dim productSummary = data.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of String)("Product")) _
.Select(Function(group, index) New With {
.Product = group.Key,
.TotalSales = group.Sum(Function(r) Convert.ToDecimal(r("Sales"))),
.Count = group.Count(),
.RowIndex = index + 2
})
' Write column headers for summary
summarySheet("A1").Value = "Product Summary"
summarySheet("A2").Value = "Product"
summarySheet("B2").Value = "Total Sales"
summarySheet("C2").Value = "Count"
' Export results to cells
For Each item In productSummary
summarySheet($"A{item.RowIndex + 1}").Value = item.Product
summarySheet($"B{item.RowIndex + 1}").Value = item.TotalSales
summarySheet($"C{item.RowIndex + 1}").Value = item.Count
Next
' Apply number formatting and style
summarySheet("B:B").FormatString = "$#,##0.00"
' Save the xlsx file
workbook.SaveAs("analysis_ironxl.xlsx")
End Sub
End ClassThis IronXL example demonstrates how to create a workbook, add worksheets, populate cells with data, and perform aggregation analysis. The code groups data by product and calculates totals and counts, creating a summary report. No COM objects need management, and the methods are straightforward .NET collections that automatically handle memory.
Excel Interop requires:
Interop involves managing COM objects carefully to avoid memory leaks and errors. Every Excel object created must be explicitly released using the correct methods. IronXL uses standard .NET objects with automatic garbage collection, reducing the risk of resource issues.
With Interop, errors often relate to Excel availability, version differences, or COM failures. IronXL errors are standard .NET exceptions, making debugging more straightforward. You can rely on familiar try-catch patterns without worrying about COM-specific issues.
Choose Excel Interop when:
Visit IronXL documentation to learn more details about implementation. For questions or support, contact the Iron Software team.
While C# Interop provides direct access to create pivot table functionality in Excel, it comes with deployment limitations and complexity. IronXL offers a modern alternative that simplifies Excel file manipulation while providing the flexibility to run anywhere .NET is supported.
For developers building new applications or modernizing existing solutions, IronXL's approach eliminates COM Interop overhead while providing powerful data manipulation capabilities. Whether you need to read, edit, or export Excel data, IronXL delivers a cleaner solution.
Get started with IronXL's free trial to experience the difference, or explore tutorials to see more examples. Ready to deploy? View licensing options to choose the right package for your task.
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.
