本文介绍了通过打开Excel窗口将数据从C#写入Excel中断的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如果我的C#程序将数据连续写入Excel电子表格,则最终用户单击右上角的菜单并打开Excel选项窗口,这将导致System.Runtime.InteropServices.COMException,其HRESULT:0x800AC472会中断数据从被写入电子表格.

While my C# program writes data continuously to an Excel spreadsheet, if the end user clicks on the upper right menu and opens theExcel Options window, this causes a System.Runtime.InteropServices.COMException with HRESULT: 0x800AC472 which interrupts the datafrom being written to the spreadsheet.

理想情况下,应该允许用户这样做而不会引起异常.

Ideally, the user should be allowed to do this without causing an exception.

我发现此错误代码的唯一方法是循环并等待异常消失: HRESULT的异常:0x800AC472 有效地挂起了应用程序,数据没有写入Excel,并且用户对问题一无所知.

The only solution I found to this error code was to loop and wait until the exception went away:Exception from HRESULT: 0x800AC472which effectively hangs the app, data is not written to Excel and the user is left in the dark about the problem.

我曾考虑过在写入Excel时禁用它的主菜单,但找不到有关如何执行此操作的参考.

I thought about disabling the main menu of Excel while writing to it, but cannot find a reference on how to do this.

我的应用程序支持Excel 2000至2013.

My app supports Excel 2000 to 2013.

以下是重现该问题的方法:

Here is how to reproduce the issue:

在Windows 7 64位和Excel 2007上使用Visual Studio Express 2013 for Windows桌面,.NET 4.5.1.

Using Visual Studio Express 2013 for Windows Desktop, .NET 4.5.1 on Windows 7 64-bit with Excel 2007.

创建一个新的Visual C#控制台应用程序项目.

Create a new Visual C# Console Application project.

添加对"Microsoft ExceL 12.0对象库"(对于Excel)和"System.Windows.Forms"(对于消息框)的引用.

Add reference to "Microsoft ExceL 12.0 Object Library" (for Excel) and to "System.Windows.Forms" (for messagebox).

这是完整的代码:

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选项对话框窗口,然后弹出该错误:

Lanch the app, Excel appears and data is written to it. Open the Excel options dialog window from the menu and up pops the error:

mscorlib.dll中发生了类型为'System.Runtime.InteropServices.COMException'的异常,在受管理/本地边界之前未进行处理

An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll and wasn't handled before a managed/native boundary

其他信息:HRESULT的异常:0x800AC472

Additional information: Exception from HRESULT: 0x800AC472

单击继续",然后单击我的消息框好吧,我在这里做什么?"出现.

Click on Continue and my messagege box "All right, what do I do here?" appears.

请告知?

最诚挚的问候,贝特朗

Best regards,Bertrand

推荐答案

我们终于解决了此问题,并一直寻求Microsoft支持.他们的最终回应是:

We finally went all the way to Microsoft Support with this issue. Their final response was:

由于在无法意识到软件已停止记录(如果掩盖错误)的情况下,我们无法理解可能决定打开窗口或记笔记的用户的想法,因此,我们决定使用以下解决方法:

Since we cannot read the minds of the user who might decide to open a window or take a note without realizing the soft has stopped logging (if you mask the error), we decided to work around using:

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

锁定Excel窗口UI.我们提供了一个明显的解锁"按钮,但是当用户单击该按钮时,会在消息框中严谨地警告他,同时显示您是否希望继续?"

to lock the Excel window UI. We provide an obvious "unlock" button but when the user clicks it, he is sternly warned in a messagebox along with a "Do you wish to continue?"

这篇关于通过打开Excel窗口将数据从C#写入Excel中断的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 22:17
查看更多