当前位置: 动力学知识库 > 问答 > 编程问答 >

vb.net - VSTO excel object performance issue

问题描述:

Hi

I wrote this little AddIn sample to show you a performance issue and how to avoid it

Can someone explain me why and how it works ?

It is just a parse of an excel workbook and runned in the main excel process (0) and a random thread created by the timer.

Thanks !

Public Class ThisAddIn

Dim a As System.Windows.Threading.Dispatcher = System.Windows.Threading.Dispatcher.CurrentDispatcher()

Dim t As New Threading.Thread(New Threading.ParameterizedThreadStart(AddressOf threadTest))

Dim tm As New System.Timers.Timer(20000)

Delegate Sub TestHandler()

Dim tt As TestHandler = AddressOf test

Private Sub ThisAddIn_Startup() Handles Me.Startup

tm.AutoReset = True

tm.Start()

AddHandler tm.Elapsed, AddressOf threadTest

End Sub

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

End Sub

Public Sub test()

Dim appE As Excel.Application = Globals.ThisAddIn.Application

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

End Sub

Private Sub threadTest()

' 800 ms

Dim o() As Object

a.Invoke(tt, o)

'12 seconds !

test()

End Sub

End Class

网友答案:

You're diving into the world of COM threading models. This is a good a start as any: http://msdn.microsoft.com/en-us/library/ms693344(VS.85).aspx.

If the code runs on the Excel main thread (which you achieve by setting up the Dispatcher), the COM calls are not marshaled across different threads. Since you have many COM calls (each .Value counts as one) the overhead adds up to the differences you see.

One of the reasons why the marshaling is expensive in this context, is that the Excel COM objects are running in a single-threaded apartment (STA), which means there is a message loop set up (actually a Windows message loop) in Excel to serialize the COM calls. Every cross-apartment call you make results in a message being posted to this message loop, which is the processed on the main Excel thread.

So the two cases differ in performance due to the COM cross-apartment marshaling. It's actually remarkably fast, given what is going on behind the scenes.

In both cases, making a single call to set a large range's .Value to an array of values will be much faster. And for the fastest (million cells a second) way to set data into your Excel sheet, see here: Fastest way to interface between live (unsaved) Excel data and C# objects.

分享给朋友:
您可能感兴趣的文章:
随机阅读: