在SaveAs之后，excel没有关闭; com对象释放麻烦 [英] excel doesn't close after SaveAs; com object release trouble
问题描述
我正在尝试通过COM自动创建excel文件。我在下面复制了我的
代码。我阅读了许多关于如何发布我创建的COM对象的文章。下面的代码运行正常，excel关闭。但是有一些注释行：
//xlSeries.XValues = xlWs.get_Range（" B2"，" B4"）; //制作com对象，但
...
// xlWb.SaveAs（exeDir +" \\test.xls"，缺少，丢失，//使得
对象，但是
//缺少，丢失，丢失，Excel.XlSaveAsAccessMode.xlNoChange，
// Excel.XlSaveConflictResolution.xlLocalSessionChang es，missing，missing，
缺失，缺少）;
如果我取消注释其中一个命令，excel won'不再关闭了。我猜这些行创建了一些我必须释放的COM对象。我的
问题：
1）这是真的吗？
2）我怎样才能找到（一般来说）哪个COM已创建对象？
3）是否有更安全/自动/更简单的方式释放所有对象，所以excel
可以关闭？
4）作为最后的手段，我有一个kill例程（参见代码）。这是
是否可取？我必须多次运行代码。杀死excel会搞砸
内存还是会出现内存泄漏？
非常感谢，最好的问候，
-Hendri Adriaens。
---------代码---------------
使用System;
使用System.Collections.Generic;
使用System.ComponentModel;
使用System.Data;
使用System.Drawing;
使用System.Text;
使用System.Windows.Forms;
使用System。 IO;
使用System.Runtime.InteropServices;
名称空间WindowsApplication1
{
public partial class Form1：Form
{
对象丢失= Type.Missing;
string exeDir;
public Form1（）
{
InitializeComponent（）;
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo（" en-US"）; // tbv bug
exeDir = Path.GetDirectoryName（Application.ExecutablePath）;
}
private void button1_Click（对象发送者） ，EventArgs e）
{
Excel.ApplicationClass xla = new Excel.ApplicationClass（）;
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add（缺失）;
Excel.Worksheet xlWs =（Excel.Worksheet）xlWb.ActiveSheet;
Excel.Range xlRange;
//在表格中添加一些内容
xlRange = xlWs.get_Range（" B2"，missing）;
xlRange.Value2 = 2;
xlRange.get_Offset（1,0）.Value2 = 3;
xlRange.get_Offset（2,0）.Value2 = 4;
xlRange.get_Offset（0,1）.Value2 = 4;
xlRange.get_Offset（1,1）.Value2 = 5;
xlRange.get_Offset（2,1）.Value2 = 6;
//创建图表
Excel.ChartObjects xlChartObjs =
（Excel.ChartObjects）xlWs.ChartObjects（缺少）;
Excel.ChartObject xlChartObj = xlChartObjs.Add（5,100,450,300）;
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range（" C2" ，C4）;
xlChart.SetSourceData（xlRange，缺失）;
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries =（Excel.Series）xlChart.SeriesCollection（1）;
//xlSeries.XValues = xlWs.get_Range（" B2"，" B4"）; //制作com对象，但
...
xla.DisplayAlerts = false;
// xlWb.SaveAs（exeDir +" ; \\test.xls"，缺失，缺失，//使得
对象，但是......
//缺少，丢失，丢失，Excel .XlSaveAsAccessMode.xlNoChange，
// Excel.XlSaveConflictResolution.xlLocalSessionChang es，missing，missing，
missing，missing）;
xla.DisplayAlerts = true;
releaseComObject（xlSeries，true）;
releaseComObject（xlChart，true）;
releaseComObject（xlChartObjs，true）;
releaseComObject（xlChartObj，true）;
releaseComObject（xlRange，true）;
foreach（Excel.Worksheet xlWsTemp in xlWb.Worksheets）
{
releaseComObject（xlWsTemp，false）;
}
xlWs = null;
xlWb.Close（false，missing，missing）;
releaseComObject（xlWb，true）;
xlWbs.Close（）;
releaseComObject（ xlWbs，true）;
xla.Quit（）;
releaseComObject（xla，true）;
GC.Collect（）;
GC。 WaitForPendingFinalizers（）;
GC.Collect（）;
GC.WaitForPendingFinalizers（）;
// foreach（System.Diagnostics.Process proc在
System.Diagnostics.Process.GetProcessesByName（" EXC EL"））
// {
// proc.Kill（） ;
//}
}
private void releaseComObject（对象theObject，bool makeNull）
{
尝试
{
while（System.Runtime.InteropServices.Marshal.ReleaseCom Object（theObject）>
0）
$
}
终于
{
if（makeNull）
{
theObject = null;
}
}
}
}
}
Hendri，
有几点要指出。首先，你对releaseComObject的调用是通过一个循环来调用ReleaseComObject的
。你不需要这样做。
这个。你应该只需要这样做一次。其次，您按值传递
对象。在该例程中将对象设置为null是不会的。
设置在返回时在theObject参数null中传递给它的引用。
你的循环通过工作表是多余的，因为你只是创建了你最初没有的引用然后
发布它们。
每次在Range实例上调用Offset属性时，它都会返回一个新的Range实例，您需要调用ReleaseComObject。我在这里没看到
。那些引用肯定是闲逛。
假设你没有在任何地方持有这些引用，你可能会想要放弃调用ReleaseComObject而只是调用它静态收集GC类上的
方法，然后在应用程序对象上调用Quit后立即调用静态WaitForPendingFinalizers
方法。
- -
- Nicholas Paldino [.NET / C＃MVP]
- mv * @ spam.guard.caspershouse.com
" Hendri Adriaens" < sp ********** @ THISgmail.com写在留言中
新闻：3d ******************** *******@news1.tudelft.n l ...
我''我试图通过COM自动创建一个excel文件。我在下面复制了我的
代码。我阅读了很多关于如何发布我创建的COM对象的文章。下面的代码运行正常，excel关闭。但是有一些注释行：
//xlSeries.XValues = xlWs.get_Range（" B2"，" B4"）; //制作com对象，但
...
// xlWb.SaveAs（exeDir +" \\test.xls"，缺少，丢失，//使得
对象，但是
//缺少，丢失，丢失，Excel.XlSaveAsAccessMode.xlNoChange，
// Excel.XlSaveConflictResolution.xlLocalSessionChang es，missing，missing，
缺失，缺少）;
如果我取消注释其中一个命令，excel won'不再关闭了。我猜这些行创建了一些我必须释放的COM对象。我的
问题：
1）这是真的吗？
2）我怎样才能找到（一般来说）哪个COM已经创建了对象？
3）是否有更安全/自动/更简单的方式来释放所有对象所以
excel可以关闭？
4）作为最后的手段，我有一个kill例程（参见代码）。这是
是否可取？我必须多次运行代码。杀戮excel会搞砸
内存还是会出现内存泄漏？
非常感谢，最好的问候，
-Hendri Adriaens。
---------代码---------------
使用System;
使用System.Collections.Generic;
使用System.ComponentModel;
使用System.Data;
使用System.Drawing;
使用System.Text;
使用System.Windows.Forms;
使用System。 IO;
使用System.Runtime.InteropServices;
名称空间WindowsApplication1
{
public partial class Form1：Form
{
对象丢失= Type.Missing;
string exeDir;
public Form1（）
{
InitializeComponent（）;
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo（" en-US"）; // tbv bug
exeDir = Path.GetDirectoryName（Application.ExecutablePath）;
}
private void button1_Click（对象发送者） ，EventArgs e）
{
Excel.ApplicationClass xla = new Excel.ApplicationClass（）;
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add（缺失）;
Excel.Worksheet xlWs =（Excel.Worksheet）xlWb.ActiveSheet;
Excel.Range xlRange;
//在表格中添加一些内容
xlRange = xlWs.get_Range（" B2"，missing）;
xlRange.Value2 = 2;
xlRange.get_Offset（1,0）.Value2 = 3;
xlRange.get_Offset（2,0）.Value2 = 4;
xlRange.get_Offset（0,1）.Value2 = 4;
xlRange.get_Offset（1,1）.Value2 = 5;
xlRange.get_Offset（2,1）.Value2 = 6;
//创建图表
Excel.ChartObjects xlChartObjs =
（Excel.ChartObjects）xlWs.ChartObjects（缺少）;
Excel.ChartObject xl ChartObj = xlChartObjs.Add（5,100,450,300）;
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range（" C2" ，C4）;
xlChart.SetSourceData（xlRange，缺失）;
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries =（Excel.Series）xlChart.SeriesCollection（1）;
//xlSeries.XValues = xlWs.get_Range（" B2"，" B4"）; //制作com对象，但
...
xla.DisplayAlerts = false;
// xlWb.SaveAs（exeDir +" ; \\test.xls"，缺失，缺失，//使得
对象，但是......
//缺少，丢失，丢失，Excel .XlSaveAsAccessMode.xlNoChange，
// Excel.XlSaveConflictResolution.xlLocalSessionChang es，missing，missing，
missing，missing）;
xla.DisplayAlerts = true;
releaseComObject（xlSeries，true）;
releaseComObject（xlChart，true）;
releaseComObject（xlChartObjs，true）;
releaseComObject（xlChartObj，true）;
releaseComObject（xlRange，true）;
foreach（Excel.Worksheet xlWsTemp in xlWb.Worksheets）
{
releaseComObject（xlWsTemp，false）;
}
xlWs = null;
xlWb.Close（false，missing，missing）;
releaseComObject（xlWb，true）;
xlWbs.Close（）;
releaseComObject（ xlWbs，true）;
xla.Quit（）;
releaseComObject（xla，true）;
GC.Collect（）;
GC。 WaitForPendingFinalizers（）;
GC.Collect（）;
GC.WaitForPendingFinalizers（）;
// foreach（System.Diagnostics.Process proc在
System.Diagnostics.Process.GetProcessesByName（" EXC EL"））
// {
// proc.Kill（） ;
//}
}
private void releaseComObject（对象theObject，bool makeNull）
{
尝试
{
while（System.Runtime.InteropServices.Marshal.ReleaseCom Object（theObject）
0）
{
}
}
终于
{
if（makeNull）
{
theObject = null;
}
}
}
}
}
嗨尼古拉斯，
有几点需要指出。首先，你对releaseComObject的调用是通过一个循环来调用ReleaseComObject的
。你不需要这样做。
这个。你应该只需要这样做一次。
我认为你指的是带有while循环的releaseComObject方法。
我读到了某些对象所必需的。事实上，如果没有这个，我无法摆脱xlSeries的b $ b。其他对象通过调用ReleaseComObject
只工作一次，但xlSeries确实不是很不幸。我在网站上看了
，一般来说，我们必须循环直到计数器为零。确实
摆脱了xlSeries。
但也许我错过了你的观点？
其次，您按值传递对象。将对象设置为null
在该例程中不会设置在返回时在
theObject参数null中传递给它的引用。
好的，但它现在可以正常工作。 Excel已成功关闭（如果我们留下
麻烦行注释）并指定ref没有编译（它抱怨
关于转换Excel。< somthingto object）。
请建议我正确的代码。
你通过工作表的循环是多余的，因为你只是
创建你首先没有的引用然后
释放它们。
呃，这也是获得excel关闭的必要条件。我在这里阅读，只是
再次验证它，这是非常必要的：
http://www.devcity.net/Articles/239/3/article.aspx
每次调用Range实例上的Offset属性时，它都会返回一个新的Range实例，你需要调用ReleaseComObject.
我不喜欢在这看到。这些参考文献肯定是闲逛。
我不这么认为。如上所述，我的初始代码工作正常。我只能得出结论
我不得不反对，抱歉。
假设你没有抓住这些引用的任何地方，你可能会
想放弃调用ReleaseComObject，只需在GC类上调用静态Collect
方法，然后立即调用静态WaitForPendingFinalizers
方法你在应用程序对象上调用Quit。
嗯，这就是我正在做的事情。离开垃圾收集也使excel
保持开放。我彻底测试了代码，并尽可能地减少了代码。
留下任何东西，释放物品或垃圾收集使得excel
保持开放。
感谢您的反应。致以最好的问候，
-Hendri。
Hendri，
我想你使用while
循环引用我的releaseComObject方法。
我读到了某些对象所必需的。事实上，如果没有这个，我无法摆脱xlSeries的b $ b。其他对象通过调用
ReleaseComObject
只工作一次，但是xlSeries真的没有不幸。我在网站上看了
，一般来说，我们必须循环直到计数器为零。确实
摆脱了xlSeries。
循环是多余的。给出了文档，其中说明每次将
接口指针映射到运行时可调用包装器时，
内部计数会增加，并将其传递给出于
非托管代码，您确切地知道释放对象的次数。你在这里知道对象的生命周期，只需要拨打一次电话。
IMO，文档的建议循环到完全释放
对象不正确，因为它促使你不应该知道对象的生命周期，这不是一个好主意。 />
与COM合作。在.NET中，如果你没有实现IDisposable，那么
对象的生命周期并不是一个问题，但COM是一个完全不同的
野兽。引用计数和具体的
生命周期。
好的，但它现在可以正常工作。 Excel已成功关闭（如果我们留下
麻烦线评论）并指定ref没有编译（它抱怨
关于转换Excel。< somthingto object）。
是的，释放部分有效，但将参数赋值给
null不起作用。你在这里没有做任何事情，所以它并不是很重要，但代码没有做任何事情，只需将你的
releaseComObject中的标志设置为true。您需要使用
ref修饰符传递theObject参数，以使赋值为null生效。
呃，那是也有必要让excel关闭。我在这里读到了这个和
只是
再次验证它，这是非常必要的：
http://www.devcity.net/Articles/239/3/article.aspx
该文章也是错误的。它只是创建可能已经存在并释放它们的参考资料。它实际上是在执行一个
no op。如果没有正确发布的引用是
工作簿或工作表，那么这将有效，但
大多数应用程序与excel交互的情况并非如此（你将引用范围，这可能是
可能是孤儿）。
这篇文章基本上建议你走完整个对象模型
$在访问工作表时，工作簿的b $ b只有一个级别。
它表明对COM互操作如何工作缺乏了解，因为它是
要遍历整个heiarchy，以便追踪并释放一个它应该一直跟踪的一个
参考。
另外，文章中的以下陈述是错误的：
由于Microsoft Excel是基于组件架构构建的，因此更多
，使用COM +接口
具体来说，关于COM +接口的最后一部分。 COM +是Excel没有利用的组件
服务提供商。 Excel使用COM。
我不这么认为。如上所述，我的初始代码工作正常。我只能
得出结论
我不得不反对，抱歉。
尽管如此，如果它工作正常，那么你不会因为Excel没有正确关闭而导致
问题。
好吧，那就是我正在做的事情。离开垃圾收集也使得
excel
保持开放状态。我对代码进行了彻底的测试，并尽可能地将其编码为
。
将任何内容留下来释放对象或垃圾回收使
excel
保持开放。
感谢您的反应。此致，
-Hendri。
我附上了一个程序文件，它可以完成你想做的事情，
正确地释放所有参考文件而不使用一般文件循环到
发布参考。使用Excel 2003运行此应用程序确实正确关闭
。唯一需要注意的更改是在SaveAs调用中输出
文件的路径，以及来自
XlSaveConflictResolution枚举的值（我在猜你正在使用Excel
2007）。
当我通过它工作时，你的代码中还有许多其他明显的遗漏，最值得注意的是，当你将xlRange变量重新分配给另一个范围实例时，未能调用ReleaseComObject（多个
次）。
-
- Nicholas Paldino [.NET / C＃MVP]
- mv*@spam.guard.caspershouse.com
Hi,
I''m trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that I
create. The code below runs just fine and excel is closed. But there are
some commented lines:
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
If I uncomment either one of these commands, excel won''t close anymore. I
guess these lines create some COM objects that I have to release. My
questions:
1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so excel
can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up the
memory or present a memory leak?
Thank a lot in advance, best regards,
-Hendri Adriaens.
--------- The code ---------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;
public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXC EL"))
// {
// proc.Kill();
// }
}
private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseCom Object(theObject) >
0)
{
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}
}
Hendri,
A few things to point out. First, your call to releaseComObject is
cycling through a loop to call ReleaseComObject. You do not need to do
this. You should only have to do this once. Second, you are passing the
object by value. Setting the object to null in that routine isn''t going to
set the reference passed to it in the theObject parameter null upon return.
Your loop through the worksheets is superfluous, as you are just
creating references that you didn''t have in the first place and then
releasing them.
Every time you call the Offset property on the Range instance, it
returns a new Range instance which you need to call ReleaseComObject on. I
don''t see that here. Those references are definitely hanging around.
Assuming you aren''t holding onto these references anywhere, you might
want to forego calling ReleaseComObject and just call the static Collect
method on the GC class, and then call the static WaitForPendingFinalizers
method right after you call Quit on the application object.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Hendri Adriaens" <sp**********@THISgmail.comwrote in message
news:3d***************************@news1.tudelft.n l...Hi,
I''m trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that
I create. The code below runs just fine and excel is closed. But there are
some commented lines:
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
If I uncomment either one of these commands, excel won''t close anymore. I
guess these lines create some COM objects that I have to release. My
questions:
1) Is that true?
2) How can I find out (in general) which COM objects have been created?
3) Is there a safer/automatic/easier way to release all the objects so
excel can close?
4) As a last resort, I have a kill routine (see the code). Is this
advisable? I have to run the code many times. Will killing excel mess up
the memory or present a memory leak?
Thank a lot in advance, best regards,
-Hendri Adriaens.
--------- The code ---------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
object missing = Type.Missing;
string exeDir;
public Form1()
{
InitializeComponent();
System.Threading.Thread.CurrentThread.CurrentCultu re = new
System.Globalization.CultureInfo("en-US"); // tbv bug
exeDir = Path.GetDirectoryName(Application.ExecutablePath);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.ApplicationClass xla = new Excel.ApplicationClass();
Excel.Workbooks xlWbs = xla.Workbooks;
Excel.Workbook xlWb = xlWbs.Add(missing);
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.ActiveSheet;
Excel.Range xlRange;
// Add some content to the sheet
xlRange = xlWs.get_Range("B2", missing);
xlRange.Value2 = 2;
xlRange.get_Offset(1, 0).Value2 = 3;
xlRange.get_Offset(2, 0).Value2 = 4;
xlRange.get_Offset(0, 1).Value2 = 4;
xlRange.get_Offset(1, 1).Value2 = 5;
xlRange.get_Offset(2, 1).Value2 = 6;
// Create a chart
Excel.ChartObjects xlChartObjs =
(Excel.ChartObjects)xlWs.ChartObjects(missing);
Excel.ChartObject xlChartObj = xlChartObjs.Add(5, 100, 450, 300);
Excel.Chart xlChart = xlChartObj.Chart;
xlRange = xlWs.get_Range("C2", "C4");
xlChart.SetSourceData(xlRange, missing);
xlChart.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
xla.DisplayAlerts = false;
// xlWb.SaveAs(exeDir + "\\test.xls", missing, missing, // makes com
objects, but which...
// missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Excel.XlSaveConflictResolution.xlLocalSessionChang es, missing, missing,
missing, missing);
xla.DisplayAlerts = true;
releaseComObject(xlSeries, true);
releaseComObject(xlChart, true);
releaseComObject(xlChartObjs, true);
releaseComObject(xlChartObj, true);
releaseComObject(xlRange, true);
foreach (Excel.Worksheet xlWsTemp in xlWb.Worksheets)
{
releaseComObject(xlWsTemp, false);
}
xlWs = null;
xlWb.Close(false, missing, missing);
releaseComObject(xlWb, true);
xlWbs.Close();
releaseComObject(xlWbs, true);
xla.Quit();
releaseComObject(xla, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// foreach (System.Diagnostics.Process proc in
System.Diagnostics.Process.GetProcessesByName("EXC EL"))
// {
// proc.Kill();
// }
}
private void releaseComObject(object theObject, bool makeNull)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseCom Object(theObject)0){
}
}
finally
{
if (makeNull)
{
theObject = null;
}
}
}
}
}
Hi Nicholas,
A few things to point out. First, your call to releaseComObject is
cycling through a loop to call ReleaseComObject. You do not need to do
this. You should only have to do this once.I think you are referring to my releaseComObject method with the while loop.
I read that that is necessary for some objects. Indeed, I couldn''t get rid
of xlSeries without this. The other objects work by calling ReleaseComObject
just once, but xlSeries really didn''t unfortunately. I read on a website
that in general, we have to loop until the counter is zero. And that indeed
gets rid of xlSeries.
But maybe I am missing your point?
Second, you are passing the object by value. Setting the object to null
in that routine isn''t going to set the reference passed to it in the
theObject parameter null upon return.Ok, but it works as it is now. Excel is closed successfully (if we leave the
trouble lines commented) and specifying ref didn''t compile (it complained
about converting Excel.<somthingto object).
Please suggest me the correct code then.
Your loop through the worksheets is superfluous, as you are just
creating references that you didn''t have in the first place and then
releasing them.Uh, that was also necessary to get excel to close. I read that here and just
verified it once again and it''s really necessary:
http://www.devcity.net/Articles/239/3/article.aspx
Every time you call the Offset property on the Range instance, it
returns a new Range instance which you need to call ReleaseComObject on.
I don''t see that here. Those references are definitely hanging around.I don''t think so. As stated, my initial code works fine. I can only conclude
that I have to disagree, sorry.
Assuming you aren''t holding onto these references anywhere, you might
want to forego calling ReleaseComObject and just call the static Collect
method on the GC class, and then call the static WaitForPendingFinalizers
method right after you call Quit on the application object.Well, that''s what I''m doing. Leaving out garbage collection also makes excel
stay open. I tested the code thoroughly and made it as minimal as possible.
Leaving anything out wrt releasing objects or garbage collection makes excel
stay open.
Thanks for your reaction. Best regards,
-Hendri.
Hendri,
I think you are referring to my releaseComObject method with the while
loop.
I read that that is necessary for some objects. Indeed, I couldn''t get rid
of xlSeries without this. The other objects work by calling
ReleaseComObject
just once, but xlSeries really didn''t unfortunately. I read on a website
that in general, we have to loop until the counter is zero. And that
indeed
gets rid of xlSeries.The loop is superfluous. Given the documentation, which says that the
internal count on the runtime callable wrapper is incremented every time an
interface pointer is mapped to it, as well as passing it in and out of
unmanaged code, you know exactly how many times to release your object. You
know the lifetime of your object here, and only have to make the call once.
IMO, the recommendation of the documentation to loop to fully release
the object is not correct, as it promotes the idea that you should not be
aware of the lifetime of your objects, which is not really a good idea when
working with COM. In .NET, if you are not implementing IDisposable, then
the lifetime of your objects is not such a concern, but COM is a different
beast entirely as you are dealing with reference counts and specific
lifetimes.
Ok, but it works as it is now. Excel is closed successfully (if we leave
the
trouble lines commented) and specifying ref didn''t compile (it complained
about converting Excel.<somthingto object).Yes, the releasing part works, but the assignment of the parameter to
null does not work. You aren''t doing anything with it here, so it doesn''t
matter, but the code does nothing by setting the flag in your
releaseComObject to true. You need to pass the theObject parameter with a
ref modifier in order to have the assignment to null take effect.
Uh, that was also necessary to get excel to close. I read that here and
just
verified it once again and it''s really necessary:
http://www.devcity.net/Articles/239/3/article.aspxThe article is wrong as well. It is just creating references that might
not have existed already and releasing them. It''s effectively performing a
no op. If the references that have not been released correctly are
workbooks or worksheets, then this will work, but that''s not the case for
most apps that interop with excel (you will have references to ranges which
are probably orphaned).
The article is basically proposing that you walk the whole object model
of the workbook, but only goes one level deep, in accessing the worksheets.
It shows a lack of understanding of how COM interop works, in that it is
going to traverse the entire heiarchy in order to track down and release one
reference that it should have been keeping track of in the first place.
Also, the following statement in the article is false:
Since Microsoft Excel is built upon component architecture, more
specifically, using COM+ interfaces
Specifically, the last part about COM+ interfaces. COM+ is a component
services provider which Excel does not take advantage of. Excel uses COM.
I don''t think so. As stated, my initial code works fine. I can only
conclude
that I have to disagree, sorry.With all due respect, if it worked fine, then you wouldn''t have the
problem with Excel not shutting down correctly.
Well, that''s what I''m doing. Leaving out garbage collection also makes
excel
stay open. I tested the code thoroughly and made it as minimal as
possible.
Leaving anything out wrt releasing objects or garbage collection makes
excel
stay open.
Thanks for your reaction. Best regards,
-Hendri.I''ve attached a program file which does what you are trying to do,
correctly releasing all the references without using a general loop to
release references. Running this using Excel 2003, the application does
shut down correctly. The only changes to note are the path to the output
file in the call to SaveAs, as well as the value from the
XlSaveConflictResolution enumeration (I''m guessing you are using Excel
2007).
There were a number of other glaring omissions from your code when I
worked through it, most notably, the failure to call ReleaseComObject when
you reassigned the xlRange variable to another range instance (multiple
times).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
这篇关于在SaveAs之后，excel没有关闭; com对象释放麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
