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 have now 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 different.
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
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
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 or equal than 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 easy. Some examples:
type Currency = decimal type GasVolume = | KWH of decimal | M3 of decimal type Languages = NL |FR
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-separates 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.
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-used is able to understand the F# code (with some explanation).