search

Excel-DNA / ExcelDna

Excel-DNA - Free and easy .NET for Excel. This repository contains the core Excel-DNA library.
https://excel-dna.net
zlib License
1.31k stars 277 forks source link

Excel scripting editor and awaitable execution on the main thread #91

Open buybackoff opened 8 years ago

buybackoff commented 8 years ago

Hi, @govert. You may be interested in my blog post about combining ExcelDna and RoslynPad to get a nice script editor that allows to write UDFs, commands and scripts as easy as Alt+F11 and VBA, but with the power of C# and this library. http://hotforknowledge.com/2016/10/31/5-data_spreads_excel_macros_in_csharp/

However, another reason I am posting this issue is to draw your attention to the XlResult pattern from here. It is just a simple awaitable wrapper over some function that will always be executed on the main thread. If I am not mistaken, there is no such short-cut functionality present in the library, and one should rewrite a similar pattern every time. So if you could review it and add such pattern to ExcelDna, it would be great. There are so many subtleties with COM objects, it is easy to make something wrong.

In addition, I believe that I have seen somewhere your comment that if we access COM objects only and strictly from the main thread, then we could skip the Marshal.ReleaseComObject clean-up of every object and that will be done automatically. Is this really the case? Usage of COM objects in UDFs without cleanup doesn't prevent Excel from exiting, but I am not sure if this is by luck or design?

Finally, is multiple re-registration of UDFs using the ExcelDna.Registration project will lead to some leaks and one should keep track of already registered functions and unregister them manually (like here), or the ExcelDna.Registration already does this, or I could just ignore the warnings unless I re-register functions million times?

Many thanks!

govert commented 8 years ago

@buybackoff That looks very cool. You might like to post to the ExcelDna Google group for a bigger audience. Are you planning to make the editor available?

I'll have a look at the XlResult stuff.

Nearly everything about the .NET / COM interop on the StackOverflow question that you link to is either wrong or completely misguided.

You're right that keeping COM access on the main thread (and in a safe context, like a macro, event or ribbon callback) means you don't have to worry about COM object lifetimes or any of the crazy advice about 'two dots'. But the function calculation context (even on the main thread) is one where I am not sure of the situation. The Excel documentation indicates that using the COM object model here is not supported. But often it works OK.

There's no support in Excel-DNA for completely unregistering or replacing functions, and no checking for duplicate re-registration. The deregistration code you point to removes the name from Excel, but not the internal registration. So eventually you'll run out of registration 'slots' - there are 10000 in the current version. You might want to look at splitting the dynamic part into a separate .xll add-in that you can unload and reload. That will completely clean up the registrations and make a new AppDomain.

buybackoff commented 8 years ago

The editor itself is the RoslynPad project by @aelij. Before publishing the integrated project, I need to clean some things and separate the Excel functionality from a mixed bag of other things, - as I wrote that was just a feature that I published "almost accidentally". From Excel-Dna point of view, I just use reflection to find all methods with the ExcelFunction and Command attributes from a dynamic assembly that Roslyn generates. It will help a lot if you could have a look at the integration details, including XlResult, so I will try to publish it quite soon.

Passing a dynamic assembly to another AppDomain could be complicated. 10000 sounds like a big enough number. For a rapid iterative development I believe Excel will crash earlier for any reason than a user would be able to rewrite a function so many times...

buybackoff commented 8 years ago

BTW, if by "available" you mean ready to use, the link to an installer is in the post. Though it is an unsigned exe/msi, which could make things complicated for some users. I am thinking about a big xll that will contain all the dependencies, however an xll file is also an executable.