OleDb とIronXLを使用して DataTable を Excel C# にエクスポートする

Install-Package IronXL
dotnet add package IronXL

using System.Data;
using System.Data.オレDB;

// Build a sample DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// オレDB connection string -- file must already exist on disk
string filename = @"C:\Output\Products.xlsx";
string connectionString =
    $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};" +
    "Extended Properties='Excel 12.0 Xml;HDR=YES'";

using オレDBConnection connection = new(connectionString);
connection.Open();

// Create the sheet structure with SQL DDL
string create = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)";
using (var createCmd = new オレDBCommand(create, connection))
    createCmd.ExecuteなしnQuery();

// Insert rows one at a time
string insert = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)";
using オレDBCommand insertCmd = new(insert, connection);
insertCmd.Parameters.Add(new オレDBParameter("ProductID", オレDBType.Integer));
insertCmd.Parameters.Add(new オレDBParameter("ProductName", オレDBType.VarChar, 255));
insertCmd.Parameters.Add(new オレDBParameter("Price", オレDBType.Double));

foreach (DataRow row in dataTable.Rows)
{
    insertCmd.Parameters[0].Value = row["ProductID"];
    insertCmd.Parameters[1].Value = row["ProductName"];
    insertCmd.Parameters[2].Value = row["Price"];
    insertCmd.ExecuteなしnQuery();
}

Imports System.Data
Imports System.Data.オレDB

' Build a sample DataTable
Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

' オレDB connection string -- file must already exist on disk
Dim filename As String = "C:\Output\Products.xlsx"
Dim connectionString As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};" &
                                 "Extended Properties='Excel 12.0 Xml;HDR=YES'"

Using connection As New オレDBConnection(connectionString)
    connection.Open()

    ' Create the sheet structure with SQL DDL
    Dim create As String = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)"
    Using createCmd As New オレDBCommand(create, connection)
        createCmd.ExecuteなしnQuery()
    End Using

    ' Insert rows one at a time
    Dim insert As String = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)"
    Using insertCmd As New オレDBCommand(insert, connection)
        insertCmd.Parameters.Add(New オレDBParameter("ProductID", オレDBType.Integer))
        insertCmd.Parameters.Add(New オレDBParameter("ProductName", オレDBType.VarChar, 255))
        insertCmd.Parameters.Add(New オレDBParameter("Price", オレDBType.Double))

        For Each row As DataRow In dataTable.Rows
            insertCmd.Parameters(0).Value = row("ProductID")
            insertCmd.Parameters(1).Value = row("ProductName")
            insertCmd.Parameters(2).Value = row("Price")
            insertCmd.ExecuteなしnQuery()
        Next
    End Using
End Using

using IronXL;
using System.Data;

// Build the same DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// Create a new workbook and worksheet -- no template file needed
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write column headers into the first row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);
}

// Write data rows
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        worksheet.SetCellValue(row + 1, col, dataTable.Rows[row][col].ToString());
    }
}

workbook.SaveAs("Products.xlsx");

Imports IronXL
Imports System.Data

' Build the same DataTable
Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

' Create a new workbook and worksheet -- no template file needed
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")

' Write column headers into the first row
For col As Integer = 0 To dataTable.Columns.Count - 1
    worksheet.SetCellValue(0, col, dataTable.Columns(col).ColumnName)
Next

' Write data rows
For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dataTable.Rows(row)(col).ToString())
    Next
Next

workbook.SaveAs("Products.xlsx")

using IronXL;
using IronXL.Styles;
using System.Data;

DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write and style the header row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    var headerCell = worksheet[$"{(char)('A' + col)}1"];
    headerCell.Value = dataTable.Columns[col].ColumnName;
    headerCell.Style.Font.Bold = true;
    headerCell.Style.BackgroundColor = "#4472C4";
    headerCell.Style.Font.FontColor = "#FFFFFF";
}

// Write data rows with price column formatted as currency
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        var cell = worksheet[$"{(char)('A' + col)}{row + 2}"];
        cell.Value = dataTable.Rows[row][col].ToString();

        // Apply currency format to the Price column
        if (dataTable.Columns[col].ColumnName == "Price")
            cell.FormatString = "$#,##0.00";
    }
}

// Auto-size columns so content is not clipped
worksheet.AutoSizeColumn(0);
worksheet.AutoSizeColumn(1);
worksheet.AutoSizeColumn(2);

workbook.SaveAs("ProductsStyled.xlsx");

Imports IronXL
Imports IronXL.Styles
Imports System.Data

Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")

' Write and style the header row
For col As Integer = 0 To dataTable.Columns.Count - 1
    Dim headerCell = worksheet($"{ChrW(AscW("A"c) + col)}1")
    headerCell.Value = dataTable.Columns(col).ColumnName
    headerCell.Style.Font.Bold = True
    headerCell.Style.BackgroundColor = "#4472C4"
    headerCell.Style.Font.FontColor = "#FFFFFF"
Next

' Write data rows with price column formatted as currency
For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        Dim cell = worksheet($"{ChrW(AscW("A"c) + col)}{row + 2}")
        cell.Value = dataTable.Rows(row)(col).ToString()

        ' Apply currency format to the Price column
        If dataTable.Columns(col).ColumnName = "Price" Then
            cell.FormatString = "$#,##0.00"
        End If
    Next
Next

' Auto-size columns so content is not clipped
worksheet.AutoSizeColumn(0)
worksheet.AutoSizeColumn(1)
worksheet.AutoSizeColumn(2)

workbook.SaveAs("ProductsStyled.xlsx")

// In an ASP.NET controller action
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Report");

// Assume dataTable is populated from your data source
for (int col = 0; col < dataTable.Columns.Count; col++)
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);

for (int row = 0; row < dataTable.Rows.Count; row++)
    for (int col = 0; col < dataTable.Columns.Count; col++)
        worksheet.SetCellValue(row + 1, col, dataTable.Rows[row][col].ToString());

byte[] fileBytes = workbook.ToByteArray();
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");

' In an ASP.NET controller action
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Report")

' Assume dataTable is populated from your data source
For col As Integer = 0 To dataTable.Columns.Count - 1
    worksheet.SetCellValue(0, col, dataTable.Columns(col).ColumnName)
Next

For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dataTable.Rows(row)(col).ToString())
    Next
Next

Dim fileBytes As Byte() = workbook.ToByteArray()
Return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx")

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("TypedData");

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Amount", typeof(decimal));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(1, 1500.75m, new DateTime(2025, 6, 15));
table.Rows.Add(2, 3200.00m, new DateTime(2025, 7, 4));

// Write headers
for (int col = 0; col < table.Columns.Count; col++)
    worksheet.SetCellValue(0, col, table.Columns[col].ColumnName);

// Write typed values -- no .ToString() conversion
for (int row = 0; row < table.Rows.Count; row++)
{
    worksheet.SetCellValue(row + 1, 0, (int)table.Rows[row]["ID"]);
    worksheet.SetCellValue(row + 1, 1, (double)(decimal)table.Rows[row]["Amount"]);
    worksheet[$"C{row + 2}"].Value = (DateTime)table.Rows[row]["Date"];
    worksheet[$"C{row + 2}"].FormatString = "yyyy-MM-dd";
}

workbook.SaveAs("TypedData.xlsx");

Imports System
Imports System.Data

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("TypedData")

Dim table As New DataTable()
table.Columns.Add("ID", GetType(Integer))
table.Columns.Add("Amount", GetType(Decimal))
table.Columns.Add("Date", GetType(DateTime))
table.Rows.Add(1, 1500.75D, New DateTime(2025, 6, 15))
table.Rows.Add(2, 3200.00D, New DateTime(2025, 7, 4))

' Write headers
For col As Integer = 0 To table.Columns.Count - 1
    worksheet.SetCellValue(0, col, table.Columns(col).ColumnName)
Next

' Write typed values -- no .ToString() conversion
For row As Integer = 0 To table.Rows.Count - 1
    worksheet.SetCellValue(row + 1, 0, CInt(table.Rows(row)("ID")))
    worksheet.SetCellValue(row + 1, 1, CDbl(CDec(table.Rows(row)("Amount"))))
    worksheet($"C{row + 2}").Value = CType(table.Rows(row)("Date"), DateTime)
    worksheet($"C{row + 2}").FormatString = "yyyy-MM-dd"
Next

workbook.SaveAs("TypedData.xlsx")

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

Imports IronXL

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