使用 IronXL 在 C# 中將資料集匯出到 Excel | 無需 Office

dotnet add package IronXL.Excel
# Or in Visual Studio Package Manager Console:
# Install-Package IronXL.Excel

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

using IronXL;
using System.Data;

// Build an in-memory DataTable
DataTable dt = new DataTable("Employees");
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));

dt.Rows.Add(1, "John Smith", "Engineering");
dt.Rows.Add(2, "Sarah Jones", "Marketing");
dt.Rows.Add(3, "Mike Wilson", "Sales");

// Create a workbook and a named worksheet
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Employees");

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

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

workbook.SaveAs("EmployeeData.xlsx");

Imports IronXL
Imports System.Data

' Build an in-memory DataTable
Dim dt As New DataTable("Employees")
dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Department", GetType(String))

dt.Rows.Add(1, "John Smith", "Engineering")
dt.Rows.Add(2, "Sarah Jones", "Marketing")
dt.Rows.Add(3, "Mike Wilson", "Sales")

' Create a workbook and a named worksheet
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Employees")

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

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

workbook.SaveAs("EmployeeData.xlsx")

using IronXL;
using System.Data;

// Build a DataSet with two related tables
DataSet ds = new DataSet("CompanyData");

DataTable dtProducts = new DataTable("Products");
dtProducts.Columns.Add("ProductID", typeof(int));
dtProducts.Columns.Add("ProductName", typeof(string));
dtProducts.Columns.Add("Price", typeof(decimal));
dtProducts.Rows.Add(101, "Widget A", 29.99m);
dtProducts.Rows.Add(102, "Widget B", 49.99m);
ds.Tables.Add(dtProducts);

DataTable dtOrders = new DataTable("Orders");
dtOrders.Columns.Add("OrderID", typeof(int));
dtOrders.Columns.Add("Customer", typeof(string));
dtOrders.Columns.Add("Total", typeof(decimal));
dtOrders.Rows.Add(1001, "Acme Corp", 599.90m);
dtOrders.Rows.Add(1002, "Tech Inc", 299.95m);
ds.Tables.Add(dtOrders);

// Create one workbook and iterate each DataTable
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

foreach (DataTable table in ds.Tables)
{
    WorkSheet sheet = workbook.CreateWorkSheet(table.TableName);

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

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

workbook.SaveAs("CompanyReport.xlsx");

Imports IronXL
Imports System.Data

' Build a DataSet with two related tables
Dim ds As New DataSet("CompanyData")

Dim dtProducts As New DataTable("Products")
dtProducts.Columns.Add("ProductID", GetType(Integer))
dtProducts.Columns.Add("ProductName", GetType(String))
dtProducts.Columns.Add("Price", GetType(Decimal))
dtProducts.Rows.Add(101, "Widget A", 29.99D)
dtProducts.Rows.Add(102, "Widget B", 49.99D)
ds.Tables.Add(dtProducts)

Dim dtOrders As New DataTable("Orders")
dtOrders.Columns.Add("OrderID", GetType(Integer))
dtOrders.Columns.Add("Customer", GetType(String))
dtOrders.Columns.Add("Total", GetType(Decimal))
dtOrders.Rows.Add(1001, "Acme Corp", 599.90D)
dtOrders.Rows.Add(1002, "Tech Inc", 299.95D)
ds.Tables.Add(dtOrders)

' Create one workbook and iterate each DataTable
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

For Each table As DataTable In ds.Tables
    Dim sheet As WorkSheet = workbook.CreateWorkSheet(table.TableName)

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

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

workbook.SaveAs("CompanyReport.xlsx")

using IronXL;
using System.Data;

DataTable dt = new DataTable("Sales");
dt.Columns.Add("Region", typeof(string));
dt.Columns.Add("Q1", typeof(decimal));
dt.Columns.Add("Q2", typeof(decimal));
dt.Columns.Add("Q3", typeof(decimal));
dt.Columns.Add("Q4", typeof(decimal));

dt.Rows.Add("無rth", 120000m, 135000m, 142000m, 158000m);
dt.Rows.Add("South", 98000m, 104000m, 112000m, 121000m);
dt.Rows.Add("East",  87000m, 93000m,  99000m,  108000m);
dt.Rows.Add("West",  145000m, 152000m, 161000m, 174000m);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet sheet = workbook.CreateWorkSheet("Sales");

// Write and style headers
for (int col = 0; col < dt.Columns.Count; col++)
{
    sheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
    sheet["A1:E1"].Style.Font.Bold = true;
    sheet["A1:E1"].Style.Font.Height = 12;
}

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

workbook.SaveAs("SalesReport.xlsx");

Imports IronXL
Imports System.Data

Dim dt As New DataTable("Sales")
dt.Columns.Add("Region", GetType(String))
dt.Columns.Add("Q1", GetType(Decimal))
dt.Columns.Add("Q2", GetType(Decimal))
dt.Columns.Add("Q3", GetType(Decimal))
dt.Columns.Add("Q4", GetType(Decimal))

dt.Rows.Add("無rth", 120000D, 135000D, 142000D, 158000D)
dt.Rows.Add("South", 98000D, 104000D, 112000D, 121000D)
dt.Rows.Add("East", 87000D, 93000D, 99000D, 108000D)
dt.Rows.Add("West", 145000D, 152000D, 161000D, 174000D)

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim sheet As WorkSheet = workbook.CreateWorkSheet("Sales")

' Write and style headers
For col As Integer = 0 To dt.Columns.Count - 1
    sheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
    sheet("A1:E1").Style.Font.Bold = True
    sheet("A1:E1").Style.Font.Height = 12
Next

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

workbook.SaveAs("SalesReport.xlsx")

using IronXL;
using System.Data;
using System.Data.SqlClient;

string connectionString = "Server=localhost;Database=SalesDB;Integrated Security=True;";
string query = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers";

// Fill DataSet from SQL query
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    adapter.Fill(ds, "Customers");
}

DataTable dt = ds.Tables["Customers"]!;

// Create workbook and export
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("CustomerData");

// Bold header row
for (int col = 0; col < dt.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
}
worksheet["A1:D1"].Style.Font.Bold = true;

// Write rows, handling DBNull values safely
for (int row = 0; row < dt.Rows.Count; row++)
{
    for (int col = 0; col < dt.Columns.Count; col++)
    {
        object cellValue = dt.Rows[row][col];
        worksheet.SetCellValue(row + 1, col,
            cellValue == DBNull.Value ? "" : cellValue.ToString()!);
    }
}

workbook.SaveAs("CustomerExport.xlsx");
Console.WriteLine("Export complete.");

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

Dim connectionString As String = "Server=localhost;Database=SalesDB;Integrated Security=True;"
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers"

' Fill DataSet from SQL query
Dim ds As New DataSet()
Using connection As New SqlConnection(connectionString)
    Dim adapter As New SqlDataAdapter(query, connection)
    adapter.Fill(ds, "Customers")
End Using

Dim dt As DataTable = ds.Tables("Customers")

' Create workbook and export
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("CustomerData")

' Bold header row
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next
worksheet("A1:D1").Style.Font.Bold = True

' Write rows, handling DBNull values safely
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        Dim cellValue As Object = dt.Rows(row)(col)
        worksheet.SetCellValue(row + 1, col, If(cellValue Is DBNull.Value, "", cellValue.ToString()))
    Next
Next

workbook.SaveAs("CustomerExport.xlsx")
Console.WriteLine("Export complete.")

using IronXL;
using System.Data;
using System.Data.SqlClient;

string connectionString = "Server=localhost;Database=InventoryDB;Integrated Security=True;";
string query = "SELECT ProductID, SKU, Description, StockLevel, UnitCost FROM Products";

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet sheet = workbook.CreateWorkSheet("Inventory");

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

using SqlCommand cmd = new SqlCommand(query, conn);
using SqlDataReader reader = cmd.ExecuteReader();

// Write schema-derived headers
for (int col = 0; col < reader.FieldCount; col++)
{
    sheet.SetCellValue(0, col, reader.GetName(col));
}

// Stream rows directly into IronXL without buffering a DataTable
int excelRow = 1;
while (reader.Read())
{
    for (int col = 0; col < reader.FieldCount; col++)
    {
        sheet.SetCellValue(excelRow, col,
            reader.IsDBNull(col) ? "" : reader.GetValue(col).ToString()!);
    }
    excelRow++;
}

workbook.SaveAs("InventoryExport.xlsx");
Console.WriteLine($"Exported {excelRow - 1} rows.");

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

Dim connectionString As String = "Server=localhost;Database=InventoryDB;Integrated Security=True;"
Dim query As String = "SELECT ProductID, SKU, Description, StockLevel, UnitCost FROM Products"

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim sheet As WorkSheet = workbook.CreateWorkSheet("Inventory")

Using conn As New SqlConnection(connectionString)
    conn.Open()

    Using cmd As New SqlCommand(query, conn)
        Using reader As SqlDataReader = cmd.ExecuteReader()

            ' Write schema-derived headers
            For col As Integer = 0 To reader.FieldCount - 1
                sheet.SetCellValue(0, col, reader.GetName(col))
            Next

            ' Stream rows directly into IronXL without buffering a DataTable
            Dim excelRow As Integer = 1
            While reader.Read()
                For col As Integer = 0 To reader.FieldCount - 1
                    sheet.SetCellValue(excelRow, col, If(reader.IsDBNull(col), "", reader.GetValue(col).ToString()))
                Next
                excelRow += 1
            End While

            workbook.SaveAs("InventoryExport.xlsx")
            Console.WriteLine($"Exported {excelRow - 1} rows.")
        End Using
    End Using
End Using

using IronXL;
using System.IO;

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet sheet = workbook.CreateWorkSheet("Report");
sheet.SetCellValue(0, 0, "Generated Report");

// Save to file path
workbook.SaveAs("Report.xlsx");

// Save as XLS (legacy format)
workbook.SaveAs("Report.xls");

// Save to MemoryStream for HTTP response
using MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
byte[] fileBytes = ms.ToArray();
// fileBytes can be returned from an ASP.NET controller action

Imports IronXL
Imports System.IO

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim sheet As WorkSheet = workbook.CreateWorkSheet("Report")
sheet.SetCellValue(0, 0, "Generated Report")

' Save to file path
workbook.SaveAs("Report.xlsx")

' Save as XLS (legacy format)
workbook.SaveAs("Report.xls")

' Save to MemoryStream for HTTP response
Using ms As New MemoryStream()
    workbook.SaveAs(ms)
    Dim fileBytes As Byte() = ms.ToArray()
    ' fileBytes can be returned from an ASP.NET controller action
End Using

using IronXL;

WorkBook loaded = WorkBook.Load("EmployeeData.xlsx");
WorkSheet sheet = loaded.WorkSheets[0];

// Verify row count (header row + data rows)
int totalRows = sheet.Rows.Count();
Console.WriteLine($"Rows in file: {totalRows}");

// Spot-check specific cell values
string header = sheet["A1"].StringValue;
string firstEmployee = sheet["A2"].StringValue;

Console.WriteLine($"Header: {header}, First row: {firstEmployee}");

Imports IronXL

Dim loaded As WorkBook = WorkBook.Load("EmployeeData.xlsx")
Dim sheet As WorkSheet = loaded.WorkSheets(0)

' Verify row count (header row + data rows)
Dim totalRows As Integer = sheet.Rows.Count()
Console.WriteLine($"Rows in file: {totalRows}")

' Spot-check specific cell values
Dim header As String = sheet("A1").StringValue
Dim firstEmployee As String = sheet("A2").StringValue

Console.WriteLine($"Header: {header}, First row: {firstEmployee}")