Managed XLL Excel Addins

| 4 Comments

Back in December and January I was working on the initial phases of a system to allow the development of custom Excel worksheet functions in managed code using the Excel XLL 'C' interface (Excel4v). Phase 1 finished with us having a pretty workable system that the client could go live with. The only problem was that we skipped the key ease of use part of the project due to time and budget constraints. Whilst the C++ side of the system worked and we could marshal values from Excel types to managed types and back again and we could dispatch calls to managed code we had to hardcode the marshalling information in the C++ code and rebuild the XLL to add new functionality.

I've just completed phase 2 of this project and it now works just like we originally envisaged. Managed code can be decorated with custom attributes and the XLL uses reflection to work out which classes within an assembly are being exposed to Excel. It then looks for [WorksheetFunction] attributes and exposes individual functions to Excel whilst working out all of the marshalling requirements itself. Additional attributes allow you to specify that some parameters are optional or have default values. More attributes allow for the definition of custom Excel menus and the provision of the code behind them. By annotating a suitable constructor you can have an IManageAddins interface passed into your managed class when it's constructed and this lets you call back into the XLL framework and/or Excel to do things like enable and disable menu items, etc. Several custom types allow us to pass ranges of Excel cells into and out of managed code, and our standard marshalling can deal with converting Excel ranges that contain consistent types to arrays of native managed types. Volatile and command equivalent worksheet functions are supported and, all in all, it all seems to work pretty nicely.

When the XLL is loaded it reads its configuration from a config file and loads the specified assemblies, parses them for attributes, builds and registers the appropriate data structures and wires up the XLL entry points to the appropriate marshaller and from there into managed code.

The next phase is likely to be the inclusion of 'results object' which can be used to store and manipulate returned ranges by name. At this point I'll have something similar to the system I wrote in C++ back in 2001, but this time all of the complex stuff just works and the developers can settle back and get on with writing the business logic.

4 Comments

We have recently released a beta version of Excel4Net 2.0 - a RAD development kit for Excel and .NET.

If you are stil interested in a Excel and .NET integration - here are the links:

Free download:
http://www.excel4net.com/Download.aspx

Quick overview:
http://www.excel4net.com/Overview.aspx

I'm happy with what we developed, and, to be honest, it looks a bit more powerful than what you guys have. Still, I may take a look at your beta.

Sounds great. Is there any way I could get a copy of this?

Nate,

The code that I was writing about here was being developed for a client. I retained the IP rights and have been working on a more general purpose version of the code for general release. I was hoping to have this new code in beta by the end of 2009 but various things got in the way and slowed me down. Anyway, take a look at JetXLL.com which is the site I'm using for the Managed XLL product. I'll add you to the list of beta users and when I get the beta ready to roll I'll send it out to you. I think I'm still a few week's worth of work away from the beta being ready and I have several other unrelated pieces of work to do before I can get back to working on the beta...

Leave a comment