C#を使用してExcelでピボット・テーブルを作成する方法 Interop vs IronXL

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