Natallie Baikevich has posted some very pretty examples of using F# with Excel-DNA. One function uses the R type provider to easily expose a feature from an R package as an UDF in Excel. Have a look at the write-up, and the code on GitHub.
There have been a some recent posts mentioning the asynchronous and reactive programming features in F#. Since Excel-DNA 0.30 added support for creating async and IObservable-based real-time data functions, I'd like to show how these F# features can be nicely exposed to Excel via Excel-DNA.
Excel-DNA 0.30 allows an add-in to expose IObservable sources to Excel as real-time data functions. (Actually Excel-DNA defines an interface called IExcelObservable that matches the semantics of `IObservable<\object> - this is because we still target .NET 2.0 with the core library.)
Asynchronous function can then be implemented as an IObservable that returns a single value before completing. Cancellation (triggered when the user removes a real-time or async formula) is supported via the standard IDisposable mechanism.
Internally, Excel-DNA implements a thread-safe RTD server and registers the IObservable as an RTD topic. So some aspects of the IObservable support are subject to Excel's RTD feature works, for example the RTD throttle interval (by default 2 seconds) will also apply to IObservable functions.
The following functions in the ExcelDna.Integration.ExcelAsyncUtil helper class are relevant:
ExcelAsyncUtil.Initialize() - this should be called in a macro context before any of the other features are used, typically from the AutoOpen() handler.
ExcelAsyncUtil.Observe(...) - registers an IExcelObservable as a real-time data function with Excel. Subsequent OnNext() calls will raise updates via RTD.
ExcelAsyncUtil.Run(...) - a helper method that runs a function asynchronously on a .NET threadpool thread.
In addition, we'll use
ExcelObservableSource - a delegate type for functions that return an IExcelObservable.IExcelObservable to any implementation of IObservable<T>, allowing the Rx libraries to be used in Excel.To initialize the async support in Excel-DNA, we need some code like the following:
namespace FsAsync
open System
open System.Threading
open System.Net
open Microsoft.FSharp.Control.WebExtensions
open ExcelDna.Integration
/// This class implements the IExcelAddin which allows us to initialize the ExcelAsyncUtil support.
/// It must not be a nested class (e.g. defined as a type inside a module) but a top-level class (inside a namespace)
type FsAsyncAddIn () =
interface IExcelAddIn with
member this.AutoOpen () =
ExcelAsyncUtil.Initialize ()
member this.AutoClose () = ExcelAsyncUtil.Uninitialize ()
// define a regular Excel UDF just to show that the add-in works
[<ExcelFunction(Description="A test function from F#")>]
static member fsaAddThem (x:double) (y:double) = x + y
F# supports an asynchronous programming model via "async computation expressions". The result of an async computation expression is a value of type Async<T>, which we need to convert to an IExcelObservable. We use a standard CancellationTokenSource hooked up to the IDisposable to enable cancellation.
module FsAsyncUtil =
/// A helper to pass an F# Async computation to Excel-DNA
let excelRunAsync functionName parameters async =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// make something like CancellationDisposable
let cts = new CancellationTokenSource ()
let disp = { new IDisposable with member __.Dispose () = cts.Cancel () }
// Start the async computation on this thread
Async.StartWithContinuations
( async,
( fun result ->
observer.OnNext(result)
observer.OnCompleted () ),
( fun ex -> observer.OnError ex ),
( fun ex ->
observer.OnCompleted () ),
cts.Token
)
// return the disposable
disp
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
Another neat feature of F# is that events are first-class types that implement IObservable. This means any F# event can serve as a real-time data source in an Excel formula. To bridge the F# events to the IExcelObservable interface is really easy, we just have the following helper:
/// A helper to pass an F# IObservable to Excel-DNA
let excelObserve functionName parameters observable =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// Subscribe to the F# observable
Observable.subscribe (fun value -> observer.OnNext (value)) observable
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
Given the above helpers, we can now explore a few ways to implement async and real-time streaming functions. As examples:
Here is a plain synchronous function to download a url into a string:
let downloadString url =
try
let uri = new System.Uri(url)
let webClient = new WebClient()
let html = webClient.DownloadString(uri)
html
with
| ex -> "!!!ERROR: " + ex.Message
downloadString on a ThreadPool threadlet downloadStringAsyncRunTP1 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP1", url, (fun () -> downloadString url :> obj))
Create an F# asynchronous computation for the download (this functions is not exported to Excel)
let downloadStringAsyncImpl url = async {
try
// In here we could check for cancellation using
// let! ct = Async.CancellationToken
// if ct.IsCancellationRequested then ...
let uri = new System.Uri(url)
let webClient = new WebClient()
let! html = webClient.AsyncDownloadString(uri)
return html
with
| ex -> return "!!!ERROR: " + ex.Message
}
ThreadPool thread because that's what ExcelAsyncUtil.Run does. Blocking calls will block a ThreadPool thread, eventually limiting the concurrency of the async callslet downloadStringAsyncTP2 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP2", url, (fun () -> Async.RunSynchronously (downloadStringAsyncImpl url) :> obj))
ThreadPool threads, and allows cancellationlet downloadStringAsync url =
FsAsyncUtil.excelRunAsync "downloadStringAsync" url (downloadStringAsyncImpl url)
Helper that will create a timer that ticks at timerInterval for timerDuration, and is then done. Also not exported to Excel (incompatible signature). Notice that from F#, the timer.Elapsed event of the BCL Timer class implements IObservable, so can be used directly with the transformations in the F# Observable module.
let createTimer timerInterval timerDuration =
// setup a timer
let timer = new System.Timers.Timer(float timerInterval)
timer.AutoReset <- true
// return an async task for stopping it after the duration
let timerStop = async {
timer.Start()
do! Async.Sleep timerDuration
timer.Stop()
}
Async.Start timerStop
// Make sure that the type we actually observe in the event is supported by Excel
// by converting the events to timestamps
timer.Elapsed |> Observable.map (fun elapsed -> DateTime.Now)
=startTimer(5000, 60000) will show a clock that ticks every 5 seconds for a minute.let startTimer timerInterval timerDuration =
FsAsyncUtil.excelObserve "startTimer" [|float timerInterval; float timerDuration|] (createTimer timerInterval timerDuration)
A complete .dna script file with the above code can be found in the Excel-DNA distribution, under Distribution\Samples\Async\FsAsync.dna.
Alternatively, the following steps would build an add-in in Visual Studio:
Install-Package Excel-DNA from the NuGet console).C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE..xll file in the output as the Command line arguments, e.g. C:\MyProjects\TestDnaFs\bin\Debug\TestDnaFs-addin.xll.Library1.fs, compile and run:namespace FsAsync
open System
open System.Threading
open System.Net
open Microsoft.FSharp.Control.WebExtensions
open ExcelDna.Integration
/// This class implements the IExcelAddin which allows us to initialize the ExcelAsyncUtil support.
/// It must not be a nested class (e.g. defined as a type inside a module) but a top-level class (inside a namespace)
type FsAsyncAddIn () =
interface IExcelAddIn with
member this.AutoOpen () =
ExcelAsyncUtil.Initialize ()
member this.AutoClose () = ExcelAsyncUtil.Uninitialize ()
// a regular Excel UDF just to show that the add-in works
static member fsaAddThem (x:double) (y:double) = x + y
/// Some utility functions for connecting Excel-DNA async with F#
module FsAsyncUtil =
/// A helper to pass an F# Async computation to Excel-DNA
let excelRunAsync functionName parameters async =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// make something like CancellationDisposable
let cts = new CancellationTokenSource ()
let disp = { new IDisposable with member __.Dispose () = cts.Cancel () }
// Start the async computation on this thread
Async.StartWithContinuations
( async,
( fun result ->
observer.OnNext(result)
observer.OnCompleted () ),
( fun ex -> observer.OnError ex ),
( fun ex ->
observer.OnCompleted () ),
cts.Token
)
// return the disposable
disp
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
/// A helper to pass an F# IObservable to Excel-DNA
let excelObserve functionName parameters observable =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// Subscribe to the F# observable
Observable.subscribe (fun value -> observer.OnNext (value)) observable
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
// Some test functions
module TestFunctions =
/// Plain synchronous download function
/// can be called from Excel
let downloadString url =
try
let uri = new System.Uri(url)
let webClient = new WebClient()
let html = webClient.DownloadString(uri)
html
with
| ex -> "!!!ERROR: " + ex.Message
/// Uses Excel-DNA async to run download on a ThreadPool thread
let downloadStringAsyncTP1 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP1", url, (fun () -> downloadString url :> obj))
/// Create an F# asynchronous computation for the download
/// Not exported to Excel
let downloadStringAsyncImpl url = async {
try
// In here we could check for cancellation using
// let! ct = Async.CancellationToken
// if ct.IsCancellationRequested then ...
let uri = new System.Uri(url)
let webClient = new WebClient()
let! html = webClient.AsyncDownloadString(uri)
return html
with
| ex -> return "!!!ERROR: " + ex.Message
}
/// This function runs the async computation synchronously on a ThreadPool thread
/// because that's what ExcelAsyncUtil.Run does
/// Blocking calls will block a ThreadPool thread, eventually limiting the concurrency of the async calls
let downloadStringAsyncTP2 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP2", url, (fun () -> Async.RunSynchronously (downloadStringAsyncImpl url) :> obj))
/// This runs the async computation using true F# async
/// Should not block ThreadPool threads, and allows cancellation
let downloadStringAsync url =
FsAsyncUtil.excelRunAsync "downloadStringAsync" url (downloadStringAsyncImpl url)
// Helper that will create a timer that ticks at timerInterval for timerDuration, then stops
// Not exported to Excel (incompatible type)
let createTimer timerInterval timerDuration =
// setup a timer
let timer = new System.Timers.Timer(float timerInterval)
timer.AutoReset Observable.map (fun elapsed -> DateTime.Now)
// Excel function to start the timer - using the fact that F# events implement IObservable
let startTimer timerInterval timerDuration =
FsAsyncUtil.excelObserve "startTimer" [|float timerInterval; float timerDuration|] (createTimer timerInterval timerDuration)
The best place to ask any questions related to Excel-DNA is the Excel-DNA Google group. Any feedback from F# users trying out Excel-DNA or the features discussed here will be very welcome. I can also be contacted directly at govert@dnakode.com.