在Excel中互操作发布 [英] Interop Releasing in Excel

问题描述

大家好，

我有一个项目，即时通讯试图用数据表中的数据填充我打开的Excel文件.

我可以完成此操作，但是挂断的地方是释放资源，并确保函数完成后正确关闭Excel.EXE实例.

我将问题范围缩小到使用数据表中的数据填充excel单元时.

我在foreach循环中这样做

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

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();

}

如果我注释掉for循环，则我的工作簿，应用程序和工作表的实例都会被正确释放.

我相信我会在此循环中创建无法正确释放的com对象，但不知道在使用Cell引用时如何最好地纠正这种情况.我在该网站上看到了其他示例，但没有看到使用循环的示例.

任何指导将不胜感激！

谢谢！

伊森


**添加了其余功能代码〜谢谢

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

推荐答案

我不认为您所显示的代码是您的问题.引用此线程. http://social. msdn.microsoft.com/Forums/eu/exceldev/thread/195a9aee-d46b-4cca-b480-ba8385421b68 [

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

xlApp.DisplayAlerts = true;

之后注释掉所有代码，会发生什么?

?

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.

这篇关于在Excel中互操作发布的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！