How to treat large integers/numbers as strings so they don't roun

How to treat large integers/numbers as strings so they don't roun

Posted by: richard on 31 March 2025, 6:25 pm EST

• 

  richard

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 31 March 2025, 6:25 pm EST

  I’m building a UI that doesn’t leverage a lot of the spreadsheet capabilities, so I don’t need calculations and that kind of thing. But I DO need to store exactly what the user inputs, not an approximation of it.

  The reason being that some of these things will potentially quite big numbers like IDs where the scientific notation/rounding for 15+ digit numbers will not be a good solution.

  I found this thread
  
  https://developer.mescius.com/forums/spreadjs/why-its-auto-rounded-the-last-digit-in-a-over-15-degits-number
  
  which shows how to intercept a new cell value, but it’s already been interpreted as a javascript number at that point.

  Is there a way to either force intercept the input value as a string, like from the event directly or disable some of the type interpretation to force numbers to be strings?

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 1 April 2025, 5:12 am EST - Updated 1 April 2025, 5:17 am EST

  Hi,

  Regarding “The reason being that some of these things will potentially quite big numbers like IDs where the scientific notation/rounding for 15+ digit numbers will not be a good solution.”, this behavior occurs because JavaScript uses a 64-bit floating-point representation (IEEE 754) for numbers. It can safely represent integers only up to 15 digits. Beyond this, precision is lost, and the result might be displayed incorrectly as 0 or with rounding errors. Refer this link to know more: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Numbers_and_dates#numbers

  To handle large numbers and prevent rounding issues in JavaScript, you can store them as strings. Numbers with fewer than 15 digits can be stored as numbers, while those with more than 15 digits should be stored as strings accordingly.

  You can use the EditEnded event to check if the entered number exceeds the limit. If it does, apply the text formatter and then set the value again using the setValue method.

  Please refer to the attached code snippet, GIF “Steps.gif,” and sample for implementation.

  spread.bind(GC.Spread.Sheets.Events.EditEnded, function (sender, args) {
          if (isValidNumber(args.editingText) && !isPrecisionWithinLimit(args.editingText)) {
              args.sheet.setFormatter(args.row, args.col, "@");
              args.sheet.setValue(args.row, args.col, args.editingText.toString());
          }
      });

  GIF:

  Sample: https://jscodemine.mescius.io/share/oUuywBzQPk_o8oD2ueXK4Q/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  References:

  • setValue: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setvalue
  • setFormatter: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setformatter
  • EditEnded: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#editended

  Regards,
  
  Priyam

• 

  richard

  • Post Options:
  • Link
    Copy

  Posted 1 April 2025, 11:02 am EST

  Excellent. Thank you @priyam.kushwaha this was helpful and informative. I had assumed the cellValue changed was the right callback to use I didn’t find EditEnded

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 2 April 2025, 12:02 am EST

  Hi,

  We are happy that your query has been resolved. We are closing this case for now. Feel free to reopen it if you face any issues regarding this.

  You could always create a new ticket for new queries/issues.

  Regards,
  
  Priyam