Will memory be released when creating Excel COM objects are creating in foreach loop

Question

0.00/5 (No votes)

See more:

Using VS 2012, .NET 4.5, Excel Interop 14

I'm reading in an Excel file and working though all the sheets to extract data from a number of them. I'm fairly certain I'm releasing all the memory correctly with the code below (source).

C#

Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
 
oSheet = null;
oWB = null;
oXL = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);

So my question is, in the code below, will the sheet created by the foreach loop properly release the memory?

C#

foreach (var oSheet in oWB.Sheets)
{
    ...next chunk of code
}

Or do I have to do it like this?

C#

for (int i = 1; i < oWB.Sheets.Count; i++)
{
    Excel._Worksheet oSheet = oWB.Sheets[i];
    ...next chunk of code    

    Marshal.ReleaseComObject(oSheet);
    oSheet = null;
}

Posted 3-Dec-14 7:24am

Will Elliott

Add a Solution

Comments

PIEBALDconsult 3-Dec-14 13:40pm

Dunno, but I recommend accessing Excel as if it's a database, using ADO.net, the ACE engine, and the OleDb provider.

Will Elliott 4-Dec-14 9:15am

Could you point me to some reference material for that? I'm always game for exploring new options.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2014-12-03T07:47:00

Solution 1

You are looping through the sheets. No need to release anything here. The sheets are already in memory and you are looping through them.

Al last, you just need to release the workbook and quit the application.

Posted 3-Dec-14 7:47am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Will Elliott · Accepted Answer · 2014-12-03T09:53:00

OK. The initial question wasn't clear enough...sorry. This might be answering my own question. Using a foreach loop, this seems to be the only way to access properties and data of the sheet.

C#

foreach (var oSheet in oWB.Sheets)
{
    Excel._Worksheet sheet = oSheet as Excel._Worksheet;
    if(sheet.Name.Contains( $TEST_VAL ) 
    {
        function1(sheet);
    }
    Marshal.ReleaseComObject(sheet);
    sheet = null;
}