Formulas with Excel Interop

Question

I'm using F# and Excel Interop to output data to an Excel spreadsheet. My first approach was to individually set each cell:

worksheet.Range(range1).Value2 <- "=sum(a1:a10)"
worksheet.Range(range2).Value2 <- "=min(a1:a10)"
worksheet.Range(range3).Value2 <- "=max(a1:a10)"
(* etc *)

However, this is too slow when there is a large number of formulas, so I switched to an array:

worksheet.Range(range).Value2 <- [| "=sum(a1:a10)"
                                    "=min(a1:a10)"
                                    "=max(a1:a10)" |]

or

worksheet.Range(range).Formula <- [| "=sum(a1:a10)"
                                     "=min(a1:a10)"
                                     "=max(a1:a10)" |]

However, now Excel just displays those strings in the cells, instead of calculating the value of the formula. I also tried:

worksheet.Range(range).FormulaArray <- [| "=sum(a1:a10)"
                                          "=min(a1:a10)"
                                          "=max(a1:a10)" |]

But that messed with the target ranges of each formula. I'm not sure what's going on there. (Instead of leaving it as "a1:a10", Excel translated the target ranges based on where the formula would be.)

Is there a better way to do this?

Update: I also tried using "=MIN($G$2:$G$5)", but that produced the same effect, where Excel is just displaying the string and not evaluating the formula.

For what it's worth, when if I enter the cell, erase and then re-add any character in the formula, when I hit "enter", Excel will evaluate it.

Update 2: I also tried the following:

worksheet.Range(range).FormulaR1C1 <- [| "=sum(R2C[0]:R4C[0])"
                                          "=average(R2C[0]:R4C[0])"
                                          "=min(R2C[0]:R4C[0])"
                                          "=max(R2C[0]:R4C[0])" |]

But I get the same problem: Excel renders them as strings instead of evaluating the formula.

Answer 1

Daniel's answer should work, but I find it easier to work with formulas in R1C1 format. Your code should look like this to use it:

ws.Range(range).FormulaR1C1 <- [| "=sum(R1C1:R10C1)";
                                  "=min(R1C1:R10C1)"; 
                                  "=max(R1C1:R10C1)" |]

This will generate formula with absolute addresses. You can build relative addresses by using R[vertical_offset]C[horizontal_offset]. Selecting whole row or column is easy too: R1 is whole first row, C4 is whole "D" column.

Building R1C1 adresses is easy with String.Format or sprintf in pure F#.

EDIT: i made a quick check in vba, it won't work. Excel is putting first array element in each cell in range. Anyway, R1C1 addressing point is still valid. It works like a charm for header formulas (ie. ws.Range(headerRange).FormulaR1C1 <- "=sum(R2C[0]:R10C[0]"), so i'll leave the answer intact.

EDIT2: i think that ArrayFormula is for array formulas, so it won't work either.

EDIT3: I've done some research and found an answer to something that I thought was impossible :).

Firstly, to put this strings as formulas, upcast them to object:

let formulas : obj[] = [| "=sum(R1C1:R10C1)";
                          "=min(R1C1:R10C1)"; 
                          "=max(R1C1:R10C1)" |]

and then put it in FormulaR1C1:

ws.Range(range).FormulaR1C1 <- formulas

Second, important part: above example assumes that range is horizontal range of three cells (ie. F3:H3). If you try to put it in vertical range (ie. F3:F5) you'll notice, that sum formula repeats in every cell in range. I bet that in this case formulas should be twodimensional object array, with first row of external array being array of formulas, but i'm getting SafeArrayTypeObjectExtension while trying to put it into range.FormulaR1C1. Take a look at this msdn docs:

If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array.

I'll look again at this exception later.

Answer 2

Assuming your first approach works and by slow you mean too slow to type (not execute), it may be quicker/easier to update a bunch of cells like this

[
  range1, "=sum(a1:a10)"
  range2, "=min(a1:a10)"
  range3, "=max(a1:a10)"
  ...
]
|> List.iter (fun (range, value) -> worksheet.Range(range).Value2 <- value)

Answer 3

Alternatively you might get some ideas from this snippet on fsnip.net http://fssnip.net/aV, which implements some higher order functions for Excel. (This is a longer shot performance wise than using Spreadsheet gear though as the snippet isn't really about performance.)

Answer 4

If you can't get round your performance issue within Excel, you might want to consider SpreadsheetGear (http://www.spreadsheetgear.com/). It replicates virtually the whole Excel API but can be much faster. (Another advantage - if you are developing something to run on a server, Excel doesn't have to be installed on the server.)