导出DataGridView数据至Excel时遇错误，求助（附相关代码）

Hey there, I see you're running into issues exporting your DevExpress GridView data to Excel using the clipboard method. Let's break down what might be going wrong with your current code and fix it up properly.

First, Let's Fix the Clipboard Copy Logic #

Your CopyToClipboard method has a common pitfall: by default, Clipboard.SetDataObject only keeps the data in memory temporarily. If Excel doesn't grab it fast enough (or if your app releases resources), the data gets lost. Here's the improved version:

public virtual void CopyToClipboard()
{
    // Ensure the GridView has focus first, otherwise SelectAll might fail
    gridView1.Focus();
    gridView1.SelectAll();
    
    DataObject dataObj = gridView1.GetClipboardContent();
    if (dataObj != null)
    {
        // The second parameter "true" makes the clipboard data persist after your app closes
        Clipboard.SetDataObject(dataObj, true);
    }
}

Complete the Excel Interop Logic #

Your Excel code was cut off, which is probably a big part of the issue. We need to properly initialize Excel, paste the data, handle cleanup (critical to avoid leftover Excel processes), and add error handling. Here's the full barButtonItem1_ItemClick method:

private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    Microsoft.Office.Interop.Excel.Application xlexcel = null;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;

    try
    {
        CopyToClipboard();

        // Initialize Excel instance
        xlexcel = new Microsoft.Office.Interop.Excel.Application();
        xlexcel.Visible = true; // Make Excel visible for debugging

        // Create a new workbook and select the first worksheet
        xlWorkBook = xlexcel.Workbooks.Add(System.Reflection.Missing.Value);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];

        // Paste the clipboard data starting at cell A1
        var startCell = xlWorkSheet.Cells[1, 1];
        startCell.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll);

        // Optional: Auto-fit columns to make data readable
        xlWorkSheet.UsedRange.Columns.AutoFit();
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Export failed: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        // Clean up clipboard (optional but good practice)
        Clipboard.Clear();

        // Critical: Release Excel COM objects to avoid orphaned processes
        if (xlWorkSheet != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
        if (xlWorkBook != null)
        {
            xlWorkBook.Close(false); // Set to true if you want to save automatically
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
        }
        if (xlexcel != null)
        {
            xlexcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlexcel);
        }

        // Force garbage collection to clean up leftover COM references
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

Common Error Causes to Check #

• Clipboard Data Loss: Always use Clipboard.SetDataObject(dataObj, true) to keep data persistent.
• Orphaned Excel Processes: Never skip releasing COM objects—this is the #1 reason Excel stays running in the background after your app closes.
• GridView Selection Failures: Make sure the GridView has focus before calling SelectAll, otherwise the selection won't work as expected.
• Missing Excel Interop References: Double-check that you've added the Microsoft.Office.Interop.Excel NuGet package or referenced the COM library for Excel.

内容的提问来源于stack exchange，提问作者Lê Anh Quân