使 Excel COM 对象保持活动状态
我目前正在使用Excel 2003 Interop机制来操作一些范围。
有时我将一些范围存储在我的 addin 中，作为类的属性，但是当我稍后尝试访问它们时，COM 对象有时是随机的，无效，并且当我尝试访问 COM 对象 的每个属性时，它们都会抛出“COMException”。
有人说 Excel 分配的对象与插件正在使用的对象之间没有强链接：因此“真正的”Range 对象 可以通过 释放>Excel 任何时候，即使从 .Net/C# 的角度来看，我仍然可以参考它们。
所以，我想知道是否有任何方法可以让我的 Range 对象保持活动状态，以便以后以安全的方式使用它们？
备注：我可以使用一种解决方法，例如存储字符串将代表范围，例如“A1”，而不是范围本身，但这会使代码不太干净。
预先感谢您的帮助。
I'm currently using the Excel 2003 Interop mechanism to manipulate some Ranges.
Sometimes I store some of the ranges in my addin, as properties of a class, but when I try to access them at a later time the COM objects are sometimes, randomly, invalid, and each property of the COM objects throws a "COMException" when I try to access them.
I have been said that there is no strong link between the objects Excel allocates and the objects the addin is using : so the "true" Range objects could be freed by Excel at any time, even if from a .Net/C# perspective I still have a reference to them.
So, I was wondering if there is any way to keep my Range objects alive to use them later in a safe manner ?
Remark : I could use a workaround like storing strings that would represent the Ranges, e.g. "A1", instead of the Ranges themselves but this would make the code less clean.
Thanks in advance for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是指 Excel 使用“Flyweights”这一事实。 Excel 内部不使用 COM；它根据需要为其内部结构（我将它们视为“视图”）创建 COM 包装器，以服务 COM 客户端（无论是外接程序、VBA 还是外部应用程序）的请求。
我不会说“随时”。
一旦获得
Range对象或任何其他 COM 对象，该 COM 对象将保持可用状态，直到被释放（在 COM 意义上 - 为每个对象调用IUnknown::Release()AddRef())。但是，如果您持有 COM 对象并且底层结构不再存在，则 COM 对象确实会变得无效。它还能做什么？
假设您保留了一个范围，并且用户删除了该范围所在的工作表。你认为应该发生什么？ Excel 不会抛出一个消息框，显示“抱歉，您无法删除工作表，因为某些代码想要保留它”。如果用户删除数据，Excel 将执行此操作，并且
Range对象将必须应对。您的外接程序只需为这种可能性做好准备。如果您的外接程序提供公式，请让它们返回#REF 等。我认为无论如何都无法主动检测到这一点。我检查了某处是否有某种 IsValid(myRange) 方法，但我没有看到任何方法。
无论如何，它不太可能很有用。许多命令可能会导致 [windows] 消息被处理，并且任何时候发生的外部代码都可能在毫无戒心的情况下运行并使您的范围无效。您确实必须在错误发生时捕获它，并适当地“下注”。
也许你是对的。也可能是“随时”。但这不是随机的。必须有人去破坏范围。
顺便说一句，我不会试图以智取胜。保存范围参考文本只会导致 AddIn 作用于用户想要的不同数据集。 “正确”的做法是“优雅地失败”。
That is referring to the fact that Excel uses "Flyweights". Excel does not use COM internally; it creates COM wrappers to its internal structures (I think of them as "views") as needed to serve requests by a COM client (be it an Add-in, VBA or an external application).
I wouldn't say "at any time".
Once you get a
Rangeobject, or any other COM object, the COM object will stay available until released (in the COM sense - callingIUnknown::Release()for eachAddRef()).However, if you hold a COM object and the underlying structures cease to exist, the COM object does become invalid. What else could it do?
Lets say that you hold onto a
Rangeand the user deletes the worksheet where the Range was located. What do you think should happen? It's not like Excel is going to throw a message box saying "sorry, you cannot delete the worksheet because some code wants to hold onto it". If the user deletes the data, Excel will comply and theRangeobject will have to cope. You Add-in just needs to be prepared for that possibility. If you Add-in provides formulas, have them return #REF, etc.I don't think there is anyway to proactively detect this. I checked to see if there is some kind of IsValid(myRange) method somewhere, but I didn't see any.
Anyway, it's unlikely it would be very useful; many commands could result on [windows] messages being processed and anytime that happens outside code could run at unsuspecting times and invalidate the range on you. You really have to catch the error as it happens, and 'punt' appropriately.
Maybe you're right. It might as well be "at any time". But it's not random. Someone has to go and muck with the range.
Incidentally, I wouldn't try to outsmart the situation. Saving the range reference text will only result in the AddIn acting on a different set of data that the user intended. The "correct" thing to do is to "fail gracefully".