C# DataGridView Export to Excel with Formatting：完整指南

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

private void Form1_Load(object sender, EventArgs e)
{
    // Create DataTable with sample data
    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));
    // Add rows with values
    dt.Rows.Add(1, "Laptop", 999.99, 50);
    dt.Rows.Add(2, "Mouse", 29.99, 200);
    dt.Rows.Add(3, "Keyboard", 79.99, 150);
    dt.Rows.Add(4, "Monitor", 349.99, 75);
    dataGridView1.DataSource = dt;
}

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' Create DataTable with sample data
    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))
    ' Add rows with values
    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)
    dataGridView1.DataSource = dt
End Sub

private void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        // Convert DataGridView to DataTable
        DataTable dt = new DataTable();
        foreach (DataGridViewColumn column in dataGridView1.Columns)
            dt.Columns.Add(column.HeaderText);
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (!row.IsNewRow)
            {
                DataRow dataRow = dt.NewRow();
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    dataRow[i] = row.Cells[i].Value;
                dt.Rows.Add(dataRow);
            }
        }
        // Create workbook and export
        WorkBook workbook = WorkBook.Create();
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        worksheet.LoadFromDataTable(dt, true);
        workbook.SaveAs("C:\\Reports\\Export.xlsx");
        MessageBox.Show("Export complete!");
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Error: {ex.Message}");
    }
}

Private Sub btnExport_Click(sender As Object, e As EventArgs)
    Try
        ' Convert DataGridView to DataTable
        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 Not row.IsNewRow Then
                Dim dataRow As DataRow = dt.NewRow()
                For i As Integer = 0 To dataGridView1.Columns.Count - 1
                    dataRow(i) = row.Cells(i).Value
                Next
                dt.Rows.Add(dataRow)
            End If
        Next
        ' Create workbook and export
        Dim workbook As WorkBook = WorkBook.Create()
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        worksheet.LoadFromDataTable(dt, True)
        workbook.SaveAs("C:\Reports\Export.xlsx")
        MessageBox.Show("Export complete!")
    Catch ex As Exception
        MessageBox.Show($"Error: {ex.Message}")
    End Try
End Sub

private void ExportWithFormatting(object sender, EventArgs e)
{
    WorkBook workbook = WorkBook.Create();
    WorkSheet worksheet = workbook.DefaultWorkSheet;
    // Add header row
    string[] headers = { "ID", "Name", "Price", "Stock" };
    for (int col = 0; col < headers.Length; col++)
    {
        worksheet.SetCellValue(0, col, headers[col]);
        worksheet[$"{(char)('A' + col)}1"].Style.Font.Bold = true;
        worksheet[$"{(char)('A' + col)}1"].Style.SetBackgroundColor("#4472C4");
    }
    // Add data with alternating row 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());
        }
        // Apply alternating background colors
        if (rowIndex % 2 == 0)
        {
            var range = worksheet[$"A{rowIndex + 1}:D{rowIndex + 1}"];
            range.Style.SetBackgroundColor("#D6DCE5");
        }
        rowIndex++;
    }
    workbook.SaveAs("C:\\Reports\\FormattedExport.xlsx");
}

Private Sub ExportWithFormatting(sender As Object, e As EventArgs)
    Dim workbook As WorkBook = WorkBook.Create()
    Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
    ' Add header row
    Dim headers As String() = {"ID", "Name", "Price", "Stock"}
    For col As Integer = 0 To headers.Length - 1
        worksheet.SetCellValue(0, col, headers(col))
        worksheet($"{ChrW(AscW("A"c) + col)}1").Style.Font.Bold = True
        worksheet($"{ChrW(AscW("A"c) + col)}1").Style.SetBackgroundColor("#4472C4")
    Next
    ' Add data with alternating row 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
        ' Apply alternating background colors
        If rowIndex Mod 2 = 0 Then
            Dim range = worksheet($"A{rowIndex + 1}:D{rowIndex + 1}")
            range.Style.SetBackgroundColor("#D6DCE5")
        End If
        rowIndex += 1
    Next
    workbook.SaveAs("C:\Reports\FormattedExport.xlsx")
End Sub