Click here to Skip to main content
15,881,413 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 28-Mar-12 10:11am
v2
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

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;
?
 
Share this answer
 
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.
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900