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,
HeadersVisibility=DataGridHeadersVisibility.All)
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,
IsReadOnly=false,
Width=DataGridLength(64.0))
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
col
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.
Resources: