多次执行DataGridView数据剪贴板复制到Excel时操作失败求助

我开发了一个包含多个DataGridView的程序，这些表格存有数百条数据，需传输至Excel文件以生成散点图。我采用剪贴板作为最快的传输方式，首次运行代码效果尚可，但因结构分析更新会导致表格数据变化，该功能需多次执行，偶尔会遇到错误：

System.Runtime.InteropServices.ExternalException (0x800401D0): Requested Clipboard operation did not succeed

以下是原代码：

public void saveInteraction_Click(object sender, EventArgs e)
{

    Process[] pp = Process.GetProcessesByName("excel");
    foreach (Process p in pp)
    {
        if (p.MainWindowTitle.Length == 0)
            p.Kill();
    }

    Excel.Application xlexcel;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
   
    xlexcel = new Excel.Application();
    xlexcel.Visible = true;
    object misValue = System.Reflection.Missing.Value;
    xlWorkBook = xlexcel.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);            

    #region DIRECTORIO DE GUARDADO Y ELIMINACION DE ARCHIVOS
    string directoryPath = null;

    if (!System.IO.Directory.Exists($@"{Path.GetDirectoryName(FormPrincipal.Var.doc.PathName)}\6. Lift & Bracing\1. Reports\INTERACTIONS"))
    {
        System.IO.DirectoryInfo di = System.IO.Directory.CreateDirectory($@"{Path.GetDirectoryName(FormPrincipal.Var.doc.PathName)}\6. Lift & Bracing\1. Reports\INTERACTIONS");
    }

    if (System.IO.Directory.Exists($@"{Path.GetDirectoryName(FormPrincipal.Var.doc.PathName)}\6. Lift & Bracing\1. Reports\INTERACTIONS"))
    {
        directoryPath = $@"{Path.GetDirectoryName(FormPrincipal.Var.doc.PathName)}\6. Lift & Bracing\1. Reports\INTERACTIONS";
    }

    string baseFileName = $"{FormPrincipal.Var.form.PanelNameButton.Text}-Interactions";

    string pdfPath = Path.Combine(directoryPath, $"{baseFileName}.xlsx");

    if (File.Exists(pdfPath))
    {
        File.Delete(pdfPath);
    }
    #endregion

    int n = 1;
    if (FormPrincipal.Var.form.RowsBox.Text == "2")
    {
        n = 2;
    }
    if (FormPrincipal.Var.form.RowsBox.Text == "4")
    {
        n = 4;
    }
    try
    {
        for (int i = 0; i < n; i++)
        {
            copyAlltoClipboard(i);

            // Allow the clipboard to update.
            Application.DoEvents();
            Thread.Sleep(1000);

            Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, i * 6 + 2];                   

            CR.PasteSpecial(Excel.XlPasteType.xlPasteValues);
        }
        xlWorkSheet.Cells[1, 1].Value = "Tension Adm";
        xlWorkSheet.Cells[1, 2].Value = "Shear Adm";
        xlWorkSheet.Cells[2, 1].Value = tensionCap_textBox.Text;
        xlWorkSheet.Cells[91, 2].Value = shearCap_textBox.Text;

        xlWorkBook.SaveAs(pdfPath);
        xlWorkBook.Close(false);
        xlexcel.Quit();

    }
    catch (Exception exp)
    {
        MessageBox.Show($"{exp}");
        xlWorkBook.Close(false);
        xlexcel.Quit();
    }            
}

private void copyAlltoClipboard(int i)
{
    DataObject dataObj1 = null;
    
    if (i == 0)
    {
        dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dataGridView1.MultiSelect = true;
        dataGridView1.SelectAll();

        dataObj1 = dataGridView1.GetClipboardContent();
    }

    if (i == 1)
    {
        dataGridView2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dataGridView2.MultiSelect = true;
        dataGridView2.SelectAll();

        dataObj1 = dataGridView2.GetClipboardContent();
    }

    if (i == 2)
    {
        dataGridView3.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dataGridView3.MultiSelect = true;
        dataGridView3.SelectAll();

        dataObj1 = dataGridView3.GetClipboardContent();
    }

    if (i == 3)
    {
        dataGridView4.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dataGridView4.MultiSelect = true;
        dataGridView4.SelectAll();

        dataObj1 = dataGridView4.GetClipboardContent();
    }
                                                                         

    if (dataObj1 != null)
        Clipboard.SetDataObject(dataObj1);            
}

错误根源 #

1. 剪贴板资源竞争：剪贴板是系统共享资源，多次快速操作时，可能被其他进程占用，导致写入/读取失败
2. 硬编码等待不可靠：Thread.Sleep(1000)无法适配不同系统的处理速度，等待时间过短可能剪贴板未完成更新，过长则浪费时间
3. 控件状态冗余设置：每次复制都重复设置ClipboardCopyMode和MultiSelect，不仅没必要，还可能引发控件状态冲突
4. COM对象释放不彻底：Excel的COM对象未正确释放，可能残留进程占用系统资源，间接影响剪贴板操作

修复措施 #

1. 添加剪贴板重试逻辑：捕获剪贴板异常，最多重试3次，每次间隔500ms
2. 优化DataGridView复制逻辑：提前设置好控件的复制模式和多选属性，无需每次重复设置；复制完成后取消选择，避免影响用户操作
3. 移除硬编码Sleep：替换为检查剪贴板内容是否存在的逻辑，确保内容就绪后再粘贴
4. 改进Excel对象释放：使用Marshal.ReleaseComObject确保COM对象被正确释放，避免残留进程

优化后的代码 #

using System.Runtime.InteropServices;

public void saveInteraction_Click(object sender, EventArgs e)
{
    // 清理无窗口的Excel进程
    Process[] pp = Process.GetProcessesByName("excel");
    foreach (Process p in pp)
    {
        if (string.IsNullOrEmpty(p.MainWindowTitle))
            p.Kill();
    }

    Excel.Application xlexcel = null;
    Excel.Workbook xlWorkBook = null;
    Excel.Worksheet xlWorkSheet = null;
    Excel.Range CR = null;

    try
    {
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];

        // 初始化保存目录
        string baseDir = Path.GetDirectoryName(FormPrincipal.Var.doc.PathName);
        string directoryPath = Path.Combine(baseDir, @"6. Lift & Bracing\1. Reports\INTERACTIONS");
        Directory.CreateDirectory(directoryPath); // 无需判断，CreateDirectory已处理存在的情况

        string baseFileName = $"{FormPrincipal.Var.form.PanelNameButton.Text}-Interactions";
        string excelPath = Path.Combine(directoryPath, $"{baseFileName}.xlsx");

        if (File.Exists(excelPath))
            File.Delete(excelPath);

        int n = 1;
        if (FormPrincipal.Var.form.RowsBox.Text == "2")
            n = 2;
        else if (FormPrincipal.Var.form.RowsBox.Text == "4")
            n = 4;

        // 提前设置所有DataGridView的复制属性
        InitializeDataGridViewCopySettings();

        for (int i = 0; i < n; i++)
        {
            // 带重试的剪贴板复制
            if (!CopyDataGridViewToClipboardWithRetry(i))
                throw new Exception($"第{i+1}个表格复制到剪贴板失败");

            // 等待剪贴板内容就绪
            if (!WaitForClipboardContent())
                throw new Exception("剪贴板内容未就绪");

            CR = (Excel.Range)xlWorkSheet.Cells[1, i * 6 + 2];
            CR.PasteSpecial(Excel.XlPasteType.xlPasteValues);
        }

        // 设置固定内容
        xlWorkSheet.Cells[1, 1].Value = "Tension Adm";
        xlWorkSheet.Cells[1, 2].Value = "Shear Adm";
        xlWorkSheet.Cells[2, 1].Value = tensionCap_textBox.Text;
        xlWorkSheet.Cells[91, 2].Value = shearCap_textBox.Text;

        xlWorkBook.SaveAs(excelPath);
    }
    catch (Exception exp)
    {
        MessageBox.Show($"操作失败：{exp.Message}");
    }
    finally
    {
        // 释放Excel COM对象
        if (CR != null) Marshal.ReleaseComObject(CR);
        if (xlWorkSheet != null) Marshal.ReleaseComObject(xlWorkSheet);
        if (xlWorkBook != null)
        {
            xlWorkBook.Close(false);
            Marshal.ReleaseComObject(xlWorkBook);
        }
        if (xlexcel != null)
        {
            xlexcel.Quit();
            Marshal.ReleaseComObject(xlexcel);
        }
    }
}

private void InitializeDataGridViewCopySettings()
{
    // 一次性设置所有表格的复制属性
    dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    dataGridView1.MultiSelect = true;

    dataGridView2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    dataGridView2.MultiSelect = true;

    dataGridView3.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    dataGridView3.MultiSelect = true;

    dataGridView4.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    dataGridView4.MultiSelect = true;
}

private bool CopyDataGridViewToClipboardWithRetry(int index, int maxRetries = 3, int delayMs = 500)
{
    for (int retry = 0; retry < maxRetries; retry++)
    {
        try
        {
            DataGridView dgv = GetDataGridViewByIndex(index);
            if (dgv == null) return false;

            dgv.SelectAll();
            DataObject dataObj = dgv.GetClipboardContent();
            if (dataObj != null)
            {
                Clipboard.SetDataObject(dataObj, true); // true表示数据保留在剪贴板，即使程序退出
                dgv.ClearSelection(); // 取消选择，不影响用户操作
                return true;
            }
        }
        catch (ExternalException)
        {
            Thread.Sleep(delayMs);
        }
    }
    return false;
}

private DataGridView GetDataGridViewByIndex(int index)
{
    return index switch
    {
        0 => dataGridView1,
        1 => dataGridView2,
        2 => dataGridView3,
        3 => dataGridView4,
        _ => null
    };
}

private bool WaitForClipboardContent(int timeoutMs = 2000)
{
    DateTime startTime = DateTime.Now;
    while ((DateTime.Now - startTime).TotalMilliseconds < timeoutMs)
    {
        if (Clipboard.ContainsText())
            return true;
        Application.DoEvents();
        Thread.Sleep(100);
    }
    return false;
}

内容的提问来源于stack exchange，提问作者Matias Morales