如何使用 IronXL 在 Excel 中使用 C# 建立資料透視表

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;

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

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

using IronXL;
using System.Linq;
using System.Data;

// Load Excel file -- no Office installation required
WorkBook workbook = WorkBook.Load("SalesData.xlsx");
WorkSheet dataSheet = workbook.WorkSheets[0];

// Convert to DataTable for flexible LINQ manipulation
var dataTable = dataSheet.ToDataTable(true); // true = first row as column headers

// Build pivot-style aggregation using LINQ grouping
var pivotData = dataTable.AsEnumerable()
    .GroupBy(row => new {
        Product = row["Product"].ToString(),
        Region = row["Region"].ToString()
    })
    .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 the pivot report worksheet
WorkSheet pivotSheet = workbook.CreateWorkSheet("PivotReport");

// Get distinct row and column values
var products = pivotData.Select(p => p.Product).Distinct().OrderBy(p => p).ToList();
var regions  = pivotData.Select(p => p.Region).Distinct().OrderBy(r => r).ToList();

// Write column headers
pivotSheet["A1"].Value = "Product / Region";
for (int c = 0; c < regions.Count; c++)
{
    pivotSheet[$"{(char)('B' + c)}1"].Value = regions[c];
}

// Populate data rows
for (int r = 0; r < products.Count; r++)
{
    pivotSheet[$"A{r + 2}"].Value = products[r];
    for (int c = 0; c < regions.Count; c++)
    {
        var sales = pivotData
            .Where(p => p.Product == products[r] && p.Region == regions[c])
            .Select(p => p.TotalSales)
            .FirstOrDefault();
        pivotSheet[$"{(char)('B' + c)}{r + 2}"].Value = sales;
    }
}

// Add a totals row using Excel SUM formulas
int totalRow = products.Count + 2;
pivotSheet[$"A{totalRow}"].Value = "Total";
for (int c = 0; c < regions.Count; c++)
{
    char col = (char)('B' + c);
    pivotSheet[$"{col}{totalRow}"].Formula = $"=SUM({col}2:{col}{totalRow - 1})";
}

// Apply currency formatting to the data range
var dataRange = pivotSheet[$"B2:{(char)('B' + regions.Count - 1)}{totalRow}"];
dataRange.FormatString = "$#,##0.00";

workbook.SaveAs("PivotReport.xlsx");

Imports IronXL
Imports System.Linq
Imports System.Data

' Load Excel file -- no Office installation required
Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)

' Convert to DataTable for flexible LINQ manipulation
Dim dataTable As DataTable = dataSheet.ToDataTable(True) ' True = first row as column headers

' Build pivot-style aggregation using LINQ grouping
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 the pivot report worksheet
Dim pivotSheet As WorkSheet = workbook.CreateWorkSheet("PivotReport")

' Get distinct row and column values
Dim products = pivotData.Select(Function(p) p.Product).Distinct().OrderBy(Function(p) p).ToList()
Dim regions = pivotData.Select(Function(p) p.Region).Distinct().OrderBy(Function(r) r).ToList()

' Write column headers
pivotSheet("A1").Value = "Product / Region"
For c As Integer = 0 To regions.Count - 1
    pivotSheet($"{ChrW(AscW("B"c) + c)}1").Value = regions(c)
Next

' Populate data rows
For r As Integer = 0 To products.Count - 1
    pivotSheet($"A{r + 2}").Value = products(r)
    For c As Integer = 0 To regions.Count - 1
        Dim sales = pivotData _
            .Where(Function(p) p.Product = products(r) AndAlso p.Region = regions(c)) _
            .Select(Function(p) p.TotalSales) _
            .FirstOrDefault()
        pivotSheet($"{ChrW(AscW("B"c) + c)}{r + 2}").Value = sales
    Next
Next

' Add a totals row using Excel SUM formulas
Dim totalRow As Integer = products.Count + 2
pivotSheet($"A{totalRow}").Value = "Total"
For c As Integer = 0 To regions.Count - 1
    Dim col As Char = ChrW(AscW("B"c) + c)
    pivotSheet($"{col}{totalRow}").Formula = $"=SUM({col}2:{col}{totalRow - 1})"
Next

' Apply currency formatting to the data range
Dim dataRange = pivotSheet($"B2:{ChrW(AscW("B"c) + regions.Count - 1)}{totalRow}")
dataRange.FormatString = "$#,##0.00"

workbook.SaveAs("PivotReport.xlsx")

using IronXL;
using System.Data;

string inputPath  = "SalesData.xlsx";
string outputPath = "DynamicSummary.xlsx";

WorkBook workbook  = WorkBook.Load(inputPath);
WorkSheet dataSheet = workbook.WorkSheets[0];

// Name the data sheet so formula references are stable
dataSheet.Name = "DataSheet";

// Convert to DataTable to enumerate unique product/region combinations
DataTable dataTable = dataSheet.ToDataTable(true);

WorkSheet summarySheet = workbook.CreateWorkSheet("DynamicSummary");

// Get unique product-region pairs
var uniqueCombos = dataTable.AsEnumerable()
    .Select(row => new {
        Product = row["Product"].ToString(),
        Region  = row["Region"].ToString()
    })
    .Distinct()
    .OrderBy(x => x.Product)
    .ThenBy(x => x.Region)
    .ToList();

// Header row
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Region";
summarySheet["C1"].Value = "Total Sales";
summarySheet["D1"].Value = "Count";

// Populate rows with live SUMIFS / COUNTIFS formulas
for (int i = 0; i < uniqueCombos.Count; i++)
{
    int rowIndex = i + 2;
    var combo = uniqueCombos[i];

    summarySheet[$"A{rowIndex}"].Value = combo.Product;
    summarySheet[$"B{rowIndex}"].Value = combo.Region;

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

// Grand total row
int totalRow = uniqueCombos.Count + 2;
summarySheet[$"A{totalRow}"].Value = "Total";
summarySheet[$"C{totalRow}"].Formula = $"=SUM(C2:C{totalRow - 1})";
summarySheet[$"D{totalRow}"].Formula = $"=SUM(D2:D{totalRow - 1})";

workbook.SaveAs(outputPath);

Imports IronXL
Imports System.Data
Imports System.Linq

Dim inputPath As String = "SalesData.xlsx"
Dim outputPath As String = "DynamicSummary.xlsx"

Dim workbook As WorkBook = WorkBook.Load(inputPath)
Dim dataSheet As WorkSheet = workbook.WorkSheets(0)

' Name the data sheet so formula references are stable
dataSheet.Name = "DataSheet"

' Convert to DataTable to enumerate unique product/region combinations
Dim dataTable As DataTable = dataSheet.ToDataTable(True)

Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("DynamicSummary")

' Get unique product-region pairs
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) _
    .ToList()

' Header row
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Region"
summarySheet("C1").Value = "Total Sales"
summarySheet("D1").Value = "Count"

' Populate rows with live SUMIFS / COUNTIFS formulas
For i As Integer = 0 To uniqueCombos.Count - 1
    Dim rowIndex As Integer = i + 2
    Dim combo = uniqueCombos(i)

    summarySheet($"A{rowIndex}").Value = combo.Product
    summarySheet($"B{rowIndex}").Value = combo.Region

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

' Grand total row
Dim totalRow As Integer = uniqueCombos.Count + 2
summarySheet($"A{totalRow}").Value = "Total"
summarySheet($"C{totalRow}").Formula = $"=SUM(C2:C{totalRow - 1})"
summarySheet($"D{totalRow}").Formula = $"=SUM(D2:D{totalRow - 1})"

workbook.SaveAs(outputPath)