Disabling the formula functionalities

[Thomas]

Hello,

I was looking for a way to disable the formula capabilities of the control is this possible?(e.g =SUM(A+B)).
This without the need to disable the cells.

Kind regards,

Thomas,

[Boryana]

Hello Thomas,

Thank you for contacting us.

A cell will display its literal content if its format is set to Text. The following snippet assigns the cell A1 in the active worksheet a formula value and sets its format to Text. As a result,  A1 will display =SUM(1, 2, 3) instead of 6. 

CellSelection cellA1 = this.radSpreadsheet.ActiveWorksheet.Cells[0, 0];
cellA1.SetValue("=SUM(1, 2, 3)");
cellA1.SetFormat(new CellValueFormat("@"));

If you would like to apply the Text format to all entered values in a worksheet, feel free to use the following sample code:

Worksheet activeWorksheet = this.radSpreadsheet.ActiveWorksheet;
activeWorksheet.Cells[activeWorksheet.UsedCellRange].SetFormat(new CellValueFormat("@"));

I hope you will find my answer helpful. Let me know if you have further questions.

Regards,
Boryana
the Telerik team

[Marcus]

Hello Boryana,

after a long time I want to reactivate this thread.

For text-cells I use your following hint:

cell.SetValue(wert);
cell.SetFormat(new CellValueFormat("@"));

This all works fine but some values for "wert" leads to an strange result:

If "wert" is "1900", "1900" will be displayed but if "wert" is "1900-01" then "1", if "1900-02" then "32", ..., "1900-06" then "153" are displayed. Although the cell is in text format, here the table seems to evaluate the number of days since 1899-31-12 (on a German Windows 10).

I am using version 2017.2.503.45 of the spreadsheet.dll

How can I prevent this behavior.

Thanks in advance

Marcus

 

[Anna]

Hello Marcus,

You can stop this behavior by setting the cell format before setting the value like so:

cell.SetFormat(new CellValueFormat("@"));
cell.SetValue(wert);

The reason why this works is that with this ordering when the time comes to parse the input, RadSpreadsheet will already know that the preferred result is a text value. Otherwise it recognizes a date and parses the value as such. Then when the text format is applied after the parsing, it strips the date format and the result is 1, 32 and 153.

Regards,
Anna
Progress Telerik

[Marcus]

Hello Anna,

thanks. Sometimes the solution is so simple.

Regards

Marcus