释放Interop Excel对象
本文关键字:对象 Excel Interop 释放 | 更新日期: 2024-10-24 14:51:26
我目前正在编写一个C#.NET应用程序,该应用程序在Excel文件中使用VBA宏。
当用户单击按钮时,我从c#应用程序运行宏。我必须等待所有数据都被下载,这就是为什么这是VBA代码谁执行"Application.Quit"。
这是我的代码:
private void toolStripButton5_Click(object sender, EventArgs e)
{
int lastUsedRowFund, lastUsedRowEquity, lastUsedRowBond, lastUsedRowAccount = 0;
Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook oWB = null;
Excel.Worksheet oSheetFund = null;
Excel.Worksheet oSheetEquity = null;
Excel.Worksheet oSheetBond = null;
Excel.Worksheet oSheetAccount = null;
oXL.Visible = false;
oWB = oXL.Workbooks.Open("C:''extract.xlsm");
oSheetFund = oWB.Sheets["Fund"];
oSheetEquity = oWB.Sheets["Equity"];
oSheetBond = oWB.Sheets["Bond"];
oSheetAccount = oWB.Sheets["Account"];
string[] valuesHeaderFund = {"field1", "field2" };
string[] valuesHeaderEquity = {"field1", "field2"};
string[] valuesHeaderBond = {"field1", "field2"};
string[] valuesHeaderAccount = {"field1", "field2"};
Excel.Range headerFund = oSheetFund.get_Range("A1", "AA1");
Excel.Range headerEquity = oSheetEquity.get_Range("A1", "AE1");
Excel.Range headerBond = oSheetBond.get_Range("A1", "AE1");
Excel.Range headerAccount = oSheetAccount.get_Range("A1", "AE1");
headerFund.Value2 = valuesHeaderFund;
headerEquity.Value2 = valuesHeaderEquity;
headerBond.Value2 = valuesHeaderBond;
headerAccount.Value2 = valuesHeaderAccount;
Excel.Range lastRowFund = oSheetFund.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range lastRowEquity = oSheetEquity.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range lastRowBond = oSheetBond.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range lastRowAccount = oSheetAccount.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
lastUsedRowFund = lastRowFund.Row;
lastUsedRowEquity = lastRowEquity.Row;
lastUsedRowBond = lastRowBond.Row;
lastUsedRowAccount = lastRowAccount.Row;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
string[] data = { row.Cells[0].Value.ToString(), row.Cells[1].Value.ToString()};
if (row.Cells[5].Value.ToString() == "FON")
{
lastUsedRowFund += 1;
oSheetFund.get_Range("A" + lastUsedRowFund, "K" + lastUsedRowFund).Value2 = data;
}
else if (row.Cells[5].Value.ToString() == "ACT")
{
lastUsedRowEquity += 1;
oSheetEquity.get_Range("A" + lastUsedRowEquity, "K" + lastUsedRowEquity).Value2 = data;
}
else if (row.Cells[5].Value.ToString() == "OBL")
{
lastUsedRowBond += 1;
oSheetBond.get_Range("A" + lastUsedRowBond, "K" + lastUsedRowBond).Value2 = data;
}
else if (row.Cells[5].Value.ToString() == "ACCOUNT")
{
lastUsedRowAccount += 1;
oSheetAccount.get_Range("A" + lastUsedRowAccount, "K" + lastUsedRowAccount).Value2 = data;
}
}
RunMacro(oXL, new Object[] { "Main" });
oXL.UserControl = false;
//oWB.Close();
//oXL.Quit();
releaseObject(lastRowFund);
releaseObject(lastRowEquity);
releaseObject(lastRowBond);
releaseObject(lastRowAccount);
releaseObject(headerFund);
releaseObject(headerEquity);
releaseObject(headerBond);
releaseObject(headerAccount);
releaseObject(oSheetFund);
releaseObject(oSheetEquity);
releaseObject(oSheetBond);
releaseObject(oSheetAccount);
releaseObject(oWB);
releaseObject(oXL);
private void RunMacro(object oApp, object[] oRunArgs)
{
oApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod,
null, oApp, oRunArgs);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
当我点击工具StripButton5时,一切都在工作。但最后一个"EXCEL.EXE"进程仍然存在于任务管理器中。当我再次点击按钮时,会创建一个新的"EXCEL.EXE",但当任务完成时,进程会正确释放。当我退出应用程序时,第一个进程是释放。
你能帮助我在第一次点击时正确释放对象吗。
问候,
丹尼。
Marshal.ReleaseComObject(excelSheets); //Worksheet
Marshal.ReleaseComObject(excelSheet); //Sheet
等等希望这能有所帮助。
现在,它非常有效。当我用Excel执行第二次提取时,当创建新进程时,第一个进程将正确释放。
我只是在"releaseObject"System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)中替换;通过Marshal.ReleaseComObject(obj);
也许是因为我在标题中使用了"using System.Runtime.InteropServices;"。
非常感谢。
问候