IntroductionThis 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# EditorTo 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:
Visual Studio ConfigurationVisual 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:
- Uses F# 3.0
- Uses .NET 4.5
- References the FCell libraries: FCell.ManagedXll and FCell.XlInterop
- References System.Drawing and System.Windows.Forms
- Generates new versions on each build
For 3 and 4, you need to add the references listed to give:
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 ()
Visual Studio - Build and DebugThe steps needed to build and debug a workbook are:
- Build the DLL
- Embed the latest version of the DLL in the workbook
- Embed any supporting DLLs into the workbook
- Open Excel and load the workbook
- Attach the debugger to Excel
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. WorkbookPackager.DeleteCustomizationAssemblies xlsm WorkbookPackager.DeleteReferencedAssemblies xlsm let dfol = argv. let toDictionary (dct : IDictionary<_, _>) : Dictionary<_, _> = Dictionary(dct) if argv. <> "" then let refs = (argv.).Split(',')|>Array.map(fun r -> r,(Path.Combine(dfol,r) |>File.ReadAllBytes))|>dict|>toDictionary WorkbookPackager.EmbedReferencedAssemblies(xlsm, refs) let cust = (argv.).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.
$(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:
I have created a starter solution to support this type of development on BitBucket at: