Excel で相互運用性なしで C# を使ってピボット テーブルを作成する方法

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

// Launch Excel and set up workbooks
var excelApp = new Excel.Application();
excelApp.Visible = false;
var workbook = excelApp.Workbooks.Add();
var dataSheet = (Excel.Worksheet)workbook.Worksheets[1];
dataSheet.Name = "SalesData";
var pivotSheet = (Excel.Worksheet)workbook.Worksheets.Add();
pivotSheet.Name = "Pivot";

// Populate header row
dataSheet.Cells[1, 1] = "Product";
dataSheet.Cells[1, 2] = "Region";
dataSheet.Cells[1, 3] = "Sales";

// Add sample data rows
object[,] rows = {
    { "Laptop",   "なしrth", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "なしrth",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "なしrth",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet.Cells[i + 2, 1] = rows[i, 0];
    dataSheet.Cells[i + 2, 2] = rows[i, 1];
    dataSheet.Cells[i + 2, 3] = rows[i, 2];
}

// Build pivot cache from source range
Excel.Range dataRange = dataSheet.Range["A1:C10"];
Excel.PivotCache pivotCache = workbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase, dataRange);

// Create the PivotTable on the pivot sheet
Excel.PivotTables pivotTables = (Excel.PivotTables)pivotSheet.PivotTables();
Excel.PivotTable pivotTable = pivotTables.Add(
    pivotCache, pivotSheet.Range["A3"], "SalesPivot");

// Assign field orientations
((Excel.PivotField)pivotTable.PivotFields("Product")).Orientation =
    Excel.XlPivotFieldOrientation.xlRowField;
((Excel.PivotField)pivotTable.PivotFields("Region")).Orientation =
    Excel.XlPivotFieldOrientation.xlColumnField;
((Excel.PivotField)pivotTable.PivotFields("Sales")).Orientation =
    Excel.XlPivotFieldOrientation.xlDataField;

// Enable grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

// Save and close
workbook.SaveAs(@"C:\output\pivot_interop.xlsx");
workbook.Close(false);
excelApp.Quit();

// Release every COM object -- skipping any of these causes Excel to stay in memory
Marshal.ReleaseComObject(pivotTable);
Marshal.ReleaseComObject(pivotTables);
Marshal.ReleaseComObject(pivotCache);
Marshal.ReleaseComObject(dataRange);
Marshal.ReleaseComObject(pivotSheet);
Marshal.ReleaseComObject(dataSheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

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

' Launch Excel and set up workbooks
Dim excelApp As New Excel.Application()
excelApp.Visible = False
Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()
Dim dataSheet As Excel.Worksheet = CType(workbook.Worksheets(1), Excel.Worksheet)
dataSheet.Name = "SalesData"
Dim pivotSheet As Excel.Worksheet = CType(workbook.Worksheets.Add(), Excel.Worksheet)
pivotSheet.Name = "Pivot"

' Populate header row
dataSheet.Cells(1, 1) = "Product"
dataSheet.Cells(1, 2) = "Region"
dataSheet.Cells(1, 3) = "Sales"

' Add sample data rows
Dim rows As Object(,) = {
    {"Laptop", "North", 1200},
    {"Laptop", "South", 1500},
    {"Phone", "North", 800},
    {"Phone", "South", 950},
    {"Tablet", "East", 600},
    {"Tablet", "West", 750},
    {"Monitor", "North", 400},
    {"Monitor", "South", 500},
    {"Keyboard", "East", 300}
}
For i As Integer = 0 To rows.GetLength(0) - 1
    dataSheet.Cells(i + 2, 1) = rows(i, 0)
    dataSheet.Cells(i + 2, 2) = rows(i, 1)
    dataSheet.Cells(i + 2, 3) = rows(i, 2)
Next

' Build pivot cache from source range
Dim dataRange As Excel.Range = dataSheet.Range("A1:C10")
Dim pivotCache As Excel.PivotCache = workbook.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase, dataRange)

' Create the PivotTable on the pivot sheet
Dim pivotTables As Excel.PivotTables = CType(pivotSheet.PivotTables(), Excel.PivotTables)
Dim pivotTable As Excel.PivotTable = pivotTables.Add(
    pivotCache, pivotSheet.Range("A3"), "SalesPivot")

' Assign field orientations
CType(pivotTable.PivotFields("Product"), Excel.PivotField).Orientation =
    Excel.XlPivotFieldOrientation.xlRowField
CType(pivotTable.PivotFields("Region"), Excel.PivotField).Orientation =
    Excel.XlPivotFieldOrientation.xlColumnField
CType(pivotTable.PivotFields("Sales"), Excel.PivotField).Orientation =
    Excel.XlPivotFieldOrientation.xlDataField

' Enable grand totals
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True

' Save and close
workbook.SaveAs("C:\output\pivot_interop.xlsx")
workbook.Close(False)
excelApp.Quit()

' Release every COM object -- skipping any of these causes Excel to stay in memory
Marshal.ReleaseComObject(pivotTable)
Marshal.ReleaseComObject(pivotTables)
Marshal.ReleaseComObject(pivotCache)
Marshal.ReleaseComObject(dataRange)
Marshal.ReleaseComObject(pivotSheet)
Marshal.ReleaseComObject(dataSheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(excelApp)

using IronXL;
using System.Data;

// Create workbook and populate source data
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet dataSheet = workbook.CreateWorkSheet("SalesData");

dataSheet["A1"].Value = "Product";
dataSheet["B1"].Value = "Region";
dataSheet["C1"].Value = "Sales";

object[,] rows = {
    { "Laptop",   "なしrth", 1200 },
    { "Laptop",   "South", 1500 },
    { "Phone",    "なしrth",  800 },
    { "Phone",    "South",  950 },
    { "Tablet",   "East",   600 },
    { "Tablet",   "West",   750 },
    { "Monitor",  "なしrth",  400 },
    { "Monitor",  "South",  500 },
    { "Keyboard", "East",   300 },
};
for (int i = 0; i < rows.GetLength(0); i++)
{
    dataSheet[$"A{i + 2}"].Value = rows[i, 0];
    dataSheet[$"B{i + 2}"].Value = rows[i, 1];
    dataSheet[$"C{i + 2}"].Value = rows[i, 2];
}

// Aggregate data with LINQ -- equivalent to a pivot table row/column/value layout
DataTable table = dataSheet["A1:C10"].ToDataTable(true);
var summary = table.AsEnumerable()
    .GroupBy(row => row.Field<string>("Product"))
    .Select((group, idx) => new
    {
        Product    = group.Key,
        TotalSales = group.Sum(r => Convert.ToDecimal(r["Sales"])),
        RegionCount = group.Select(r => r.Field<string>("Region")).Distinct().Count(),
        RowIndex   = idx + 2
    })
    .OrderByDescending(x => x.TotalSales);

// Write the summary sheet
WorkSheet summarySheet = workbook.CreateWorkSheet("Summary");
summarySheet["A1"].Value = "Product";
summarySheet["B1"].Value = "Total Sales";
summarySheet["C1"].Value = "Regions";

foreach (var item in summary)
{
    summarySheet[$"A{item.RowIndex}"].Value = item.Product;
    summarySheet[$"B{item.RowIndex}"].Value = (double)item.TotalSales;
    summarySheet[$"C{item.RowIndex}"].Value = item.RegionCount;
}

// Apply currency format to the sales column
summarySheet["B:B"].FormatString = "$#,##0.00";

// Bold the header row
summarySheet["A1:C1"].Style.Font.Bold = true;

// Save -- no COM cleanup needed
workbook.SaveAs(@"C:\output\analysis_ironxl.xlsx");

Imports IronXL
Imports System.Data
Imports System.Linq

' Create workbook and populate source data
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim dataSheet As WorkSheet = workbook.CreateWorkSheet("SalesData")

dataSheet("A1").Value = "Product"
dataSheet("B1").Value = "Region"
dataSheet("C1").Value = "Sales"

Dim rows As Object(,) = {
    {"Laptop", "なしrth", 1200},
    {"Laptop", "South", 1500},
    {"Phone", "なしrth", 800},
    {"Phone", "South", 950},
    {"Tablet", "East", 600},
    {"Tablet", "West", 750},
    {"Monitor", "なしrth", 400},
    {"Monitor", "South", 500},
    {"Keyboard", "East", 300}
}

For i As Integer = 0 To rows.GetLength(0) - 1
    dataSheet($"A{i + 2}").Value = rows(i, 0)
    dataSheet($"B{i + 2}").Value = rows(i, 1)
    dataSheet($"C{i + 2}").Value = rows(i, 2)
Next

' Aggregate data with LINQ -- equivalent to a pivot table row/column/value layout
Dim table As DataTable = dataSheet("A1:C10").ToDataTable(True)
Dim summary = table.AsEnumerable() _
    .GroupBy(Function(row) row.Field(Of String)("Product")) _
    .Select(Function(group, idx) New With {
        .Product = group.Key,
        .TotalSales = group.Sum(Function(r) Convert.ToDecimal(r("Sales"))),
        .RegionCount = group.Select(Function(r) r.Field(Of String)("Region")).Distinct().Count(),
        .RowIndex = idx + 2
    }) _
    .OrderByDescending(Function(x) x.TotalSales)

' Write the summary sheet
Dim summarySheet As WorkSheet = workbook.CreateWorkSheet("Summary")
summarySheet("A1").Value = "Product"
summarySheet("B1").Value = "Total Sales"
summarySheet("C1").Value = "Regions"

For Each item In summary
    summarySheet($"A{item.RowIndex}").Value = item.Product
    summarySheet($"B{item.RowIndex}").Value = CDbl(item.TotalSales)
    summarySheet($"C{item.RowIndex}").Value = item.RegionCount
Next

' Apply currency format to the sales column
summarySheet("B:B").FormatString = "$#,##0.00"

' Bold the header row
summarySheet("A1:C1").Style.Font.Bold = True

' Save -- no COM cleanup needed
workbook.SaveAs("C:\output\analysis_ironxl.xlsx")

IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE";

Imports IronXL

IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE"