Getting System.Runtime.InteropServices.COMException Error in Server Side Generated Excel

Question

We have a web application that generates excel spreadsheets and run macros on the server side. It then sends them to different individuals via email. It is a part of a legacy reporting style which we are transitioning but still supporting on our new application which we delivered as a website in IIS.

I know it is a bad practice to do Office Automation as I saw info from Microsoft that this is not supported. It is also indicated in the answer here opening excel error: System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID

Anyways to cut the story short the excel reports are generated in a batch scenario which can generate from 3 to 300 reports sending to different people. The report generation works fine until it hits around the 15 to 20th item then it craps out, it gives me the error below

System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).
   at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType)
   at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType)
   at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj)
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at Ci.Infrastructure.Reporting.ReportProviderExcel.RunReport()

What might be wrong? The first reports came out successful and I know its not the excel templates becuase when I rerun it again all the failed ones will continue successfully and when the 15 to 20th report gets generated it throws the error again.

Update: We know were asking for trouble but I need a solution to fix the issue. Remember this is for legacy stuff which we will stop supporting in the future but in the transition period we need it it work.

We tried serializing still does not work. We tried sleeping the thread that generates the excel report when it hits a COM Exception, it works but this is not an elegant result. Any good solution to address this issue will be granted the bounty.

Another Update:

Solved by doing this on the finally block

finally
{
    if (dataWorksheet != null)
    {
        Marshal.ReleaseComObject(dataWorksheet);
    }

    if (worksheets != null)
    {
        Marshal.ReleaseComObject(worksheets);
    }

    if (workbook != null)
    {
        workbook.Close(false);
        Marshal.ReleaseComObject(workbook);
    }

    if (workbooks != null)
    {
        workbooks.Close();
        Marshal.ReleaseComObject(workbooks);
    }

    if (excel != null)
    {
        excel.Quit();
        Marshal.ReleaseComObject(excel);
    }
}

Like what MarkWalls said close each of the excel instances as a unit of work which completely clears out EXCEL process before it is used again.

Solution 1

I had a problem much like this. The answers above are like correct - you probably have essentially a pool of un-garbage collected excel instances gumming up the server.

My solution for it was to very carefully open-create-close each of the excel instances as a unit of work, then check to see if any of the excel instances remained on the server process. It just went through all of them one by one like that at night and had them in a repository to send out in the morning.

Another thought that we tried, but had to not use in the end, was creating CSV files rather than excel files. If you just need the data, then CSV is much more lightweight. We had complicated formulas in the workbooks as well that just couldn't be done without.

Solution 2

Server execution failed

You haven't shown much research, you should always look in the Windows application event log to get more details about this. I'll just assume you are suffering from the typical problem with using Excel from a .NET program.

This error description is always accurate, COM can literally not start Excel.exe. This is almost always because the operating is flat out of resources, the kernel memory pool is usually the one that runs out. You can typically see why by starting Task Manager (hopefully it still works), and look at the Processes tab. Good odds that you'll see dozens of copies of Excel.exe running. If you can't get Task Manager started then keep an eye on the list when you restart your app.

Excel is a "fat" process, it uses many operating system resources. It was really written for desktop use, only one instance of Excel.exe would be running. Even if you start it up again, the 2nd instance starts and notices that another instance is already running. It talks to the 1st one and ask it to do what you meant it to do, like opening another document. And quits, leaving only the 1st running. This same mechanism is not in place when you use COM. Not without you taking care of it yourself by only ever creating one instance and have it do all the work.

There's a good reasons why these Excel.exe instances don't quit when you stop using them. You have a problem with garbage collection in your program. The collector doesn't run often enough. It is easy to get into this kind of trouble, you'd typically have Excel doing all the heavy lifting and don't yourself ever allocate enough objects to get the garbage collector to run.

That spells trouble, Excel can only quit when its interface instance get garbage collected. Memory management is very different in a COM interop scenario, it is reference counted. When you start using an interface, like Application, then the reference count goes up. It goes down when the finalizer runs. Which will not happen until after a garbage collection.

Many programmers work around this problem by calling Marshal.ReleaseComObject() to force the reference count to go down. Many of those programmers also get in trouble with this, it won't work unless you call it for every interface reference. Are there are some that are not visible in a program.

The best way to do this is by triggering a garbage collection forcibly after you are done using Excel. Set any interface reference to null and call GC.Collect() + GC.WaitForPendingFinalizers(). Be sure to test this for the Release build without a debugger attached. And be sure to do it in a method that is separate from the method that uses the Excel interfaces. Task Manager tells you whether you're ahead.

It is still a bad idea to run Excel on a server, but if this approach works for you then you'll have some breathing room.

Solution 3

You're really asking for trouble. See if this helps: http://blogs.msdn.com/b/adioltean/archive/2005/06/24/432519.aspx