Small values get converted to scientific notation

4 posts, 0 answers
  1. Joy
    Joy avatar
    5 posts
    Member since:
    May 2014

    Posted 18 Aug 2014 Link to this post

    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.

  2. Anna
    Admin
    Anna avatar
    99 posts

    Posted 19 Aug 2014 Link to this post

    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.
     
  3. UI for WPF is Visual Studio 2017 Ready
  4. Joy
    Joy avatar
    5 posts
    Member since:
    May 2014

    Posted 19 Aug 2014 in reply to Anna Link to this post

    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
  5. Anna
    Admin
    Anna avatar
    99 posts

    Posted 20 Aug 2014 Link to this post

    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.
     
Back to Top