I have been working on an ExcelDna - an open-source project to integrate Excel and .Net. The topic has appeared in these forums from time to time, so I would like to gauge interest and feedback for the project from the financial software community. (Being free, I trust this doesn't sound too much like an advertisement.) So far I have a library that makes it easy to create user-defined functions and macros in .NET. The glue is through an .xll Add-In, thus supporting most versions of Excel. Currently the project files are hosted at http://exceldna.typepad.com. Introducing ExcelDnaExcelDna brings .NET to Excel power users and developers. The primary target is the Excel user who currently writes VBA code for functions and macros, and would like to start using .NET. Also interested would be C/C++ -based .xll add-in developers who want to use the .NET framework to develop their add-ins.ExcelDna is free for all use, and distributed under a permissive open-source license that also allows commercial use. ExcelDna is developed using .NET 2.0, and users have to install the freely available .NET Framework 2.0 runtime. The integration is by an Excel Add-In (.xll) that exposes .NET code to Excel. The user code can be in text-based (.dna) script files (Visual Basic or C#), or compiled .NET libraries (.dll) from any language (e.g. F#). Creating a user-defined function in C#: * Make a copy of ExcelDna.xll (found in the distribution) to a convenient directory, calling the copy Test1.xll. * Create a new text file, called Test1.dna (the same prefix as the .xll file), with contents: <DnaLibrary Language="CS"> <![CDATA[ public class MyFunctions { public static double AddThem(double x, double y) { return x + y; } } ]]> </DnaLibrary> * Load Test1.xll in Excel (File->Open or Tools->Add-Ins and Browse). * Enter =AddThem(4,2) into a cell - you should get 6. * There will also be an entry for AddThem in the function wizard, under the category Test1 (the same prefix as the .xll file). - Govert van Drimmelen

Dear Govert,I like the idea of this. I'm really looking forward to getting it working on my PC.There are a few things that I might add:1. I can't get it to work yet because of the Net 2.0 thing (that you mention in all the release notes). I actually have a number of Net 1.1 framework addins that I currently use on the machine in question, so I need to check that they still work on 2.0 before I can test it!2. Performance: most people trading or writing spreadsheets for traders will worry about performance. This is one of the main reasons why the xl C-interface is still so popular. When you provide a compiled dll will the exceldna.xll file call the exported functions via reflection? In which case I imagine there will be some perofrmance hit? How about when you provide your functions as a test file - does exceldna compile them to an assembly that it dynamically loads and executes? Certainly in 1.1 this resulted in less optimised code than when compiled separately.3. Most banks have fairly restrictive policies on the software that can be used. Although I do realise that exceldna requires very little 'installation' - just copying files, assuming you don't run into the problem described in (1) above. These policies mean that very little software of this sort gets used unless developed in house. Others may disagree with this statement - but it's just my impression.Good luck with the project, hopefully I will get it working and be able to offer some more useful comments.Regards,Russ

GovertWhat features does this offer over creating automation addins automatically? (Other than automation addins only work for Excel XP and 2003). With the techniques described in the link I gave I also get to add functions to an Excel workbook etc.Cheers,Mark

using the xll interface should involve less data marshalling than the com interface, but then if it's going to .net managed data types, it may be just as slow...

just checked it out, this is a very cool project, i like the way it doesnt need compiling and takes the scripts directly...good job!any idea how performance compares to vba? if it's a lot faster, then this would be great alternative to do quick UDF's without the need for compiling an xll...

Hi all,Thanks for the interest and comments so far.Let me reply to Russel's points first.1. The .Net 2.0 requirement can be a problem, certainly in the short term. You should find that version 2.0 and 1.1 coexist quite happily on a machine, but an application like Excel can only load one version at a time. So there is a problem if you are also using add-ins that require 1.1. By default Excel will still load 1.1 of the runtime if present, and the ExcelDna add-ins will not work. You need some fiddling to ensure that 2.0 is loaded, as explained in the Getting Started.2. Which brings me to your second point. I made peace with the 2.0 requirement because of the much improved interop performance. Performance via ExcelDna is excellent, and compares favourably with pure C/C++ code using the .xll interface. Code from text files is compiled behind the scenes, then works like a normal assembly. I use reflection to hook up the functions initially, but the functions are JIT compiled and the call is through a native (.Net generated) stub that does the native-to-runtime transition. If I recall correctly, the performance overhead per call from Excel to a UDF is in the region of 60 instructions. This is noticable for really simple functions - checking the 'add two numbers' function is a great test as it represents the worst case. So there is some inevitable transition cost. Once on the .Net side of the function call, you should find the performance to be outstanding, also for numerical routines. The JIT compiler generates very good code, and can optimise for the particular processor at runtime. I suspect you need the Intel optimising compilers to do much better. (You should also be careful when comparing with VBA performance, especially for UDFs, which are slow when recalculation is triggered from the sheet, but pretty fast when recalculation is triggered from within VBA.)3. As to the concern about using the library in some institutions, I hope that by making the software open-source with few strings attached, institutions of the type you describe can access at the library source and convince themselves that everything is OK, or even develop their own 'in-house' version, if need be.Mark asks what the advantages are over creating automation add-ins. Well, the Excel version issue is a big one for me, I need to support Excel 2000. But mostly, automation add-ins seem like a distant cousin to Excel - integration into the function wizard is poor, and the whole story is basically undocumented. Versioning, installation and registration of automation add-ins is tricky. Also, I could figure out how to do the dynamic code generation and hook-up through the .xll, whereas for the automation add-ins you need a class and interface to be on disk and registered before it is hooked up into Excel. (Maybe I've just had enough of COM for a while.)ExcelDna uses the well documented, supported and widely used (albeit not often fixed) .xll interface, which will also be updated for Excel 12 and glues into Excel in the right way. The downside: making .xll add-ins in managed code is discouraged and unsupported by Microsoft. However, I think it can be made to work very well.A very useful contribution to the project would be a set of benchmarks of raw C/C++ vs. ExcelDna vs. automation add-ins vs. VBA for some different kinds of calculations and caller scenarios. I think many people here will be surprised by the results.Why do I think it is compelling? C# is much, much nicer than C++, VB.NET is nicer than VBA, and new .Net languages (think F#) and libraries are fun to play with.Regards,Govert

QuoteExcelDna is developed using .NET 2.0, and users have to install the freely available .NET Framework 2.0 runtime. The integration is by an Excel Add-In (.xll) that exposes .NET code to Excel. The user code can be in text-based (.dna) script files (Visual Basic or C#), or compiled .NET libraries (.dll) from any language (e.g. F#). I am trying to undertand what is going on here. It seems that you are using XLL technology as the plumbing to communicate with Excel and DTD (Document Type Definition) for wrapper. Is that correct?I don't want to be a spoil sport but here a number of concerns:. XLL is dead end technology in the sense that it may not be supported in new versions of Excel (e.g. what about Reflection API?). Why use the outdated DTD technology instead of XML Schema?. Do I have access to the full .NET framework? . What's F#? QuoteExcelDna uses the well documented, supported and widely used (albeit not often fixed) .xll interface, which will also be updated for Excel 12 and glues into Excel in the right way. The downside: making .xll add-ins in managed code is discouraged and unsupported by Microsoft. However, I think it can be made to work very wellI had a look at the XLL SDK last week and made a few apps. It is pure C!!!

Hi Cuchulainn,QuoteI am trying to understand what is going on here.I'll try to explain. I have made an .xll that will expose functions from a .Net assembly to Excel. There is no DTD or other schema information involved, but the .Net code can be in an xml format script file, or in a compiled .dll. From your code you have access to the full .Net framework, and any .Net libraries. You can make calls to Excel through either the SDK style interfaces or the COM automation interfaces (with the usual caveats).Using .xll technology for the plumbing is almost certainly the right plan for the foreseeable future:- xlls have been stable and around since Excel 97, and seem to integrate into Excel as the 'native' add-in technology. It is definitely not a dead technology - even though the interface has not changed since Excel97. For Excel 12, some new interfaces and types will be added to allow integration into the expanded grid. You can read more at this blog entry. It seems like xlls will be the only way to make UDFs that take full advantage of the multithreading calculation abilities.QuoteI had a look at the XLL SDK last week and made a few apps. It is pure C!!!Indeed. Creating an xll is not easy, though there are some libraries and toolkits that can help. I want to easily write add-ins using .Net.F# is a .Net variant of the ML programming language, a mixed functional imperative language.Regards,Govert

This project looks interesting. Are you with MS or this another enterprise?I see that ExcelDna uses Reflection in some way but I was wondering how it compares to:Automation AddInsCOM Addins using ATLDoes Dna solve problems that these cannot? BTW once you get the hang of COM Add with ATL life is not so bad.At the moment I supppose there is no native .NET support for Excel and in the short term we must use Xll and/or COM. Is that a realistic assessment?

Hi Cuchulainn,Neither me nor the project is related to Microsoft in any way. You should also note that "Microsoft does not recommend using managed code with [...] an XLL solution" - from Information about designing Office XP add-ins and Office 2003 add-ins by using the .NET Framework.Automation Add-Ins allow you to create user-defined functions in COM components, for Excel 2002 and later. COM Add-Ins follow a general plug-in model for Office applications.With ExcelDna you can create user-defined functions and commands using .Net, and one of my previous posts discuss why it may be preferable to creating Automation Add-Ins. In general, the integration via an .xll is easier and tighter than via Automation Add-Ins.You are correct that there is no native support for .Net in Excel. ExcelDna uses the Xll interface. Visual Studio Tools for Office allows one to develop applications around Excel using .Net, but does not help with user-defined functions. It uses COM for integration.QuoteBTW once you get the hang of COM Add with ATL life is not so bad.You might be pleasantly surprised with how good life can be using .Net. Getting a C++ add-in project started is pretty heavy going, and C++ is certainly not the friendliest programming language. .Net significantly lowers the barriers to entry without compromising on performance or future potential.Regards,Govert

Hi Govert,QuoteYou might be pleasantly surprised with how good life can be using .Net. Yes, it's a great environment, but C++ is still hugely important.QuoteGetting a C++ add-in project started is pretty heavy going But once you have learned it they cannot takes it away from you. I have documented how to do it in Chapter 29 of my C++ book.Quoteand C++ is certainly not the friendliest programming language I Agree, it is a language for experienced developers (or those who wish to go in that direction). C++ is an ISO standard and will be here forever. On the other hand, there are people who take to C++ as a fish takes to water, so it is possible if one does it right. Compare it to learning a foreign natural language, same challenges. QuoteNet significantly lowers the barriers to entry without compromising on performance or future potential I do not have a crystal ball but you might be right. I don't suppose you remember software technologies from the past? To take an example, what about all VB6 code in the world? We must effectively rewrite it in VB.NET or C#. I see this as a huge spillage of human effort.

Govert,Thanks for your answers I will try again with the addin tomorrow. My other addins should work with NET2.0 so should be ok forcing them to use the new runtime. Btw do you know offhand if the Net2.0 runtime has been loaded will dll's compiled against the 1.1 runtime need to be recompiled before they are run?Just thinking about this xll vs COM addin thing. If Microsoft wanted us to use COM for addins (what they seem to call Automation addins) why is the functionality crap?1. You cannot assign your COM worksheetfunctions to custom categories or provide help with the argument functions.2. Why not have an IWorksheetFunctionLibrary interface like the IRTDServer interface that actually provides the same level of integration that you can get via xll's?i.e. if you Implement IWorksheetFunctionLibrary there would be methods like RegisterFunctions() that Excel would call upon loading the addin.I can only assume from the lack of an IWorksheetFunctionLibrary interface or something similar that MS does not want you to write your WorksheetFunctions in COM. Perhaps because of the random recalculation issues?

I have experimented with you interface. I like it a lot. It’s nice not to have to mess around with xlopers.Is it possible to relax the .net v2.0 requirement? I would like to be able to use the interface with .net 1.1. Is it the scripting which uses Reflection the main driver for using 2.0? Are there any other areas of the code that are not 1.1 compliant? My trail copy of vs2005 has finally arrived, so I will be able to experiment this weekend…

I am using Xcode IDE uner OS-X. Is there anybody who knows how to use it or set it up in order to generate or create ATL equivalent projects with the ultimate goal of linking my C++ libraries to Excel in Mac.

What is the status of Excel_DNA and RTD server?Any code, tips or dare I say even Docs?