Formula shows as text in cell

FLX TMS FlexCel Studio for .NET
1

I have a formula that references two totals that are named ranges. When I open the spreadsheet, it displays the text of the formula in the cell. For example, = TotalRevenue - TotalExpenses. If I click on the cell, press F2 to edit and then Enter (without changing the contents), it will show the result of the fomula correctly.

 
Any thoughts as to why this happening and how to fix it?
 
//create the ranges

xl.SetNamedRange(new TXlsNamedRange("TotalRevenue", 1, 0), "=C10");

xl.SetNamedRange(new TXlsNamedRange("TotalExpenses", 1, 0), "=D10");

// create the formula


xl.XlBook.SetCellValue(10, 5, string.Format("= {0}-{1}", "TotalRevenue", "TotalExpenses"));

 

2

Hi,

FlexCel will enter any of the cell types Excel supports: Numbers, strings, errors, booleans or formulas, depending on the data type you pass to SetCellValue

For example:
xl.SetCellValue(10, 5, 1)
will enter the number 1 in the cell.
xl.SetCellValue(10,5,"1")
will enter the string "1" in the cell, which isn't the same. (the value of the cell for calculations will be 0, as with any string. The string "1" isn't different from the string "hello" to Excel)

Same way, If you enter
xl.XlBook.SetCellValue(10, 5, string.Format("= {0}-{1}", "TotalRevenue", "TotalExpenses"));
You will enter the string "=TotalRevenue-TotalExpenses", not the formula.

To enter formulas, you need to pass a TFormula object to SetCellValue:
xl.XlBook.SetCellValue(10, 5, new TFormula(string.Format("= {0}-{1}", "TotalRevenue", "TotalExpenses")));

If you want the excel behavior (that is automatically convert the string "1" to the number 1 or the string "=A1" to the formula "=A1"), then you can use
xl.SetCellFromString(...)

But normally I wouldn't recommend that, since SetCellFromString needs to "Guess" what the data type is from the data. For example, "1.2.3" might be Feb 2, 2003 in german dates, or jan 1, 2003, or jan 1 1903 depending on what you expect. So, whenever possible, just enter the right datatype with TCellValue. If your data is already converted to strings, then you need to use SetCellFromString, but if not (as in this case) you don't.

I mention SetCellFromString so you are aware for other cases.

A last note: You can see how to enter whatever you want by using Excel and APIMate (start menu->FlexCel->Tools->ApiMate). Enter the formula in Excel, save the file, open it in APIMate, and it will tell you exactly the code you need for the formula in both C# or VB.NET

Regards,
   Adrian.