Formulas with Excel Interop

Question

I use 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, it is too slow when there are 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, Excel now simply displays these rows in cells, rather than calculating the value of the formula. I also tried:

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

But this was confused with the target ranges of each formula. I'm not sure what is going on there. (Instead of leaving it as "a1:a10" , Excel translated the target ranges based on where the formula will be.)

Is there a better way to do this?

Refresh . I also tried using "= MIN ($ G $ 2: $ G $ 5)", but this gave the same effect when Excel just shows a line and doesn't evaluate the formula.

What is it worth when, if I enter a cell, delete and add any character to the formula again, when I press 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 displays them as rows instead of evaluating the formula.

Answer 1

Daniel's answer should work, but it’s easier for me to work with formulas in the R1C1 format. Your code should look like this:

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

This will generate a formula with absolute addresses. You can create relative addresses using R [vertical_offset] C [horizontal_offset]. Selecting an entire row or column is also simple: R1 is the whole first row, C4 is the full column "D".

R1C1 strings are easily simple using String.Format or sprintf in pure F #.

EDIT: I quickly checked vba, this will not work. Excel places the first element of the array in each cell of the range. In any case, the address point R1C1 remains valid. It works like a charm for header formulas (i.e. Ws.Range (headerRange) .FormulaR1C1 <- "= sum (R2C [0]: R10C [0]"), so I will leave the answer intact.

EDIT2: I think ArrayFormula is for an array formula , so it won't work either.

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

First, to put these lines in formulas, drag them into the object:

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

and then put it in FormulaR1C1:

 ws.Range(range).FormulaR1C1 <- formulas 

Secondly, the important part: in the above example, it is assumed that the range is the horizontal range of three cells (i.e. F3: H3). If you try to place it in a vertical range (for example, F3: F5), you will notice that this sum formula is repeated in each cell of the range. I am sure that in this case the formulas should be a two-dimensional array of objects, and the first line of the external array is an array of formulas, but I get SafeArrayTypeObjectExtension, trying to put it in a range. FormulaR1C1. Take a look at this msdn file :

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

I will consider this exception again.

Answer 2

Assuming your first approach works, and if you mean slow, you mean too slow type (don't do it), maybe it’s faster or 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 can get some ideas from this snippet at fsnip.net http://fssnip.net/aV , which implements some higher order functions for Excel. (This is a longer snapshot than using a spreadsheet tool, although the snippet really doesn't match the performance.)

Answer 4

If you cannot get around the performance issue in Excel, you may need SpreadsheetGear (http://www.spreadsheetgear.com/). It copies almost the entire Excel API, but can be much faster. (Another advantage is that if you are developing something to run on the server, Excel does not need to be installed on the server.)