使用IronXL建立Excel樞紐分析表的優勢是什麼？

IronXL允許您在建立Excel樞紐分析表時無需Office依賴性，這使其成為比傳統C# Interop方法更為簡化與高效的解決方案。

IronXL如何處理樞紐分析表的資料操作？

IronXL提供強大的資料操作能力，使建立樞紐報告不會有Excel Interop相關的複雜性。

IronXL可獨立於Excel Interop使用嗎？

是的，IronXL能夠獨立運作，讓開發者無需依賴Excel Interop和其相關依賴性即可生成樞紐分析表。

為什麼開發者偏好使用IronXL而非傳統的Interop方法來處理Excel？

開發者偏好使用IronXL，因其在建立樞紐分析表時不需Office依賴性，這是傳統Interop方法所需要的。

使用IronXL是否需要安裝Microsoft Office？

不，IronXL不需要安裝Microsoft Office，因為它獨立於Office運作，與需要Office依賴性的C# Interop不同。

IronXL是否與現代C#程式設計相容？

是的，IronXL設計上能夠無縫整合至現代C#程式設計中，提供處理Excel資料操作任務的現代方法。

How does IronXL support modern .NET development?

IronXL is compatible with modern .NET versions, including .NET Core and .NET 5+. It supports cross-platform development, which is essential for creating solutions that run on different operating systems and cloud services.

What kind of security features does IronXL offer?

IronXL includes security features such as workbook encryption and worksheet protection. These features help secure Excel files generated in your applications, an essential consideration for sensitive data handling.

Is IronXL a better choice for resource-constrained environments compared to Interop?

Yes, IronXL is more suited for resource-constrained environments as it does not require heavy Office installations or complex COM object handling, using only around 50MB for its library and the .NET runtime for execution.

Can IronXL export datasets to various file formats besides Excel?

IronXL supports exporting data to multiple file formats, such as CSV, JSON, and XML, which helps in integrating Excel automation with different systems and preserving data in the desired formats.

C# Excel 程式庫

Explore IronXL C# .NET 的 Excel 試算表程式庫開始免費試用

無需 Interop 即可讀取 Excel 檔案

查看所有程式碼範例

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

PM >Install-Package IronXL.Excel

Install-Package IronXL.Excel

USING IRONXL

如何在不使用 Office 依賴項的情況下，使用 C# 在 Excel 中建立資料透視表？

Curtis Chau

Updated: 2026年1月5日

用C#程式生成樞紐分析表通常需要Office Interop和複雜的COM管理，但現代程式庫如IronXL使得在無需安裝Office下可生成跨平台的樞紐分析表，支援Docker容器和雲端部署，並消除記憶體洩漏。

程式生成樞紐分析表需要C# Interop及其Office依賴，或者像IronXL這樣獨立運行的現代程式庫。本教程展示了兩種方法，突出說明為何開發者越來越選擇IronXL的容器友好解決方案而不是傳統方法。

在本文中，我們將學習如何編輯、建立、設計和計算樞紐分析表，實現自動分析和錯誤處理。無論您是在部署到AWS還是運行在Azure，本指南涵蓋了Excel自動化的現代方法。

什麼是Excel樞紐分析表？

為什麼樞紐分析表對於資料分析很重要？

樞紐分析表是Excel中最強大的工具之一，可以用來總結大型資料集。它提供了一種簡單的方式來顯示、理解和分析資料。樞紐分析表不僅在Excel中可用，在Google Sheets、Apple Numbers和CSV匯出中也可使用。它們通過建立連結到基礎資訊的互動摘要，將原始資料轉換為有意義的見解。

對於使用C#中的Excel公式的開發者來說，樞紐分析表提供了關鍵的聚合功能。與基本數學函式只在單個單元格上運作不同，樞紐分析表可以在整個資料集上聚合Excel函式。

什麼時候應使用樞紐分析表而非常規報告？

讓我們來探索如何錯誤地建立樞紐分析表，然後學習在C#中正確的方法：

如何使用C# Interop在Excel中建立樞紐分析表？

為何在有限制的情況下仍然使用Interop？

C# Excel Interop通過COM自動化提供了對Excel樞紐分析表功能的直接存取。這是許多開發者在尋找C#生成樞紐分析表時發現的傳統方法：（已棄用）

如何以老式方式在.NET中建立樞紐分析表

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

// Create Excel application instance - requires Office installation
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;

// Create pivot cache and pivot table - COM objects require explicit cleanup
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);

// Configure pivot table fields - traditional row/column/data setup
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 - critical for preventing memory leaks
xlWorkbook.SaveAs(@"C:\Data\PivotReport.xlsx");
xlWorkbook.Close();
xlApp.Quit();

// Release COM objects to prevent memory leaks - must release in reverse order
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 - requires Office installation
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;

// Create pivot cache and pivot table - COM objects require explicit cleanup
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);

// Configure pivot table fields - traditional row/column/data setup
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 - critical for preventing memory leaks
xlWorkbook.SaveAs(@"C:\Data\PivotReport.xlsx");
xlWorkbook.Close();
xlApp.Quit();

// Release COM objects to prevent memory leaks - must release in reverse order
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(xlPivotSheet);
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

' Create Excel application instance - requires Office installation
Dim xlApp As New Excel.Application()
Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Open("C:\Data\SalesData.xlsx")
Dim xlSheet As Excel.Worksheet = CType(xlWorkbook.Sheets(1), Excel.Worksheet)
Dim xlPivotSheet As Excel.Worksheet = CType(xlWorkbook.Sheets.Add(), Excel.Worksheet)

' Define data range for pivot table
Dim dataRange As Excel.Range = xlSheet.UsedRange

' Create pivot cache and pivot table - COM objects require explicit cleanup
Dim pivotCache As Excel.PivotCache = xlWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange, Type.Missing)
Dim pivotTable As Excel.PivotTable = pivotCache.CreatePivotTable(xlPivotSheet.Cells(3, 1), "SalesPivot", Type.Missing, Type.Missing)

' Configure pivot table fields - traditional row/column/data setup
Dim productField As Excel.PivotField = CType(pivotTable.PivotFields("Product"), Excel.PivotField)
productField.Orientation = Excel.XlPivotFieldOrientation.xlRowField
productField.Position = 1
Dim regionField As Excel.PivotField = CType(pivotTable.PivotFields("Region"), Excel.PivotField)
regionField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
regionField.Position = 1
Dim salesField As Excel.PivotField = CType(pivotTable.PivotFields("Sales"), Excel.PivotField)
pivotTable.AddDataField(salesField, "Sum of Sales", Excel.XlConsolidationFunction.xlSum)

' Save and cleanup - critical for preventing memory leaks
xlWorkbook.SaveAs("C:\Data\PivotReport.xlsx")
xlWorkbook.Close()
xlApp.Quit()

' Release COM objects to prevent memory leaks - must release in reverse order
Marshal.ReleaseComObject(pivotTable)
Marshal.ReleaseComObject(pivotCache)
Marshal.ReleaseComObject(xlPivotSheet)
Marshal.ReleaseComObject(xlSheet)
Marshal.ReleaseComObject(xlWorkbook)
Marshal.ReleaseComObject(xlApp)

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

' Create Excel application instance - requires Office installation
Dim xlApp As New Excel.Application()
Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Open("C:\Data\SalesData.xlsx")
Dim xlSheet As Excel.Worksheet = CType(xlWorkbook.Sheets(1), Excel.Worksheet)
Dim xlPivotSheet As Excel.Worksheet = CType(xlWorkbook.Sheets.Add(), Excel.Worksheet)

' Define data range for pivot table
Dim dataRange As Excel.Range = xlSheet.UsedRange

' Create pivot cache and pivot table - COM objects require explicit cleanup
Dim pivotCache As Excel.PivotCache = xlWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange, Type.Missing)
Dim pivotTable As Excel.PivotTable = pivotCache.CreatePivotTable(xlPivotSheet.Cells(3, 1), "SalesPivot", Type.Missing, Type.Missing)

' Configure pivot table fields - traditional row/column/data setup
Dim productField As Excel.PivotField = CType(pivotTable.PivotFields("Product"), Excel.PivotField)
productField.Orientation = Excel.XlPivotFieldOrientation.xlRowField
productField.Position = 1
Dim regionField As Excel.PivotField = CType(pivotTable.PivotFields("Region"), Excel.PivotField)
regionField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
regionField.Position = 1
Dim salesField As Excel.PivotField = CType(pivotTable.PivotFields("Sales"), Excel.PivotField)
pivotTable.AddDataField(salesField, "Sum of Sales", Excel.XlConsolidationFunction.xlSum)

' Save and cleanup - critical for preventing memory leaks
xlWorkbook.SaveAs("C:\Data\PivotReport.xlsx")
xlWorkbook.Close()
xlApp.Quit()

' Release COM objects to prevent memory leaks - must release in reverse order
Marshal.ReleaseComObject(pivotTable)
Marshal.ReleaseComObject(pivotCache)
Marshal.ReleaseComObject(xlPivotSheet)
Marshal.ReleaseComObject(xlSheet)
Marshal.ReleaseComObject(xlWorkbook)
Marshal.ReleaseComObject(xlApp)

此Interop範例建立了一個本地Excel樞紐分析表，將產品作為行，地區作為列，銷售額在資料區域中進行匯總。雖然功能齊全，但此方法需要安裝Microsoft Office並仔細管理COM物件。微軟自己的文件現在不建議這種方法用於現代開發。對於容器化部署，不使用Interop的Excel工作是不可或缺的。

如果COM物件未正確釋放會發生什麼？

C# Interop會產生哪些問題？

為何Interop在容器化環境中失敗？

Interop方法為現代DevOps實踐和Docker設置帶來了幾個顯著挑戰：

**部署依賴：**需要在運行源程式碼的每台機器上安裝Microsoft Office，包括生產伺服器。這增加了許可成本和部署複雜性。

**記憶體管理：**必須使用Marshal.ReleaseComObject()明確釋放COM物件。即使漏掉一個物件也會導致Excel進程駐留在記憶體中，如在Stack Overflow上有詳細文件。

**平台限制詳情：**這種老式解決方案僅在安裝了Office的Windows上運行，而且可能極慢、難以理解，並且導致記憶體洩漏。不支援Linux、macOS、Docker容器或Azure Functions等雲端平台。

**性能問題：**啟動Excel應用程式實例既慢又資源密集，特別是伺服器端處理。

**版本相容性：**不同的Office版本可能有不同的COM接口，在環境間會導致相容性問題。

IronXL如何在不使用Interop的情況下程式化建立樞紐分析表？

IronXL以不同方式處理樞紐分析表建立，使用受控碼而無需COM依賴。雖然它不建立原生Excel樞紐分析表，但它提供了強大的聚合功能。

如何以現代方式程式生成XLSX或XLS樞紐分析表

using IronXL;
using System.Linq;
using System.Data; // Essential for DataTable manipulation
using static System.Data.DataTableExtensions; // Extension methods for LINQ queries

class Program
{
    static void Main(string[] args)
    {
        // Load Excel file - works on all platforms without Office
        WorkBook workbook = WorkBook.Load("SalesData.xlsx");
        WorkSheet dataSheet = workbook.WorkSheets[0];
        
        // Convert to DataTable for powerful manipulation - maintains data types
        var dataTable = dataSheet.ToDataTable(true); // true = use first row as column headers
        
        // Create pivot-style aggregation using LINQ - no COM objects needed
        var pivotData = dataTable.AsEnumerable()
            .GroupBy(row => new {
                Product = row["Product"].ToString(),
                Region = row["Region"].ToString()
            }) // Group by multiple dimensions
            .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 - no Excel process started
        WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");
        
        // Build cross-tabulation structure programmatically
        var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p);
        var regions = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r);
        
        // Create headers with formatting options
        pivotSheet["A1"].Value = "Product/Region";
        int col = 2;
        foreach (var region in regions)
        {
            pivotSheet[$"{(char)('A' + col - 1)}1"].Value = region; // Dynamic column addressing
            col++;
        }
        
        // Populate pivot data - memory efficient for large datasets
        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 - maintains live calculations
        pivotSheet[$"A{row}"].Value = "Total"; // Grand totals row
        for (int c = 2; c <= regions.Count() + 1; c++)
        {
            // Formula references ensure dynamic updates
            pivotSheet[$"{(char)('A' + c - 1)}{row}"].Formula = 
                $"=SUM({(char)('A' + c - 1)}2:{(char)('A' + c - 1)}{row - 1})";
        }
        
        // Apply professional formatting - currency format for sales data
        var dataRange = pivotSheet[$"B2:{(char)('A' + regions.Count())}{row}"];
        dataRange.FormatString = "$#,##0.00";
        
        // Save without Office dependencies - works in containers
        workbook.SaveAs("PivotReport.xlsx");
    }
}

using IronXL;
using System.Linq;
using System.Data; // Essential for DataTable manipulation
using static System.Data.DataTableExtensions; // Extension methods for LINQ queries

class Program
{
    static void Main(string[] args)
    {
        // Load Excel file - works on all platforms without Office
        WorkBook workbook = WorkBook.Load("SalesData.xlsx");
        WorkSheet dataSheet = workbook.WorkSheets[0];
        
        // Convert to DataTable for powerful manipulation - maintains data types
        var dataTable = dataSheet.ToDataTable(true); // true = use first row as column headers
        
        // Create pivot-style aggregation using LINQ - no COM objects needed
        var pivotData = dataTable.AsEnumerable()
            .GroupBy(row => new {
                Product = row["Product"].ToString(),
                Region = row["Region"].ToString()
            }) // Group by multiple dimensions
            .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 - no Excel process started
        WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");
        
        // Build cross-tabulation structure programmatically
        var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p);
        var regions = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r);
        
        // Create headers with formatting options
        pivotSheet["A1"].Value = "Product/Region";
        int col = 2;
        foreach (var region in regions)
        {
            pivotSheet[$"{(char)('A' + col - 1)}1"].Value = region; // Dynamic column addressing
            col++;
        }
        
        // Populate pivot data - memory efficient for large datasets
        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 - maintains live calculations
        pivotSheet[$"A{row}"].Value = "Total"; // Grand totals row
        for (int c = 2; c <= regions.Count() + 1; c++)
        {
            // Formula references ensure dynamic updates
            pivotSheet[$"{(char)('A' + c - 1)}{row}"].Formula = 
                $"=SUM({(char)('A' + c - 1)}2:{(char)('A' + c - 1)}{row - 1})";
        }
        
        // Apply professional formatting - currency format for sales data
        var dataRange = pivotSheet[$"B2:{(char)('A' + regions.Count())}{row}"];
        dataRange.FormatString = "$#,##0.00";
        
        // Save without Office dependencies - works in containers
        workbook.SaveAs("PivotReport.xlsx");
    }
}

Imports IronXL
Imports System.Linq
Imports System.Data ' Essential for DataTable manipulation
Imports System.Data.DataTableExtensions ' Extension methods for LINQ queries

Class Program
    Shared Sub Main(args As String())
        ' Load Excel file - works on all platforms without Office
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
        Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
        
        ' Convert to DataTable for powerful manipulation - maintains data types
        Dim dataTable = dataSheet.ToDataTable(True) ' true = use first row as column headers
        
        ' Create pivot-style aggregation using LINQ - no COM objects needed
        Dim pivotData = dataTable.AsEnumerable() _
            .GroupBy(Function(row) New With {
                Key .Product = row("Product").ToString(),
                Key .Region = row("Region").ToString()
            }) ' Group by multiple dimensions
            .Select(Function(g) New With {
                .Product = g.Key.Product,
                .Region = g.Key.Region,
                .TotalSales = g.Sum(Function(row) Convert.ToDecimal(row("Sales"))),
                .AverageSale = g.Average(Function(row) Convert.ToDecimal(row("Sales"))),
                .Count = g.Count()
            })
        
        ' Create pivot report worksheet - no Excel process started
        Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")
        
        ' Build cross-tabulation structure programmatically
        Dim products = pivotData.Select(Function(p) p.Product).Distinct().OrderBy(Function(p) p)
        Dim regions = pivotData.Select(Function(p) p.Region).Distinct().OrderBy(Function(r) r)
        
        ' Create headers with formatting options
        pivotSheet("A1").Value = "Product/Region"
        Dim col As Integer = 2
        For Each region In regions
            pivotSheet($"{ChrW(AscW("A"c) + col - 1)}1").Value = region ' Dynamic column addressing
            col += 1
        Next
        
        ' Populate pivot data - memory efficient for large datasets
        Dim row As Integer = 2
        For Each product In products
            pivotSheet($"A{row}").Value = product
            col = 2
            For Each region In regions
                Dim sales = pivotData _
                    .Where(Function(p) p.Product = product AndAlso p.Region = region) _
                    .Select(Function(p) p.TotalSales) _
                    .FirstOrDefault()
                pivotSheet($"{ChrW(AscW("A"c) + col - 1)}{row}").Value = sales
                col += 1
            Next
            row += 1
        Next
        
        ' Add totals using Excel formulas - maintains live calculations
        pivotSheet($"A{row}").Value = "Total" ' Grand totals row
        For c As Integer = 2 To regions.Count() + 1
            ' Formula references ensure dynamic updates
            pivotSheet($"{ChrW(AscW("A"c) + c - 1)}{row}").Formula = 
                $"=SUM({ChrW(AscW("A"c) + c - 1)}2:{ChrW(AscW("A"c) + c - 1)}{row - 1})"
        Next
        
        ' Apply professional formatting - currency format for sales data
        Dim dataRange = pivotSheet($"B2:{ChrW(AscW("A"c) + regions.Count())}{row}")
        dataRange.FormatString = "$#,##0.00"
        
        ' Save without Office dependencies - works in containers
        workbook.SaveAs("PivotReport.xlsx")
    End Sub
End Class

Imports IronXL
Imports System.Linq
Imports System.Data ' Essential for DataTable manipulation
Imports System.Data.DataTableExtensions ' Extension methods for LINQ queries

Class Program
    Shared Sub Main(args As String())
        ' Load Excel file - works on all platforms without Office
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
        Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
        
        ' Convert to DataTable for powerful manipulation - maintains data types
        Dim dataTable = dataSheet.ToDataTable(True) ' true = use first row as column headers
        
        ' Create pivot-style aggregation using LINQ - no COM objects needed
        Dim pivotData = dataTable.AsEnumerable() _
            .GroupBy(Function(row) New With {
                Key .Product = row("Product").ToString(),
                Key .Region = row("Region").ToString()
            }) ' Group by multiple dimensions
            .Select(Function(g) New With {
                .Product = g.Key.Product,
                .Region = g.Key.Region,
                .TotalSales = g.Sum(Function(row) Convert.ToDecimal(row("Sales"))),
                .AverageSale = g.Average(Function(row) Convert.ToDecimal(row("Sales"))),
                .Count = g.Count()
            })
        
        ' Create pivot report worksheet - no Excel process started
        Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")
        
        ' Build cross-tabulation structure programmatically
        Dim products = pivotData.Select(Function(p) p.Product).Distinct().OrderBy(Function(p) p)
        Dim regions = pivotData.Select(Function(p) p.Region).Distinct().OrderBy(Function(r) r)
        
        ' Create headers with formatting options
        pivotSheet("A1").Value = "Product/Region"
        Dim col As Integer = 2
        For Each region In regions
            pivotSheet($"{ChrW(AscW("A"c) + col - 1)}1").Value = region ' Dynamic column addressing
            col += 1
        Next
        
        ' Populate pivot data - memory efficient for large datasets
        Dim row As Integer = 2
        For Each product In products
            pivotSheet($"A{row}").Value = product
            col = 2
            For Each region In regions
                Dim sales = pivotData _
                    .Where(Function(p) p.Product = product AndAlso p.Region = region) _
                    .Select(Function(p) p.TotalSales) _
                    .FirstOrDefault()
                pivotSheet($"{ChrW(AscW("A"c) + col - 1)}{row}").Value = sales
                col += 1
            Next
            row += 1
        Next
        
        ' Add totals using Excel formulas - maintains live calculations
        pivotSheet($"A{row}").Value = "Total" ' Grand totals row
        For c As Integer = 2 To regions.Count() + 1
            ' Formula references ensure dynamic updates
            pivotSheet($"{ChrW(AscW("A"c) + c - 1)}{row}").Formula = 
                $"=SUM({ChrW(AscW("A"c) + c - 1)}2:{ChrW(AscW("A"c) + c - 1)}{row - 1})"
        Next
        
        ' Apply professional formatting - currency format for sales data
        Dim dataRange = pivotSheet($"B2:{ChrW(AscW("A"c) + regions.Count())}{row}")
        dataRange.FormatString = "$#,##0.00"
        
        ' Save without Office dependencies - works in containers
        workbook.SaveAs("PivotReport.xlsx")
    End Sub
End Class

這種現代方法建立的樞紐分析表可無縫運行在Docker容器中，並支援各種Excel格式。您還可以匯出到不同格式，包括CSV、JSON和XML。

輸出看起來是什麼樣的？

Excel電子表格顯示左側的原始銷售資料和右側的樞紐分析表總結按地區排列的產品銷售

如何使用IronXL公式建立動態匯總？

何時應使用公式而非靜態聚合？

對於需要類似於樞紐分析表刷新功能的動態更新場景，IronXL可以利用Excel內建公式。這種方法更優雅且易於維護，程式碼易於理解而無需手冊或支持。它很好地結合條件格式進行視覺資料展示。

基於公式的總結如何保持資料連接？

// Load the workbook - container-friendly approach
WorkBook workbook = WorkBook.Load(inputPath);

// Rename the first worksheet so formulas reference correctly
WorkSheet dataSheet = workbook.WorkSheets[0];
dataSheet.Name = "DataSheet"; // Named reference for formulas

// Convert worksheet to DataTable for efficient processing
DataTable dataTable = dataSheet.ToDataTable(true);

// Create new summary worksheet - no COM objects
WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");

// Get unique product-region combinations using LINQ
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 with proper formatting
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";

// Populate rows with formulas - maintains live data connection
int rowIndex = 2;
foreach (var combo in uniqueCombos)
{
    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;
    
    // SUMIFS formula for conditional aggregation
    summarySheet[$"C{rowIndex}"].Formula =
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    
    // COUNTIFS for record counting
    summarySheet[$"D{rowIndex}"].Formula =
        $"=COUNTIFS(DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    
    rowIndex++;
}

// Optional: add total row with grand totals
summarySheet[$"A{rowIndex}"].Value = "Total";
summarySheet[$"C{rowIndex}"].Formula = $"=SUM(C2:C{rowIndex - 1})";
summarySheet[$"D{rowIndex}"].Formula = $"=SUM(D2:D{rowIndex - 1})";

// Apply number formatting for professional appearance
var salesColumn = summarySheet[$"C2:C{rowIndex}"];
salesColumn.FormatString = "$#,##0.00";

// Save output file - works in any environment
workbook.SaveAs(outputPath);  // No Office required

// Load the workbook - container-friendly approach
WorkBook workbook = WorkBook.Load(inputPath);

// Rename the first worksheet so formulas reference correctly
WorkSheet dataSheet = workbook.WorkSheets[0];
dataSheet.Name = "DataSheet"; // Named reference for formulas

// Convert worksheet to DataTable for efficient processing
DataTable dataTable = dataSheet.ToDataTable(true);

// Create new summary worksheet - no COM objects
WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");

// Get unique product-region combinations using LINQ
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 with proper formatting
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";

// Populate rows with formulas - maintains live data connection
int rowIndex = 2;
foreach (var combo in uniqueCombos)
{
    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;
    
    // SUMIFS formula for conditional aggregation
    summarySheet[$"C{rowIndex}"].Formula =
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    
    // COUNTIFS for record counting
    summarySheet[$"D{rowIndex}"].Formula =
        $"=COUNTIFS(DataSheet!A:A,\"{combo.Product}\",DataSheet!B:B,\"{combo.Region}\")";
    
    rowIndex++;
}

// Optional: add total row with grand totals
summarySheet[$"A{rowIndex}"].Value = "Total";
summarySheet[$"C{rowIndex}"].Formula = $"=SUM(C2:C{rowIndex - 1})";
summarySheet[$"D{rowIndex}"].Formula = $"=SUM(D2:D{rowIndex - 1})";

// Apply number formatting for professional appearance
var salesColumn = summarySheet[$"C2:C{rowIndex}"];
salesColumn.FormatString = "$#,##0.00";

// Save output file - works in any environment
workbook.SaveAs(outputPath);  // No Office required

' Load the workbook - container-friendly approach
Dim workbook As WorkBook = WorkBook.Load(inputPath)

' Rename the first worksheet so formulas reference correctly
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
dataSheet.Name = "DataSheet" ' Named reference for formulas

' Convert worksheet to DataTable for efficient processing
Dim dataTable As DataTable = dataSheet.ToDataTable(True)

' Create new summary worksheet - no COM objects
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")

' Get unique product-region combinations using LINQ
Dim uniqueCombos = dataTable.AsEnumerable() _
    .Select(Function(row) New With {
        Key .Product = row("Product").ToString(),
        Key .Region = row("Region").ToString()
    }) _
    .Distinct() _
    .OrderBy(Function(x) x.Product) _
    .ThenBy(Function(x) x.Region)

' Add header row with proper formatting
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Region"
summarySheet("C1").Value = "Total Sales"
summarySheet("D1").Value = "Count"

' Populate rows with formulas - maintains live data connection
Dim rowIndex As Integer = 2
For Each combo In uniqueCombos
    summarySheet($"A{rowIndex}").Value = combo.Product
    summarySheet($"B{rowIndex}").Value = combo.Region

    ' SUMIFS formula for conditional aggregation
    summarySheet($"C{rowIndex}").Formula = 
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,""{combo.Product}"",DataSheet!B:B,""{combo.Region}"")"

    ' COUNTIFS for record counting
    summarySheet($"D{rowIndex}").Formula = 
        $"=COUNTIFS(DataSheet!A:A,""{combo.Product}"",DataSheet!B:B,""{combo.Region}"")"

    rowIndex += 1
Next

' Optional: add total row with grand totals
summarySheet($"A{rowIndex}").Value = "Total"
summarySheet($"C{rowIndex}").Formula = $"=SUM(C2:C{rowIndex - 1})"
summarySheet($"D{rowIndex}").Formula = $"=SUM(D2:D{rowIndex - 1})"

' Apply number formatting for professional appearance
Dim salesColumn = summarySheet($"C2:C{rowIndex}")
salesColumn.FormatString = "$#,##0.00"

' Save output file - works in any environment
workbook.SaveAs(outputPath) ' No Office required

' Load the workbook - container-friendly approach
Dim workbook As WorkBook = WorkBook.Load(inputPath)

' Rename the first worksheet so formulas reference correctly
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
dataSheet.Name = "DataSheet" ' Named reference for formulas

' Convert worksheet to DataTable for efficient processing
Dim dataTable As DataTable = dataSheet.ToDataTable(True)

' Create new summary worksheet - no COM objects
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")

' Get unique product-region combinations using LINQ
Dim uniqueCombos = dataTable.AsEnumerable() _
    .Select(Function(row) New With {
        Key .Product = row("Product").ToString(),
        Key .Region = row("Region").ToString()
    }) _
    .Distinct() _
    .OrderBy(Function(x) x.Product) _
    .ThenBy(Function(x) x.Region)

' Add header row with proper formatting
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Region"
summarySheet("C1").Value = "Total Sales"
summarySheet("D1").Value = "Count"

' Populate rows with formulas - maintains live data connection
Dim rowIndex As Integer = 2
For Each combo In uniqueCombos
    summarySheet($"A{rowIndex}").Value = combo.Product
    summarySheet($"B{rowIndex}").Value = combo.Region

    ' SUMIFS formula for conditional aggregation
    summarySheet($"C{rowIndex}").Formula = 
        $"=SUMIFS(DataSheet!C:C,DataSheet!A:A,""{combo.Product}"",DataSheet!B:B,""{combo.Region}"")"

    ' COUNTIFS for record counting
    summarySheet($"D{rowIndex}").Formula = 
        $"=COUNTIFS(DataSheet!A:A,""{combo.Product}"",DataSheet!B:B,""{combo.Region}"")"

    rowIndex += 1
Next

' Optional: add total row with grand totals
summarySheet($"A{rowIndex}").Value = "Total"
summarySheet($"C{rowIndex}").Formula = $"=SUM(C2:C{rowIndex - 1})"
summarySheet($"D{rowIndex}").Formula = $"=SUM(D2:D{rowIndex - 1})"

' Apply number formatting for professional appearance
Dim salesColumn = summarySheet($"C2:C{rowIndex}")
salesColumn.FormatString = "$#,##0.00"

' Save output file - works in any environment
workbook.SaveAs(outputPath) ' No Office required

這些公式保持了與資料源的即時連接，當資料表改變時會自動更新——類似於樞紐分析表刷新行為，但無需Interop依賴。對於複雜場景，您可以建立Excel圖表或處理命名範圍以便更好的公式管理。

基於公式的方法提供何種性能益處？

將此程式碼應用於我們的Excel文件範例中會生成以下輸出：

Excel電子表格顯示產品銷售資料，其中包含產品、地區、總銷售額和數量等欄位，顯示不同地區的各種電子產品（筆記型電腦、手機、平板）零銷售值

基於公式的方法提供顯著的性能優勢：它們在Excel的計算引擎中原生執行，支援背景計算，並無縫整合Excel的列印設置以進行報告。您還可以應用單元格式化和樣式單元格以增強可讀性。

C# Interop與IronXL在樞紐分析表上的比較如何？

哪種部署場景更適合每種方法？

方面	C# Interop	IronXL
需要Office	是 - 完整安裝	否 - 獨立程式庫
平台支援	僅限Windows	Windows、Linux、macOS、Docker
記憶體管理	需要手動COM清理	.NET自動垃圾回收
部署	複雜 - Office授權	簡單 - 單一DLL
性能	慢 - Excel程式啟動	快 - 記憶體中的計算
雲端相容	否 - Azure限制	Yes - Azure Functions support
原生樞紐分析表	是	否 - 聚合替代方案
開發速度	慢 - COM複雜性	快 - 直觀API
容器支援	否 - 無法在Docker中運行	是 - 完全Docker支援
授權管理	Office每台機器授權	簡單授權金鑰

每個解決方案的資源需求是什麼？

C# Interop需要顯著的系統資源：完整的Office安裝（2-4GB磁碟空間）、Windows操作系統、適當的RAM用於Excel進程，以及COM註冊的管理權限。相比之下，IronXL僅需.NET運行時和大約50MB的程式庫，這使其非常適合受資源限制的環境。 IronXL的文件大小限制已經過良好記錄以便於容量規劃。