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
I am not an expert in F# (yet), so feel free to drop comments on how this can be done better or differently.
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) =
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
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
Currency instead of
decimal makes it easy to see what is the purpose of a variable. It takes away the guessing about what a variable holds. Technically, it is not different from
The gas volume can be expressed in either KWH or cubic meters. That is what we see in this data type. Again, using 2 different constructors make clear what we are dealing with.
Languages is just an enumerator of 2 languages, as we would do in C#.
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:
let ref = box "#REF!"
let prepareStr obj =
if obj = null then "null"
|> sprintf "\"%s\""
let combine s1 s2 = s1 + ";" + s2
let mapReadableProperties f (t: System.Type) =
|> Array.filter (fun p -> p.CanRead)
|> Array.map f
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
Let’s start with the
ToCsvString function. It almost says what it does:
- 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
let newOutputHeaders = newOutput |> List.head |> newOutputPropertyHeaders
let newOutputCsv = newOutput |> List.map newOutputPropertyValues
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-user is able to understand the F# code (with some explanation).