writing data from C# to Excel interrupted by openi

问题:

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

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

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

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

My app supports Excel 2000 to 2013.

Here is how to reproduce the issue:

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

Create a new Visual C# Console Application project.

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

Here is the complete code:

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
        }
    }
}

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:

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

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

Please advise?

Best regards, Bertrand

回答1:

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

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.

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;

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?"

回答2:

xlApp = new Excel.Application();
xlApp.Interactive = false;

回答3:

Make Excel Interactive is a perfect solution. The only problem is if the user is doing something on Excel at the same time, like selecting range or editing a cell. And for example your code is returning from a different thread and trying to write on Excel the results of the calculations. So to avoid the issue my suggestions is:

private void x(string str)
{
    while (this.Application.Interactive == true)
    {
        // If Excel is currently busy, try until go thru
        SetAppInactive();
    }

    // now writing the data is protected from any user interaption
    try
    {
        for (int i = 1; i < 2000; i++)
        {
            sh.Cells[i, 1].Value2 = str;
        }
    }
    finally
    {
        // don't forget to turn it on again
        this.Application.Interactive = true;
    }
}
private void SetAppInactive()
{
    try
    {
        this.Application.Interactive = false;
    }
    catch
    {
    }
}

回答4:

What I have done successfully is to make a temp copy of the target excel file before opening it in code.

That way I can manipulate it independent of the source document being open or not.

回答5:

One possible alternative to automating Excel, and wrestling with its' perculiarities, is to write the file out using the OpenXmlWriter writer (DocumentFormat.OpenXml.OpenXmlWriter).

It's a little tricky but does handle sheets with > 1 million rows without breaking a sweat.

OpenXml docs on MSDN