Small values get converted to scientific notation

[Joy]

I am importing a blank .xlsx file in spreadsheet control to enter data and later read the data from ActiveWorksheet and save it somewhere.

The issue that I am facing is:  In the ActiveWorksheet cells, when entering a number that is less than .0001 changes the representation of that number to scientific notation in the cell. 
I would like to keep the format as entered, i.e. .00005 stays .00005 and not 5E05.

Would appreciate quick help with this.
Thanks.

[Anna]

Hi Joy,

Thank you for your question.

This behavior is consistent with the behavior of Excel, the difference being that for Excel the cut-off value is 0.000000001.

The option I can offer you would be to set a number format either to the entire area or to the affected values. If you would like to do the latter, you would need something along the lines of this:

private void Initialize()
{
    Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;
    worksheet.Cells.CellPropertyChanged += Cells_CellPropertyChanged;
}

private static bool isInUpdate = false;

private void Cells_CellPropertyChanged(object sender, CellPropertyChangedEventArgs e)
{
    Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;

    if (!isInUpdate && e.Property == CellPropertyDefinitions.ValueProperty)
    {
        isInUpdate = true;

        NumberCellValue value = worksheet.Cells[e.CellRange].GetValue().Value as NumberCellValue;

        if (value != null && value.Value < 0.0001)
        {
            worksheet.Cells[e.CellRange].SetFormat(new CellValueFormat("0.#########"));
        }

        isInUpdate = false;
    }
}

I hope this helps. If you have further concerns, please, let us know.


Regards,
Anna
Telerik

 

 

[Joy]

Thanks Anna for getting back to me.

This solutions works when I enter data in any cell and move on to next cell, but when I tried to edit the cell data that I just entered seconds ago, cell value gets converted back to scientific notation.
How can I keep the cell value as entered when cell goes in edit mode?
Please help.
Thanks

Joy

[Anna]

Hello,

The display of these values in edit mode is something which will have to be fixed on our side. I just included it in our backlog and I've also created an item in our feedback portal which you can track. It can be found by following this link.

Regards,
Anna
Telerik