excel prefix formula F#

Question

I have a simple Excel spreadsheet where each cell either contains nothing, a value, or a simple 2 argument formula of the form Function(Cell,Cell), (e.g. cell A3 might have the formula "=A1 + A2")

I would like to be able to open an Excel file using F# and for any cell get: 1 - null (because the cell has no value and no formula) 2 - decimal or string (for a value) 3 - a tuple of (string,string string) where the first value is the function (e.g. "+" and the 2nd and 3rd part of the tuple is the cell name (e.g. "A1" and "A2")

So: Get("A1") and Get("A2") would both return a double, but Get("A3") would return a tuple ("+","A1","A2")

How would I do this in F# ?

Answer 1

Once you get a reference to a worksheet object (after starting excel and opening the file with your data), you can use the Range member to get a specified cell. Then you can use Value2 property to get a value (e.g. string/double), which also returns the result of a formula. To get the formula, you can use Formula member.

I didn't find any good way for testing whether the value is automatically calculated or not, but you can check if the Formula value starts with the = character. If you really only need as simple formulas as the one you describe (without parentheses etc.) then a simple regular expression should do the trick:

let reg = new Regex("=([A-Z]+[0-9]+)([^A-Z0-9]+)([A-Z]+[0-9]+)")

let getInfo (cell:Range) =
  let formula = cell.Formula.ToString().Replace(" ", "")
  match formula.StartsWith("="), cell.Value2 with
  | true, _ -> 
      let g = reg.Match(formula).Groups
      Formula(g.Item(1),g.Item(2),g.Item(3))
  | :? float as n -> Float(n)
  | :? string as s -> String(s)
  | _ -> Empty

This returns a value of the following data type which covers all your cases:

type CellInfo = 
  | Empty
  | Float of float
  | String of string
  | Formula of string * string * string

BTW: To load an excel in F# Interactive, you need something like this:

#r "Microsoft.Office.Interop.Excel.dll"

// Run Excel as a visible application
let app = new ApplicationClass(Visible = true) 
// Create new file using the default template
let workbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet) 
// Get the first worksheet
let worksheet = (workbook.Worksheets.[1] :?> _Worksheet) 

Answer 2

This really gave me an idea and I tried using FsLex/FsYacc to process your Excel cells. It's maybe not exactly what you imagined, but it may be useful and it's also not very big. Basically, the code I wrote runs like this:

let teststrings = [ "1002";
                    "=SUM(B2:B12)";
                    "=A1+AVERAGE(B2:B4)";
                    "=B3+G7";
                    "=MIN(MEAN(A3:A20))";
                    "=A1+B4*C6"
                    ];

teststrings |> List.iter (fun s -> (convert s |> printfn "%A" s))

and the output looks like

"1002" "(SUM,(RANGE,B2,B12))" "(+,A1,(AVERAGE,(RANGE,B2,B4)))" "(+,B3,G7)" "(MIN,(MEAN,(RANGE,A3,A20)))" "(+,A1,(*,B4,C6))"

The whole thing is too big to put in this box, but have a look here.