C# Excel.Worksheet.Cells[row,col] = "=Formula" vs. Range.set_Value(Missing.Value, arrayFormulas)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16400302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel.Worksheet.Cells[row,col] = "=Formula" vs. Range.set_Value(Missing.Value, arrayFormulas)
提问by HeinrichStack
Excel.Worksheet.Cells[row,col] = "=Formula / reference"
While in the above Excel updates the formula / reference and shows the result in the datasheet, in the code below, when using Range.set_Value(..)the datasheet is not updated at all
虽然在上面的 Excel 更新公式/引用并在数据表中显示结果，但在下面的代码中，使用Range.set_Value(..)数据表时根本没有更新
string[,] myFormulas = new string[nrRows, nrColumns];
...
myFormulas [x,y] = e.g. "=SUM(D1:D" + rowNr + ")";
myFormulas [x,y+1] = e.g. "=Table1!A1";
...
Range.set_Value(Missing.Value, myFormulas)
and it only shows the formula as a string, e.g. =Table1!A1.
它只将公式显示为字符串，例如=Table1!A1.
I cannot make it update. Neither with CalucalteAll(), nor with RefreshAll(), nor with anyhing. Any suggestions how to achieve an update in the datasheet?
我不能让它更新。既不带CalucalteAll()，也不带RefreshAll()，也不带任何东西。任何建议如何在数据表中实现更新？
EDIT : You can set an entire array with a single statement Range.set_Value(Missing.Value, myFormulas). My question is how to make excel evaluate the formulas in this array (and not treat them as simple strings, or setting the cells one by one which Excel than recalculates.)?
编辑：您可以使用单个语句设置整个数组Range.set_Value(Missing.Value, myFormulas)。我的问题是如何让 excel 评估这个数组中的公式（而不是将它们视为简单的字符串，或者将 Excel 重新计算的单元格一一设置。）？
采纳答案by PunkUnicorn
I found that rangeVariable.Formula = rangeVariable.Value will translate a 'formula as text' into a bona fide Excel formula, and this is done against all cells in that range.
我发现 rangeVariable.Formula = rangeVariable.Value 会将“作为文本的公式”转换为真正的 Excel 公式，这是针对该范围内的所有单元格完成的。
Worksheet sheetOne = ...
int numberOfRows = 5000;
// Make a massive range on sheet 1, then use cell assignment by array for fastness
Range range = sheetOne.Range["A1"];
string[,] links = new string[numberOfRows+1, 1];
range = range.Resize[numberOfRows+1, 1];
for (int count = 0; count < numberOfRows; count++)
{
// Build the =HYPERLINK formula to set as text in each cell
string worksheet = "Sheet2";
string cellRef = string.Format("A{0}", count + 1);
string formula = string.Format("=HYPERLINK(\"#{0}!{1}\", \"{2}\")", worksheet, cellRef, string.Format("Hyperlink number {0}", count));
links[count, 0] = formula;
}
//range.set_Item(Type.Missing, Type.Missing, links);
range.set_Value(Type.Missing, links) // thanks HeinrichStack
range.Formula = range.Value; //<--- Boom baby, all 'formula as text' turns into bona fide excel formula
Hope this helps
希望这可以帮助
回答by Nevyn
I dont think that there is any way to set the Formulavalue for any given cell, except by deliberately setting the Formulaproperty for it, one cell at a time.
我不认为有任何方法可以Formula为任何给定单元格设置值，除非故意Formula为它设置属性，一次一个单元格。
Check out the properties and examples here
在此处查看属性和示例
so I think what you need to do is something more like this:
所以我认为你需要做的是更像这样的事情：
Worksheet.Cells[x,y].Formula = "=SUM(D1:D" + rowNr + ")";
Worksheet.Cells[x,y+1].Formula = "=Table1!A1";
回答by Tuong Lan Nguyen
I had just met this problem a short while ago, and was searching nearly whole day for the solution but no luck.
不久前我刚刚遇到了这个问题，并且几乎一整天都在寻找解决方案，但没有运气。
And I finally found a solution when trying to fix another problem also relate to cell datatype, in which you should set your 2-dimension array to 'object' type instead of 'string' type. The reason of the problem, in my opinion, is the Formula properties (also Value, Value2) automatically write the data depends on datatype in code. The code should be something like this:
在尝试解决另一个与单元格数据类型相关的问题时，我终于找到了解决方案，您应该将二维数组设置为“对象”类型而不是“字符串”类型。问题的原因，在我看来，是公式属性（还有值，值2）自动写入数据取决于代码中的数据类型。代码应该是这样的：
Worksheet sampleSheet = ...;
object[,] dataArr = new object[rowNum,colNum];
for (int i = 0; i < rowNum; i+=1) {
for (int j = 0; j < colNum; j+=1) {
dataArr[i,j] = "=SUM(..<something here>..)";
}
}
sampleSheet.Range[....].Formula = dataArr;
Hope this help!
希望这有帮助！
