Proper coding with releasing of the COM objects must be done when coding to create excel reports using Microsoft.Office.Interop.Excel in order to avoid memory leak.
Please refer to this
example to automate excel with proper releasing of COM objects in VS2010.
In the example, Solution1.AutomateExcel demonstrates automating Microsoft Excel application by using Microsoft Excel Primary Interop Assembly (PIA) and explicitly assigning each COM accessor object to a new variable that you would explicitly call
Marshal.FinalReleaseComObject to release it at the end. When you use this solution, it is important to avoid calls that tunnel into the object model because they will orphan Runtime Callable Wrapper (RCW) on the heap that you will not be able to access in order to call Marshal.ReleaseComObject. You need to be very careful. For example,
Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Calling oXL.Workbooks.Add creates an RCW for the Workbooks object. If you invoke these accessors via tunneling as this code does, the RCW for Workbooks is created on the GC heap, but the reference is created under the hood on the stack and are then discarded. As such, there is no way to call MarshalFinalReleaseComObject on this RCW. To get such kind of RCWs released, you would either need to force a garbage collection as soon as the calling function is off the stack (see Solution2.AutomateExcel), or you would need to explicitly assign each accessor object to a variable and free it.
Excel.Workbooks oWBs = oXL.Workbooks;
Excel.Workbook oWB = oWBs.Add(missing);
In the example, Solution2.AutomateExcel demonstrates automating Microsoft Excel application by using Microsoft Excel Primary Interop Assembly (PIA) and forcing a garbage collection as soon as the automation function is off the stack (at which point the Runtime Callable Wrapper (RCW) objects are no longer rooted) to clean up RCWs and release COM objects.
public static void AutomateExcel()
{
AutomateExcelImpl();
// Clean up the unmanaged Excel COM resources by forcing a garbage
// collection as soon as the calling function is off the stack (at
// which point these objects are no longer rooted).
GC.Collect();
GC.WaitForPendingFinalizers();
// GC needs to be called twice in order to get the Finalizers called
// - the first time in, it simply makes a list of what is to be
// finalized, the second time in, it actually is finalizing. Only
// then will the object do its automatic ReleaseComObject.
GC.Collect();
GC.WaitForPendingFinalizers();
}
Read more on the Marshal.ReleaseCOMObjects is dangerous to be used
here.
