Interop Releasing in Excel

Question

0.00/5 (No votes)

See more:

Hey Everyone,

I have a project in which im' trying to populate an excel file i've opened with data from a datatable.

I can accomplish this but where I get hung up is in releasing resources and making sure the instance of Excel.EXE closes properly upon the function finishing.

I've narrowed down the problem to when I populate excel cells with data from the datatable.

I do so in a foreach loop

C#

void saveExcelFile()
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlWorkSheet = null;

object missingVal = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();

xlWorkBook = xlApp.Workbooks.Open(filename, 0, false, 5, "", "", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,false,0,true,1,0);

xlSheets = xlWorkBook.Worksheets;
xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item("Summary");

int i = 13;
foreach(DataRow in dt.rows)
{
  if(i <=32)
  {
    xlWorkSheet.Cells[i, 2] = row["price"];
    xlWorkSheet.Cells[i, 3] = row["width"];
    xlWorkSheet.Cells[i, 4] = row["depth"];
    xlWorkSheet.Cells[i, 5] = row["height"];
  }
  i++;
}

xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(filename2);
xlApp.DisplayAlerts = true;

if (xlApp != null)
{
	xlWorkBook.Close(false, missingVal, missingVal);
	//xlWorkBook.Close();
	xlApp.Quit();
	while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp) != 0) { }

	if (xlSheets != null)
	{
	   // while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rg) !=0) { }
		while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheets) != 0) { }
		while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet) != 0) { }
	}
	if (xlWorkBook != null)
	{
		while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook) != 0) { }
	}
	if (xlApp != null)
	{
		while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp) != 0) { }
	}
}
//rg = null;
xlSheets = null;
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

}

If I comment out my for loop, the instances of my workbook, application and worksheet all get released properly.

I believe I'm creating com objects here in this loop that don't get released properly, but don't know how to best rectify the situation when using the Cell reference. I've seen examples on this site and others but none using a loop.

Any guidance would be appreciated!

Thanks!

Ethan

**Added rest of function code ~ Thanks

Posted 28-Mar-12 9:33am

easy_E

Updated 28-Mar-12 10:11am

v2

Add a Solution

Comments

ZurdoDev 28-Mar-12 15:45pm

Please show the code where you are releasing the objects.

easy_E 28-Mar-12 16:32pm

Done - Thanks

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)

Wes Aday · Answer 1 · 2012-03-28T09:53:00

Solution 1

~~I do not think that the code you are showing is your problem. Reference this thread.~~ http://social.msdn.microsoft.com/Forums/eu/exceldev/thread/195a9aee-d46b-4cca-b480-ba8385421b68[^]

What exactly is happening?
What happens if you comment out all the code after

C#

xlApp.DisplayAlerts = true;

?

Posted 28-Mar-12 9:53am

Wes Aday

Updated 28-Mar-12 10:17am

v2

Comments

easy_E 28-Mar-12 16:22pm

If i comment the code after that line it predictably doesn't populate my excel file and also does not kill the Excel.EXE process.

With the code as is I can populate the excel file just fine but it once again will not get rid of the Excel.EXE process. However if i just comment the foreach loop out, the process is terminated.

[no name] 28-Mar-12 16:44pm

On my system, using mostly your displayed code, when I commented out all of the code after the xlApp.DisplayAlerts = true; line and added xlApp.Quit() then it properly exited excel. When I left it in Excel kept running.

Tim Groven · Answer 2 · 2012-03-28T10:36:00

C#

if (excelWorksheet != null)
{
    Marshal.ReleaseComObject(excelWorksheet);
}
if (excelWorkbook != null)
{
    excelWorkbook.Close();
    Marshal.ReleaseComObject(excelWorkbook);
}
if (excelApplication != null)
{
    excelApplication.Quit();
    Marshal.ReleaseComObject(excelApplication);
}

Here is what I do that closes everything out just fine.