Phillip Trelford's Array

POKE 36879,255

Units in Cells

Last year, inspired by F#’s built-in compile-time Units of Measure feature I developed a small run-time library for units.

Last week I created a short F# spreadsheet script, harvesting code from the open source project Cellz. The script is hosted on the F# Snippets site and can be run inside your browser using the Try F# Silverlight-based web app.

Over the last few days I’ve put the 2 together. The Silverlight application below demonstrates the spreadsheet script and the run-time units library combined.

 

Inside the spreadsheet units can be appended to numbers, e.g.

  • = 1m
  • = 3m^2/s
  • = 5kW/h

Type signatures:

type UnitType =
  | Empty
  | Unit of string * int
  | CompositeUnit of UnitType list
  with
    override ToString : unit -> string
    static member ( + ) : lhs:UnitType * rhs:UnitType -> UnitType
    static member ( / ) : lhs:UnitType * rhs:UnitType -> UnitType
    static member ( * ) : lhs:UnitType * rhs:UnitType -> UnitType
    static member ( * ) : v:ValueType * u:UnitType -> UnitValue
  end
and ValueType = decimal
and UnitValue =
  class
    interface IComparable
    new : v:ValueType -> UnitValue
    new : v:ValueType * s:string -> UnitValue
    new : v:ValueType * u:UnitType -> UnitValue
    override Equals : that:obj -> bool
    override GetHashCode : unit -> int
    override ToString : unit -> string
    member Unit : UnitType
    member Value : ValueType
    static member One : UnitValue
    static member Pow : lhs:UnitValue * rhs:UnitValue -> UnitValue
    static member ( + ) : lhs:UnitValue * rhs:UnitValue -> UnitValue
    static member ( / ) : lhs:UnitValue * rhs:UnitValue -> UnitValue
    static member ( / ) : lhs:UnitValue * rhs:ValueType -> UnitValue
    static member ( / ) : v:UnitValue * u:UnitType -> UnitValue
    static member ( * ) : lhs:UnitValue * rhs:UnitValue -> UnitValue
    static member ( * ) : lhs:UnitValue * rhs:ValueType -> UnitValue
    static member ( * ) : v:UnitValue * u:UnitType -> UnitValue
    static member ( - ) : lhs:UnitValue * rhs:UnitValue -> UnitValue
    static member ( ~- ) : v:UnitValue -> UnitValue
  end

Resources:

Cellz

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:

Concurrency for Cats

F# asynchronous programming has been instrumental in taming concurrency while building a Silverlight 4 Trading Application, written with both C# and F#.

To learn more, PLEASE VOTE for my MIX11 open session proposal:

Simplifying Silverlight Concurrency with F# Asynchronous Programming

Phillip Trelford

In Silverlight communication is asynchronous to avoid blocking the UI thread of the browser. Writing asynchronous code in C# or VB.Net can be complex and error prone. F# is a powerful new programming language available in Visual Studio 2010 and compatible with Silverlight. F# has a number of built-in features which greatly help simplify asynchronous programming. With F# Asynchronous Workflows communication code can be written in a more natural and concise synchronous style then executed asynchronously with the compiler doing the hard work. F# Agents provide Erlang style message parsing, which make it easy to write reactive applications that receive inputs from various sources in parallel. This talk will include plenty of live code examples, and draw on recent experience of building a Silverlight based Trading Application.

UPDATE! The results are now in at http://live.visitmix.com/OpenCall. And I will be attending MIX 2011, but unfortunately not speaking, thanks all the same for everyone who voted.

If you are still interested in the talk I have proposed it for DDD Scotland where again it can be voted for (or not).