ASP.NET匯出到Excel：IronXL、ClosedXML和EPPlus的對比

dotnet add package IronXL.Excel

Install-Package IronXL.Excel

// Traditional approach -- exports HTML disguised as Excel
public void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("content-disposition", "attachment;filename=Report.xls");
    StringWriter stringWriter = new StringWriter();
    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
    // Render grid content as HTML
    DataGrid1.RenderControl(htmlTextWriter);
    Response.Write(stringWriter.ToString());
    Response.End();
}

public override void VerifyRenderingInServerForm(Control control)
{
    // Required to prevent server form rendering errors
}

' Traditional approach -- exports HTML disguised as Excel
Public Sub ExportToExcel(sender As Object, e As EventArgs)
    Response.Clear()
    Response.Buffer = True
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("content-disposition", "attachment;filename=Report.xls")
    Dim stringWriter As New StringWriter()
    Dim htmlTextWriter As New HtmlTextWriter(stringWriter)
    ' Render grid content as HTML
    DataGrid1.RenderControl(htmlTextWriter)
    Response.Write(stringWriter.ToString())
    Response.End()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Required to prevent server form rendering errors
End Sub

using IronXL;
using System.Data;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;

public class ReportController : Controller
{
    private readonly string _connectionString;

    public ReportController(IConfiguration config)
    {
        _connectionString = config.GetConnectionString("DefaultConnection");
    }

    [HttpGet]
    public IActionResult DownloadReport()
    {
        DataTable table = new DataTable();

        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string query = "SELECT OrderId, CustomerName, Total, OrderDate FROM Orders";
            using SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            adapter.Fill(table);
        }

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

        // Write column headers from DataTable schema
        for (int col = 0; col < table.Columns.Count; col++)
        {
            worksheet[0, col].Value = table.Columns[col].ColumnName;
        }

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

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

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.AspNetCore.Mvc

Public Class ReportController
    Inherits Controller

    Private ReadOnly _connectionString As String

    Public Sub New(config As IConfiguration)
        _connectionString = config.GetConnectionString("DefaultConnection")
    End Sub

    <HttpGet>
    Public Function DownloadReport() As IActionResult
        Dim table As New DataTable()

        Using connection As New SqlConnection(_connectionString)
            Dim query As String = "SELECT OrderId, CustomerName, Total, OrderDate FROM Orders"
            Using adapter As New SqlDataAdapter(query, connection)
                adapter.Fill(table)
            End Using
        End Using

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

        ' Write column headers from DataTable schema
        For col As Integer = 0 To table.Columns.Count - 1
            worksheet(0, col).Value = table.Columns(col).ColumnName
        Next

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

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

Install-Package ClosedXML

using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;

public class ExportController : Controller
{
    [HttpGet]
    public IActionResult ExportWithClosedXML()
    {
        using var workbook = new XLWorkbook();
        var worksheet = workbook.AddWorksheet("Data");
        worksheet.Cell(1, 1).Value = "Name";
        worksheet.Cell(1, 2).Value = "Amount";
        worksheet.Cell(2, 1).Value = "Alpha";
        worksheet.Cell(2, 2).Value = 1200;

        using var stream = new MemoryStream();
        workbook.SaveAs(stream);
        return File(stream.ToArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "export.xlsx");
    }
}

Imports ClosedXML.Excel
Imports Microsoft.AspNetCore.Mvc
Imports System.IO

Public Class ExportController
    Inherits Controller

    <HttpGet>
    Public Function ExportWithClosedXML() As IActionResult
        Using workbook As New XLWorkbook()
            Dim worksheet = workbook.AddWorksheet("Data")
            worksheet.Cell(1, 1).Value = "Name"
            worksheet.Cell(1, 2).Value = "Amount"
            worksheet.Cell(2, 1).Value = "Alpha"
            worksheet.Cell(2, 2).Value = 1200

            Using stream As New MemoryStream()
                workbook.SaveAs(stream)
                Return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx")
            End Using
        End Using
    End Function
End Class