Assign Formula to Cell

FLX TMS FlexCel for VCL & FMX
1

Hope someone could help on this.


On my C# code, I have one line like this:

xls.SetCellValue(17, 9, "=SUM(H21:H33)");

When I open the generate Excel file. The cell shows exactly like this: =SUM(H21:H33)

I have to double click on the cell to get the value display.

Any help on this?
2
As far as I remember from TMS-Day-Training-Session (which was very interesting btw) it must be something like

xls.SetCellValue(17,9, TFormula.Create('=SUM(H21:H33');

3

Sorry - some brackets are missing - but I guess you get it...

4

Hi,

Thank Thomas for the answer! Indeed the correct code would bexls.SetCellValue(17,9, TFormula.Create('=SUM(H21:H33)'));

But I just wanted to add also that FlexCel has 2 different methods for entering values:

1)SetCellValue. This will enter whatever type you have in the parameter. So
xls.SetCellValue(1, 1, 7)  will enter the number 7 into the file, while
xls.SetCellValue(1, 1, '7') will enter the string "7" into the cell (which is not the same).

Same way, xls.SetCellValue(1, 1, '=A2') will enter the string A2, while xls.SetCellValue(1, 1, TFormula.Create('=A2')) will enter the formula.

SetCellValue is the preferred method to enter data when you know the type of the data, it is the fastest, and it gives you complete control over what you enter in the cell.

2)SetCellFromString: This method tries to convert a string into the "most likely thing you are actually trying to enter". So,
xls.SetCellFromString(1, 1, '1') will enter the number 1, not the string "1".
xls.SetCellFromString(1, 1, '=SUM(H21:H33)');  will enter the formula, not the string.

SetCellFromString is slower and not as reliable as SetCellValue since it has to "guess" what you are trying to actually say. But it behaves more like Excel itself when you enter data into cells or through VBA, and if your data is already in strings, then it is probably the best choice. If you know the type of the data, then SetCellValue is better.