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