CODEWITHSUNDEEP
vb.netmultithreadingexcelpowerpoint
Avlin
Avlin

Reputation: 520

Excel Object Model Performance issue (cross appdomain and threading)

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 :

  1. Calling Excel object model directly from PowerPoint :on average 13 s
  2. Calling this code from PowerPoint trough a WCF services hosted by an excel Addin : on average 9 s
  3. Calling this code from an Excel Addin through a timer (at startup, wait 20 seconds and launch test()) : 9 s
  4. 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

Upvotes: 1

Views: 542

Answers (2)

user846913
user846913

Reputation: 11

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.

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23505

.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.

Upvotes: 1

Related Questions