3 people like it.

Insert Deedle frame into Excel

Some bare-bones example code of inserting a Deedle frame into excel. Adapted from https://github.com/tpetricek/Documents/blob/master/Talks%202013/FsLab%20Showcase%20%28Seattle%29/code/Excel/Excel.fsx

 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: 
open Deedle
open NetOffice
open NetOffice.ExcelApi
open NetOffice.OfficeApi

// Starts new excel app. The DisplayAlerts <- false makes sure that we don't get popups asking us if we want to save when we exit.
let app = new Application()
app.DisplayAlerts <- false
app.Visible <- true

// When Excel is started it's empty, so we add a new workbook with a worksheet
let wb = app.Workbooks.Add()
let ws = wb.ActiveSheet :?> Worksheet

// You could also open an existing sheet instead
//app.Workbooks.Open(@".\deedleTest.xlsx")
//let wb = app.ActiveWorkbook
//let ws = wb.ActiveSheet :?> Worksheet

// Takes a Deedle.OptionalValue and converts it to a string, which is then boxed
// At the call site for this function we are also given the Type of the object, so this could be used to do something more sophisticated
let getExcelValue obj =
    match obj with
    | Deedle.OptionalValue.Present obj -> box (obj.ToString()) 
    | _ -> box ""

// Takes the values from a Deedle.Frame and converts them to a 2d array for insertion into a sheet
let frameValuesToArray (data : FrameData) =
    let transpose (array:'T[,]) =
      Array2D.init (array.GetLength(1)) (array.GetLength(0)) (fun i j -> array.[j, i])

    data.Columns
    |> Seq.map (fun (ty, vec) -> vec.ObjectSequence |> Seq.map getExcelValue |> Array.ofSeq) // Here the ty variable could be used by getExcelValue
    |> array2D
    |> transpose

/// Inserts a given 2d array at the specified cell
let insertArrayAt (cell : Range) (array : 'a[,]) =
    let cell2 = cell.Offset.[Array2D.length1 array, Array2D.length2 array]
    let range = cell.Worksheet.Range(cell, cell2)

    range.Value2 <- array

/// Inserts a given Deedle.Frame at the specified cell
let insertFrameAt (cell : Range) (frame : Frame<_,_>) =
    let frameData = frame.GetFrameData()
    let ws = cell.Worksheet
    
    // Converts the row/col keys to an array of strings
    // This only supports single dimensional row/col keys
    let keysToArray (keys : obj[] seq) = keys |> Seq.map (fun arr -> if arr.Length > 0 then arr.[0].ToString() else "") |> Array.ofSeq

    let cols = 
        let arr = frameData.ColumnKeys |> keysToArray
        Array2D.init 1 arr.Length (fun i j -> arr.[j])

    let rows =
        let arr = frameData.RowKeys |> keysToArray
        Array2D.init arr.Length 1 (fun i j -> arr.[i])

    let valsArray = frameValuesToArray frameData

    // Offsets are 1-indexed, so for example right and down is 2,2
    valsArray |> insertArrayAt cell.Offset.[2,2]
    cols |> insertArrayAt cell.Offset.[1,2]
    rows |> insertArrayAt cell.Offset.[2,1]

// Create an example dataframe
let data =
    [ for x in 1 .. 10 do
        for y in 1 .. 20 do
            yield (x,y,x+y) ]
    |> Frame.ofValues

// Inserting the frame
data |> insertFrameAt (ws.Range("A1"))
// Or to a named cell
// data |> insertFrameAt ws ws.Cells.["NamedCell1"]
// Or
// data |> insertFrameAt ws ws.Cells.[1,1]

// It's important to also call the dispose method to fully close Excel
app.Quit()
app.Dispose()
namespace Deedle
namespace NetOffice
namespace NetOffice.ExcelApi
namespace NetOffice.OfficeApi
val app : Application

Full name: Script.app
Multiple items
type Application =
  inherit _Application
  new : unit -> Application + 6 overloads
  member CreateEventBridge : unit -> unit
  member Dispose : unit -> unit + 1 overload
  member DisposeEventBridge : unit -> unit
  member EventBridgeInitialized : bool
  member GetCountOfEventRecipients : eventName:string -> int
  member GetEventRecipients : eventName:string -> Delegate[]
  member HasEventRecipients : unit -> bool
  member RaiseCustomEvent : eventName:string * paramsArray:obj[] -> int
  event NewWorkbookEvent : Application_NewWorkbookEventHandler
  ...

Full name: NetOffice.ExcelApi.Application

--------------------
Application() : unit
Application(replacedObject: COMObject) : unit
Application(progId: string) : unit
Application(parentObject: COMObject, comProxy: obj) : unit
Application(factory: Core, parentObject: COMObject, comProxy: obj) : unit
Application(parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
Application(factory: Core, parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
property _Application.DisplayAlerts: bool
property _Application.Visible: bool
val wb : Workbook

Full name: Script.wb
property _Application.Workbooks: Workbooks
Workbooks.Add() : Workbook
Workbooks.Add(template: obj) : Workbook
val ws : Worksheet

Full name: Script.ws
property _Workbook.ActiveSheet: obj
Multiple items
type Worksheet =
  inherit _Worksheet
  new : unit -> Worksheet + 6 overloads
  member CreateEventBridge : unit -> unit
  member DisposeEventBridge : unit -> unit
  member EventBridgeInitialized : bool
  member GetCountOfEventRecipients : eventName:string -> int
  member GetEventRecipients : eventName:string -> Delegate[]
  member HasEventRecipients : unit -> bool
  member RaiseCustomEvent : eventName:string * paramsArray:obj[] -> int
  event SelectionChangeEvent : Worksheet_SelectionChangeEventHandler
  event BeforeDoubleClickEvent : Worksheet_BeforeDoubleClickEventHandler
  ...

Full name: NetOffice.ExcelApi.Worksheet

--------------------
Worksheet() : unit
Worksheet(replacedObject: COMObject) : unit
Worksheet(progId: string) : unit
Worksheet(parentObject: COMObject, comProxy: obj) : unit
Worksheet(factory: Core, parentObject: COMObject, comProxy: obj) : unit
Worksheet(parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
Worksheet(factory: Core, parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
val getExcelValue : obj:'a opt -> obj

Full name: Script.getExcelValue
Multiple items
val obj : 'a opt

--------------------
type obj = System.Object

Full name: Microsoft.FSharp.Core.obj
Multiple items
module OptionalValue

from Deedle

--------------------
type OptionalValue<'T> =
  struct
    new : value:'T -> OptionalValue<'T>
    private new : hasValue:bool * value:'T -> OptionalValue<'T>
    override Equals : y:obj -> bool
    override GetHashCode : unit -> int
    override ToString : unit -> string
    member HasValue : bool
    member Value : 'T
    member ValueOrDefault : 'T
    static member Missing : OptionalValue<'T>
  end

Full name: Deedle.OptionalValue<_>

--------------------
type OptionalValue

Full name: Deedle.OptionalValue

--------------------
OptionalValue()
new : value:'T -> OptionalValue<'T>
active recognizer Present: 'T opt -> Choice<unit,'T>

Full name: Deedle.OptionalValue.( |Missing|Present| )
Multiple items
val obj : 'a

--------------------
type obj = System.Object

Full name: Microsoft.FSharp.Core.obj
val box : value:'T -> obj

Full name: Microsoft.FSharp.Core.Operators.box
System.Object.ToString() : string
val frameValuesToArray : data:FrameData -> obj [,]

Full name: Script.frameValuesToArray
val data : FrameData
type FrameData =
  {ColumnKeys: seq<obj []>;
   RowKeys: seq<obj []>;
   Columns: seq<Type * IVector<obj>>;}

Full name: Deedle.FrameData
val transpose : ('T [,] -> 'T [,])
Multiple items
val array : 'T [,]

--------------------
type 'T array = 'T []

Full name: Microsoft.FSharp.Core.array<_>
module Array2D

from Microsoft.FSharp.Collections
val init : length1:int -> length2:int -> initializer:(int -> int -> 'T) -> 'T [,]

Full name: Microsoft.FSharp.Collections.Array2D.init
System.Array.GetLength(dimension: int) : int
val i : int
val j : int
FrameData.Columns: seq<System.Type * IVector<obj>>
module Seq

from Microsoft.FSharp.Collections
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val ty : System.Type
val vec : IVector<obj>
property IVector.ObjectSequence: seq<OptionalValue<obj>>
module Array

from Microsoft.FSharp.Collections
val ofSeq : source:seq<'T> -> 'T []

Full name: Microsoft.FSharp.Collections.Array.ofSeq
val array2D : rows:seq<#seq<'T>> -> 'T [,]

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.array2D
val insertArrayAt : cell:Range -> array:'a [,] -> unit

Full name: Script.insertArrayAt


 Inserts a given 2d array at the specified cell
val cell : Range
Multiple items
type Range =
  inherit Range_
  new : unit -> Range + 6 overloads
  member Activate : unit -> obj
  member AddComment : unit -> Comment + 1 overload
  member AddIndent : obj with get, set
  member Address : string
  member AddressLocal : string
  member AdvancedFilter : action:XlFilterAction -> obj + 3 overloads
  member AllocateChanges : unit -> unit
  member AllowEdit : bool
  member Application : Application
  ...

Full name: NetOffice.ExcelApi.Range

--------------------
Range() : unit
Range(replacedObject: COMObject) : unit
Range(progId: string) : unit
Range(parentObject: COMObject, comProxy: obj) : unit
Range(factory: Core, parentObject: COMObject, comProxy: obj) : unit
Range(parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
Range(factory: Core, parentObject: COMObject, comProxy: obj, comProxyType: System.Type) : unit
Multiple items
val array : 'a [,]

--------------------
type 'T array = 'T []

Full name: Microsoft.FSharp.Core.array<_>
val cell2 : Range
Multiple items
property Range.Offset: Range

--------------------
Range_.Offset(rowOffset: obj) : Range
Range_.Offset(rowOffset: obj, columnOffset: obj) : Range
val length1 : array:'T [,] -> int

Full name: Microsoft.FSharp.Collections.Array2D.length1
val length2 : array:'T [,] -> int

Full name: Microsoft.FSharp.Collections.Array2D.length2
val range : Range
property Range.Worksheet: Worksheet
_Worksheet.Range(cell1: obj) : Range
_Worksheet.Range(cell1: obj, cell2: obj) : Range
property Range.Value2: obj
val insertFrameAt : cell:Range -> frame:Frame<'a,'b> -> unit (requires equality and equality)

Full name: Script.insertFrameAt


 Inserts a given Deedle.Frame at the specified cell
val frame : Frame<'a,'b> (requires equality and equality)
Multiple items
module Frame

from Deedle

--------------------
type Frame =
  static member ReadReader : reader:IDataReader -> Frame<int,string>
  static member CustomExpanders : Dictionary<Type,Func<obj,seq<string * Type * obj>>>
  static member NonExpandableInterfaces : List<Type>
  static member NonExpandableTypes : HashSet<Type>

Full name: Deedle.Frame

--------------------
type Frame<'TRowKey,'TColumnKey (requires equality and equality)> =
  interface IDynamicMetaObjectProvider
  interface INotifyCollectionChanged
  interface IFsiFormattable
  interface IFrame
  new : names:seq<'TColumnKey> * columns:seq<ISeries<'TRowKey>> -> Frame<'TRowKey,'TColumnKey>
  new : rowIndex:IIndex<'TRowKey> * columnIndex:IIndex<'TColumnKey> * data:IVector<IVector> * indexBuilder:IIndexBuilder * vectorBuilder:IVectorBuilder -> Frame<'TRowKey,'TColumnKey>
  member AddColumn : column:'TColumnKey * series:ISeries<'TRowKey> -> unit
  member AddColumn : column:'TColumnKey * series:seq<'V> -> unit
  member AddColumn : column:'TColumnKey * series:ISeries<'TRowKey> * lookup:Lookup -> unit
  member AddColumn : column:'TColumnKey * series:seq<'V> * lookup:Lookup -> unit
  ...

Full name: Deedle.Frame<_,_>

--------------------
new : names:seq<'TColumnKey> * columns:seq<ISeries<'TRowKey>> -> Frame<'TRowKey,'TColumnKey>
new : rowIndex:Indices.IIndex<'TRowKey> * columnIndex:Indices.IIndex<'TColumnKey> * data:IVector<IVector> * indexBuilder:Indices.IIndexBuilder * vectorBuilder:Vectors.IVectorBuilder -> Frame<'TRowKey,'TColumnKey>
val frameData : FrameData
member Frame.GetFrameData : unit -> FrameData
val ws : Worksheet
val keysToArray : (seq<obj []> -> string [])
val keys : seq<obj []>
type obj = System.Object

Full name: Microsoft.FSharp.Core.obj
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

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

--------------------
type seq<'T> = System.Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val arr : obj []
property System.Array.Length: int
val cols : string [,]
val arr : string []
FrameData.ColumnKeys: seq<obj []>
val rows : string [,]
FrameData.RowKeys: seq<obj []>
val valsArray : obj [,]
val data : Frame<int,int>

Full name: Script.data
val x : int
val y : int
static member Frame.ofValues : values:seq<'R * 'C * 'V> -> Frame<'R,'C> (requires equality and equality)
_Application.Quit() : unit
Application.Dispose() : unit
Application.Dispose(disposeEventBinding: bool) : unit
Raw view Test code New version

More information

Link:http://fssnip.net/sj
Posted:8 years ago
Author:Kristian Schmidt
Tags: excel , deedle