Phillip Trelford's Array

POKE 36879,255


Cellz is an Open Source functional .Net Silverlight Spreadsheet application written in F# and published on CodePlex.

It is part inspired by the last chapter of Martin Odersky’s excellent Programming in Scala book where he develops a simple Spreadsheet implementation in a couple of hundred lines. As per Scala, F# can excel when building a Spreadsheet app too. Parser combinators can be used to parse formulas and .Net events to propagate changes back to dependent cells. Silverlight’s built-in DataGrid control can be used for displaying and editing a sheet:

The grid supports decimal and string literals, cell references as well as formulas with operators and functions e.g.

  • Hello World
  • 1234.5
  • = 1 + 1
  • = A1
  • = SUM(A1:E1)

Techie bit


The View type binds a DataGrid to a sheet of cells using different templates for viewing and editing modes:

type View(sheet:Sheet) as view = 
    inherit UserControl()
    let grid = DataGrid(AutoGenerateColumns=false,
    do  grid.LoadingRow.Add (fun e ->
            let row = e.Row.DataContext :?> Row
            e.Row.Header <- row.Index
    do  view.Content <- grid
    let createColumn i =
        let header = colIndex.toColName i
        let col = DataGridTemplateColumn(Header=header,
        let path = sprintf "Cells.[%d]" i
        col.CellTemplate <- 
            sprintf "<TextBlock Text='{Binding %s.Value}'/>" path 
            |> toDataTemplate
        col.CellEditingTemplate <- 
            sprintf "<TextBox Text='{Binding %s.Data,Mode=TwoWay}'/>" path
            |> toDataTemplate
    do  for i = 0 to sheet.ColumnCount-1 do createColumn i |> grid.Columns.Add
    do  grid.ItemsSource <- sheet.Rows

The sheet type simply exposes rows of cells:

type Sheet (colCount,rowCount) as sheet =
    let rows =  
        [|for i = 0 to rowCount-1 do 
            let cells = [|for i=0 to colCount-1 do yield Cell()|]
            yield Row(RowIndex(i),cells)|]
    member sheet.Rows = rows

The cell type exposes a Data property for the formula while editing and a Value property for display:

type Cell () as cell =
    inherit ObservableObject()
    let mutable expr = expr.empty
    let mutable formula = ""
    let mutable value = value.empty
    let updated = Event<_>()
    let update newValue generation =
        value <- newValue
        cell.NotifyPropertyChanged <@ cell.Value @>
        updated.Trigger generation
    let eval () =
        try expr.Evaluate() with
        e -> String "N/A"
    member cell.Data
        with get () = formula
        and set value =
            formula <- value
            expr <- try parse value 
                    with e -> Value(String "N/A")
            cell.NotifyPropertyChanged <@ cell.Data @>
            let newValue = eval ()
            update newValue 0
    member cell.Value
        with get () = value
        and set newValue = update newValue 0

All the source code is available on CodePlex.


Comments (2) -

  • Paul Cechner

    4/3/2011 5:33:20 PM |

    Hey man, I've decided to take the plunge and libraries like this really help beginners like me on the way.

    Keep up the good work bro

  • Paul Cechner

    4/3/2011 5:36:10 PM |

    (now you just have to convince Jon to offer up a free beginners version of his visualisation libraries)

Pingbacks and trackbacks (3)+

Comments are closed