This is a migrated thread and some comments may be shown as answers.

Small values get converted to scientific notation

3 Answers 121 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Joy
Top achievements
Rank 1
Joy asked on 18 Aug 2014, 03:31 PM
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.

3 Answers, 1 is accepted

Sort by
0
Anna
Telerik team
answered on 19 Aug 2014, 02:03 PM
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
 
Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
 
0
Joy
Top achievements
Rank 1
answered on 19 Aug 2014, 07:35 PM
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
0
Anna
Telerik team
answered on 20 Aug 2014, 08:35 AM
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
 
Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
 
Tags
Spreadsheet
Asked by
Joy
Top achievements
Rank 1
Answers by
Anna
Telerik team
Joy
Top achievements
Rank 1
Share this question
or