Excel 对象模型性能问题（跨应用程序域和线程）
我正在 VB 中使用 VS2010 编写 Office 2007 的 Excel 插件，在与 PowerPoint 中的 Excel 对象模型交互时遇到严重的性能问题。 请在下面找到一个基本测试来演示该问题（sub test()）。
我们以 4 种不同的方式尝试了此代码，得到了不同的结果：
- 直接从 PowerPoint 调用 Excel 对象模型：平均 13 秒
- 通过 Excel 插件托管的 WCF 服务从 PowerPoint 调用此代码：平均 9 秒
- 从 Excel 插件调用此代码通过计时器（启动时，等待 20 秒并启动 test()）：9 s
- 通过功能区按钮直接从 Excel 插件调用代码：800ms
我可以了解为什么 1. 速度慢是因为代码与 Excel 不在同一个 AppDomain 中。 我也可以理解为什么 2. 和 3. 很慢，因为代码不是在 Addin 线程中执行的。 但我想不出任何其他解决方案来解决我的性能问题。
最后我有两个问题
1）当我说方法2和3因为多线程而慢时，我是否在正确的轨道上？
2）一般来说，如何在.Net应用程序中使用Excel对象模型而不会有这样的性能损失。
预先感谢您可能有的任何想法。
尼古拉斯
Public Sub test(ByVal appE As Excel.Application)
Dim chrono As New System.Diagnostics.Stopwatch
chrono.Start()
appE.Interactive = False
appE.ScreenUpdating = False
Dim wb As Excel.Workbook = appE.ActiveWorkbook
Dim ws As Excel.Worksheet = wb.ActiveSheet
Dim rng As Excel.Range = ws.Cells(1, 1)
Dim nbit As Integer = 10000
For i = 1 To nbit
rng.Value = i
Next
appE.ScreenUpdating = True
appE.Interactive = true
chrono.Stop()
MsgBox(chrono.ElapsedMilliseconds.ToString + " " + System.Threading.Thread.CurrentThread.ManagedThreadId.ToString)
End Sub
I’am writing an Excel AddIn for Office 2007 with VS2010 in VB, and I have severe performance issues when interacting with the Excel Object model from PowerPoint.
Please find below a basic test to demonstrate the issue (sub test()).
We tried this code in 4 different ways with different results :
- Calling Excel object model directly from PowerPoint :on average 13 s
- Calling this code from PowerPoint trough a WCF services hosted by an excel Addin : on average 9 s
- Calling this code from an Excel Addin through a timer (at startup, wait 20 seconds and launch test()) : 9 s
- Calling the code directly from an Excel Addin through a ribbon button : 800ms
I can understand the difference why 1. is slow since the code is not in the same AppDomain as Excel.
I can also understand why 2. and 3. are slow since the code is not executed in the Addin Thread.
But I can't think of any additional solution to solve my performance issue.
In the end I have two questions
1) Am I on the right track when I say that method 2 and 3 are slow because of multithreading?
2) In general, how can I use Excel object model from a .Net Application and not have such a loss of performance.
Thanks in advance for any ideas you might have.
Nicolas
Public Sub test(ByVal appE As Excel.Application)
Dim chrono As New System.Diagnostics.Stopwatch
chrono.Start()
appE.Interactive = False
appE.ScreenUpdating = False
Dim wb As Excel.Workbook = appE.ActiveWorkbook
Dim ws As Excel.Worksheet = wb.ActiveSheet
Dim rng As Excel.Range = ws.Cells(1, 1)
Dim nbit As Integer = 10000
For i = 1 To nbit
rng.Value = i
Next
appE.ScreenUpdating = True
appE.Interactive = true
chrono.Stop()
MsgBox(chrono.ElapsedMilliseconds.ToString + " " + System.Threading.Thread.CurrentThread.ManagedThreadId.ToString)
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
目前 Excel 的 .Net 性能很差，每次引用 Excel 对象模型都会产生非常高的开销。
因此，加快速度的一种方法是将要传输的所有数据收集到一个数组中，并在对对象模型的一次调用中将其传输。
.Net performance with Excel is currently poor, there is a very high overhead associated with each reference to the Excel object model.
So one way of speeding this up is to collect all the data you want to transfer into an array and the transfer it in a single call to the object model.
我们在 Excel 对象模型中遇到了类似的性能问题。我们转而使用 MS ACE 驱动程序来读取 Excel。尽管我们遇到了一些问题，例如处理注册表中的 typeguessrows 标志，但现在的性能非常好。可用的文档不多，因此我们一直严重依赖 Microsoft 支持团队。
We encountered similar performance issues with the Excel object model. We switched to using the MS ACE Driver to read Excel. Performance is now fantastic, though we have encountered some issues such as dealing with the typeguessrows flag in the registry. Not a lot of documentation available so we have been leaning heavily on the Microsoft support team.