Excel.Worksheet.Cells [行，列] =" =式QUOT;与Range.set_Value（Missing.Value，arrayFormulas） [英] Excel.Worksheet.Cells[row,col] = "=Formula" vs. Range.set_Value(Missing.Value, arrayFormulas)

问题描述

Excel.Worksheet.Cells[row,col] = "=Formula / reference" 

尽管在上述的Excel更新公式/参考，并显示在数据表中的结果，在下面的代码，使用时 Range.set_Value（..）数据表未更新所有

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

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)

它仅示出了公式作为字符串，如 =表1！A1 。

我不能让它更新。既不符合 CalucalteAll（），也没有与 RefreshAll（），也不能与anyhing。任何建议如何实现数据表中的更新

I cannot make it update. Neither with CalucalteAll(), nor with RefreshAll(), nor with anyhing. Any suggestions how to achieve an update in the datasheet?

编辑：您可以设置一个语句整个数组 Range.set_Value（缺少。值，myFormulas）。我的问题是如何使Excel计算公式此数组中（而不是把他们当作简单字符串，或其中之一的Excel比重新计算。设置单元一个）？

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.)?

推荐答案

我发现rangeVariable.Formula = rangeVariable.Value将转化一个公式文本变成一个真正的Excel公式，这是针对在该范围内的所有单元格进行。

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.

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

希望这有助于

这篇关于Excel.Worksheet.Cells [行，列] =" =式QUOT;与Range.set_Value（Missing.Value，arrayFormulas）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！