C# Excel automation causes Excel memory leak

Question

I'm trying to use C# with the COM Interop library to open a set of very heavy excel workbooks. I have to use C#, because I also need to start macros, move some cells around, and start a custom excel-add-in my company uses.

My program then exits, leaving the workbooks open, each in a separate excel instance. I DO NOT want the workbooks to be closed when the program exits.

The problem is that when my C# program exits, over time, the excel workbooks gradually consume more memory, until they're consuming 3.5 gigs of memory from an original 500 mb.

I used to open the workbooks by hand, and the sheets never consumed that much memory. Once I started opening them using C#, they started to break because of extreme memory usage. My theory is that somehow, when I interact with the COM Excel object, I create a memory leak.

Below is my original code:

using Excel = Microsoft.Office.Interop.Excel;
...
excelApp = new Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Open(filename, misValue, misValue, misValue, misValue, misValue,
               true, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;

I read about how you need to use Marshal to release uses, so I'm now trying the following code, but have no easy way to test it, other than opening all the sheets and seeing if they consume too much data.

            excelApp = new Excel.Application();
            excelApp.Visible = true;
            Excel.Workbooks currWorkbooks = excelApp.Workbooks;
            Excel.Workbook currWorkbook = currWorkbooks.Open(filename, misValue, misValue, misValue, misValue, misValue,
               true, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            //excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;

            int x = Marshal.ReleaseComObject(currWorkbook);
            currWorkbook = null;

            int y = Marshal.ReleaseComObject(currWorkbooks);
            currWorkbooks = null;

Answer 1

When using the MS Office COM Interop libraries, there are a couple of things I've come across to avoid the memory leaks:

First, "Don't use two dots" is the best way to remember it, but basically, always assign a new COM object reference to a new variable, do not chain-call members, even if Intellisense encourages it. Chained calling does some stuff in the background that prevents proper release by the .NET framework.. Here is some code I use for starting an Excel report:

//use vars for every COM object so references don't get leftover
//main Excel app
var excelApp = new Application();
var workbooks = excelApp.Workbooks;

//workbook template
var wbReport = workbooks.Add(@"C:\MyTemplate.xltx");

//Sheets objects for workbook
var wSheetsReport = wbReport.Sheets;
var wsReport = (Worksheet)wSheetsReport.get_Item("Sheet1");

Secondly, Call Marshal.ReleaseComObject() for each variable created in reverse order of creation, and call a couple of garbage collection methods before doing so:

//garbage collector
GC.Collect();
GC.WaitForPendingFinalizers();

//cleanup
Marshal.ReleaseComObject(wsReport);
Marshal.ReleaseComObject(wSheetsReport);
Marshal.ReleaseComObject(wbReport);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excelApp);

Using this scheme every time I use Excel has solved my memory issues, though it is tedious and sad we can't use the chained members the way we're used to.