Excel中的樞紐分析表是什麼？

Excel中的樞紐分析表是一個強大的工具，用於彙總、分析、探索和展示資料。它允許使用者將列轉換為行，反之亦然，使動態資料分析得以實現。

為什麼在C#中建立Excel樞紐分析表時使用IronXL？

IronXL允許開發者在C#中建立Excel樞紐分析表而不依賴Office Interop，消除了對Excel安裝的需求並減少了依賴性，是一個現代且高效的選擇。

IronXL與C# Interop相比在Excel操作中如何？

IronXL提供了一種更流暢和獨立的方法，相比之下C# Interop需要Office安裝。IronXL簡化了樞紐分析表和其他Excel操作的建立，無需Interop的複雜性。

沒有安裝Excel，我可以生成樞紐分析表嗎？

可以，使用IronXL，您可以在您的C#應用中生成樞紐分析表，而無需安裝Excel，因為它獨立於Microsoft Office運作。

IronXL適合處理大型資料集嗎？

IronXL設計為能有效地處理大型資料集，非常適用於需要強大資料處理和樞紐分析表生成的應用程式。

與傳統方法相比，使用IronXL有什麼優勢？

IronXL提供了一種現代、不依賴的替代傳統方法如C# Interop，提供易用性、靈活性，以及對複雜資料操作的支持，而無需Excel安裝。

我需要學習VBA來使用IronXL生成樞紐分析表嗎？

不需要，IronXL允許開發人員直接在C#中工作來建立和管理樞紐分析表，無需學習VBA或其他Excel特定的編程語言。

What security features does IronXL offer?

IronXL offers security features like DigiCert certification and avoids using COM interfaces, thereby reducing potential security vulnerabilities and attack vectors.

Why is C# Interop considered outdated for pivot table generation?

C# Interop is considered outdated due to its reliance on Microsoft Office, which is not scalable in modern cloud and containerized environments. It also requires manual memory management and is limited to Windows platforms.

What are the development advantages of using IronXL?

IronXL simplifies development with an intuitive API, automatic .NET garbage collection, and eliminates the need for COM object management, allowing faster development cycles and easier code maintenance.

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

如何在 C# 中建立 Excel 資料透視表？

Curtis Chau

Updated: 2026年4月21日

在C#中建立Excel樞紐分析表可以通過Office Interop（需要安裝Microsoft Office）或現代程式庫如IronXL來實現，IronXL提供了優越的部署靈活性和跨平台支持，適用於DevOps環境。

程式化生成樞紐分析表需要使用C# Interop及其Office依賴項，或使用像IronXL這樣不依賴外部的現代程式庫。本教程展示了這兩種方法，並突出說明為什麼越來越多的開發人員選擇IronXL而不是傳統的Interop方法，特別是在部署到Docker容器或Azure和AWS這樣的雲環境時。

在本文中，我們將學習如何編輯、建立、設計和計算樞紐分析表和群組，實現自動分析和錯誤處理，同時保持DevOps工程師所需的簡單部署。

什麼是Excel樞紐分析表？

樞紐分析表是Excel最強大的工具之一。它是總結大型資料集的簡單方法，對.NET應用程式中的資料分析非常有價值。樞紐分析表能讓您輕鬆顯示、理解和分析資料。它們不僅在Excel中可用，還在Google Sheets、Apple Numbers和CSV導出中可用。它們提供了一種查看資料概覽的解決方案，作為一個資料控制台，可以讓人們以有意義的方式查看資訊。

對於容器化應用程式，程式化建立樞紐分析表消除了在Docker映像中需要安裝Excel的需求，顯著減少了容器大小和部署複雜性。此方法完美符合現代的CI/CD管道和容器部署策略。

讓我們先探討建立樞紐分析表的錯誤方式，再學習正確的C#方法：

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

C# Excel Interop通過COM自動化提供了對Excel樞紐分析表功能的直接存取。以下是許多開發人員在尋找用C#生成樞紐分析表工具時發現的傳統方法：

為什麼在.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);

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

' Create Excel application instance
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
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
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
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)

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

' Create Excel application instance
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
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
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
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)

此Interop範例建立了一個本地Excel樞紐分析表，將產品作為行，地區作為列，銷售額在資料區域中進行匯總。雖然功能齊全，但此方法需要安裝Microsoft Office並仔細管理COM物件。 Microsoft文件解釋了為什麼這種方法不夠現代。從DevOps的角度來看，這種方法尤其問題嚴重，因為它無法有效容器化——無法在Linux Docker容器中安裝Microsoft Office或部署到無伺服器環境。

C# Interop會產生哪些問題？

Interop方法存在幾個重大挑戰，使其不適合現代DevOps實踐和雲原生部署。

不幸的是，Stack Overflow和其他程式設計網站仍然推薦它，因為它們被困在2000年代初期的帖子中。

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

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

平台限制：此解決方案僅在安裝了Office的Windows上運行。它可能非常慢，並導致記憶體洩漏。不支持Linux、macOS、Docker容器或雲平台如Azure Functions。這嚴重限制了部署選項，並防止使用現代容器編排平台。

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

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

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

IronXL使用受管程式碼而非COM依賴來不同地實現樞紐分析表建立。雖然它不建立本地Excel樞紐分析表，但它提供的強大聚合能力非常適合容器化部署和雲原生架構。該程式庫的性能優化包括速度提高40倍，記憶體使用量從19.5 GB減少到不到1 GB，這使其非常適合資源有限的容器環境。

什麼使這種方法適用於XLSX或XLS文件的現代化？

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");
    }
}

Imports IronXL
Imports System.Linq
Imports System.Data ' Keep this namespace
Imports static System.Data.DataTableExtensions ' Use 'using static' for DataTableExtensions

Module Program
    Sub Main(args As String())
        ' Load Excel file - no Office required
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
        Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
        ' Convert to DataTable for powerful manipulation
        Dim dataTable = dataSheet.ToDataTable(True) ' true = use first row as column headers
        ' Create pivot-style aggregation using LINQ
        Dim pivotData = dataTable.AsEnumerable() _
            .GroupBy(Function(row) New With {
                Key .Product = row("Product").ToString(),
                Key .Region = row("Region").ToString()
            }) _
            .Select(Function(g) New With {
                Key .Product = g.Key.Product,
                Key .Region = g.Key.Region,
                Key .TotalSales = g.Sum(Function(row) Convert.ToDecimal(row("Sales"))),
                Key .AverageSale = g.Average(Function(row) Convert.ToDecimal(row("Sales"))),
                Key .Count = g.Count()
            })
        ' Create pivot report worksheet
        Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")
        ' Build cross-tabulation structure
        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
        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 ' string  
            col += 1
        Next
        ' Populate pivot data
        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
        pivotSheet($"A{row}").Value = "Total" ' grand totals
        For c As Integer = 2 To regions.Count() + 1
            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
        ' Proceeding to apply formatting
        Dim dataRange = pivotSheet($"B2:{ChrW(AscW("A"c) + regions.Count())}{row}")
        dataRange.FormatString = "$#,##0.00"
        workbook.SaveAs("PivotReport.xlsx")
    End Sub
End Module

Imports IronXL
Imports System.Linq
Imports System.Data ' Keep this namespace
Imports static System.Data.DataTableExtensions ' Use 'using static' for DataTableExtensions

Module Program
    Sub Main(args As String())
        ' Load Excel file - no Office required
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
        Dim dataSheet As WorkSheet = workbook.WorkSheets(0)
        ' Convert to DataTable for powerful manipulation
        Dim dataTable = dataSheet.ToDataTable(True) ' true = use first row as column headers
        ' Create pivot-style aggregation using LINQ
        Dim pivotData = dataTable.AsEnumerable() _
            .GroupBy(Function(row) New With {
                Key .Product = row("Product").ToString(),
                Key .Region = row("Region").ToString()
            }) _
            .Select(Function(g) New With {
                Key .Product = g.Key.Product,
                Key .Region = g.Key.Region,
                Key .TotalSales = g.Sum(Function(row) Convert.ToDecimal(row("Sales"))),
                Key .AverageSale = g.Average(Function(row) Convert.ToDecimal(row("Sales"))),
                Key .Count = g.Count()
            })
        ' Create pivot report worksheet
        Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")
        ' Build cross-tabulation structure
        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
        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 ' string  
            col += 1
        Next
        ' Populate pivot data
        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
        pivotSheet($"A{row}").Value = "Total" ' grand totals
        For c As Integer = 2 To regions.Count() + 1
            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
        ' Proceeding to apply formatting
        Dim dataRange = pivotSheet($"B2:{ChrW(AscW("A"c) + regions.Count())}{row}")
        dataRange.FormatString = "$#,##0.00"
        workbook.SaveAs("PivotReport.xlsx")
    End Sub
End Module

這是您如何以容器友好的方式建立樞紐分析表的方法。此方法在Docker容器、Kubernetes Pods和無伺服器功能上無縫運行，無需任何外部依賴項。整個應用程式可以被打包成一個輕量級的容器映像，在任何支持.NET的地方都能運行。

樞紐分析表輸出看起來是什麼樣的？

（原始Excel銷售資料和生成的樞紐分析表比較顯示按地區匯總的產品銷售總額）

輸出顯示IronXL如何將原始銷售資料轉換為結構化的樞紐分析報告，無需安裝Excel，對於CI/CD管道中的自動化報告非常理想。

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

對於需要類似於樞紐分析表刷新功能的動態更新場景，IronXL可以使用Excel內建公式。這種方法是首選——資料以更現代和優雅的方式處理。程式碼易於理解和設置，無需聯繫支援或閱讀手冊。這種方法在需要基於公式的計算自動更新的容器化環境中特別有價值。

基於公式的匯總如何自動更新？

// 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

Imports System.Data
Imports System.Linq

' Load the workbook
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"
' Convert worksheet to DataTable
Dim dataTable As DataTable = dataSheet.ToDataTable(True)
' Create new summary worksheet
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")
' Get unique product-region combinations
Dim uniqueCombos = dataTable.AsEnumerable() _
    .Select(Function(row) New With {
        .Product = row("Product").ToString(),
        .Region = row("Region").ToString()
    }) _
    .Distinct() _
    .OrderBy(Function(x) x.Product) _
    .ThenBy(Function(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
Dim rowIndex As Integer = 2
For Each 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 += 1
Next
' 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

Imports System.Data
Imports System.Linq

' Load the workbook
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"
' Convert worksheet to DataTable
Dim dataTable As DataTable = dataSheet.ToDataTable(True)
' Create new summary worksheet
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")
' Get unique product-region combinations
Dim uniqueCombos = dataTable.AsEnumerable() _
    .Select(Function(row) New With {
        .Product = row("Product").ToString(),
        .Region = row("Region").ToString()
    }) _
    .Distinct() _
    .OrderBy(Function(x) x.Product) _
    .ThenBy(Function(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
Dim rowIndex As Integer = 2
For Each 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 += 1
Next
' 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

這些公式維持到源資料的實時連接，當資料表發生變化時自動更新——類似於樞紐分析表刷新行為，但無需Interop依賴。這種方法非常適合需要生成動態報告而無需外部依賴的容器化微服務。

您可以從動態匯總中期待什麼結果？

如果我們將此程式碼應用於上一篇例子的Excel文件中，我們會得到如下輸出：

（Excel電子表格顯示產品銷售資料的動態匯總公式，顯示各地區的產品（筆記型電腦、手機、平板電腦）並計算總數和計數）

動態匯總方法提供實時計算，當源資料改變時自動更新，對於容器化環境中的自動化報告管道非常理想。這消除了計劃的樞紐分析表刷新需求，並在.NET MAUI和Blazor應用程式中無縫工作。

您應該如何比較C# Interop和IronXL之間的樞紐分析表？

要素	C# Interop	IronXL
需要Office	是的-完整安裝	不-獨立程式庫
平台支持	僅限Windows	Windows, Linux, macOS, Docker
記憶體管理	需要手動COM清理	自動.NET垃圾回收
部署	複雜-Office許可	簡單-單一DLL
性能	慢-Excel進程啟動	快-記憶體計算
雲相容	否-Azure限制	是-支持Azure Functions
原生樞紐分析表	是	否-聚合替代方案
開發速度	慢-COM複雜性	快-直觀API
容器支持	否-無法容器化Office	是-Docker準備就緒
健康檢查	困難-COM過程監測	簡單-標準.NET監測

從DevOps的角度來看，IronXL的架構為現代部署場景提供了顯著優勢。該程式庫可以在無外部依賴的容器中運行，意味著您可以建立輕量級Docker映像，快速部署並有效地擴展。健康檢查可以使用標準的.NET模式實施，該程式庫的安全功能包括DigiCert認證和無COM接口，減少攻擊向量。

您應該選擇哪種方法？

什麼時候應該使用C# Interop？

當以下情況選擇C# Interop：

絕對需要本地Excel樞紐分析表物件
僅在裝有Office的Windows上工作
只部署到您管理的桌面系統
現有遺留程式碼依賴於Interop
使用遺留的.NET Framework版本
您無打算容器化或移動到雲端

什麼時候IronXL能提供更好的結果？

當以下情況選擇IronXL：

部署到伺服器或雲環境（Azure, AWS）
構建在容器中運行的跨平台應用程式
需要40倍速度提升的更好性能
避免Office許可成本和部署複雜性
需要更簡潔的程式碼和自動授權金鑰管理
支持Mac, iOS, Android, 和Linux系統
在現代的.NET Core和.NET 5-10上工作
以程式化方式配置樞紐分析表欄位
構建導出到多種格式的微服務
在CI/CD管道中實施自動化報告
為容器編排建立健康檢查端點
在不同的電子表格格式間轉換

我們學到了什麼關於在C#中建立樞紐分析表？

雖然C# Excel Interop可以建立本地樞紐分析表，但其部署限制和複雜性使其對於現代應用程式，特別是在容器化環境中變得越來越不實用。 IronXL通過資料聚合和基於公式的匯總提供強大的替代方案，消除Office依存關係，同時保持分析能力。

對於尋求無Interop開發樞紐分析表替代方案的開發者和DevOps工程師，IronXL提供了一條優越的途徑，避免了COM的複雜性，能夠跨所有平台工作，並簡化了部署。缺少本地樞紐物件的權衡被更大的靈活性，更好的性能和消除Office許可要求所抵消。對於DevOps團隊來說，最重要的是，IronXL使實現基於程式碼的基礎設施成為可能，支持容器化部署，自動擴展，並無縫整合到現代CI/CD管道中。

該程式庫的全面功能集包括條件格式化，儲存格樣式，公式支持和資料驗證，使其成為現代.NET應用程式中Excel自動化的完整解決方案。無論您是在處理CSV文件，管理工作表，還是實施複雜的資料轉換，IronXL提供了一個一致且適合部署的API。

第一步：

準備好現代化您的Excel自動化和使用現代的C#建立樞紐分析程式碼了嗎？

通過NuGet套件管理器，IronXL可以在您的C#應用程式中於幾秒鐘內實現。試用免費試用版來消除生產應用程式中的Interop依賴，並簡化容器部署。