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

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 System.Data.DataTableExtensions ' Use 'Imports' 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

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