Ícono de mensaje
Saltar al pie de página
  1. IronXL
  2. Blog de IronXL
  3. Usando IronXL
  4. Crear una tabla dinámica de Excel en C#
USANDO IRONXL

Cómo crear una tabla dinámica de Excel en C#

Jordi Bardia
Jordi Bardia
Publicado:

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 pivot table in Excel using C# Interop or choose a better alternative.

Understanding the Two Approaches

What is Excel Interop?

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.

What is IronXL?

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.

Setting Up Your Environment

For Excel Interop

Install-Package Microsoft.Office.Interop.Excel

For IronXL

Install-Package IronXL.Excel

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.

Creating an Excel Pivot Table Programmatically with C# Interop

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
    }
}
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
    }
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

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

The IronXL Alternative Approach

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");
    }
}
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");
    }
}
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

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

Output

How to Create an Excel Pivot Table in C#: Figure 6 - IronXL Output

How to Create an Excel Pivot Table in C#: Figure 7 - Summary Output

Key Differences and Considerations

Deployment Requirements

Excel Interop requires:

  • Microsoft Excel installation with a valid license
  • Windows operating system
  • Proper COM permissions and settings

  • Server configuration for Office automation IronXL requires:

  • Only the IronXL library package
  • Works on any platform that supports .NET
  • No Office installation or license needed
  • Simplified deployment process

How to Create an Excel Pivot Table in C#: Figure 8 - Features

Code Quality and Maintenance

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.

Error Handling

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.

Best Practices and Recommendations

Choose Excel Interop when:

  • You need exact Excel pivot table features with all formatting options
  • Excel is guaranteed to be available on the system
  • Working only on Windows desktop applications

  • Legacy code requirements Choose IronXL when:

  • Building server applications or web solutions
  • Requiring cross-platform compatibility
  • Needing reliable performance without COM overhead
  • Deploying to containers or cloud environments

Visit IronXL documentation to learn more details about implementation. For questions or support, contact the Iron Software team.

Conclusion

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.

How to Create an Excel Pivot Table in C#: Figure 9 - Licensing

Preguntas Frecuentes

¿Cuál es la ventaja de usar IronXL sobre Excel Interop para crear tablas dinámicas?

IronXL ofrece ventajas significativas sobre Excel Interop, incluyendo facilidad de uso, mejor rendimiento y la capacidad de crear tablas dinámicas sin necesidad de tener Excel instalado en el servidor.

¿Puedo crear una tabla dinámica de Excel en C# sin usar Excel Interop?

Sí, puedes crear una tabla dinámica de Excel en C# utilizando IronXL, que ofrece una alternativa moderna y eficiente a Excel Interop.

¿Es necesario tener instalado Microsoft Excel para usar IronXL?

No, IronXL no requiere que Microsoft Excel esté instalado en tu sistema, lo que lo convierte en una solución flexible para crear y gestionar archivos de Excel.

¿Cuáles son los pasos para crear una tabla dinámica en Excel usando IronXL?

Para crear una tabla dinámica usando IronXL, primero carga tu archivo de Excel, especifica el rango de datos, define los campos de tu tabla dinámica y luego genera la tabla dinámica. La completa API de IronXL hace que este proceso sea sencillo.

¿IronXL admite otras funcionalidades de Excel además de las tablas dinámicas?

Sí, IronXL admite una amplia gama de funcionalidades de Excel, incluyendo la lectura y escritura de archivos de Excel, el formato de celdas y la realización de cálculos, entre otros.

¿Cómo maneja IronXL grandes conjuntos de datos al crear tablas dinámicas?

IronXL está diseñado para manejar eficientemente grandes conjuntos de datos, asegurando la creación rápida y confiable de tablas dinámicas incluso con datos extensos.

¿Puede usarse IronXL en aplicaciones en la nube?

Sí, IronXL puede integrarse en aplicaciones en la nube, proporcionando una solución perfecta para gestionar archivos de Excel en la nube.

¿Qué lenguajes de programación son compatibles con IronXL para crear tablas dinámicas?

IronXL admite principalmente C#, facilitando la creación de tablas dinámicas y otras operaciones de Excel dentro de aplicaciones .NET.

¿Existen tutoriales disponibles para aprender a usar IronXL?

Sí, Iron Software proporciona documentación completa y tutoriales en su sitio web para ayudar a los usuarios a aprender a usar IronXL de manera efectiva.

¿Cuáles son las opciones de licencia disponibles para IronXL?

IronXL ofrece varias opciones de licencia, incluyendo niveles gratuitos y de pago, para acomodar diferentes necesidades y escalas de proyectos.

Jordi Bardia
Jordi Bardia
  Chatea con el equipo de ingeniería ahora
Ingeniero de Software
Jordi es más competente en Python, C# y C++. Cuando no está aprovechando sus habilidades en Iron Software, está programando juegos. Compartiendo responsabilidades para pruebas de productos, desarrollo de productos e investigación, Jordi agrega un valor inmenso a la mejora continua del producto. La experiencia variada lo mantiene ...
Leer más

Artículos Relacionados

Prueba de 30 días → Producto completo. Sin límites. Sin tarjeta. Comenzar prueba gratuita