Combining F# and Excel using Excel DNA
The topic of integrating F# and Microsoft Excel arises frequently, particularly in financial, insurance, statistical and data science work. There are several approaches to this, and in a recent discussion about F# in Finance, the topic of Excel DNA came up. To quote:
Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .xll file requiring no installation or registration.
Excel DNA comes with F# examples, allowing you to integrate F# and Microsoft Excel nicely:
- a wrapper for the ExcelReference type and C API with some cell access features,
- an object handle wrapper, and
- a WPF-based Custom Task Pane for Excel.
and recently Excel DNA added support for the exciting topic of Async and event-streaming Excel UDFs with F#, a crucial technique for many modern financial applications.
Here’s some more information on Excel DNA generally, quoted from the Excel DNA site.
Excel-DNA is developed using .NET, and users have to install the freely available .NET Framework 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 (C#, Visual Basic or F#), or compiled .NET libraries (.dll). Excel-DNA supports both the .NET runtime version 2.0 (which is used by .NET versions 2.0, 3.0 and 3.5) and version 4. Add-ins can target either version of the runtime, and concurrent loading of both runtime versions into an Excel instance is supported.
Excel versions ’97 through 2010 can be targeted with a single add-in. Advanced Excel features are supported, including multi-threaded recalculation (Excel 2007 and later), registration-free RTD servers (Excel 2002 and later) and customized Ribbon interfaces (Excel 2007 and 2010). There is support for integrated Custom Task Panes (Excel 2007 and later), offloading UDF computations to a Windows HPC cluster (Excel 2010 and later), and for the 64-bit versions of Excel 2010 and 2013.
Most managed UDF assemblies developed for Excel Services can be exposed to the Excel client with no modification. (Please contact me if you are interested in this feature.)
Since Excel-DNA uses the Excel C API, porting C/C++ add-in code based on the Excel XLL SDK is very easy. (No more XLOPERs!)
The Excel-DNA Runtime is free for all use, and distributed under a permissive open-source license that also allows commercial use.
There are many tutorials, samples, blog entries, sample projects and other resources available on the Excel DNA site and the Excel DNA CodePlex site.. Corporate users of Excel-DNA may be interested in a more formal support arrangement, see the details on these sites, and the project also accepts direct financial support.
Don, for the Visual F# team
p.s. another approach to F# + Excel integration is FCell. If you know of others, please mention them in the comments below.