Re: F# object handler

[Govert van Drimmelen]

Hi Dave,

Thanks for posting this! I think there are more F# & Excel-DNA users
than you'd expect. F# seems to be fairly popular in the financial
world, where Excel happens to find some use too ;-)

An issue that might need a bit more refinement in future is how to
clean up resources that you have behind the object handles. In
particular if you delete the cells that 'created' the object, how do
we clean up the underlying object. One option is to have a periodic
garbage collector that checks whether references to the objects are
still present.

I think a better way might be to use the RTD features to implement
this. With RTD, Excel will reliably notify your RTD server if a topic
is 'disconnected' - when there is no longer a call to the RTD server
for that topic. This might allow us to make a reliable object handler
that keep track of formulae being changed, moved or deleted.

Regards,
Govert


On Sep 30, 11:58 pm, DC <da...@minnesotacarlson.com> wrote:
> I know that there have been previous posts that discussed on referencing objects from Excel, but I didn't see any that:
>
> 1) used F#
>
> 2) dealt with the lifetime of the objects
>
> I have not stressed tested this on larger objects, but it does seem to be working as intended.  It does require a full recalc on initial workbook open to create instances of the objects in the dictionary.
>
> I don't think there are probably a lot of people using F#/ExcelDna, but thought I would post since it might be useful if there are.
>
> - Dave
>
> I based this on the discussions:
>
> https://groups.google.com/forum/?fromgroups=#!topic/exceldna/zGPUrQ5wPqs
>
> http://exceldna.codeplex.com/discussions/261919
>
> ___________________________________________________________________________ ______
>
> namespace ExcelDna.ObjectCacheExample
>
> open ExcelDna.Integration
>
> open System.Collections.Generic
>
>  /// object cache to create objects in Excel
>
> module XLCache =
>
>    /// object cache for Excel
>
>   let objects = new Dictionary<string,obj>()
>
>    /// lookup based on object handle
>
>   let lookup handle =
>
>      match objects.ContainsKey(handle) with
>
>      |true  -> Some(objects.[handle])
>
>      |false -> None
>
>   /// register new object based on Excel caller cell
>
>   let register (o:obj) (caller:obj) =
>
>     let ref = caller :?> ExcelReference              // reference calling cell
>
>     let oldHandle = ref.GetValue().ToString()        // get current handle in calling cell
>
>     if objects.ContainsKey(oldHandle) then           // remove previous handle if exists
>
>       objects.Remove(oldHandle) |> ignore
>
>     let handle = "N" + o.GetHashCode().ToString()    // and register new object
>
>     objects.Add(handle, o) |> ignore
>
>     handle                                           // return the objects handle
>
>  module ExcelFunctions =
>
>    // class to use for example
>
>   type MySeq (n:float) =
>
>     let _seq = {1 .. int n}
>
>     member x.Sum() = _seq |> Seq.sum
>
>     member x.Average() = _seq |> Seq.map float |> Seq.average
>
>     member x.Multiples(m) = _seq |> Seq.filter(fun x -> x % m = 0) |> Seq.length
>
>    [<ExcelFunction(IsMacroType=true)>]
>
>   /// creates object and returns ojbects handle to Excel
>
>   let createSequence(n) =
>
>     // create object
>
>     let seq = MySeq(n)
>
>     // cache object and return handle to Excel
>
>     XlCall.Excel(XlCall.xlfCaller) |> XLCache.register seq
>
>    [<ExcelFunction>]
>
>   /// sum of the sequence from object handle
>
>   let getSum(handle) =
>
>     match XLCache.lookup(handle) with
>
>     |Some(o) ->
>
>         o :?> MySeq
>
>         |> (fun o -> o.Sum())
>
>     |None -> failwith "object not found"
>
>    [<ExcelFunction>]
>
>   /// average of the sequence from object handle
>
>   let getAverage(handle) =
>
>     match XLCache.lookup(handle) with
>
>     |Some(o) ->
>
>         o :?> MySeq
>
>         |> (fun o -> o.Average())
>
>     |None -> failwith "object not found"
>
>    [<ExcelFunction>]
>
>   /// returns multiples of m that are in the sequence
>
>   let getMultiples(handle, m) =
>
>     match XLCache.lookup(handle) with
>
>     |Some(o) ->
>
>         o :?> MySeq
>
>         |> (fun o -> o.Multiples(m))
>
>     |None -> failwith "object not found"

[Naju Mancheril]

What's the reason for using GetHashCode in handle creation?

Why not use some unique integer or long. You can use a counter and Interlocked.Increment to get a new value in register.

If you want more human readable support, you can also prepend the type name. Like "Dog.3" or "[Dog 3]".

[Naju Mancheril]

Everything else looks really good! [at least from what I understand, F# is not my first language]

Lots of people paste code in their posts, so it's no big deal. You can also attach source files to their posts.

[Govert van Drimmelen]

Hi Dave,

That looks pretty cool!

You might now need to look at this Excel bug report:
http://social.msdn.microsoft.com/Forums/is/exceldev/thread/24353308-b4ba-48dc-8fa3-a27fcfa6aaac

It suggests there might be an issue when you are on manual
recalculation, and deleting a row or column, and have RTD formulae on
the sheet (which you now have behind the scenes). I'm not sure whether
it impacts on this RTD-based handle manager, or the way you'd use it -
any comments you might have would be helpful.

Regards,
Govert


On Oct 8, 6:02 am, DC <da...@minnesotacarlson.com> wrote:
> ngm,
>
> Thanks for the feedback.  I took your advice and added a parameter
> to pass a human readable "tag" to the register function.  It still should
> stand out as a different result than a typical function while still being
> a little more useful than "N2342323".  Since Govert's new push this
> week adds the "ExcelRtdSever" class I was able to remove a lot of
>  what I had in my RTD class and also added an example below that
> creates an object handle asynchronously.  I find often that when there
> is something that takes a while to run, that I often need to call multiple
> functions using the same result set ... hence the async/handle desire.
> I would think that this would be easy enough to extend the method to
> cache SQL or web service results.
>
> Hopefully someone knows enough of what is going on behind the
> scenes to tell me if there is some "gotcha" that I am missing.  Any
> feedback on thread/memory issues that I might be overlooking would
> be great. I am by no means a programmer or tester, but when I tried
> this on some larger/slower algorithms and while holding down Ctrl+Alt+F9
> I saw my CPU jump to 100%, but no jump on threads or RAM.
>
> *Revised Object Handler*
>
> namespace ExcelDna.FsObjectHandleropen Systemopen System.Textopen System.Collections.Genericopen ExcelDna.Integrationopen ExcelDna.Integration.Rtd
>  type public ExcelInterface() =
>   interface IExcelAddIn with
>     member x.AutoOpen() = ExcelAsyncUtil.Initialize()
>     member x.AutoClose() = ExcelAsyncUtil.Uninitialize()
>  /// Excel object handler / cache / async wrappermodule XlCache =
>   let private _objects = new Dictionary<string,obj>()
>   let private _tags = new Dictionary<string, int>()
>   let lookup handle =
>       match _objects.ContainsKey(handle) with
>       |true -> _objects.[handle]
>       |false -> failwith "object not found"
>   let register tag (o:obj) =
>       let counter =
>           match _tags.ContainsKey(tag) with
>           |true  -> _tags.[tag] + 1
>           |false -> 1
>       _tags.[tag] <- counter
>       let handle =
>           new StringBuilder()
>           |> fun s -> s.AppendFormat("[{0}.{1}]", tag, string counter).ToString()
>       _objects.[handle] <- o
>       handle
>   let unregister handle =
>       if _objects.ContainsKey(handle) then
>           _objects.Remove(handle) |> ignore
>   let asyncRun myName myFunction myInputs =
>       ExcelAsyncUtil.Run(
>           myName,
>           myInputs,
>           fun () -> (myFunction myInputs) |> box)
>       |> unbox
>  /// Excel RTD server to handle the registering/unregistering of object handlestype public StaticRTD() =
>   inherit ExcelRtdServer()
>   let _topics = new Dictionary<ExcelRtdServer.Topic, string>()
>   override x.ConnectData(topic:ExcelRtdServer.Topic, topicInfo:IList<string>, newValues:bool byref) =
>           let name = topicInfo.Item(0)
>           _topics.[topic] <- name
>           name |> box
>   override x.DisconnectData(topic:ExcelRtdServer.Topic) =
>              _topics.[topic] |> XlCache.unregister
>              _topics.Remove(topic) |> ignore
>
> *Revised Example*
>
> namespace ExcelDna.FsObjectHandleropen ExcelDna.Integrationopen ExcelDna.Integration.Rtdopen System.Threading
>  module FsObjectHandlerExample =
>
>  // class example
>
>   // class we want to create an object handle for
>   type Person(firstName:string, lastName:string) =
>     let _first = firstName
>     let _last = lastName
>     member x.FirstName with get() = _first
>     member x.LastName with get() = _last
>
>   // function to create object and pass it's handle to excel
>   [<ExcelFunction(Name="Person.create")>]
>   let createPerson (firstName, lastName) =
>     new Person(firstName, lastName)
>     |> XlCache.register "Person"
>     |> fun handle -> XlCall.RTD("ExcelDna.FsObjectHandler.StaticRTD", null, handle)
>
>   // function that uses the object handle
>   [<ExcelFunction(Name="Person.getFirstName")>]
>   let getPersonFirstName personHandle =
>      personHandle
>      |> XlCache.lookup :?> Person
>      |> fun person -> person.FirstName
>  // slow function example
>
>   // slow running function
>   let myArray (n,a) =
>       Thread.Sleep 2000
>       [| 0 .. n |]
>       |> Array.map ((+) a)
>
>   // creates object asynchronously and returns handle when done
>   [<ExcelFunction(Name="MyArray.create")>]
>   let createMyArray (n:int,a:int) =
>     // need to restate function to accept object array input
>     let f (p:obj[]) = myArray (p.[0] :?> int, p.[1] :?> int)
>     [| box n; box a |]
>     |> XlCache.asyncRun "MyArray" f
>     |> fun result ->
>           if result = box ExcelError.ExcelErrorNA then
>               "hold on"
>           else
>               result
>               |> XlCache.register "MyArray"
>               |> fun handle ->
>                   XlCall.RTD("ExcelDna.FsObjectHandler.StaticRTD", null, handle)
>                   |> string
>
>   [<ExcelFunction(Name="MyArray.getSum")>]
>   let getMyArraySum myArrayHandle =
>      myArrayHandle
>      |> XlCache.lookup :?> int[]
>      |> Array.sum