Using Excel-DNA with NetOffice

[Andrei F]

Has anyone tried using NetOffice in an Excel-DNA addin? Any problems with them working together?
Do I have to create a NetOffice COM addin to get access to the Excel's COM API and the Application object, or there are simpler ways to do this?

[Govert van Drimmelen]

Since .NET 4 and the interop embedding the easiest is to not use NetOffice, just add the ExcelDna.Interop package (or a reference to the Interop assemblies otherwise).

To get the right Application object with ExceDnaUtil.Application.

-Govert

On 15 Dec 2016, at 09:34, Andrei F <fandre...@gmail.com> wrote:

Has anyone tried using NetOffice in an Excel-DNA addin? Any problems with them working together?
Do I have to create a NetOffice COM addin to get access to the Excel's COM API and the Application object, or there are simpler ways to do this?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Andrei F]

Unfortunately, using it leads to this OLE bug https://groups.google.com/forum/#!searchin/exceldna/OLE$20word%7Csort:relevance/exceldna/W15ZSPRtQCo/6nNqnreQIA8J
(and releasing Workbook objects passed to event handlers doesn't solve this problem)

[Govert van Drimmelen]

Interesting - I had forgotten about the embedding problem you point to.

If that's a concern, you can certainly use the NetOffice library instead of the PIA assembly.

Just ensure that you hook it up with the right Excel Application object (from ExcelDnaUtil.Application).

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Andrei F [fandre...@gmail.com]
Sent: 15 December 2016 09:50 AM
To: Excel-DNA
Subject: Re: [ExcelDna] Using Excel-DNA with NetOffice

[Fabian]

I'm using NetOffice as well for a current project; at least so far I haven't noticed any deal-breaking issues.

I find PIA a bit more user-friendly, even easier to handle, but from what I know about (which, regarding COM, admittedly isn't much, so maybe this is unfounded) I am very concerned about scenarios where they may be used in other threads, where releasing these afterwards is terribly difficult, or nearly impossible in some scenarios.

Cross-Excel-version compatibility could also be a bit more misleading.

From my experience so far, NetOffice's onyl real disadvantage is if you need need to marshal these objects accross AppDomains, which, in contrast to the PIA types, essentially isn't possible as they can't be marshaled by ref/value(/bleed) I believe - or at least I haven't found any direct solution for it. But that may be a very specific/niche use case.

[Gareth Hayter]

My add-in, FormulaDesk, is based on ExcelDNA and NetOffice.

Everything mostly works fine, except I need to do a lot of processing on different threads.

I've had problems with unreleased COM objects. The approach I've used is that whenever I access COM objects from a different thread (not the main UI thread) then I transition back to the main UI thread by using QueueAsMacro(). It requires some creative coding, but I think it's worth it.

Also, I created a modified debug version of the NetOffice assemblies that tracks COM objects that have not been Disposed explicitly. It reports back to me the object as well as the stacktrace that created it, so that I can easily track down naughty code ;-)

Kind regards,
Gareth.

[Andrei F]

"Just ensure that you hook it up with the right Excel Application object"

I wonder how can I do that? Can't find any docs covering this.

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

Hi Andrei,

Is the Excel embedding issue really an important use case for you?

To get the root object you need some code like this:

    var xlApp = new Application(null, ExcelDnaUtil.Application);

where the Application class is now from the NetOffice library.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Andrei F [fandre...@gmail.com]

Sent: 16 December 2016 06:09 AM

[Andrei F]

Hi Govert,

"Is the Excel embedding issue really an important use case for you?"

I'm afraid so.

"var xlApp = new Application(null, ExcelDnaUtil.Application);"

Thank you.

[Fabian]

Hey Gareth,

Hey Gareth,

interesting to hear;

I haven't used it a ton yet (and so far only from Excel's main thread, so I wouldn't have run into these issues in practice) -
but I thought one of NetOffice's greatest strength was management/internal tracking of all COM references. As in, automatic releasing of all child object, e.g. disposing the Application object would automatically release all child COM objects retrieved from this.

Are there any particular issues with this mechanism in multithreaded scenarios?

[Andrei F]

PS I wonder if it's possible to add an event in Excel-DNA.Interop that would be triggered for all COM interop objects created, similarly to NetOffice?

[Govert van Drimmelen]

I just want to add three comments about using the COM object model here.

1. For an Excel add-in, as long as the COM access is on the main thread, you need no manual management of the COM references. You never need to call Marshal.ReleaseComObject or anything like that. The .NET runtime and its garbage collector does the COM reference management correctly. So all the manual COM reference management that NetOffice adds is not needed in this context. You can file all the confusing advice about 'double-dots' etc. under "fake news".

2. Access Excel's COM object model from other threads are a mess, and cannot be made reliable. In part, this is due to bugs in Excel's implementation - for example, the COM message filter mechanism that would allow you to safely back off from COM calls while the host is busy is broken with Excel. There is no way to know whether any particular COM call from another thread will work or not - Excel can and will 'suspend' the object model at any point in the execution, and you can't reliably deal with this from your other thread. NetOffice or other wrappers cannot help with these limitations of Excel.

3. The 'Embed Interop Types' option avaiable since .NET 4.0 means that the version-specific nature of the Primary Interop Assemblies don't really matter anymore. You no longer need to ship and manage the extra set of assemblies - the bits you use are embedded at compile time. And regarding versions you're in the same position as a VBA developer - your COM calls will work on those Excel versions that support the called COM objects and methods. If you use Excel 2013-specific parts of the object model, then that will fail when running on Excel 2010. Same with VBA, same with NetOffice. One place where NetOffice helps is to show you in the IntelliSense when developing which Excel versions support a given method.

The weird bug where Excel COM event handling sometimes breaks when Excel is running in 'embedded' mode inside another application might be one exception to these rules - I don't know how many people actually do this.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Fabian [f.me...@gmail.com]
Sent: 16 December 2016 10:51 AM
To: Excel-DNA
Subject: [ExcelDna] Re: Using Excel-DNA with NetOffice