C# DataGridView 匯出到 Excel：完整格式指南 | IronXL

Install-Package IronXL.Excel

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

void Form1_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ProductID", typeof(int));
    dt.Columns.Add("ProductName", typeof(string));
    dt.Columns.Add("Price", typeof(decimal));
    dt.Columns.Add("Stock", typeof(int));

    dt.Rows.Add(1, "Laptop", 999.99m, 50);
    dt.Rows.Add(2, "Mouse", 29.99m, 200);
    dt.Rows.Add(3, "Keyboard", 79.99m, 150);
    dt.Rows.Add(4, "Monitor", 349.99m, 75);
    dt.Rows.Add(5, "Webcam", 89.99m, 120);

    dataGridView1.DataSource = dt;
}

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt As New DataTable()
    dt.Columns.Add("ProductID", GetType(Integer))
    dt.Columns.Add("ProductName", GetType(String))
    dt.Columns.Add("Price", GetType(Decimal))
    dt.Columns.Add("Stock", GetType(Integer))

    dt.Rows.Add(1, "Laptop", 999.99D, 50)
    dt.Rows.Add(2, "Mouse", 29.99D, 200)
    dt.Rows.Add(3, "Keyboard", 79.99D, 150)
    dt.Rows.Add(4, "Monitor", 349.99D, 75)
    dt.Rows.Add(5, "Webcam", 89.99D, 120)

    dataGridView1.DataSource = dt
End Sub

void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        DataTable dt = new DataTable();

        foreach (DataGridViewColumn column in dataGridView1.Columns)
            dt.Columns.Add(column.HeaderText);

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.IsNewRow) continue;

            DataRow dataRow = dt.NewRow();
            for (int i = 0; i < dataGridView1.Columns.Count; i++)
                dataRow[i] = row.Cells[i].Value ?? DBNull.Value;

            dt.Rows.Add(dataRow);
        }

        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        worksheet.Name = "Product Data";

        worksheet.LoadFromDataTable(dt, true);

        string outputPath = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
            "DataGridViewExport.xlsx"
        );

        workbook.SaveAs(outputPath);
        MessageBox.Show($"Exported successfully to:\n{outputPath}", "Export Complete",
            MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Export failed: {ex.Message}", "Error",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Imports System
Imports System.Data
Imports System.IO
Imports System.Windows.Forms

Public Sub btnExport_Click(sender As Object, e As EventArgs)
    Try
        Dim dt As New DataTable()

        For Each column As DataGridViewColumn In dataGridView1.Columns
            dt.Columns.Add(column.HeaderText)
        Next

        For Each row As DataGridViewRow In dataGridView1.Rows
            If row.IsNewRow Then Continue For

            Dim dataRow As DataRow = dt.NewRow()
            For i As Integer = 0 To dataGridView1.Columns.Count - 1
                dataRow(i) = If(row.Cells(i).Value, DBNull.Value)
            Next

            dt.Rows.Add(dataRow)
        Next

        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        worksheet.Name = "Product Data"

        worksheet.LoadFromDataTable(dt, True)

        Dim outputPath As String = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
            "DataGridViewExport.xlsx"
        )

        workbook.SaveAs(outputPath)
        MessageBox.Show($"Exported successfully to:{Environment.NewLine}{outputPath}", "Export Complete",
                        MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MessageBox.Show($"Export failed: {ex.Message}", "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

void ExportWithFormatting(object sender, EventArgs e)
{
    WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
    WorkSheet worksheet = workbook.DefaultWorkSheet;
    worksheet.Name = "Formatted Export";

    string[] headers = { "ID", "Product Name", "Price", "Stock" };

    // Write and style header row
    for (int col = 0; col < headers.Length; col++)
    {
        char colLetter = (char)('A' + col);
        string cellAddress = $"{colLetter}1";

        worksheet.SetCellValue(0, col, headers[col]);
        worksheet[cellAddress].Style.Font.Bold = true;
        worksheet[cellAddress].Style.Font.Height = 12;
        worksheet[cellAddress].Style.SetBackgroundColor("#4472C4");
        worksheet[cellAddress].Style.Font.Color = "#FFFFFF";
        worksheet[cellAddress].Style.HorizontalAlignment =
            IronXL.Styles.HorizontalAlignment.Center;
    }

    // Write data rows with alternating background colors
    int rowIndex = 1;
    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        if (row.IsNewRow) continue;

        for (int col = 0; col < dataGridView1.Columns.Count; col++)
        {
            worksheet.SetCellValue(rowIndex, col,
                row.Cells[col].Value?.ToString() ?? string.Empty);
        }

        if (rowIndex % 2 == 0)
        {
            string rangeAddress = $"A{rowIndex + 1}:D{rowIndex + 1}";
            worksheet[rangeAddress].Style.SetBackgroundColor("#D6DCE5");
        }

        rowIndex++;
    }

    // Format the Price column as currency
    worksheet["C2:C100"].Style.Format = "$#,##0.00";

    // Auto-fit column widths
    worksheet.AutoSizeColumn(0);
    worksheet.AutoSizeColumn(1);
    worksheet.AutoSizeColumn(2);
    worksheet.AutoSizeColumn(3);

    string outputPath = Path.Combine(
        Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
        "FormattedExport.xlsx"
    );

    workbook.SaveAs(outputPath);
    MessageBox.Show("Formatted export complete.", "Done",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

Option Strict On



Sub ExportWithFormatting(sender As Object, e As EventArgs)
    Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
    Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
    worksheet.Name = "Formatted Export"

    Dim headers As String() = {"ID", "Product Name", "Price", "Stock"}

    ' Write and style header row
    For col As Integer = 0 To headers.Length - 1
        Dim colLetter As Char = ChrW(AscW("A"c) + col)
        Dim cellAddress As String = $"{colLetter}1"

        worksheet.SetCellValue(0, col, headers(col))
        worksheet(cellAddress).Style.Font.Bold = True
        worksheet(cellAddress).Style.Font.Height = 12
        worksheet(cellAddress).Style.SetBackgroundColor("#4472C4")
        worksheet(cellAddress).Style.Font.Color = "#FFFFFF"
        worksheet(cellAddress).Style.HorizontalAlignment = IronXL.Styles.HorizontalAlignment.Center
    Next

    ' Write data rows with alternating background colors
    Dim rowIndex As Integer = 1
    For Each row As DataGridViewRow In dataGridView1.Rows
        If row.IsNewRow Then Continue For

        For col As Integer = 0 To dataGridView1.Columns.Count - 1
            worksheet.SetCellValue(rowIndex, col, If(row.Cells(col).Value?.ToString(), String.Empty))
        Next

        If rowIndex Mod 2 = 0 Then
            Dim rangeAddress As String = $"A{rowIndex + 1}:D{rowIndex + 1}"
            worksheet(rangeAddress).Style.SetBackgroundColor("#D6DCE5")
        End If

        rowIndex += 1
    Next

    ' Format the Price column as currency
    worksheet("C2:C100").Style.Format = "$#,##0.00"

    ' Auto-fit column widths
    worksheet.AutoSizeColumn(0)
    worksheet.AutoSizeColumn(1)
    worksheet.AutoSizeColumn(2)
    worksheet.AutoSizeColumn(3)

    Dim outputPath As String = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "FormattedExport.xlsx")

    workbook.SaveAs(outputPath)
    MessageBox.Show("Formatted export complete.", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub