Issue with number rounds

Posted by: sergey_chizhik on 2 November 2021, 9:46 pm EST

  • Posted 2 November 2021, 9:46 pm EST

    Frontend - wijmo with pure js
    Backend - asp.net core 3.1 with GemBox.Spreadsheet package.

    Guys, how can we solve an issue with rounds?

    From DB we, for example, receive the number 0.00360000
    I need to receive as a result in the cell value 0.0036.
    Currently, I setup number format on the backend and usually receive correct data on the frontend (e.g. 0.0036), but when I enter something either from the formula bar or directly in the cell, for example, number 0.003601, I need to dynamically change cell format and receive number 0.003601, but actually, now it will be rounded to 0.0036.

    Help, please
  • Replied 2 November 2021, 9:56 pm EST

    And one important thing. To DB will be saved correct number (e.g. 0.003601)
  • Replied 2 November 2021, 10:10 pm EST

    Formulas also should works. It's very important.
  • Replied 8 November 2021, 1:25 am EST

    Hello,

    We apologize for the late response, but could you please provide the following information listed below in order for us to investigate and find the solution:

    1. which wijmo control you are using?
    2. could you also let us know how you have implemented the formula bar(mentioned above in your response)?
    3. have you implemented any formatting on the columns on the front-end? also, the property and method used in the implementation.

    If possible, please provide a small sample for the same, so that we can assist you accordingly.

    Regards
  • Replied 8 November 2021, 8:38 am EST

    Hi, Wijmo support,

    Brief code samples:

    @addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
    @addTagHelper *, C1.AspNetCore.Mvc
    @addTagHelper *, C1.AspNetCore.Mvc.FlexSheet

    =====

    Razor component:
    @*FlexSheet control using TagHelper*@
    <c1-flex-sheet class="flexSheet" id="flexSheet"
    show-errors="true"
    enable-drag-drop="true"
    remote-saved="onFileSaved"
    load-action-url="@Url.Action("RemoteBind")"
    save-action-url="@Url.Action("SaveData")"
    enable-formulas="true"
    item-validator="validateCell">
    <c1-formula-bar />
    </c1-flex-sheet>

    =====

    JS part:
    const flexSheet = wijmo.Control.getControl('#flexSheet');

    For formating on the Front-end side now we use nothing, but had several approaches like below:
    function formatNumberCell(row, col) {
    const cellValue = flexSheet.getCellValue(row, col);
    const cellStyle = flexSheet.selectedSheet.getCellStyle(row, col);
    const trimmedValue = cellValue.toString().substr(0, 10);
    const numbersAfterPoint = getDecimalFormat(trimmedValue);

    cellStyle.format = 'n' + numbersAfterPoint;
    flexSheet.setCellData(row, col, trimmedValue);
    }

    we tried to use a self-written function with native flexSheet.itemFormatter and flexSheet.cellEditEnded methods, but for the formula bar couldn't find an approach or some relative callbacks.

    We are looking for a way to allow users to input numbers "like is" without applying any rounds, trims, and so on.

    Several examples:
    B5 cell:
    1 . data from backend 0.00521, show in cell 0.00521
    2. User changed value in the cell B5 to 0.0052100001, expected output in the cell "as is", e.g. 0.0052100001
    3. User changed value in the formula bar for cell B5 from 0.0052100001 to 0.0052100, expected output 0.00521 (should trim trailing zeros)
    4. User decided to apply the formula for cell B5. For example, we would like to add several cells, Insert formula directly in the cell B5 (or from formula bar) value =A4+A5, where value for A4 - 1.123 and for A5 - 2.554564, EXPECTED output in the cell B5 -> 3.677564

    C# dependencies:

    c1.aspnetcore.mvc.flexsheet(3.0.20212.322)
    gembox.spreadsheet(47.0.1268)
    c1.aspnetcore.api(3.0.20212.302)
    c1.aspnetcore.mvc(3.0.20212.322)

    King regards
  • Replied 8 November 2021, 6:12 pm EST

    Hello,

    Thank you for providing the above information, To implement the above requirement, we may perform the following:

    * We may handle the CellEditEnding event of FlexSheet and may update the formatting at the given cell.

    * To get the unformatted entered value of a cell, we may use the activeEditor property of FlexSheet which gets the HTMLInputElement that represents the currently active cell editor.

    * After which, we may proceed to calculate the required formatting for the cell and may update the cell's formatting by using the applyCellsStyle() method of FlexSheet.

    Please refer to the sample below,
    FlexSheet_.zip

    Regards
    Dushyant Sharma
  • Replied 8 November 2021, 7:55 pm EST

    Hello,

    thanks for providing examples, but the result as you described we also already achieved. In your code samples, we also have the same issues.
    If we input some number in the formula bar, the related cell has a rounded result.
    Steps to reproduce:
    - input value 8,741.0845 to any cell (the result will be 8,741.0845, as expected)
    - input value 8,741.084598854 in the formula bar for the same cell (in the cell result will be rounded to 8,741.0846, but the expected result is 8,741.084598854)

    Do we have some events for changes in the formula bar, where we directly can get related cell and format?

    Regards,
    Serhii
  • Marked as Answer

    Replied 14 November 2021, 10:17 pm EST

    Hello,

    To implement the same requirement, when the value is entered through the formula bar, we may add a keydown event listener to the above element. after which, we may proceed to calculate and set the required formatting for the given cell.

    To set the new value, we may use the setCellData() method of FlexSheet and may update the value accordingly.

    Please refer to the updated sample below,
    FlexSheet_updated.zip

    Regards
    Dushyant Sharma
  • Replied 15 November 2021, 7:05 pm EST

    Hi Dushyant,
    Thank you, it was helpful

    Best regards,
    Serhii
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels