A Third Way: DNA?

(stackoverflow rep: 8417, Project Euler 87/261 complete

When we want Excel to talk to a compiled library, the list of options available to us is not long.

On the one hand, we can write a compiled library that references the Excel SDK (pre– or post-2007), setting its prefix to XLL for convenience, typically we’d grit our collective teeth and accomplish this with C or (cough, spit) C++. One big plus here is speed – we’re talking to Excel in its own language. Or something awfully close to it. Another plus, of course, is that this approach probably involves the minimum set of dependencies for non-enterprise distribution convenience. If you haven’t spotted the big minus then we should probably agree to disagree on the relative merits of programming languages.

On the other hand, we can write a library that exposes its functions through good ol’ COM, referencing it though the Tool…Add-ins dialog. That’s probably still the best-known way to integrate functionality developed in .NET, although it does involve paying a performance price as the COM interface is crossed and recrossed. This would be the place in the world most usually occupied by VSTO. Of course, you could also write your COM library in non-managed code: VB6 is easy (if you can find it) but slow, the choice from others depends on your capacity (or desire) for pain.

On the gripping hand, we have ExcelDNA, which for my money is one of the all-round cunningest things you’re likely to come across in quite a long time. (Unless you’ve already encountered its cunningness, of course, in which case you’re probably already nodding along in sage agreement). How so? How about being able to write your spiffy new functions in C# but without having to incur the needless ins and outs of the wasteful and superfluous COM middleman?

ExcelDNA provides a small XLL that can talk to managed code. There’s one attribute to set in order to make a function visible to Excel and one libary to import (in order to be able to add the attribute). Oh, and a little config file to tell the XLL what to load. That’s it. Actually, that’s the complicated version. In case you didn’t know, the CLR includes a compiler, allowing code to be created and compiled at run-time. The simplest way to talk to Excel from .NET via ExcelDNA is just to put your code directly into that little config file and let the XLL compile it at load time. OK, there’s a second or so’s overhead, but how simple – anything simpler would probably have to be involve 21st-Century language integration where VBA lives today.

By now you’re probably muttering something like “write code, fat bloke”, which is a little cruel, but a sentiment otherwise understandable. Now only the other day, Dick Kusleika posted his take on the everybody-has-one timeless RangeJoin() UDF topic. Here’s a rather simpler implementation:


<DnaLibrary Language="CS">
<![CDATA[
using ExcelDna.Integration;
using System.Collections.Generic;

    public class MyFunctions
    {
        [ExcelFunction(Description="Joins cell values", Category="My ExcelDNA functions")]
        public static object RangeJoin(object[,] cells)
        {
            List<string> list = new List<string>();

            foreach (object o in cells)
                list.Add(o.ToString());

            return string.Join(",", list.ToArray());
        }
    }
]]>
</DnaLibrary>

To get it running, I saved it as “RangeJoin.dna”, then copied the ExcelDNA.xll into the same folder and renamed that to “RangeJoin.xll”. Because it isn’t currently anywhere on my path, I also put ExcelDna.Integration.dll in the folder. Then I double-clicked my new XLL file to start Excel and load the library. (Pictures below are from Excel 2002, but the code is tested up to 2007).

Not surprising...

There’s a tiny extra delay before the usual macro warning appears – the C# code’s being compiled, which is where compile-time failures are displayed. Then you’re up and running. The function shows up in the Insert Function dialog…

… and we can try calling it:

… with the following result:

I did notice the the =NA() output and the Euro amount came out different to what I’d have hoped. It rather looks like I’d have to dig a little deeper into the ExcelDna.Integration.dll and the ExcelReference object in particular in order to be able to access the equivalent of VBA’s Text property.

Still, not bad for a quick exercise, I’d say.

There are some alternative approaches to the dot-net-and-excel-without-going-anywhere-near-COM-add-ins topic:

For Free

Excel4Net is now free – it seems to work by implementing a single worksheet function that calls out to managed code;

The last time I looked at XLW it was focused on C++. That’s changed in the interim – C# and VB.NET are now options.

Possibly Paid-for Propositions

Len Holgate appears to be at beta test stage with JetXLL, which looks like it’ll be a commercial offering;

I also just discovered ExHale. In beta, appears current – can’t see what the terms are likely to be;

Definitely Demanding Dollars

Possibly the first implementation, ManagedXLL is (was?) a paid-for product that looks to have been similar to, but possibly broader in scope than ExcelDNA. It’s not clear whether the company is still active or the product is still available – a curious colleague recently tried to make enquiries and failed.

Advertisements

13 Responses to A Third Way: DNA?

  1. Ross says:

    Great post Mike. XLDNA has been on my list for a while, I’ve just got to give it a go now!
    Thansk
    Ross

  2. Len Holgate says:

    The JetXLL beta is still on track to start in the new year, the aim is for a commercial product with a low price aimed at people who don’t want to have to build the infrastructure themselves. It probably wont appeal to the kind of people that can use ExcelDNA or XLW.

    If you’re interested in taking part in the beta then let me know!

  3. Hi Mike, thanks for the nice example.
    You actually don’t need to distribute the ExcelDna.Integration.dll library. A copy is stored inside the .xll and is extracted at runtime. So you need only copy the .xll and .dna files. If you compile your functions into a .dll you need to reference the ExcelDna.Integration.dll in your project, but need not distribute a copy.
    –Govert

  4. Mathias says:

    Thanks for this post. As a former VBA developer who works mostly with .NET now, whenever I do anything Excel-related, I either use VSTO or Interop from a C# app. Your post opened an interesting 3rd way to explore for me!

  5. Simon says:

    Mike I can’t believe I missed this post, have added you to my blog roll so I don’t make the same mistake again.
    There is also xll+ a c++ based commercial tool.

  6. Pingback: Mutant Excel and .Net with ExcelDNA

  7. Pingback: Xll add-ins; are they worth the trouble? « Newton Excel Bach, not (just) an Excel Blog

  8. Pingback: The MIE Podcasts – Govert van Drimmelen « Methods In Excel

  9. johny why says:

    hello, do any of these options include the ability to encrypt the .dna file, to prevent reverse engineering? cheers, mate.

  10. If you just need a pure, lightweight C++ solution, check this out.

  11. Here is how to do this example using xll.codeplex.com including individual argument help in the Function Wizard.


    static const char* xav_range_join[] = {
    "is a range of cells.",
    "is an optional seperator used when joining cells. "
    };
    static AddIn xai_range_join(
    "?xll_range_join", XLL_LPOPER XLL_LPOPER XLL_LPOPER,
    "RANGE.JOIN", "Range, Separator",
    "My XLL Functions", "Joins cells in Range using Separator.",
    dimof(xav_range_join), xav_range_join
    );
    LPOPER WINAPI
    xll_range_join(LPOPER poRange, LPOPER poSep)
    {
    #pragma XLLEXPORT
    static OPER oJoin;
    const OPER& oRange(*poRange);

    oJoin = oRange[0];
    for (WORD i = 1; i < oRange.Size(); ++i) {
    oJoin = Excel(xlfConcatenate, oJoin, *poSep, oRange[i]);
    }

    return &oJoin;
    }

  12. Pingback: Clear Lines Blog

  13. Pingback: The Excel-DNA | kumbukumbu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: