FCell Calculation Engine is a runtime component which can be used to programmatically build Excel-like, cell and formula based models.

The Calculation Engine is a pure .NET component and can be used in and out of Excel, e.g. in F# Interactive. The API is C# and VB.NET friendly.

It is very similar in concept to Excel calculation engine: you create cells, set their values or formulas which depend on values in other cells. Cells with formulas are manually or automatically recalculated in the correct order, e.g. if cell A depends via its formula on cell B then B will be recalculated first.

Let us see a simple example:

F# Copy imageCopy
open FCell.ManagedXll

let cell1 = Cell<int>.Create()
let cell2 = Cell<int>.Create()
cell1.SetValue(1)
cell2.SetFormula(cell1, fun x -> x + 1)
cell2.AsObservable.Add(fun x -> printfn "%i" x)
CalcSpace.Default.Calc()
CalcSpace.Default.SetAutoCalc(true)

We have created 2 cells of type "int". Initially they do not hold any values. We then set a value in cell1. We want cell2 to depend on cell1 so that its value is always equal to the value of cell1 plus 1.

Creating cells and setting their values and formulas is a non blocking operation in FCell Calculation Engine and can be done safely on any thread.

Each cell can be exposed as IObservable<'T> so that you get notified when a new value has been set or recalculated.

Finally, all cells live in a CalcSpace. When not running in Excel the default CalcSpace is static CalcSpace.Default.

By default CalcSpace is set to manual recalculation and you have to call a non blocking thread safe calcSpace.Calc() to start recalculation.

You can also set CalcSpace recalculation mode with non blocking thread safe call to calcSpace.SetAutoCalc

In the following sections we will have a closer look at the Calculation Engine API.

Creating cells

The main type in the component is a generic Cell<'T>. Cells can be created with one of the following Cell<'T>.Create overloads:

  • Cell<'T>.Create() creates an empty cell.

  • Cell<'T>.Create(initValue : 'T) creates a cell with initial value.

  • Cell<'T>.Create(initValue : 'T, isVolatile : bool) creates a cell with initial value and given volatility. Volatile cells are always recalculated.

  • Cell<'T>.Create(initValue : 'T, isVolatile : bool, label : string) creates a cell with initial value, given volatility and label. Labels are only used for error reporting, e.g. cycle detection.

  • Cell<'T>.Create(initValue : 'T, isVolatile : bool, label : string, equals : 'T -> 'T -> bool) creates a cell with initial value, given volatility, label and custom equality. The equality is used when setting a new value to the cell or after recalculation: if the new value "equals" the old value then cells which depend on this one are not made "dirty" and recalculated.

  • Cell<'T>.Create(initValue : 'T, isVolatile : bool, label : string, equals : IEqualityComparer<'T>) is similar to above

By default, cells are non volatile, have empty label and F# Unchecked.equals is used for equality.

Getting and setting cell values

Internally, each cell holds a value of F# Choice<Option<'T>, Exception>. You can set cell value with SetValue method which has 2 overloads:

  • SetValue(x : 'T)

  • SetValue(x : Choice<Option<'T>, Exception>)

Calls to SetValue are non blocking and thread safe.

You can get cell value synchronously and asynchronously:

  • cell.Value will block if cells are being recalculated and return synchronously current value or throw exception if value does not exist.

  • cell.ValueChoice will block if cells are being recalculated and return synchronously current value as F# Choice<Option<'T>, Exception>

  • cell.AsObservable will expose the cell as IObservable<'T> so that you get notified when new values are set or recalculated

  • cell.OnNewValue will expose the cell as IEvent<Choice<Option<'T>, Exception>> so that you get notified whenever cell value changes, including when it is set to None or Exception

Setting cell formulas

In order to create a model we need to introduce dependencies between cells with formulas. You can set cell formula with Cell.SetFormula which has many overloads:

  • cell.SetFormula<'T>(formula : Func<'T>): formula simply returns a value of 'T when cell is recalculated, does not depend on other cells

  • cell.SetFormula<'T,'S>(cell : Cell<'S>, formula : Func<'S, 'T>): depends on 1 cell, when recalculated formula is applied to the input cell's value

  • cell.SetFormula<'T, 'S1, 'S2>(cell1 : Cell<'S1>, cell2 : Cell<'S2>, formula : 'S1 * 'S2 -> 'T)

  • cell.SetFormula<'T, 'S1, 'S2>(cell1 : Cell<'S1>, cell2 : Cell<'S2>, formula : Func<'S1, 'S2, 'T>)

    There are similar overloads for up to 8 input cells. For a sequence of cells of the same type you can use this overload:

    cell.SetFormula<'T, 'U>(cells : seq<Cell<'U>>, formula : seq<'U> -> 'T)

In addition you can create cells which recalculate asynchronously:

  • cell.SetFormula<'T>(formula : Async<'T>)

  • cell.SetFormula<'T>(formula : IEvent<'T>)

  • cell.SetFormula<'T>(formula : IObservable<'T>)

  • cell.SetFormula<'T,'S>(cell : Cell<'S>, formula : Func<'S, Async<'T>>)

  • cell.SetFormula<'T,'S>(cell : Cell<'S>, formula : Func<'S, IEvent<'T>>)

  • cell.SetFormula<'T,'S>(cell : Cell<'S>, formula : Func<'S, IObservable<'T>>)

When async operation produces a value of 'T then cell.SetValue is automatically called for that value.

This is safe because SetValue is a non blocking thread safe operation.

Async formula can be cancelled with cell.CancelAsync()

CalcSpaces

All cells are automatically created in a CalcSpace. By default, if a cell is created by code running outside Excel then a static default CalcSpace is used automatically.

If you create the cell while running within Excel process with ActiveWorkbook then a special CalcSpace is created and used for that workbook only.

You can also explicitly create a cell in a given CalcSpace:

F# Copy imageCopy
open FCell.ManagedXll

let calcSpace = new CalcSpace(autoCalc = true, canCancel = true) // automatic recalculation and recalculation can be cancelled
let cell = Cell<float>.Create(calcSpace) // all other Cell.Create overloads also accept CalcSpace as last argument
calcSpace.Calc()
calcSpace.CancelCalc()

By default, CalcSpace has manual recalculation and its recalculation can be cancelled.

If CalcSpace has automatic recalculation then recalculation will happen on each cell value or formula change, taking into account custom equality.

Otherwise you can call calcSpace.Calc() to force recalculation. This is a non blocking thread safe operation.

You can also switch between auto and manual recalculation by calling calcSpace.SetAutoCalc(false) or calcSpace.SetAutoCalc(true)

Recalculation can normally be cancelled with non blocking calcSpace.CancelCalc()

For performance reasons you can switch off the ability to cancel recalculation with non blocking calcSpace.SetCanCancel(false)

Finally, there are 4 CalcSpace events: OnError, CalcCancelled, CalcStarted and CalcFinished.

Integration with Excel

FCell Calculation Engine can be used in Excel, replacing partially or completely formulas in your spreadsheets.

When running in Excel, cells are created in a CalcSpace associated with ActiveWorkbook. This way each FCell workbook customization can use its own CalcSpace.

You can bind cells in a calcSpace to regions in Excel workbook, e.g. run this code in FCell embeded F# Interactive:

F# Copy imageCopy
open FCell.ManagedXll

let cell1 = Cell<int>.Create()
let cell2 = Cell<int>.Create()
let cell1Subscription = ThisWorkbook.SubscribeCell(cell1, "Sheet1", "A1") // returns IDisposable
cell2.SetFormula(cell1, fun x -> x + 1)
cell2.AsObservable.Add(fun x -> printfn "%i" x)

With ThisWorkbook.SubscribeCell you bind your Calculation Engine cell to a region in Excel. When you enter a new value in that region your cell will update its value as well.

For this to work we need a conversion from Excel data to the type of our cell. FCell will use the same type conversion system here as that used in UDFs.

Calls to SubscribeCell return IDisposable which allows you to unsubscribe the cell easily.

SubscribeCell will also initialize the cell with current data in specified Excel region, if possible.

There are overloads which allow you to specify Excel region in different styles and pass custom type converter assembly explicitly:

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, sheetName : string, rangeA1Ref : string) : IDisposable

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, sheetName : string, rangeA1Ref : string, converterAssembly : Assembly) : IDisposable

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int) : IDisposable

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly) : IDisposable

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, xlName : string) : IDisposable

  • ThisWorkbook.SubscribeCell(cell : Cell<'T>, xlName : string, converterAssembly : Assembly) : IDisposable

You can also pass Excel workbook as parameter:

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, sheetName : string, rangeA1Ref : string) : IDisposable

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, sheetName : string, rangeA1Ref : string, converterAssembly : Assembly) : IDisposable

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int) : IDisposable

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, sheetName : string, firstRow : int, firstCol : int, lastRow : int, lastCol : int, converterAssembly : Assembly) : IDisposable

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, xlName : string) : IDisposable

  • ThisExcel.SubscribeCell(wbk : Workbook, cell : Cell<'T>, xlName : string, converterAssembly : Assembly) : IDisposable

Note

See Chapter about ThisExcel and ThisWorkbook for more details about interacting with Excel and ActiveWorkbook.

Showing output cell values in Excel is also easy. Each Calculation Engine cell is IObservable<'T> and from the Chapter about ThisExcel and ThisWorkbook we know how to send generic values of 'T to Excel from any thread:

F# Copy imageCopy
open FCell.ManagedXll

let cell1 = Cell<int>.Create()
let cell2 = Cell<int>.Create()
let cell1Subscription = ThisWorkbook.SubscribeCell(cell1, "Sheet1", "A1") // returns IDisposable
cell2.SetFormula(cell1, fun x -> x + 1)
cell2.AsObservable.Add(fun x -> ThisWorkbook.SetValue(x, "Sheet1", "A2"))

Alternatively, we can integrate our Calculation Engine cells with Excel via FCell UDFs:

F# Copy imageCopy
namespace Test
open FCell.ManagedXll

module UDFs =
    let setCell1(x : int) =
        cell1.SetValue(x)
        x

    let getCell2() = cell2.AsObservable

FCell UDFs use the same type conversion system and can expose IObservable<'T> so it is easy to create UDFs for getting and setting Calculation Engine cells.

Note

Use [XlWorkbookOpen] marked static method to create cells, set formulas and bind to Excel ranges. This method runs on Excel workbook open and each code editor Build.

[XlWorkbookClose] marked static method can be used for any cleanup. On each code editor Build the workbook calcSpace is cleared automatically.

Finally, you have programmatic and FCell Ribbon based access to the CalcSpace associated with Excel ActiveWorkbook:

F# Copy imageCopy
open FCell.ManagedXll
ThisWorkbook.Calc()
ThisWorkbook.CancelCalc()
ThisWorkbook.SetAutoCalc(true)
FCell Menu

Cell arrays

CellArray<'T> is a convenience type which wraps Cell<'T> array.

For each Cell.Create there is a corresponding CellArray<'T>.Create which takes the length of the cell array or array of initial values as first argument, e.g.

F# Copy imageCopy
open FCell.ManagedXll
let cellArray1 = CellArray<float>.Create(100) // create 100 empty cells
let cellArray2 = CellArray<float>.Create(100, 1.1) // create 100 cells with init value 1.1
let cellArray3 = CellArray<float>.Create( [|1.0;2.0;3.0|] ) // create 3 cells with initial values 1.0 2.0 and 3.0

Similar to Cell<'T>, getting cell array values can be done with cellArray.Value and cellArray.ValueChoice and you can also expose cell array as IObservable<'T[]>

CellArray.SetValue can accept a scalar, which will set each cell in the array to that value, or an array of new values.

For each Cell.SetFormula there is a corresponding method which takes CellArrays as input and a function which is applied elementwise, e.g.

F# Copy imageCopy
open FCell.ManagedXll
let cellArray1 = CellArray<float>.Create(100) 
let cellArray2 = CellArray<float>.Create(100)
cellArray2.SetFormula(cellArray1, fun x -> x + 1) // sets the formula elementwise to each cell in cellArray2

One interesting overload of CellArray.SetFormula takes only a function 'T -> 'T, e.g.

F# Copy imageCopy
open FCell.ManagedXll
let cellArray1 = CellArray<int>.Create(100) 
cellArray1.SetFormula(fun x -> x + 1) // sets the formula recursively so that cell at position i depends on cell at position i - 1, i = 1,2...

You can get individual cells or parts of CellArray with indexers and slicing:

F# Copy imageCopy
open FCell.ManagedXll
let cellArray1 = CellArray<int>.Create(100) 
let cell = cellArray1.[3] 
let slice = cellArray.[3..6] // returns new CellArray which wraps 4 cells