Friday, 15 May 2015

FCell Calculation Engine - Development Environment

Introduction

This post describes how to set up for development using the FCell Calculation Engine to build an application held within an Excel workbook . You can choose to develop using the internal F# editor or externally, typically using Visual Studio.

The former is simpler. The latter is useful when you have a more complex application to develop or wish to develop code that you may want to optionally run outside of Excel.

Internal F# Editor

To get started using the internal editor you need to use FCell to create a ".NET enabled workbook". You open Excel and in the FCell Ribbon click the New Doc button. You then get this dialog:











In this case, we can use the Excel model as the "existing workbook". (This can be downloaded from - https://bitbucket.org/pb_bwfc/calceng/src/2797abf436fc46a7bb60f077af84eb0375c35c78/SingleProj/Excel/SingleProjXl.xlsx?at=default).

You then need to add some code using the editor opened using the F# button on the FCell Ribbon. To get started, use the following:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
namespace CalcEng
open System
open FCell.ManagedXll
open FCell.ManagedXll.ExplicitConversion
open System.Windows.Forms
open System.Reflection

//force a new assembly version on each build
[<assembly: AssemblyVersion("1.0.0.*")>]
do()

[<XlInvisible>]
module Model = 
    [<XlWorkbookOpen>]
    let workbookOpen () = MessageBox.Show("Workbook open")

  • Lines 2 to 6 add required references: Line 5 is used in line 15 and line 6 is used in line 9. Line 3 is the main reference to FCell code and line 4 provides useful conversion functionality between .NET and Excel.   
  • Lines 9 and 10 force new versions to be built as the code is changed, which is useful, particularly to support debugging.
  • Line 12 ensures the code in the module Model as not interpreted as providing Excel UDFs. 
  • Lines 14 and 15 is just some starter code to generate a dialog when the workbook is opened.

If you now click the Build button you will see the dialog. You can also force the code to rerun using the Reload Addins button.

A copy of the workbook created at this stage can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/c79cf19b9e4f4e64c249d50b6e93723ff3b5ef68/SingleProj/Excel/SingleProj_v0.xlsm?at=default

Visual Studio Configuration

Visual Studio can now be obtained without cost via the Community Edition. This is a great development environment for F#, made even better by adding Visual F# Power Tools and F# Outlining.

To use Visual Studio with the Calculation Engine, you simply need to create an F# Library that contains the code. The library needs to be suitably configured:
  1. Uses F# 3.0
  2. Uses .NET 4.5
  3. References the FCell libraries: FCell.ManagedXll and FCell.XlInterop
  4. References System.Drawing and System.Windows.Forms
  5. Generates new versions on each build 
For 1 and 2, these can be set in the project properties:

For 3 and 4, you need to add the references listed to give:
For 5, the main code could be adjusted as for the embedded version above. However, it is better to add a standard code file to the project (AssemblyInfo.fs), containing:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
9: 
namespace System
open System.Reflection

[<assembly: AssemblyTitle("StarterCe")>]
[<assembly: AssemblyProduct("F# for Actuaries")>]
[<assembly: AssemblyDescription("A starter sample for the FCell Calculation Engine.")>]
[<assembly: AssemblyVersion("1.0.0.*")>]
[<assembly: AssemblyFileVersion("1.0")>]
do ()
This sets some properties for the DLL being created, but in particular line 7 creates new versions as required.

Visual Studio - Build and Debug

The steps needed to build and debug a workbook are:
  1. Build the DLL
  2. Embed the latest version of the DLL in the workbook
  3. Embed any supporting DLLs into the workbook
  4. Open Excel and load the workbook
  5. Attach the debugger to Excel 
The above can be done by hand. For example, steps 2 and 3 can be done using the dialog loaded with the Doc .NET Addin button on the FCell Ribbon.

However, it is very helpful if these steps are automated, so I have created a "starter project" to support this.

To support 2 and 3 you can use the WorkbookPackager provided with FCell. For this, I have created a simple console application BuildXlsm, with this code:

 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: 
open FCell.ManagedXll
open System.IO
open System.Collections.Generic

let info() =
    printfn "Need to provide 4 arguments as follows:"
    printfn "  1. XLSM file, e.g. ..\MyXlDoc.xlsm"
    printfn "  2. Assembly folder, e.g. ..\bin\Debug"
    printfn "  3. Reference assemblies, e.g. ref1.dll,ref2.dll,ref3.dll"
    printfn "  4. Customization assemblies, e.g. cust1.dll,cust2.dll"
    printfn "For example:"
    printfn "BuildXlsm.exe D:\MyProj\MyXlDoc.xlsm D:\MyProj\bin\Debug ref1.dll,ref2.dll,ref3.dll cust1.dll,cust2.dll"

[<EntryPoint>]
let main argv = 
    if argv.Length<>4 then 
        info()
        1 // return an integer exit code
    else
        try
            let xlsm = argv.[0]
            WorkbookPackager.DeleteCustomizationAssemblies xlsm
            WorkbookPackager.DeleteReferencedAssemblies xlsm
            let dfol = argv.[1]
            let toDictionary (dct : IDictionary<_, _>) : Dictionary<_, _> = Dictionary(dct)
            if argv.[2] <> "" then
                let refs = (argv.[2]).Split(',')|>Array.map(fun r -> r,(Path.Combine(dfol,r) |>File.ReadAllBytes))|>dict|>toDictionary
                WorkbookPackager.EmbedReferencedAssemblies(xlsm, refs)
            let cust = (argv.[3]).Split(',')|>Array.map(fun r -> r,(Path.Combine(dfol,r) |>File.ReadAllBytes))|>dict|>toDictionary       
            WorkbookPackager.EmbedCustomizationAssemblies(xlsm, cust)
            0 // return an integer exit code
        with
        |e -> 
            printfn "Failed with message: %s" e.Message
            info()
            2 // return an integer exit code

This expects 4 arguments: the workbook, the folder containing the DLLs, a list of reference DLLs and a list of customization DLLs.
  • Lines 1 to 3 add necessary references
  • Lines 5 to 12 provide information if something goes wrong
  • The main code starts on line 14
  • Lines 16 to 18 handle the case where the wrong number of arguments are supplied
  • Line 21 reads the location of the workbook
  • Lines 22 and 23 remove any existing DLLs from the workbook
  • Line 24 gets the location of the DLLs
  • Line 25 is just a utility function to convert to a dictionary needed by the packager
  • Lines 26 to 28 add the reference DLLs, expected as a comma separated list, with "" expected if there are none of these
  • Lines 29 and 30 add the cusomization DLL(s), expected as a comma separated list
  • Line 33 to 36 handle any errors

You can then use this application in the Post-build event for the main customization DLL.
This includes this line:
$(TargetDir)BuildXlsm.exe $(ProjectDir)Excel\StarterCe.xlsm $(TargetDir) "" StarterCe.dll
This works with a workbook stored in the Excel subfolder of the project. (You can create sub-folders using Visual F# Power Tools)

For step 4 and 5, you can change the project debug settings to load Excel and add the location of the workbook as the command line argument:
 When you start debugging, Excel opens and then you can click the Reload Addins button to debug the DLL.

I have created a starter solution to support this type of development on BitBucket at:
https://bitbucket.org/pb_bwfc/fcell-calculation-engine-starter


No comments:

Post a Comment