How to release memory after running program in C#?

Question

I have a C# program in which I read data from an excel file that contains about 40000 numbers. This is a part of my code:

        Microsoft.Office.Interop.Excel.Application _excelApp = new          Microsoft.Office.Interop.Excel.Application();
        _excelApp.Visible = true;
        string fileName = "D:\\data.xlsx";
        Workbook workbook = _excelApp.Workbooks.Open(fileName, Type.Missing);
        Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
        Range excelRange = worksheet.UsedRange;
        Object[,] valueArray =    (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
        int num = worksheet.UsedRange.Rows.Count;
        workbook.Close(false, Type.Missing);

        _excelApp.Quit();
        for (int row = 1; row <= num; ++row)
        {
            data1.Add(Convert.ToDouble(valueArray[row, 1]));
            data2.Add(Convert.ToDouble(valueArray[row, 2]));
        }

Every time I run the program, the excel file appears for a moment and is closed. But there are some opened excel file in the background process of windows, and consume ram and reduce speed of windows. How can I completely close excel file and quit them from the memory in my code?

Thank you.

score 2 · Answer 1 · answered Mar 05 '16 at 06:31

You need to release the COM objects that you use so that they can properly close.

Try this code:

var _excelApp = new Microsoft.Office.Interop.Excel.Application();
_excelApp.Visible = true;
var fileName = "D:\\data.xlsx";
var workbooks = _excelApp.Workbooks;
var workbook = workbooks.Open(fileName, Type.Missing);
var worksheet = (Worksheet)workbook.Worksheets[1];
var excelRange = worksheet.UsedRange;
Object[,] valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
int num = worksheet.UsedRange.Rows.Count;

Marshal.FinalReleaseComObject(excelRange);
Marshal.FinalReleaseComObject(worksheet);

workbook.Close(false, Type.Missing);

Marshal.FinalReleaseComObject(workbook);
Marshal.FinalReleaseComObject(workbooks);

_excelApp.Quit();

Marshal.FinalReleaseComObject(_excelApp);

for (int row = 1; row <= num; ++row)
{
    data1.Add(Convert.ToDouble(valueArray[row, 1]));
    data2.Add(Convert.ToDouble(valueArray[row, 2]));
}

I haven't tested this code, but it should be close.

Please note that you absolutely have to do this kind of code:

var workbooks = _excelApp.Workbooks;
var workbook = workbooks.Open(fileName, Type.Missing);

...and you can't just do this:

var workbook = _excelApp.Workbooks.Open(fileName, Type.Missing);

...as this leaves the _excelApp.Workbooks intermediate object unable to be released.

Enigmativity, I thought you where better than this to be spreading wrong information. Calling `FinalReleaseComObject()` to fix this problem is incorrect information is rampant across the internet. See [this](http://stackoverflow.com/questions/3937181/when-to-use-releasecomobject-vs-finalreleasecomobject/3938075#3938075) and [this](http://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable/25135685#25135685) from Hans Passant — Scott Chamberlain, Mar 05 '16 at 07:13
@ScottChamberlain - Hmmm, I've run into this situation myself many moons ago and this was the correct approach to take. I don't know what changed. — Enigmativity, Mar 05 '16 at 07:40
@Yaser - I'd have a go at trying the two solution presented here, but one thing I know was key was that I had to reboot my machine every time I got my Excel closing down code wrong. It seemed that something in the OS got corrupted, so even if you get the code right unless you reboot after a failed attempt it won't work. It is a PITA! — Enigmativity, Mar 05 '16 at 07:43

score -1 · Answer 2 · answered Mar 05 '16 at 06:23

-1

You want to force the Garbage collector to invoke, which is basically achieved by calling GC.Collect().

When you call GC.Collect, the GC will run each object's finalizer on a separate thread. Therefore, another method to keep in mind is GC.WaitForPendingFinalizers. This synchronous method that will not return until the GC.Collect has finished its work.

Example:

public void MethodUsingTonsOfMemory(){
  // do memory intensive stuff

  GC.Collect();
  GC.WaitForPendingFinalizers(); // wait for memory to be released

  Debug.WriteLine("Memory should be clear now");
}

answered Mar 05 '16 at 06:23

Pedro G. Dias

3,162
1
18
30

Oh you mean how to close OTHER excel files in the background? – Pedro G. Dias Mar 05 '16 at 06:30
1

I don't know why this is getting downvoted, calling `GC.Collect()` followed by `GC.WaitForPendingFinalizers();` is the correct way to fix the excel problem. calling `FinalReleaseComObject` is the wrong way to do it but that answer is rampant all over the internet. The only thing you have to be careful of is if you do it in the same method that uses the COM objects it won't collect the objects in debug mode or with the debugger attached due to object lifetime extensions done by the debugger. – Scott Chamberlain Mar 05 '16 at 07:14
I tested this suggestion. But it didn't had any effect! – Yaser Mar 05 '16 at 07:33
@Yaser You likely called `GC.Collect()` from the same method you had the excel refrence and you had the debugger open. See [this answer](http://stackoverflow.com/questions/17130382/understanding-garbage-collection-in-net/17131389#17131389) for a more indepth explination of why you need to call it from outside of the function or without a debugger. – Scott Chamberlain Mar 05 '16 at 07:48

How to release memory after running program in C#?

2 Answers2