问题描述
如果我的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中断的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!