17 people like it.

Transform expressions into Excel formulae

Sometimes it is extremely useful to check some calculations with Excel. The snippet shows how F# expressions can be transformed into Excel formulae. The data is exported together with the formulae, e.g. a, b and sum function as input sets A1's value to a, B1's to b and C1's formula to "=$A$1+$B$1"

Transform expressions into Excel formulae

  1: 
  2: 
  3: 
  4: 
  5: 
  6: 
  7: 
  8: 
  9: 
 10: 
 11: 
 12: 
 13: 
 14: 
 15: 
 16: 
 17: 
 18: 
 19: 
 20: 
 21: 
 22: 
 23: 
 24: 
 25: 
 26: 
 27: 
 28: 
 29: 
 30: 
 31: 
 32: 
 33: 
 34: 
 35: 
 36: 
 37: 
 38: 
 39: 
 40: 
 41: 
 42: 
 43: 
 44: 
 45: 
 46: 
 47: 
 48: 
 49: 
 50: 
 51: 
 52: 
 53: 
 54: 
 55: 
 56: 
 57: 
 58: 
 59: 
 60: 
 61: 
 62: 
 63: 
 64: 
 65: 
 66: 
 67: 
 68: 
 69: 
 70: 
 71: 
 72: 
 73: 
 74: 
 75: 
 76: 
 77: 
 78: 
 79: 
 80: 
 81: 
 82: 
 83: 
 84: 
 85: 
 86: 
 87: 
 88: 
 89: 
 90: 
 91: 
 92: 
 93: 
 94: 
 95: 
 96: 
 97: 
 98: 
 99: 
100: 
101: 
102: 
103: 
104: 
105: 
106: 
107: 
108: 
109: 
110: 
111: 
112: 
113: 
114: 
115: 
116: 
117: 
118: 
119: 
120: 
121: 
122: 
123: 
124: 
125: 
126: 
127: 
128: 
129: 
130: 
131: 
132: 
133: 
134: 
135: 
136: 
137: 
138: 
139: 
140: 
141: 
142: 
143: 
144: 
145: 
146: 
147: 
148: 
149: 
150: 
151: 
152: 
153: 
154: 
155: 
156: 
157: 
158: 
159: 
160: 
161: 
162: 
163: 
164: 
165: 
166: 
167: 
168: 
169: 
170: 
171: 
172: 
173: 
174: 
175: 
176: 
177: 
178: 
179: 
180: 
181: 
182: 
183: 
184: 
185: 
186: 
187: 
open Mono.Reflection

/// Get reflected definition from a closure obj with Mono.Reflection
let defForObj() =
    let cache = System.Collections.Generic.Dictionary<_, _>()
    fun obj ->
        let ty = obj.GetType()
        if cache.ContainsKey ty then cache.[ty]
        else
            let methods = ty.GetMethods()

            // find closure's invoke
            let invoke = 
                methods |> Array.tryFind (fun mi -> mi.DeclaringType = ty && mi.Name = "Invoke")
            let def = 
                match invoke with
                | Some mi -> 
                    // invoke methods does have a body, so we don't check it for null 
                    // (but e.g. GetType doesn't)
                    mi.GetInstructions() 
                    |> Seq.tryFind (fun instr -> instr.OpCode = Emit.OpCodes.Call)
                    |> Option.bind (fun instr -> 
                        instr.Operand :?> System.Reflection.MethodInfo // original function info
                        |> Expr.TryGetReflectedDefinition)
                | _ -> None
            cache.Add(ty, def)
            def

/// Get reflected definition for method info 
let defForMethodInfo() =(...)

/// Simplified SpecificCall
let inline (|Func|_|) expr =(...)

/// Info for the transformation steps
type Info = {
    Scope: Map<string, Expr>
    Prior: int
    RightOperand: bool
}

/// Generate a formula pattern for an expression
/// column number - index of var in the env; '#' is a temp placeholder for a row number
let generatePattern expr env =     
    Defaults
    
    // check if we need to add the parens
    let inline addParens (info: Info) currPrior = 
        info.Prior < currPrior || (info.Prior = currPrior && info.RightOperand)

    // let x = ...; let f x y = ...; f 1 x
    // inside f x is Value(1), y is outer x value 
    let updateScope names parms scope =
        Seq.zip names parms
        |> Seq.fold (fun sc -> function
            | name, Var var -> replace name sc.[var.Name] sc
            | name, param -> replace name param sc) scope

    // print binary ops: (x op y)
    let rec inline printBinaryOp op x y curr (info: Info) = 
        let left = transform { info with Prior = curr; RightOperand = false } x
        let right = transform { info with Prior = curr; RightOperand = true } y
        let res = left + op + right in if addParens info curr then "(" + res +  ")" else res

    // print functions: name(arg1, arg2, ...)
    and inline printFunc name args info  =
        let argValues: string[] = List.map (transform !!info) args |> List.toArray
        sprintf "%s(%s)" name (String.Join (", ", argValues))

    // apply function with given parameters
    and applyFunc (var: Var, parms) info =(...)

    // transform an expression into pattern
    and transform (info: Info) = function
        | Func <@@ (+) @@> [x; y] -> printBinaryOp "+" x y 3 info
        | Func <@@ (-) @@> [x; y] -> printBinaryOp "-" x y 3 info
        | Func <@@ (*) @@> [x; y] -> printBinaryOp "*" x y 2 info
        | Func <@@ (/) @@> [x; y] -> printBinaryOp "/" x y 2 info
        | Func <@@ ( ** ) @@> [x; y] -> printBinaryOp "^" x y 1 info 
        | Func <@@ (~-) @@> [x] -> "-" + transform { info with Prior = 0 } x
        | Func <@@ (~+) @@> [x] -> transform { info with Prior = 0 } x
        | Func <@@ decimal @@> [x] 
        | Func <@@ double @@> [x] 
        | Func <@@ float @@> [x] -> string (transform { info with Prior = 0 } x)

        | Func <@@ mduration @@> args -> printFunc "MDURATION" args info
        | Func <@@ accrint @@> args -> printFunc "ACCRINT" args info
        | Lambdas (_, e) -> transform !!info e

        // let a, b, ... = 1, 2, ...
        // Note: nested tuples and tuples as return values are not supported
        | Let (_, NewTuple vs, e) -> 
            let res, newScope = Update the scope
            transform { info with Scope = newScope } res

        | Let (var, value, e) -> 
            transform { info with Scope = replace var.Name value info.Scope } e

        | Value (v, _) -> string v
        // try to replace a varname with its column index
        | Var var -> 
            match Map.tryFind var.Name info.Scope with
            | Some replacement -> transform info replacement
            | _ -> var.Name
        // args.[i] means reference to the (i+1)th column
        | Call(None, mi, _::[Value (i, _)]) when mi.DeclaringType.Name = "IntrinsicFunctions" 
                                              && mi.Name = "GetArray" -> 
            let ind = unbox i in "R#C" + string (ind + 1)
        
        // replace MakeDecimal with a value
        | Call(None, mi, Value (v, _)::_) when mi.DeclaringType.Name = "IntrinsicFunctions" 
                                            && mi.Name = "MakeDecimal"  -> 
            string v
        // try to inline a method call
        | Call(None, mi, ps) ->
            let names = mi.GetParameters() |> Array.map (fun p -> p.Name)
            let newScope = updateScope names ps info.Scope
            
            match tryGetReflectedDef mi with
            | Some impl ->
                let rec getCall e =
                    match e with
                    | Lambda(_, Lambda (_, e)) -> getCall e // skip parameters
                    | call -> call
                transform { info with Scope = newScope } (getCall impl)
                
            | _ -> failwith (sprintf "Can't get reflected definition for %s" mi.Name)
        // DateTime ctor -> Excel DATE function: DATE(year, month, day)
        | NewObject(ci, Value(y,_)::Value(m,_)::Value(d,_)::_) 
                                                when ci.DeclaringType.Name="DateTime"-> 
            sprintf "DATE(%A, %A, %A)" y m d    

        | Application ((Application args) as f, value) -> 
            collect params for the chain of function applications
            applyFunc (collectParams [value] f) info

        | Application (Var f, value) ->  applyFunc (f, [value]) info

        | LetRecursive _ -> failwith "Recursive functions are not supported"
        | expr -> failwith (sprintf "Unknown expression type: %A" expr)

    "=" + transform { Prior = defaultPrior; Scope = scope; RightOperand = false } expr

(Patterns example)

module Test =
    [<ReflectedDefinition>]
    [<MethodImpl(MethodImplOptions.NoInlining)>]
    let sum (a: decimal) b = a + b
  
    [<ReflectedDefinition>]
    [<MethodImpl(MethodImplOptions.NoInlining)>]
    let mdurationMonth m c y f basis = 
        (mduration (DateTime(2012, 1, 7)) m c y f basis) * 12M

    let run export = 
        let getReflectedDefinition = defForObj()

        let data: obj [][] = (Some data)

        // the vars with such names will be replaced with R{rownum}C{var index + 1}
        let dataColumns = ["a"; "b"; "s"; "m"; "c"; "y"; "f"; "basis"]
        let funcs: obj list = [sum; mdurationMonth]

        // transform quotation into a pattern & split by the rownum replacement '#'
        let unquote = 
            Option.bind (fun expr -> 
                try 
                    Some ((generatePattern expr dataColumns).Split [|'#'|])
                with _ -> None)

        // reflected definitions -> formulae
        let formulae = funcs
                        |> List.map (getReflectedDefinition >> unquote)
                        |> List.filter Option.isSome
                        |> List.map Option.get
        
        data |> Array.iteri (export formulae)

/// Export data with given pattern
let export exportValue exportFunc (patterns: string[] list) row (items: _[]) =(...)

// Standard output
Test.run (export 
            (fun (row, fst) col item -> printfn "Cells.[%d, %d]<-%A" row (col+fst) item)
            (fun (row, fst) col formula -> 
                printfn "Cells.[%d, %d].Formula <- \"%s\"" row (col+fst) formula))

Simple export to Excel

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
let app = new ApplicationClass()
let workbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
let worksheet = workbook.Worksheets.[1] :?> Worksheet

// fill the cells
Test.run (export 
            (fun (row,fst) col item -> worksheet.Cells.[row, col+fst] <- item)
            (fun (row,fst) col formula -> 
                (worksheet.Cells.[row, col+fst] :?> Range).Formula <- formula))

app.ReferenceStyle <- XlReferenceStyle.xlA1

(Close workbook and release objects)
Multiple items
namespace System.Reflection

--------------------
namespace Microsoft.FSharp.Reflection
val defForObj : unit -> ('a -> Expr option)

Full name: Script.defForObj


 Get reflected definition from a closure obj with Mono.Reflection
val cache : Collections.Generic.Dictionary<Type,Expr option>
namespace System
namespace System.Collections
namespace System.Collections.Generic
Multiple items
type Dictionary<'TKey,'TValue> =
  new : unit -> Dictionary<'TKey, 'TValue> + 5 overloads
  member Add : key:'TKey * value:'TValue -> unit
  member Clear : unit -> unit
  member Comparer : IEqualityComparer<'TKey>
  member ContainsKey : key:'TKey -> bool
  member ContainsValue : value:'TValue -> bool
  member Count : int
  member GetEnumerator : unit -> Enumerator<'TKey, 'TValue>
  member GetObjectData : info:SerializationInfo * context:StreamingContext -> unit
  member Item : 'TKey -> 'TValue with get, set
  ...
  nested type Enumerator
  nested type KeyCollection
  nested type ValueCollection

Full name: System.Collections.Generic.Dictionary<_,_>

--------------------
Collections.Generic.Dictionary() : unit
Collections.Generic.Dictionary(capacity: int) : unit
Collections.Generic.Dictionary(comparer: Collections.Generic.IEqualityComparer<'TKey>) : unit
Collections.Generic.Dictionary(dictionary: Collections.Generic.IDictionary<'TKey,'TValue>) : unit
Collections.Generic.Dictionary(capacity: int, comparer: Collections.Generic.IEqualityComparer<'TKey>) : unit
Collections.Generic.Dictionary(dictionary: Collections.Generic.IDictionary<'TKey,'TValue>, comparer: Collections.Generic.IEqualityComparer<'TKey>) : unit
Multiple items
val obj : 'a

--------------------
type obj = Object

Full name: Microsoft.FSharp.Core.obj
val ty : Type
Object.GetType() : Type
Collections.Generic.Dictionary.ContainsKey(key: Type) : bool
val methods : MethodInfo []
Type.GetMethods() : MethodInfo []
Type.GetMethods(bindingAttr: BindingFlags) : MethodInfo []
val invoke : MethodInfo option
type Array =
  member Clone : unit -> obj
  member CopyTo : array:Array * index:int -> unit + 1 overload
  member GetEnumerator : unit -> IEnumerator
  member GetLength : dimension:int -> int
  member GetLongLength : dimension:int -> int64
  member GetLowerBound : dimension:int -> int
  member GetUpperBound : dimension:int -> int
  member GetValue : [<ParamArray>] indices:int[] -> obj + 7 overloads
  member Initialize : unit -> unit
  member IsFixedSize : bool
  ...

Full name: System.Array
val tryFind : predicate:('T -> bool) -> array:'T [] -> 'T option

Full name: Microsoft.FSharp.Collections.Array.tryFind
val mi : MethodInfo
property MemberInfo.DeclaringType: Type
property MemberInfo.Name: string
val def : Expr option
union case Option.Some: Value: 'T -> Option<'T>
module Seq

from Microsoft.FSharp.Collections
val tryFind : predicate:('T -> bool) -> source:seq<'T> -> 'T option

Full name: Microsoft.FSharp.Collections.Seq.tryFind
val instr : obj
namespace System.Reflection.Emit
type OpCodes =
  static val Nop : OpCode
  static val Break : OpCode
  static val Ldarg_0 : OpCode
  static val Ldarg_1 : OpCode
  static val Ldarg_2 : OpCode
  static val Ldarg_3 : OpCode
  static val Ldloc_0 : OpCode
  static val Ldloc_1 : OpCode
  static val Ldloc_2 : OpCode
  static val Ldloc_3 : OpCode
  ...

Full name: System.Reflection.Emit.OpCodes
field Emit.OpCodes.Call
module Option

from Microsoft.FSharp.Core
val bind : binder:('T -> 'U option) -> option:'T option -> 'U option

Full name: Microsoft.FSharp.Core.Option.bind
namespace System.Reflection
type MethodInfo =
  inherit MethodBase
  member Equals : obj:obj -> bool
  member GetBaseDefinition : unit -> MethodInfo
  member GetGenericArguments : unit -> Type[]
  member GetGenericMethodDefinition : unit -> MethodInfo
  member GetHashCode : unit -> int
  member MakeGenericMethod : [<ParamArray>] typeArguments:Type[] -> MethodInfo
  member MemberType : MemberTypes
  member ReturnParameter : ParameterInfo
  member ReturnType : Type
  member ReturnTypeCustomAttributes : ICustomAttributeProvider

Full name: System.Reflection.MethodInfo
Multiple items
type Expr =
  override Equals : obj:obj -> bool
  member GetFreeVars : unit -> seq<Var>
  member Substitute : substitution:(Var -> Expr option) -> Expr
  member ToString : full:bool -> string
  member CustomAttributes : Expr list
  member Type : Type
  static member AddressOf : target:Expr -> Expr
  static member AddressSet : target:Expr * value:Expr -> Expr
  static member Application : functionExpr:Expr * argument:Expr -> Expr
  static member Applications : functionExpr:Expr * arguments:Expr list list -> Expr
  ...

Full name: Microsoft.FSharp.Quotations.Expr

--------------------
type Expr<'T> =
  inherit Expr
  member Raw : Expr

Full name: Microsoft.FSharp.Quotations.Expr<_>
static member Expr.TryGetReflectedDefinition : methodBase:MethodBase -> Expr option
union case Option.None: Option<'T>
Collections.Generic.Dictionary.Add(key: Type, value: Expr option) : unit
val defForMethodInfo : unit -> ('a -> Expr option) (requires equality and 'a :> MethodBase)

Full name: Script.defForMethodInfo


 Get reflected definition for method info
let cache = System.Collections.Generic.Dictionary<_,_>()
    fun mi ->
        if cache.ContainsKey mi then cache.[mi]
        else
            let res = Expr.TryGetReflectedDefinition mi
            cache.Add (mi, res)
            res
Multiple items
type Func<'TResult> =
  delegate of unit -> 'TResult

Full name: System.Func<_>

--------------------
type Func<'T,'TResult> =
  delegate of 'T -> 'TResult

Full name: System.Func<_,_>

--------------------
type Func<'T1,'T2,'TResult> =
  delegate of 'T1 * 'T2 -> 'TResult

Full name: System.Func<_,_,_>

--------------------
type Func<'T1,'T2,'T3,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 -> 'TResult

Full name: System.Func<_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 -> 'TResult

Full name: System.Func<_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 -> 'TResult

Full name: System.Func<_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'T15,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 * 'T15 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'T15,'T16,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 * 'T15 * 'T16 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>
val expr : Expr
match expr with
    | Lambdas(_,(Call(_,minfo1,_))) -> function
        | Call(obj, minfo2, args) when minfo1.MetadataToken = minfo2.MetadataToken ->
            Some args
        | _ -> None
    | _ -> failwith "invalid template parameter"
type Info =
  {Scope: Map<string,Expr>;
   Prior: int;
   RightOperand: bool;}

Full name: Script.Info


 Info for the transformation steps
Info.Scope: Map<string,Expr>
Multiple items
module Map

from Microsoft.FSharp.Collections

--------------------
type Map<'Key,'Value (requires comparison)> =
  interface IEnumerable
  interface IComparable
  interface IEnumerable<KeyValuePair<'Key,'Value>>
  interface ICollection<KeyValuePair<'Key,'Value>>
  interface IDictionary<'Key,'Value>
  new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
  member Add : key:'Key * value:'Value -> Map<'Key,'Value>
  member ContainsKey : key:'Key -> bool
  override Equals : obj -> bool
  member Remove : key:'Key -> Map<'Key,'Value>
  ...

Full name: Microsoft.FSharp.Collections.Map<_,_>

--------------------
new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
Info.Prior: int
Multiple items
val int : value:'T -> int (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
Info.RightOperand: bool
type bool = Boolean

Full name: Microsoft.FSharp.Core.bool
val generatePattern : expr:Expr -> env:seq<string> -> string

Full name: Script.generatePattern


 Generate a formula pattern for an expression
 column number - index of var in the env; '#' is a temp placeholder for a row number
val env : seq<string>
let scope =
        Seq.mapi (fun i name -> name, Expr.Value("R#C" + string (i + 1))) env
        |> Map.ofSeq

    let defaultPrior = 4
    let tryGetReflectedDef = defForMethodInfo()

    // info with default priority
    let inline (!!) info = { info with Prior = defaultPrior }
val addParens : (Info -> int -> bool)
val info : Info
val currPrior : int
val updateScope : (seq<string> -> seq<'a> -> Map<string,'a> -> Map<string,'a>) (requires 'a :> Expr)
val names : seq<string>
val parms : seq<#Expr>
val scope : Map<string,#Expr>
val zip : source1:seq<'T1> -> source2:seq<'T2> -> seq<'T1 * 'T2>

Full name: Microsoft.FSharp.Collections.Seq.zip
val fold : folder:('State -> 'T -> 'State) -> state:'State -> source:seq<'T> -> 'State

Full name: Microsoft.FSharp.Collections.Seq.fold
val sc : Map<string,#Expr>
val name : string
Multiple items
active recognizer Var: Expr -> Var option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Var|_| )

--------------------
type Var =
  interface IComparable
  new : name:string * typ:Type * ?isMutable:bool -> Var
  member IsMutable : bool
  member Name : string
  member Type : Type
  static member Global : name:string * typ:Type -> Var

Full name: Microsoft.FSharp.Quotations.Var

--------------------
new : name:string * typ:Type * ?isMutable:bool -> Var
val var : Var
val replace : name:'a -> value:'b -> map:Map<'a,'b> -> Map<'a,'b> (requires comparison)

Full name: Script.replace
property Var.Name: string
val param : #Expr
val printBinaryOp : (string -> Expr -> Expr -> int -> Info -> string)
val op : string
val x : Expr
val y : Expr
val curr : int
val left : string
val transform : (Info -> Expr -> string)
val right : string
val res : string
val printFunc : (string -> Expr list -> Info -> string)
val args : Expr list
val argValues : string []
Multiple items
module List

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
  interface IEnumerable
  interface IEnumerable<'T>
  member Head : 'T
  member IsEmpty : bool
  member Item : index:int -> 'T with get
  member Length : int
  member Tail : 'T list
  static member Cons : head:'T * tail:'T list -> 'T list
  static member Empty : 'T list

Full name: Microsoft.FSharp.Collections.List<_>
val map : mapping:('T -> 'U) -> list:'T list -> 'U list

Full name: Microsoft.FSharp.Collections.List.map
val toArray : list:'T list -> 'T []

Full name: Microsoft.FSharp.Collections.List.toArray
val sprintf : format:Printf.StringFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.sprintf
Multiple items
active recognizer String: Expr -> string option

Full name: Microsoft.FSharp.Quotations.DerivedPatterns.( |String|_| )

--------------------
type String =
  new : value:char -> string + 7 overloads
  member Chars : int -> char
  member Clone : unit -> obj
  member CompareTo : value:obj -> int + 1 overload
  member Contains : value:string -> bool
  member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
  member EndsWith : value:string -> bool + 2 overloads
  member Equals : obj:obj -> bool + 2 overloads
  member GetEnumerator : unit -> CharEnumerator
  member GetHashCode : unit -> int
  ...

Full name: System.String

--------------------
String(value: nativeptr<char>) : unit
String(value: nativeptr<sbyte>) : unit
String(value: char []) : unit
String(c: char, count: int) : unit
String(value: nativeptr<char>, startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int) : unit
String(value: char [], startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int, enc: Text.Encoding) : unit
String.Join(separator: string, values: Collections.Generic.IEnumerable<string>) : string
String.Join<'T>(separator: string, values: Collections.Generic.IEnumerable<'T>) : string
String.Join(separator: string, [<ParamArray>] values: obj []) : string
String.Join(separator: string, [<ParamArray>] value: string []) : string
String.Join(separator: string, value: string [], startIndex: int, count: int) : string
val applyFunc : (Var * seq<Expr> -> Info -> string)
val parms : seq<Expr>
match Map.tryFind var.Name info.Scope with
        | Some (Lambdas (((x::_)::_) as vars, expr)) ->
            let newScope =
                let names = seq { for [v] in vars do yield v.Name }
                updateScope names parms info.Scope

            transform { info with Scope = newScope } expr
        | _ -> failwith "cannot apply function"
Multiple items
active recognizer Func: Expr -> Expr -> Expr list option

Full name: Script.( |Func|_| )


 Simplified SpecificCall


--------------------
type Func<'TResult> =
  delegate of unit -> 'TResult

Full name: System.Func<_>

--------------------
type Func<'T,'TResult> =
  delegate of 'T -> 'TResult

Full name: System.Func<_,_>

--------------------
type Func<'T1,'T2,'TResult> =
  delegate of 'T1 * 'T2 -> 'TResult

Full name: System.Func<_,_,_>

--------------------
type Func<'T1,'T2,'T3,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 -> 'TResult

Full name: System.Func<_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 -> 'TResult

Full name: System.Func<_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 -> 'TResult

Full name: System.Func<_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'T15,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 * 'T15 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>

--------------------
type Func<'T1,'T2,'T3,'T4,'T5,'T6,'T7,'T8,'T9,'T10,'T11,'T12,'T13,'T14,'T15,'T16,'TResult> =
  delegate of 'T1 * 'T2 * 'T3 * 'T4 * 'T5 * 'T6 * 'T7 * 'T8 * 'T9 * 'T10 * 'T11 * 'T12 * 'T13 * 'T14 * 'T15 * 'T16 -> 'TResult

Full name: System.Func<_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_>
Multiple items
val decimal : value:'T -> decimal (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.decimal

--------------------
type decimal = Decimal

Full name: Microsoft.FSharp.Core.decimal

--------------------
type decimal<'Measure> = decimal

Full name: Microsoft.FSharp.Core.decimal<_>
Multiple items
val double : value:'T -> float (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.double

--------------------
type double = Double

Full name: Microsoft.FSharp.Core.double
Multiple items
val float : value:'T -> float (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.float

--------------------
type float = Double

Full name: Microsoft.FSharp.Core.float

--------------------
type float<'Measure> = float

Full name: Microsoft.FSharp.Core.float<_>
val mduration : settlement:'a -> maturity:'b -> coupon:'c -> yld:'d -> frequency:'e -> basis:'f -> decimal

Full name: Script.mduration
val accrint : issue:'a -> first_interest:'b -> settlement:'c -> rate:'d -> par:'e -> frequency:'f -> basis:'g -> decimal

Full name: Script.accrint
active recognizer Lambdas: Expr -> (Var list list * Expr) option

Full name: Microsoft.FSharp.Quotations.DerivedPatterns.( |Lambdas|_| )
val e : Expr
active recognizer Let: Expr -> (Var * Expr * Expr) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Let|_| )
active recognizer NewTuple: Expr -> Expr list option

Full name: Microsoft.FSharp.Quotations.Patterns.( |NewTuple|_| )
val vs : Expr list
val res : Expr
val newScope : Map<string,Expr>
let tupleItems = List.toArray vs
                let rec initTuple (e, newScope) =
                    match e with
                    | Let(v, TupleGet(_, ind), e') ->
                        initTuple (e', replace v.Name tupleItems.[ind] newScope)
                    | _ -> e, newScope
                initTuple (e, info.Scope)
val value : Expr
active recognizer Value: Expr -> (obj * Type) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Value|_| )
val v : obj
val tryFind : key:'Key -> table:Map<'Key,'T> -> 'T option (requires comparison)

Full name: Microsoft.FSharp.Collections.Map.tryFind
val replacement : Expr
active recognizer Call: Expr -> (Expr option * MethodInfo * Expr list) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Call|_| )
val i : obj
val ind : int
val unbox : value:obj -> 'T

Full name: Microsoft.FSharp.Core.Operators.unbox
val ps : Expr list
val names : string []
MethodBase.GetParameters() : ParameterInfo []
val map : mapping:('T -> 'U) -> array:'T [] -> 'U []

Full name: Microsoft.FSharp.Collections.Array.map
val p : ParameterInfo
property ParameterInfo.Name: string
val tryGetReflectedDef : (MethodInfo -> Expr option)
val impl : Expr
val getCall : (Expr -> Expr)
active recognizer Lambda: Expr -> (Var * Expr) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Lambda|_| )
val call : Expr
val failwith : message:string -> 'T

Full name: Microsoft.FSharp.Core.Operators.failwith
active recognizer NewObject: Expr -> (ConstructorInfo * Expr list) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |NewObject|_| )
val ci : ConstructorInfo
val y : obj
val m : obj
val d : obj
active recognizer Application: Expr -> (Expr * Expr) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |Application|_| )
val args : Expr * Expr
val f : Expr
let rec collectParams parms = function
                | Application(Var f, v) -> f, v :: parms
                | Application(applArgs, v) -> collectParams (v :: parms) applArgs
                | expr -> failwith (sprintf "unexpected expression collecting params: %A" expr)
val collectParams : (Expr list -> Expr -> Var * Expr list)
val f : Var
active recognizer LetRecursive: Expr -> ((Var * Expr) list * Expr) option

Full name: Microsoft.FSharp.Quotations.Patterns.( |LetRecursive|_| )
val defaultPrior : int
val scope : Map<string,Expr>
module PatternsExample =
    [<ReflectedDefinition>]
    [<MethodImpl(MethodImplOptions.NoInlining)>]
    let id x = x

    [<ReflectedDefinition>]
    [<MethodImpl(MethodImplOptions.NoInlining)>]
    let sum (a: decimal) b = id a + b
    
    let patterns = [
        <@@ sum 42M (decimal 42) @@>, [], "=42+42"
        
        <@@
            let f a b = b ** a
            let f2() = f 3. 2. + double 0
            f2()
         @@>, [], "=2^3+0"

        <@@ ((1. + 2.**3.) * 3. - 4.) / 5. @@>, [], "=((1+2^3)*3-4)/5"
        <@@ fun (x: decimal) -> -x + +1M @@>, [], "=-x+1"
        <@@ fun a b -> a ** 4. @@>, ["a"; "b"], "=R#C1^4"
        
        // arrays can be used instead of explicit var names
        <@@ fun (args: _ array) -> args.[0] + args.[1] @@>, [], "=R#C1+R#C2"

        <@@ fun issue settlement ->
                accrint issue (DateTime(2010,9,8)) settlement 10 100 2 0
         @@>, [ "issue"; "settlement"], "=ACCRINT(R#C1, DATE(2010, 9, 8), R#C2, 10, 100, 2, 0)"

        <@@ fun x ->
                let a, b = 4., 0.0001
                a * 43. - (let x = 1. in 1. - (let x = 2.-3./6. in x) + x) / b + x
         @@>, ["x"], "=4*43-(1-(2-3/6)+1)/0.0001+R#C1"

        <@@ (1.+2.)-(4.+5.) @@>, [], "=1+2-(4+5)"
    ]
 
    let run() =
        patterns
        |> List.iteri (fun i (expr, env, expected) ->
                let res = generatePattern expr env
                if res <> expected then printfn "test %d: %s; expected %s" i res expected)

PatternsExample.run()
Multiple items
type ReflectedDefinitionAttribute =
  inherit Attribute
  new : unit -> ReflectedDefinitionAttribute

Full name: Microsoft.FSharp.Core.ReflectedDefinitionAttribute

--------------------
new : unit -> ReflectedDefinitionAttribute
Multiple items
type MethodImplAttribute =
  inherit Attribute
  new : unit -> MethodImplAttribute + 2 overloads
  val MethodCodeType : MethodCodeType
  member Value : MethodImplOptions

Full name: System.Runtime.CompilerServices.MethodImplAttribute

--------------------
MethodImplAttribute() : unit
MethodImplAttribute(methodImplOptions: MethodImplOptions) : unit
MethodImplAttribute(value: int16) : unit
type MethodImplOptions =
  | Unmanaged = 4
  | ForwardRef = 16
  | PreserveSig = 128
  | InternalCall = 4096
  | Synchronized = 32
  | NoInlining = 8
  | NoOptimization = 64

Full name: System.Runtime.CompilerServices.MethodImplOptions
field MethodImplOptions.NoInlining = 8
val sum : a:decimal -> b:decimal -> decimal

Full name: Script.Test.sum
val a : decimal
val b : decimal
val mdurationMonth : m:'a -> c:'b -> y:'c -> f:'d -> basis:'e -> decimal

Full name: Script.Test.mdurationMonth
val m : 'a
val c : 'b
val y : 'c
val f : 'd
val basis : 'e
Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 10 overloads
    member Add : value:TimeSpan -> DateTime
    member AddDays : value:float -> DateTime
    member AddHours : value:float -> DateTime
    member AddMilliseconds : value:float -> DateTime
    member AddMinutes : value:float -> DateTime
    member AddMonths : months:int -> DateTime
    member AddSeconds : value:float -> DateTime
    member AddTicks : value:int64 -> DateTime
    member AddYears : value:int -> DateTime
    ...
  end

Full name: System.DateTime

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)
val run : export:(string [] list -> int -> obj [] -> unit) -> unit

Full name: Script.Test.run
val export : (string [] list -> int -> obj [] -> unit)
val getReflectedDefinition : (obj -> Expr option)
val data : obj [] []
type obj = Object

Full name: Microsoft.FSharp.Core.obj
[|
            [| 42M; 1M; DateTime(2012, 1, 7); DateTime(2030, 1, 1); 15M; 0.9M; 1; 1 |]
            [| null; null; null; DateTime(2016, 1, 7); 8M; 9M; 2; 1 |]
        |]
val dataColumns : string list
val funcs : obj list
type 'T list = List<'T>

Full name: Microsoft.FSharp.Collections.list<_>
val unquote : (Expr option -> string [] option)
val formulae : string [] list
val filter : predicate:('T -> bool) -> list:'T list -> 'T list

Full name: Microsoft.FSharp.Collections.List.filter
val isSome : option:'T option -> bool

Full name: Microsoft.FSharp.Core.Option.isSome
val get : option:'T option -> 'T

Full name: Microsoft.FSharp.Core.Option.get
val iteri : action:(int -> 'T -> unit) -> array:'T [] -> unit

Full name: Microsoft.FSharp.Collections.Array.iteri
val export : exportValue:(int * int -> int -> 'a -> unit) -> exportFunc:(int * int -> int -> string -> unit) -> patterns:string [] list -> row:int -> items:'a [] -> unit

Full name: Script.export


 Export data with given pattern
val exportValue : (int * int -> int -> 'a -> unit)
val exportFunc : (int * int -> int -> string -> unit)
val patterns : string [] list
val row : int
val items : 'a []
let row = row + 1
    let j = items.Length + 1
    let formulae = patterns |> List.map (fun arr -> String.Join (string row, arr))
    Array.iteri (exportValue (row, 1)) items
    List.iteri (exportFunc (row, j)) formulae
module Test

from Script
val fst : int
val col : int
val item : obj
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val formula : string
val app : ApplicationClass

Full name: Script.app
Multiple items
type ApplicationClass =
  new : unit -> ApplicationClass
  member ActivateMicrosoftApp : Index:XlMSApplication -> unit
  member ActiveCell : Range
  member ActiveChart : Chart
  member ActiveDialog : DialogSheet
  member ActiveEncryptionSession : int
  member ActiveMenuBar : MenuBar
  member ActivePrinter : string with get, set
  member ActiveProtectedViewWindow : ProtectedViewWindow
  member ActiveSheet : obj
  ...

Full name: Microsoft.Office.Interop.Excel.ApplicationClass

--------------------
ApplicationClass() : unit
val workbook : Workbook

Full name: Script.workbook
Workbooks.Add(?Template: obj) : Workbook
type XlWBATemplate =
  | xlWBATChart = -4109
  | xlWBATExcel4IntlMacroSheet = 4
  | xlWBATExcel4MacroSheet = 3
  | xlWBATWorksheet = -4167

Full name: Microsoft.Office.Interop.Excel.XlWBATemplate
field XlWBATemplate.xlWBATWorksheet = -4167
val worksheet : Worksheet

Full name: Script.worksheet
type Worksheet =

Full name: Microsoft.Office.Interop.Excel.Worksheet
property _Worksheet.Cells: Range
type Range =
  member Activate : unit -> obj
  member AddComment : ?Text:obj -> Comment
  member AddIndent : obj with get, set
  member Address : obj * obj * XlReferenceStyle * obj * obj -> string
  member AddressLocal : obj * obj * XlReferenceStyle * obj * obj -> string
  member AdvancedFilter : Action:XlFilterAction * ?CriteriaRange:obj * ?CopyToRange:obj * ?Unique:obj -> obj
  member AllocateChanges : unit -> unit
  member AllowEdit : bool
  member Application : Application
  member ApplyNames : ?Names:obj * ?IgnoreRelativeAbsolute:obj * ?UseRowColumnNames:obj * ?OmitColumn:obj * ?OmitRow:obj * ?Order:XlApplyNamesOrder * ?AppendLast:obj -> obj
  ...

Full name: Microsoft.Office.Interop.Excel.Range
type XlReferenceStyle =
  | xlA1 = 1
  | xlR1C1 = -4150

Full name: Microsoft.Office.Interop.Excel.XlReferenceStyle
field XlReferenceStyle.xlA1 = 1
// release COM objects
let inline release (objs: obj list) =
    List.iter (System.Runtime.InteropServices.Marshal.ReleaseComObject >> ignore) objs

let filename = "test.xls"
try workbook.SaveAs(filename, XlFileFormat.xlWorkbookNormal) with _ -> ()
workbook.Close false
app.Quit()

release [worksheet; workbook; app]

More information

Link:http://fssnip.net/9T
Posted:5 years ago
Author:Natallie Baikevich
Tags: quotations , excel , reflection