Serialization / Deserialization issue while using ExcelDNA

12 views
Skip to first unread message

jant...@gmail.com

unread,
Jul 29, 2021, 9:44:52 AMJul 29
to Excel-DNA
Hi

I am stuck with an unexpected issue when using BinaryFormatter inside an ExcelDNA function.

Running the following script in the F# Interactive window works fine:

> M1.store "abc" "def" """C:\temp\rec1.bin""" ;;
val it : string = "12:02:29"

> M1.load """C:\temp\rec1.bin""" ;;
val it : string option = Some "abc - def"


However when I run the same inside Excel, I run into problems when loading (deserializing) the object from file. 

=rec1_store("abc","def","C:/temp/rec1.bin") works fine and output a time.

but =rec1_load("C:/temp/rec1.bin") returns "-failed-"

Buy placing a break-point on the line 36 (let ty = o.GetType()) and inspect ty, the type of the deserialized object) I can see that the program *apparently* returns a Rec1 :
ty: {Name = "Rec1" FullName = "testBINFORMAT2.Rec1"}  Type: System.Type {System.RuntimeType}
but the other tests show that the object is not recognized as a genuine Rec1 object.

I am not sure why this happens within an ExcelDNA registered function, any help would be greatly welcome.
Thank you.
Jan

PS : I am aware that the use of BinaryFormatter is discouraged for security reasons, but this is for a private project and its functionality is very helpful (well, if I can get it to work).


The F# program:

namespace testBINFORMAT2

open System
open System.IO

type Rec1 = { foo: string; bar: string }

module M0 = 
    open System.Runtime.Serialization.Formatters.Binary
    let ioStoreBin<'a> (fpath: string) (o: 'a) : string =
        use stream = new FileStream(fpath, FileMode.Create)
        (new BinaryFormatter()).Serialize(stream, o)
        DateTime.Now.ToString("HH:mm:ss")

    let ioLoadBin (fpath: string) : obj =
        use stream = new FileStream(fpath, FileMode.Open)
        let o = (new BinaryFormatter()).Deserialize(stream)
        o

module M1 =
    let store (fooArg: string) (barArg: string) (fpath: string) : string =
        let rec1 : Rec1 = { foo = fooArg; bar = barArg }
        M0.ioStoreBin<Rec1> fpath rec1

    let load (fpath: string) : string option =
        match M0.ioLoadBin fpath with
        | :? Rec1 as rec1 -> 
            // BRANCH WHEN USED WITHIN F# INTERACTIVE WINDOW
            sprintf "%s - %s" rec1.foo rec1.bar
            |> Some
        | o -> 
            // BRANCH WHEN USED WITHIN EXCEL WITH EXCEL DNA
            // sample type for comparison
            let tySample = let rec1 = { foo = "F"; bar = "B" } in rec1.GetType()
            // deserialized object type
            let ty = o.GetType()

            // few tests
            let test1 = ty = tySample // returns false
            let test2 = ty.Equals(tySample) // returns false
            let test3 = ty.IsEquivalentTo(tySample) // returns false
            None

module M2 = 
    open ExcelDna.Integration

    [<ExcelFunction(Category="Test", Description="Creates and stores a Rec1 instance.")>]
    let rec1_store
        ([<ExcelArgument(Description= "Foo.")>] fooArg: string)
        ([<ExcelArgument(Description= "Bar")>] barArg: string)
        ([<ExcelArgument(Description= "File path.")>] fpath: string)
        : obj =

        M1.store fooArg barArg fpath
        |> box

    [<ExcelFunction(Category="Test", Description="Outputs a string representation of a Rec1 instance.")>]
    let rec1_load
        ([<ExcelArgument(Description= "File path.")>] fpath: string)
        : obj =

        // result
        match M1.load fpath with
        | None -> box "-failed-"
        | Some sRec1 -> box sRec1




Govert van Drimmelen

unread,
Jul 29, 2021, 5:47:36 PMJul 29
to Excel-DNA
--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
You can add Excel-DNA sponsorship to encourage support and future development, with easy billing through a corporate GitHub account.
-------------------------------------------------- 

Hi Jan,

This kind of type identity problem happens when an assembly is loaded into the AppDomain multiple times, in different Load Contexts.
In an Excel-DNA environment, this happens most easily if you are packing the assembly into the .xll file (with the default <ExternalLibrary ... Pack="true" /> entry or similar <Reference /> entry in the .dna file) and then also have the .dll file in the directory where the add-in is running (or in the GAC). I'm not sure what API calls the BinaryFormatter library uses to find and load the types.

In your case you are probably just running the unpacked add-in in the debugger. In this case you are tripped up by my attempt long ago to make the debug load situation similar to the runtime situation. To do this I added an option to "LoadFromBytes" the assembly, even when the assembly is present and not packed. But the "LoadFromBytes" behaviour is loaded the assebmly once, and now the BinaryFormatter is loading it again, causing the type identity issue.

The fix for you is to disable my "improvement" by editing your .dna file:

  <ExternalLibrary Path="xxxxxxx.dll" ExplicitExports="false" LoadFromBytes="false" Pack="true" IncludePdb="false" />

The you have to explicitly rebuild the project to make sure the change is picked up, and your deserialization should work right.
(Somehow changes to the .dna file were not being picked up by VS to trigger a rebuild in my F# project ...?)

If you want to test the packed add-in, remember you must make sure the actual .dll is not sitting next to it - I just copy the -packed.xll it to a different directory.

Finally, for the packed add-in, remember to add the FSharp library (and others you use) into the .dna file:

My test .dna file looked like this:

<DnaLibrary Name="FsDna Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2018/05/dnalibrary">
  <ExternalLibrary Path="FsDna.dll" ExplicitExports="false" LoadFromBytes="false" Pack="true" IncludePdb="false" />
  <Reference Path="FSharp.Core.dll" Pack="true" />
</DnaLibrary>

-Govert


J Anthelme

unread,
Jul 30, 2021, 4:38:27 AMJul 30
to exce...@googlegroups.com
Thanks Govert,

This was very helpful and using LoadFromBytes="false" in the .dna solved the issue. (Well it worked for the non-packed xll at least, I still have an unrelated issue with the packed xll, for which I'll try to isolate the issue and maybe send a new post later).

I also confirm that, as yourself, changes to my .dna files are never picked-up by VS to trigger the rebuild (despite the fact that <None Include="MyProject-Addin.dna" /> line of code is included to the fsproj file). Not entirely sure why that is. 

Thanks for your help
Jan


--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/GQ8yk8BG12M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/cc32fc86-d0b1-44f4-b759-d31a39767090n%40googlegroups.com.

Reply all
Reply to author
Forward
0 new messages