Implementing the Excel Simulator in F#

Introduction

In my previous post, we talked about how to structure an Excel workbook to make it easy to perform changes. As a side effect, we now have a good idea of what is the data, and what is the functionality that the workbook implements. This allows us to replace the workbook by an equivalent F# program, which was our “hidden agenda” all along.

What we want to achieve:

• high-level: Use a set of input parameters to generate a data set of output records
• mid-level: Some calculations can already be done on the input parameters without the corresponding records in the database. We want to separate these out.
• low-level: We want to read data from the database to lookup some values, to filter the rows that we need and to transform them into the output records.
• We will need to implement some Excel functionality, for example the VLOOKUP function.

I am not an expert in F# (yet), so feel free to drop comments on how this can be done better or differently.

Attack Plan

There are 2 main ways to start this journey: top-down or bottom-up. Actually, there is also a third way in which we work top-down and bottom-up at the same time, to meet in the middle. This may be what we need here.

First Helper Functions (aka bottom-up)

let round100 (x:decimal) = // ROUND(i_ConsJr*r.NormalPricePerkWh/100;2)
    let y = System.Math.Round x 
    y / 100M

This is an easy function to start with: round a decimal on 2 digits after the decimal point. This function is used quite a few times, and it is very easy to write, so why not use it.

let n2z (x:Nullable<decimal>) =
    if x.HasValue then x.Value else 0M

let n2zi (x:Nullable<int>) =
    if x.HasValue then x.Value else 0

let n2b (x:Nullable<bool>) =
    if x.HasValue then x.Value else false

Another set of easy functions to cope with database NULL values. Very simple, but useful!

let rec LookupNotExact ls f v =
    match ls with
    | [x] -> x
    | h::t::s -> 
        if f t > v then h
        else LookupNotExact (t::s) f v
    | [] -> raise (OuterError("LookupNotExact over empty list"))

(* Tests for LookupNotExact
let testls1 = [1;2;3;4;5]
let res1_3 = LookupNotExact testls1 (fun x -> x) 3
let res1_5= LookupNotExact testls1 (fun x -> x) 7

let testls2 = [1;2;3;6;7]
let res2_3 = LookupNotExact testls2 (fun x -> x) 3
let res2b_3 = LookupNotExact testls2 (fun x -> x) 5
let res2_7 = LookupNotExact testls2 (fun x -> x) 7
let res2b_7 = LookupNotExact testls2 (fun x -> x) 9
*)

The LookupNotExact function mimics the behavior of the Excel VLookup function. It finds in a sorted list the first value that is greater than or equal to v. The nice thing is that this function can easily be tested using F# interactive. Just remove the comment from the tests, select the function with its tests and hit alt+enter. This will execute the selected code and display the results in the F# interactive window.

Some Data Structures

The next data structures serve only to make the code more readable. We could do without them just as easily. Some examples:

type Currency = decimal

type GasVolume =
    | KWH of decimal
    | M3 of decimal

type Languages = NL |FR

With these functions in place (and then some more boring ones), we can start to emulate the Excel formulas that we need. I’m not going into detail on this because I don’t want law suits.

Converting to CSV

In the end, the results are exported. We export the values as a CSV file, which can be easily read back into Excel (for validation purposes). This will involve some reflection, here is the code:

module Csv

let ref = box "#REF!"

// prepare a string for writing to CSV            
let prepareStr obj =
    if obj = null then "null"
    else
        obj.ToString()
            .Replace("\"","\"\"") // replace single with double quotes
            |> sprintf "\"%s\""   // surround with quotes

let combine s1 s2 = s1 + ";" + s2   // used for reducing

let mapReadableProperties f (t: System.Type) =
    t.GetProperties() 
        |> Array.filter (fun p -> p.CanRead)
        |> Array.map f
        |> Array.toList

let getPropertyvalues x =
    let t = x.GetType()
    t   |> mapReadableProperties (fun p -> 
            let v = p.GetValue(x)
            if v = null then ref else v
            )

let getPropertyheaders (t: System.Type) =
    t   |> mapReadableProperties (fun p -> p.Name)
        |> Seq.map prepareStr
        |> Seq.reduce combine

let getNoneValues (t: System.Type) =
    t   |> mapReadableProperties (fun p -> ref)

let toCsvString x =
    x |> getPropertyvalues
      |> Seq.map prepareStr
      |> Seq.reduce combine

• Get the property values from x (which is the part using reflection).
• Each property value is mapped to a good CSV value (if it contains a double quote, then the double quote will be doubled, surround the value by double quotes)
• Everything is combined in a comma-separated string using the Seq.reduce method.

The other functions are quite easy to understand as well.

The Actual Program

let main  = 
    let newOutput = Calculate myInput

    // output
    let newOutputHeaders = newOutput |> List.head |> newOutputPropertyHeaders
    let newOutputCsv = newOutput |> List.map newOutputPropertyValues

    System.IO.File.WriteAllLines(@"C:\temp\NewOutput.csv", 
	newOutputHeaders :: newOutputCsv);

    printfn "Find the output in %s" @"C:\temp\NewOutput.csv"

    printfn "Press enter to terminate..."
    Console.ReadLine() |> ignore

The program is composed of some simple statements that use the functions that we previously described. This makes the program very easy to read. Not much explanation is needed, but here goes:

• newOutput will contain the result of the calculations using the input. This is the main purpose of the program. If this were implemented as a service, newOutput would be returned and that’s it.
• For debugging purposes, we output this as a CSV file, using the functions in the Csv module.

Conclusion

Writing this simulation as an F# program was not too hard. Immutability is baked into the FP paradigm, which was perfect for this case. So you could say that this is a nice match.

The Excel workbook itself is quite complex (and big). It is hard to maintain and to extend. The F# code on the other hand is quite readable. A nice (and unexpected) side-effect is that now we understand much better what goes on in the Excel, which helps us to maintain the Excel for as long as it is still used. Another nice thing is that the (non-technical) end-user is able to understand the F# code (with some explanation).