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