如何使用 Interop 与 IronXL.Excel 将 DataTable 导出到 Excel C#

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

// Create a sample DataTable with employee data
DataTable dt = new DataTable("Employees");
dt.Columns.Add("EmployeeID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("Salary", typeof(decimal));

// Fill the DataTable with sample rows
dt.Rows.Add(1, "John Smith", "Engineering", 75000);
dt.Rows.Add(2, "Sarah Johnson", "Marketing", 65000);
dt.Rows.Add(3, "Michael Chen", "Finance", 70000);
dt.Rows.Add(4, "Emily Davis", "Engineering", 80000);

// Initialize Excel Application object
Application excelApp = new Application
{
    Visible = false,
    DisplayAlerts = false
};

// Create new Workbook and get the active Worksheet
Workbook workbook = excelApp.Workbooks.Add();
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;

try
{
    // Write column headers to the first row
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
    }
    // Write data rows starting from row 2
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
        }
    }
    // Define file path and save the Excel file
    string filePath = @"C:\Reports\EmployeeReport_Interop.xlsx";
    workbook.SaveAs(filePath);
    Console.WriteLine("Excel file created successfully using Interop.");
}
catch (Exception ex)
{
    Console.WriteLine("Error exporting data to Excel: " + ex.Message);
}
finally
{
    // Close workbook and quit Excel application
    workbook.Close();
    excelApp.Quit();
    // Critical: Release COM objects to prevent memory leaks
    Marshal.ReleaseComObject(worksheet);
    Marshal.ReleaseComObject(workbook);
    Marshal.ReleaseComObject(excelApp);
}

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

' Create a sample DataTable with employee data
Dim dt As New DataTable("Employees")
dt.Columns.Add("EmployeeID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Department", GetType(String))
dt.Columns.Add("Salary", GetType(Decimal))

' Fill the DataTable with sample rows
dt.Rows.Add(1, "John Smith", "Engineering", 75000)
dt.Rows.Add(2, "Sarah Johnson", "Marketing", 65000)
dt.Rows.Add(3, "Michael Chen", "Finance", 70000)
dt.Rows.Add(4, "Emily Davis", "Engineering", 80000)

' Initialize Excel Application object
Dim excelApp As New Application With {
    .Visible = False,
    .DisplayAlerts = False
}

' Create new Workbook and get the active Worksheet
Dim workbook As Workbook = excelApp.Workbooks.Add()
Dim worksheet As Worksheet = CType(workbook.ActiveSheet, Worksheet)

Try
    ' Write column headers to the first row
    For i As Integer = 0 To dt.Columns.Count - 1
        worksheet.Cells(1, i + 1) = dt.Columns(i).ColumnName
    Next
    ' Write data rows starting from row 2
    For i As Integer = 0 To dt.Rows.Count - 1
        For j As Integer = 0 To dt.Columns.Count - 1
            worksheet.Cells(i + 2, j + 1) = dt.Rows(i)(j).ToString()
        Next
    Next
    ' Define file path and save the Excel file
    Dim filePath As String = "C:\Reports\EmployeeReport_Interop.xlsx"
    workbook.SaveAs(filePath)
    Console.WriteLine("Excel file created successfully using Interop.")
Catch ex As Exception
    Console.WriteLine("Error exporting data to Excel: " & ex.Message)
Finally
    ' Close workbook and quit Excel application
    workbook.Close()
    excelApp.Quit()
    ' Critical: Release COM objects to prevent memory leaks
    Marshal.ReleaseComObject(worksheet)
    Marshal.ReleaseComObject(workbook)
    Marshal.ReleaseComObject(excelApp)
End Try

Install-Package IronXL.Excel

dotnet add package IronXL.Excel

using IronXL;
using System.Data;

// Create a sample DataTable
DataTable dt = new DataTable("Employees");
dt.Columns.Add("EmployeeID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("Salary", typeof(decimal));

// Fill DataTable with rows of data
dt.Rows.Add(1, "John Smith", "Engineering", 75000);
dt.Rows.Add(2, "Sarah Johnson", "Marketing", 65000);
dt.Rows.Add(3, "Michael Chen", "Finance", 70000);
dt.Rows.Add(4, "Emily Davis", "Engineering", 80000);

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

// Write column names to the first row as headers
for (int i = 0; i < dt.Columns.Count; i++)
{
    sheet.SetCellValue(0, i, dt.Columns[i].ColumnName);
}

// Export DataTable rows to Excel cells
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        sheet.SetCellValue(i + 1, j, dt.Rows[i][j]);
    }
}

// Save to file path as xlsx format
string filePath = @"C:\Reports\EmployeeReport_IronXL.xlsx";
workbook.SaveAs(filePath);
Console.WriteLine("Excel file created successfully using IronXL.");

using IronXL;
using System.Data;
using System.IO;
using System;
public class ExcelExporter
{
    /// <summary>
    /// Export a DataTable to Excel file at the specified path
    /// </summary>
    public static bool ExportToExcel(DataTable dt, string filePath)
    {
        if (dt == null || dt.Rows.Count == 0)
            return false;
        try
        {
            // Create new workbook and worksheet
            WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
            WorkSheet sheet = workbook.CreateWorkSheet(dt.TableName ?? "Sheet1");
            // Write column names as headers in the first row
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                var cell = sheet.GetCellAt(0, i);
                cell.Value = dt.Columns[i].ColumnName;
                cell.Style.Font.Bold = true;
            }
            // Fill cells with data values from each row
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var value = dt.Rows[i][j];
                    // Handle missing or null values
                    if (value == DBNull.Value || value == null)
                        sheet.SetCellValue(i + 1, j, "");
                    else
                        sheet.SetCellValue(i + 1, j, value);
                }
            }
            // Validate file path and save
            FileInfo fileInfo = new FileInfo(filePath);
            if (!fileInfo.Directory.Exists)
                fileInfo.Directory.Create();
            workbook.SaveAs(filePath);
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Export failed: " + ex.Message);
            return false;
        }
    }
    /// <summary>
    /// Query a DataTable and export filtered results
    /// </summary>
    public static DataTable FilterAndExport(DataTable dt, string filterExpression, string filePath)
    {
        if (dt == null)
            return dt;
        // Create filtered view and export
        DataRow[] filteredRows = dt.Select(filterExpression);
        DataTable filteredTable = dt.Clone();
        foreach (DataRow row in filteredRows)
        {
            filteredTable.ImportRow(row);
        }
        ExportToExcel(filteredTable, filePath);
        return filteredTable;
    }
    /// <summary>
    /// Export DataTable to CSV file as an alternative format
    /// </summary>
    public static string ExportToCsv(DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        // Write column headers
        string[] columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
        sb.AppendLine(string.Join(",", columnNames));
        // Write data rows
        foreach (DataRow row in dt.Rows)
        {
            string[] values = row.ItemArray.Select(field => field?.ToString() ?? "").ToArray();
            sb.AppendLine(string.Join(",", values));
        }
        return sb.ToString();
    }
}
// Usage example in Windows Forms or WPF event handler
public void ExportButton_Click(object sender, EventArgs e)
{
    DataTable dt = GetEmployeeData(); // Your data source
    string filePath = @"C:\Reports\Export.xlsx";
    bool success = ExcelExporter.ExportToExcel(dt, filePath);
    if (success)
        Console.WriteLine("Export completed successfully");
}

Imports IronXL
Imports System.Data
Imports System.IO
Imports System

Public Class ExcelExporter
    ''' <summary>
    ''' Export a DataTable to Excel file at the specified path
    ''' </summary>
    Public Shared Function ExportToExcel(dt As DataTable, filePath As String) As Boolean
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then Return False
        Try
            ' Create new workbook and worksheet
            Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
            Dim sheet As WorkSheet = workbook.CreateWorkSheet(If(dt.TableName, "Sheet1"))
            ' Write column names as headers in the first row
            For i As Integer = 0 To dt.Columns.Count - 1
                Dim cell = sheet.GetCellAt(0, i)
                cell.Value = dt.Columns(i).ColumnName
                cell.Style.Font.Bold = True
            Next
            ' Fill cells with data values from each row
            For i As Integer = 0 To dt.Rows.Count - 1
                For j As Integer = 0 To dt.Columns.Count - 1
                    Dim value = dt.Rows(i)(j)
                    ' Handle missing or null values
                    If value Is DBNull.Value OrElse value Is Nothing Then
                        sheet.SetCellValue(i + 1, j, "")
                    Else
                        sheet.SetCellValue(i + 1, j, value)
                    End If
                Next
            Next
            ' Validate file path and save
            Dim fileInfo As New FileInfo(filePath)
            If Not fileInfo.Directory.Exists Then fileInfo.Directory.Create()
            workbook.SaveAs(filePath)
            Return True
        Catch ex As Exception
            Console.WriteLine("Export failed: " & ex.Message)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Query a DataTable and export filtered results
    ''' </summary>
    Public Shared Function FilterAndExport(dt As DataTable, filterExpression As String, filePath As String) As DataTable
        If dt Is Nothing Then Return dt
        ' Create filtered view and export
        Dim filteredRows As DataRow() = dt.Select(filterExpression)
        Dim filteredTable As DataTable = dt.Clone()
        For Each row As DataRow In filteredRows
            filteredTable.ImportRow(row)
        Next
        ExportToExcel(filteredTable, filePath)
        Return filteredTable
    End Function

    ''' <summary>
    ''' Export DataTable to CSV file as an alternative format
    ''' </summary>
    Public Shared Function ExportToCsv(dt As DataTable) As String
        Dim sb As New StringBuilder()
        ' Write column headers
        Dim columnNames As String() = dt.Columns.Cast(Of DataColumn)().Select(Function(column) column.ColumnName).ToArray()
        sb.AppendLine(String.Join(",", columnNames))
        ' Write data rows
        For Each row As DataRow In dt.Rows
            Dim values As String() = row.ItemArray.Select(Function(field) If(field?.ToString(), "")).ToArray()
            sb.AppendLine(String.Join(",", values))
        Next
        Return sb.ToString()
    End Function
End Class

' Usage example in Windows Forms or WPF event handler
Public Sub ExportButton_Click(sender As Object, e As EventArgs)
    Dim dt As DataTable = GetEmployeeData() ' Your data source
    Dim filePath As String = "C:\Reports\Export.xlsx"
    Dim success As Boolean = ExcelExporter.ExportToExcel(dt, filePath)
    If success Then Console.WriteLine("Export completed successfully")
End Sub

using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Runtime.InteropServices;
DataTable dt = new DataTable("Sales");
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("Revenue", typeof(decimal));
dt.Rows.Add("Widget A", 15000.50m);
dt.Rows.Add("Widget B", 22500.75m);
Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Add();
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;
// Write column headers with formatting to first row
for (int i = 0; i < dt.Columns.Count; i++)
{
    Range headerCell = worksheet.Cells[1, i + 1];
    headerCell.Value = dt.Columns[i].ColumnName;
    headerCell.Font.Bold = true;
    headerCell.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
    headerCell.Borders.LineStyle = XlLineStyle.xlContinuous;
}
// Write data values to cells
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
    }
}
string filePath = @"C:\Reports\FormattedReport_Interop.xlsx";
workbook.SaveAs(filePath);
workbook.Close();
excelApp.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

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

Dim dt As New DataTable("Sales")
dt.Columns.Add("Product", GetType(String))
dt.Columns.Add("Revenue", GetType(Decimal))
dt.Rows.Add("Widget A", 15000.5D)
dt.Rows.Add("Widget B", 22500.75D)
Dim excelApp As New Application()
Dim workbook As Workbook = excelApp.Workbooks.Add()
Dim worksheet As Worksheet = CType(workbook.ActiveSheet, Worksheet)

' Write column headers with formatting to first row
For i As Integer = 0 To dt.Columns.Count - 1
    Dim headerCell As Range = worksheet.Cells(1, i + 1)
    headerCell.Value = dt.Columns(i).ColumnName
    headerCell.Font.Bold = True
    headerCell.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue)
    headerCell.Borders.LineStyle = XlLineStyle.xlContinuous
Next

' Write data values to cells
For i As Integer = 0 To dt.Rows.Count - 1
    For j As Integer = 0 To dt.Columns.Count - 1
        worksheet.Cells(i + 2, j + 1) = dt.Rows(i)(j)
    Next
Next

Dim filePath As String = "C:\Reports\FormattedReport_Interop.xlsx"
workbook.SaveAs(filePath)
workbook.Close()
excelApp.Quit()
Marshal.ReleaseComObject(worksheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(excelApp)

using IronXL;
using IronXL.Styles;
using System.Data;
DataTable dt = new DataTable("Sales");
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("Revenue", typeof(decimal));
dt.Rows.Add("Widget A", 15000.50m);
dt.Rows.Add("Widget B", 22500.75m);
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet sheet = workbook.CreateWorkSheet("Sales");
// Write column headers with formatting to first row
for (int i = 0; i < dt.Columns.Count; i++)
{
    sheet.SetCellValue(0, i, dt.Columns[i].ColumnName);
    var cell = sheet.GetCellAt(0, i);
    cell.Value = dt.Columns[i].ColumnName;
    cell.Style.Font.Bold = true;
    cell.Style.SetBackgroundColor("#ADD8E6");
    cell.Style.BottomBorder.SetColor("#000000");
    cell.Style.BottomBorder.Type = BorderType.Thin;
}
// Write data rows with values from DataTable
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        sheet.SetCellValue(i + 1, j, dt.Rows[i][j]);
    }
}
string filePath = @"C:\Reports\FormattedReport_IronXL.xlsx";
workbook.SaveAs(filePath);

Imports IronXL
Imports IronXL.Styles
Imports System.Data

Dim dt As New DataTable("Sales")
dt.Columns.Add("Product", GetType(String))
dt.Columns.Add("Revenue", GetType(Decimal))
dt.Rows.Add("Widget A", 15000.5D)
dt.Rows.Add("Widget B", 22500.75D)

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

' Write column headers with formatting to first row
For i As Integer = 0 To dt.Columns.Count - 1
    sheet.SetCellValue(0, i, dt.Columns(i).ColumnName)
    Dim cell = sheet.GetCellAt(0, i)
    cell.Value = dt.Columns(i).ColumnName
    cell.Style.Font.Bold = True
    cell.Style.SetBackgroundColor("#ADD8E6")
    cell.Style.BottomBorder.SetColor("#000000")
    cell.Style.BottomBorder.Type = BorderType.Thin
Next

' Write data rows with values from DataTable
For i As Integer = 0 To dt.Rows.Count - 1
    For j As Integer = 0 To dt.Columns.Count - 1
        sheet.SetCellValue(i + 1, j, dt.Rows(i)(j))
    Next
Next

Dim filePath As String = "C:\Reports\FormattedReport_IronXL.xlsx"
workbook.SaveAs(filePath)