如何使用 IronXL.Excel 使用 C# 將 SQL 資料匯出至 Excel

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Define connection string for SQL Server
string connectionString = @"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";
// SQL query to retrieve data from SQL Server table
string sql = "SELECT * FROM Customers";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Use SqlDataAdapter to fill DataTable with query results
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    // Create new workbook and worksheet
    WorkBook workBook = WorkBook.Create();
    WorkSheet workSheet = workBook.CreateWorkSheet("CustomerData");
    // Export DataTable to Excel worksheet with column names
    int rowIndex = 1;
    // Write column headers from DataTable
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        workSheet.SetCellValue(0, j, dt.Columns[j].ColumnName);
    }
    // Fill worksheet with data values
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            string cellValue = dt.Rows[i][j].ToString();
            workSheet.SetCellValue(rowIndex, j, cellValue);
        }
        rowIndex++;
    }
    // Save Excel file in xlsx format
    workBook.SaveAs("CustomerExport.xlsx");
}

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

' Define connection string for SQL Server
Dim connectionString As String = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"
' SQL query to retrieve data from SQL Server table
Dim sql As String = "SELECT * FROM Customers"

Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Use SqlDataAdapter to fill DataTable with query results
    Dim adapter As New SqlDataAdapter(sql, connection)
    Dim dt As New DataTable()
    adapter.Fill(dt)
    ' Create new workbook and worksheet
    Dim workBook As WorkBook = WorkBook.Create()
    Dim workSheet As WorkSheet = workBook.CreateWorkSheet("CustomerData")
    ' Export DataTable to Excel worksheet with column names
    Dim rowIndex As Integer = 1
    ' Write column headers from DataTable
    For j As Integer = 0 To dt.Columns.Count - 1
        workSheet.SetCellValue(0, j, dt.Columns(j).ColumnName)
    Next
    ' Fill worksheet with data values
    For i As Integer = 0 To dt.Rows.Count - 1
        For j As Integer = 0 To dt.Columns.Count - 1
            Dim cellValue As String = dt.Rows(i)(j).ToString()
            workSheet.SetCellValue(rowIndex, j, cellValue)
        Next
        rowIndex += 1
    Next
    ' Save Excel file in xlsx format
    workBook.SaveAs("CustomerExport.xlsx")
End Using

using IronXL;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI;
public partial class ExportPage : Page
{
    private void ExportButton_Click(object sender, EventArgs e)
    {
        string connectionString = @"Data Source=localhost;Initial Catalog=Sales;Integrated Security=True";
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Execute SQL query to get data from SQL server
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Orders", connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                // Create Excel workbook from DataSet - each table becomes a sheet
                WorkBook workBook = WorkBook.Create();
                WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
                // Convert to byte array for download
                byte[] fileBytes = workBook.ToByteArray();
                string filename = "OrdersExport.xlsx";
                // Set response headers for file download
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                Response.BinaryWrite(fileBytes);
                Response.End();
            }
        }
        catch (Exception ex)
        {
            // Handle any errors during export
            Response.Write("Export error: " + ex.Message);
        }
    }
}

Imports IronXL
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Web.UI

Public Partial Class ExportPage
    Inherits Page

    Private Sub ExportButton_Click(sender As Object, e As EventArgs)
        Dim connectionString As String = "Data Source=localhost;Initial Catalog=Sales;Integrated Security=True"
        Try
            Using connection As New SqlConnection(connectionString)
                ' Execute SQL query to get data from SQL server
                Dim adapter As New SqlDataAdapter("SELECT * FROM Orders", connection)
                Dim dataSet As New DataSet()
                adapter.Fill(dataSet)
                ' Create Excel workbook from DataSet - each table becomes a sheet
                Dim workBook As WorkBook = WorkBook.Create()
                WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
                ' Convert to byte array for download
                Dim fileBytes As Byte() = workBook.ToByteArray()
                Dim filename As String = "OrdersExport.xlsx"
                ' Set response headers for file download
                Response.Clear()
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                Response.AddHeader("content-disposition", "attachment;filename=" & filename)
                Response.BinaryWrite(fileBytes)
                Response.End()
            End Using
        Catch ex As Exception
            ' Handle any errors during export
            Response.Write("Export error: " & ex.Message)
        End Try
    End Sub
End Class

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Connection to SQL database
string connectionString = @"Data Source=.;Initial Catalog=Inventory;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Query multiple tables from database
    SqlDataAdapter productsAdapter = new SqlDataAdapter("SELECT * FROM Products", connection);
    SqlDataAdapter categoriesAdapter = new SqlDataAdapter("SELECT * FROM Categories", connection);
    DataSet dataSet = new DataSet();
    productsAdapter.Fill(dataSet, "Products");
    categoriesAdapter.Fill(dataSet, "Categories");
    // Create new workbook and load all tables
    WorkBook workBook = WorkBook.Create();
    WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
    // Save as xlsx file
    workBook.SaveAs("InventoryReport.xlsx");
}

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

' Connection to SQL database
Dim connectionString As String = "Data Source=.;Initial Catalog=Inventory;Integrated Security=True"
Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Query multiple tables from database
    Dim productsAdapter As New SqlDataAdapter("SELECT * FROM Products", connection)
    Dim categoriesAdapter As New SqlDataAdapter("SELECT * FROM Categories", connection)
    Dim dataSet As New DataSet()
    productsAdapter.Fill(dataSet, "Products")
    categoriesAdapter.Fill(dataSet, "Categories")
    ' Create new workbook and load all tables
    Dim workBook As WorkBook = WorkBook.Create()
    WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
    ' Save as xlsx file
    workBook.SaveAs("InventoryReport.xlsx")
End Using