Why it's auto rounded the last digit in a over 15 degits number

Why it's auto rounded the last digit in a over 15 degits number

Posted by: nguyenvu.work on 21 February 2025, 12:00 am EST

• 

  nguyenvu.work

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 February 2025, 12:00 am EST

  • Hi! I got a case with a number that over 15 digits, it’s auto rounded at the digit “15”. I researched then know that is a limitation. But I try input “1234567891234567”:
  • In SpreadJS: 1234567891234570
  • In Excel : 1234567891234560
    
    =>What is the differences ? I need to explain to my client cause they base on excel’s standard.
  • Are there any way to resolve this? Basically I need the number are stable as I input and still deal with calculation and formula!
• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 21 February 2025, 6:46 am EST

  Hi,

  We can replicate the behavior you mentioned on our end. However, it is because of the technological differences between Microsoft Excel and SpreadJS. JavaScript can store numbers with lengths exceeding 15 digits, therefore the underlying value remains the same but the stripped display value is formatted like Excel.

  This behavior can be customized by handling the values when entering into SpreadJS cells and converting the value to the floor value if the digit length of the number exceeds 15. Please refer to the code snippet below which works according to this workaround and rounds off the value as in Microsoft Excel

  function handleLargeValues(sheet, row, col, value) {
    if (
      value !== "" &&
      value !== null &&
      value !== void 0 &&
      !isNaN(value) &&
      value.length > 15 &&
      value.indexOf(".") < 0
    ) {
      len = value.length;
      spread.suspendEvent();
      sheet.setValue(
        row,
        col,
        Math.floor(+value / Math.pow(10, len - 15)) * Math.pow(10, len - 15)
      );
      spread.resumeEvent();
    }
  }

  You can also refer to the attached code sample that uses the above code snippet and resolves the behavior difference (see below).

  Please feel free to reach out if you encounter any further issues or require additional guidance.

  Regards,

  Ankit

  RoundOff.zip

• 

  nguyenvu.work

  • Post Options:
  • Link
    Copy

  Posted 23 February 2025, 11:50 pm EST

  • Thank you so much! It helped!