Excel automation: Problem with inserting an array of formulas into a range

[mariuskrogh]

Hi!

I'm creating a spreadsheet (in ASP.NET) where I insert the values via
arrays.
I do this because I need to insert several hundreds rows. Inserting
numbers works fine, but inserting formulas this way doesn't work.
Example from my code:

int[,] _data = new int[500,4];
// Then I do some stuff to fill the array with data
string[,] _formulas = new string[500,1];
for(int _row = 1;_row<=500;_row++)
_formulas[_row-1,0] = "=SUM(A"+_row.ToString()+""+_row.ToString()
+")";
// Now I have an array of integers and an array of strings which
contains my formulas

// First I insert my integers which works fine
Excel.Rang _range = _worksheet.get_Range("A1", _null);
_range = _range.get_Resize(_rowsCount, 4);
_range.set_Value(XlRangeValueDataType.xlRangeValueDefault, _data);

// Then I insert my formulas the same way, but it doesn't work as
expected
_range = _worksheet.get_Range("E1", _null);
_range = _range.get_Resize(_rowsCount, 1);
_range.set_Value(XlRangeValueDataType.xlRangeValueDefault, _formulas);

_range.Formula = _formulas; // This doesn't work either.


The problem is that when I open the spreadsheet the cells with the
formulas shown the formula and not the "calculated" formula, they show
"=SUM(A11)". The cell formats are all General. If I focus on the
cell content and hit enter the formula gets evaluated and shows
correct the number instead of the formula-text.
If I open the "Evaluate formula" dialog it shows this message: "The
cell currently being evaluated contains a constant".

If I loop through all the cells and set their individual formulas it
all works fine. But I don't want to do it like that - it takes much
more time on the server when I insert the values into the cells one by
one.

Can anyone explain to me why the formulas don't get evaluated but only
shows as "text"?

Regards,
Marius T. Krogh