c# - 通过打开 Excel 窗口将数据从 C# 写入 Excel 中断

Question

当我的 C# 程序不断将数据写入 Excel 电子表格时，如果最终用户单击右上角的菜单并打开Excel 选项 窗口，这会导致以下异常:

System.Runtime.InteropServices.COMException with HRESULT: 0x800AC472

这会中断数据写入电子表格。

理想情况下，应该允许用户执行此操作而不会导致异常。

我发现此错误代码的唯一解决方案是循环并等待异常消失: Exception from HRESULT: 0x800AC472这实际上会挂起应用程序，数据不会写入 Excel，用户对问题一无所知。

我想在写入 Excel 时禁用主菜单，但找不到有关如何执行此操作的引用。

我的应用程序支持 Excel 2000 到 2013。

重现问题的方法如下:

• 在 Windows 7 64 位上使用 Visual Studio Express 2013 for Windows Desktop，.NET 4.5.1 和 Excel 2007 , 创建一个新的 Visual C# 控制台应用程序项目。

• 添加对“Microsoft ExcelL 12.0 对象库”(对于 Excel)和“System.Windows.Forms”(对于消息框)的引用。

  完整代码如下:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Threading.Tasks;
    using System.Threading; // for sleep
    using System.IO;
    using System.Runtime.InteropServices;
    using System.Reflection;
    using Microsoft.Win32;
    using Excel = Microsoft.Office.Interop.Excel; 
  
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                int i = 3; // there is a split pane at row two
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
  
                try 
                { 
                    object misValue = System.Reflection.Missing.Value;
  
                    xlApp = new Excel.Application();
                    xlApp.Visible = false;
                    xlWorkBook = xlApp.Workbooks.Add(misValue);
  
                    xlApp.Visible = true;
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    // next 2 lines for split pane in Excel:
                    xlWorkSheet.Application.ActiveWindow.SplitRow = 2; 
                    xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
                    xlWorkSheet.Cells[1, 1] = "Now open the";
                    xlWorkSheet.Cells[2, 1] = "Excel Options window";
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    System.Windows.Forms.MessageBox.Show("Microsoft Excel does not seem to be installed on this computer any longer (although there are still registry entries for it). Please save to a .tem file. (1)");
                      return;
                }
                catch (Exception)
                {
                    System.Windows.Forms.MessageBox.Show("Microsoft Excel does not seem to be installed on this computer any longer (although there are still registry entries for it). Please save to a .tem file. (2)");
                    return;
                }
  
                while(i < 65000)
                {
                    i++;
  
                    try
                    {
                        xlWorkSheet.Cells[i, 1] = i.ToString();
                        Thread.Sleep(1000);
                    }
                    catch (System.Runtime.InteropServices.COMException)
                    {
                        System.Windows.Forms.MessageBox.Show("All right, what do I do here?");
                    }
                    catch (Exception) 
                    {
                        System.Windows.Forms.MessageBox.Show("Something else happened.");    
                    }
                }
  
                Console.ReadLine(); //Pause
            }
        }
    }
  
  

• 启动应用程序，出现 Excel 并向其中写入数据。从菜单中打开 Excel 选项对话框窗口，向上弹出错误:

  An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll and wasn't handled before a managed/native boundary
  Additional information: Exception from HRESULT: 0x800AC472

• 单击“继续”，我的消息框“好的，我在这里做什么？”出现。

请指教？

最好的问候，伯特兰

Answer 1

对于这个问题，我们最终一路求助于 Microsoft 支持。他们的最终回应是:

I am able to reproduce the issue. I researched on this further and found that this behaviour is expected and by design. This exception, 0x800AC472 – VBA_E_IGNORE, is thrown because Excel is busy and will not service any Object Model calls. Here is one of the discussions that talks about this. http://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto The work around I see is to explicitly catch this exception and retry after sometime until your intended action is completed.

由于我们无法读懂可能决定打开窗口或记笔记而没有意识到软件已停止记录(如果您屏蔽错误)的用户的想法，我们决定使用:

 xlWorkSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection;

锁定 Excel 窗口 UI。我们提供了一个明显的“解锁”按钮，但是当用户单击它时，会在消息框中严厉警告他，并显示“您要继续吗？”