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:

Exclusive: New C# 5 features available in VS2010 SP1

I can exclusively reveal that the new async feature of C# 5 is already available in Visual Studio SP1!

The feature is hidden behind a menu little used in these times of austerity – New Project:

NewProject

Simply start a new F# project and a production version of the async feature is already available.

The documentation for the async feature has been cleverly hidden from C# users in the MSDN documentation under the F# language:

Async Workflows

Truth be told this feature has been available for Visual Studio users since way back in 2007 via a plug-in.

For those who have so far been left in the dark, you can think of F# as Microsoft code for C# + 3 versions, i.e.

  1. D# (+1)
  2. E# (+2)
  3. F# (+3)

That’s right, you’ve guessed it, if you are looking for the new features in C# 6, 7 and 8 they are actually available NOW!

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: