.net - 公式与 Excel 互操作

Question

我正在使用 F# 和 Excel Interop 将数据输出到 Excel 电子表格。我的第一种方法是单独设置每个单元格:

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

不过这样在公式较多的时候就太慢了，所以我换了个数组:

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

或者

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

但是，现在 Excel 只是在单元格中显示这些字符串，而不是计算公式的值。我也试过:

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

但这与每个公式的目标范围混淆了。我不确定那里发生了什么。 (而不是将其保留为 "a1:a10" ，Excel 根据公式所在的位置转换目标范围。)

有一个更好的方法吗？

更新 :我也尝试使用“=MIN($G$2:$G$5)”，但这产生了相同的效果，Excel 只是显示字符串而不是计算公式。

对于它的值(value)，当我输入单元格，删除然后重新添加公式中的任何字符时，当我点击“输入”时，Excel 将对其进行评估。

更新 2 : 我还尝试了以下方法:

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

但我遇到了同样的问题:Excel 将它们呈现为字符串而不是计算公式。

Answer 1

Daniel 的回答应该有效，但我发现使用 R1C1 格式的公式更容易。要使用它，您的代码应如下所示:

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

这将生成具有绝对地址的公式。您可以使用 R[vertical_offset]C[horizo​​ntal_offset] 构建相对地址。选择整行或整列也很容易:R1 是整个第一行，C4 是整个“D”列。

在纯 F# 中使用 String.Format 或 sprintf 可以轻松构建 R1C1 地址。

编辑:我在 vba 中进行了快速检查，它不起作用。 Excel 将第一个数组元素放入范围内的每个单元格中。无论如何，R1C1 寻址点仍然有效。它就像标题公式的魅力一样(即 ws.Range(headerRange).FormulaR1C1 <- "=sum(R2C[0]:R10C[0]")，所以我会保持答案不变。

编辑2:我认为 ArrayFormula 适用于 array formulas ，所以它也不起作用。

EDIT3:我做了一些研究，找到了我认为不可能的事情的答案:)。

首先，将此字符串作为公式，将它们向上转换为对象:

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

然后把它放在FormulaR1C1中:

ws.Range(range).FormulaR1C1 <- formulas

第二，重要部分:上面的例子假设范围是三个单元格的水平范围(即 F3:H3)。如果您尝试将其放在垂直范围内(即 F3:F5)，您会注意到，该求和公式在范围内的每个单元格中都会重复。我敢打赌，在这种情况下，公式应该是二维对象数组，外部数组的第一行是公式数组，但是我在尝试将其放入 range.FormulaR1C1 时得到了 SafeArrayTypeObjectExtension。看看 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.

稍后我将再次查看此异常。