office-fable

Fable bindings for Office-js.

Example

I created a minimal SAFE stack example for a office excel add-in: Check it out here.

Installation

This repository is split into three submodules available on nuget:

• OfficeJS.Fable - Main package
• WordJS.Fable
• ExcelJS.Fable

OfficeJS.Fable contains all office-js bindings, whereas WordJS.Fable and ExcelJS.Fable only contain the related bindings.

In a SAFE-stack add these packages to the Client and open them like such:

open ExcelJS.Fable //WordJS.Fable, OfficeJS.Fable
open ExcelJS.Fable.Excel
open ExcelJS.Fable.GlobalBindings

You need to load the website into your Excel application (desktop app or browser) with a manifest.xml. An example for one of those can be seen in the Swate repository, in which ExcelJS.Fable is used.

To initialize the client-excel connection you need to call:

open ExcelJS.Fable.GlobalBindings

let initializeAddIn() = Office.onReady()

And call the initializeAddIn() function as part of the init() function:

// Example taken from Swate!
// defines the initial state and initial command (= side-effect) of the application
let init (pageOpt: Routing.Route option) : Model * Cmd<Msg> =
    let initialModel = initializeModel pageOpt
    let route = (parseHash Routing.Routing.route) Browser.Dom.document.location
    // The initial command from urlUpdate is not needed yet. As we use a reduced variant of subModels with no own Msg system.
    let model, _ = urlUpdate route initialModel
    let initialCmd =
        Cmd.batch [
            Cmd.OfPromise.either
                initializeAddIn
                ()
                (fun x -> 
                  (x.host.ToString(),x.platform.ToString()) 
                  |> Initialized 
                  |> ExcelInterop 
                )
                (fun x -> x |> GenericError |> Dev)
        ]
    model, initialCmd

How to use

The communication between Client and Excel can be imagined as the communication between Client and Server in Elmish. In a standard SAFE-stack the Client-Server communication uses Cmd.OfAsync.xx. For the communication between Client and Excel we will use Cmd.OfPromise.xx.

// Example taken from Swate
let handleExcelInteropMsg (excelInteropMsg: ExcelInteropMsg) (currentModel:Model) : Model * Cmd<Msg> =

    match excelInteropMsg with
    | AutoFitTable ->
        let cmd =
            Cmd.OfPromise.either
                OfficeInterop.autoFitTable
                ()
                (GenericLog >> Dev)
                (GenericError >> Dev)
        currentModel, cmd

Which then call a function of the type:

/// This function is used to hide all '#h' tagged columns and to fit rows and columns to their values.
/// The main goal is to improve readability of the table with this function.
let autoFitTable () =
    Excel.run(fun context ->

        promise {
            
            let! annotationTable = getActiveAnnotationTableName()

            let sheet = context.workbook.worksheets.getActiveWorksheet()

            let annotationTable = sheet.tables.getItem(annotationTable)
            let allCols = annotationTable.columns.load(propertyNames = U2.Case1 "items")
    
            let annoHeaderRange = annotationTable.getHeaderRowRange()
            let _ = annoHeaderRange.load(U2.Case2 (ResizeArray[|"values"|]))

            let r = context.runtime.load(U2.Case1 "enableEvents")

            let! res = context.sync().``then``(fun _ ->

                r.enableEvents <- false
                // Auto fit on all columns to fit cols and rows to their values.
                let allTableCols = allCols.items |> Array.ofSeq
                let _ =
                    allTableCols
                    |> Array.map (fun col -> col.getRange())
                    |> Array.map (fun x ->
                        // make all columns visible, we will later selectively hide all with '#h' tag
                        x.columnHidden <- false
                        x.format.autofitColumns()
                        x.format.autofitRows()
                    )
                // Get all column headers
                let headerVals = annoHeaderRange.values.[0] |> Array.ofSeq
                // Get only column headers with values inside and map object to string
                let headerArr = headerVals |> Array.choose id |> Array.map string
                // Parse header elements into record type
                let parsedHeaderArr = headerArr |> Array.map parseColHeader
                // Find all columns to hide (with '#h' tag)
                let colsToHide =
                    parsedHeaderArr
                    |> Array.filter (fun header -> header.TagArr.IsSome && Array.contains ColumnTags.HiddenTag header.TagArr.Value)
                // Get all column ranges (necessary to change 'columnHidden' attribute) for all headers with '#h' tag.
                let ranges =
                    colsToHide
                    |> Array.map (fun header -> (annotationTable.columns.getItem (U2.Case2 header.Header)).getRange())
                // Hide columns
                let _ = ranges |> Array.map (fun x -> x.columnHidden <- true)

                r.enableEvents <- true

                // return message
                "Info","Autoformat Table"
            )

            return res
        }
    )

An important concept in using the office-js API is the load functionality. The Client code needs to request information from Excel to use it downstream. This is done for example with the following:

let sheet = context.workbook.worksheets.getActiveWorksheet()

let annotationTable = sheet.tables.getItem(annotationTable)
let allCols = annotationTable.columns.load(propertyNames = U2.Case1 "items")

To actually use the information loaded we first need to run context.sync().``then``(fun _ -> ... ) which returns a promise. To chain multiple of these we use the promise { .. } function. Inside of this we can use let! .. to await the result of the promise function and use it inside of the same function. More examples for the use of the Excel API can be found in the Swate repo.

How was this created

• Install ts2fable
  • npm install -g ts2fable
• Get office.js typescript file
  • npm install @types/office-js
  • node_modules@types\office-js\index.d.ts
• ts2fable will not work without Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
• Create Fable bindings with ts2fable "node_modules\@types\office-js\index.d.ts" src\office-fable\OfficeJS.fs
• Add Fable.Core to .fsproj <PackageReference Include="Fable.Core" Version="3.2.8" />
• Add Fable.React to .fsproj <PackageReference Include="Fable.React" Version="7.4.1" />
• Replace all PromiseLike in OfficeJS.fs to Promise.
• ts2fable translates union cases which start with a number to start with an underscore. Both is not valid in fsharp. Therefore all union cases starting with an _ are replaced to start with an N. Exmp.: _3DColumnClustered -> N3DColumnClustered
• Change union case [<CompiledName "Tags">] Tags to Tags2.