## 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
```

```
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
```

`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 `decimal`

.`Languages`

is just an enumerator of 2 languages, as we would do in C#.## 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
```

`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
// 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).