Running Operation in Excel

 

I have an Excel spreadsheet and I have a .Net library which does some fairly complex calculcations. The .Net assembly is exposed via COM to the VBA code in Excel.

When I call out to the .Net library, the Excel UI thread is getting blocked while the calculation is in progress.

This prevents the user from interacting with Excel and in some cases indicates to the user that Excel is (Not Responding) in the title bar.

I don't know very much about VBA in Excel 2010, but is there any way to spawn off the call to my .Net library in a different thread and present an "In Progress" dialog to the user?

It is possible, but may take a bit of effort to wire everything up. There are a number of ways you could approach this, one of which might look something like:

  • VBA calls C# COM object on UI thread
  • C# COM object starts a BackgroundWorker to run the calculation. Note that you can't access the Excel object model from the BackgroundWorker 's DoWork event handler. If you want to update Excel while the calculation is in progress, the BackgroundWorker should use the ReportProgress method to report progress on the UI thread.
  • In the ProgressChanged event handler (which runs on the UI thread), the C# code can either directly update the Excel object model, or perhaps call into VBA code to perform the update.
  • The VBA code could display a Progress dialog while the BackgroundWorker is running, to avoid any problems of reentrancy.

    • Chances are that NO, you can't use another thread in C#. Excel is a single threaded app and that thread is the UI thread, so working with Excel's object model will block the UI.

    An alternative is to use OpenXmlSdk and use it to perform your operation on a copy of Excel's data on a separate thread then load the data back in. Depending on the size of the data and calculation time it may be well worth it. I've used this technique in Word on documents with ~100 pages and it shortened processing time from 10 minutes to <30 sec, most of these 30 sec being used to export and import the data from/to Word. Excel tends to be a bit faster in that area.

    Thats difficult to do using VBA because Excel VBA is pretty much single-threaded synchronous. But Excel 2010 does support asynchronous UDFs: look at Excel DNA or Addin Express for some help on doing this from .NET.
    http://exceldna.codeplex.com/wikipage?title=Performing%20Asynchronous%20Work&referringTitle=Documentation
    http://www.add-in-express.com/add-in-net/index.php

    2: http://www.add-in-express.com/add-in-net/index.php
    You can also do this from a C-based XLL
    http://msdn.microsoft.com/en-us/library/office/ff796219%28v=office.14%29.aspx

    链接地址: http://www.djcxy.com/p/37996.html

    上一篇: 选择范围时避免焦点改变

    下一篇: 在Excel中运行操作