Cell formatting have

Cell formatting have

Posted by: scrw on 8 October 2024, 12:48 am EST

• 

  scrw

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 October 2024, 12:48 am EST - Updated 8 October 2024, 12:57 am EST

  My project has the below requirements:
  
  Enter a number with up to 20 decimal places and 10 digits.
  
  The integer part will be formatted with a thousand separator (‘,’).
  
  Extra zeros in the decimal part will be removed (e.g., 12.90 → 12.9). I currently have the following formatter for my cell: ##,###,###,###,###,###,###.##########.
  
  And here are a few cases that occur: (unformatted value → formatted value)

  1200 → 1200. (I think the dot character shouldn’t be there)
  
  9999999999999999.9999999999 → 10,000,000,000,000,000 (Numbers with a length greater than 16 get rounded when displayed). For editable cells, when I double-click, the correct value is still there, but the display is incorrect
  
  Is there any way to display exactly the value what I enter?
  
  I hope to find a solution for this problem. Thank you.
  
  The below is my testing:
  
  

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 8 October 2024, 11:34 pm EST - Updated 8 October 2024, 11:40 pm EST

  Hi,

  As per my understanding, you want a formatter that displays up to 20 digits with 10 decimal places, uses a comma as a thousand separator, and removes trailing zeros after the decimal. For example, 12.50 should be displayed as 12.5, and whole numbers like 1200 should be shown as 1,200 without a trailing decimal point (i.e., not as ‘1,200.’).

  Regarding the issue of displaying 1200 as ‘1,200.’, this behavior is expected when using this’##,###,###,###,###,###,###.##########’ formatter. Excel also behaves the same way for this formatter.

  For the issue of ‘9999999999999999.9999999999’ being displayed as ‘10,000,000,000,000,000’, both edit mode and display mode showing the same value in my end that is ‘10,000,000,000,000,000.’. Refer to the attached gif “Steps.gif.”

  Designer: https://developer.mescius.com/spreadjs/designer/index.html

  Gif:

  If you’re seeing different behavior (e.g., correct value in edit mode but different in display mode), please share a minimal working example with the steps to replicate the issue. This will help me investigate further. Additionally, A GIF or video illustrating the issue would also be helpful.

  The reason why ‘9999999999999999.9999999999’ becomes ‘10,000,000,000,000,000’ is due to how JavaScript handles floating-point numbers. JavaScript follows the IEEE 754 standard (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Numbers_and_dates#numbers), which represents numbers with finite precision (typically 64 bits), allowing for about 15-17 decimal digits of precision.

  When inputting 9999999999999999.9999999999, the number exceeds JavaScript’s precise representation, and the nearest representable value becomes 10 quadrillion. This is why parseFloat(“9999999999999999.9999999999”) returns 10,000,000,000,000,000. You can refer to the attached gif “Steps.gif.”

  For your specific requirement, you could use custom formatting. Refer this demo to learn more about custom formatting: https://developer.mescius.com/spreadjs/demos/features/cells/formatter/custom-formatter/purejs

  Regards,
  
  Priyam

• 

  scrw

  • Post Options:
  • Link
    Copy

  Posted 9 October 2024, 9:12 pm EST - Updated 9 October 2024, 9:17 pm EST

  Hello,
  
  Thank you for your quickly supporting.
  
  For the issue 1: I have tried to you your formatter to my number. But it did not work normally. There is also a dot at the end of number (see my demo on the video).
  
  

  For the issue 2: I know if the number exceed the the max length limit of javascript. But is anyway to remain the value I want to enter?
  
  Example: when I enter number 999999999.9999999999. The cell will remain as 9,999,999,999.999999999. And it does not around to 10000000000. If there are no solution cause the limit of javascript then It will be oke to around it. thank you.

  In summary, my expectation is that when I set the cell formatter to ‘##,###,###,###,###,###,###.##########’, the cell value should remain exactly as I entered it, without any rounding or alteration.

  Regards,
  
  SCRW
  
  

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 11 October 2024, 7:14 am EST - Updated 11 October 2024, 7:19 am EST

  Hi,

  I have addressed your issues in the same order below:

  I couldn’t fully understand which formatter you’re referring to, as we haven’t shared a specific formatter for your requirement yet. Additionally, the provided video is not clear enough to see and understand the behavior. Could you kindly share a clearer video and an example so that we can assist you better?

  For your requirement to “display up to 20 digits with 10 decimal places, use a comma as a thousand separator, and remove trailing zeros after the decimal (e.g., 12.50 should display as 12.5, and 1200 should display as 1,200 without a trailing decimal point),” you can use a custom formatter to format the numbers as needed. Regarding your concern about JavaScript’s handling of large numbers exceeding its maximum length, you can save the number as a string to prevent rounding, and use a custom formatter to display the value as required. Please refer to the attached code snippet, GIF “Steps.gif,” and sample for implementation.

  function CustomNumberFormat() { }
  
  CustomNumberFormat.prototype = new GC.Spread.Formatter.FormatterBase();
  CustomNumberFormat.prototype.typeName = "customNumberFormat";
  
  CustomNumberFormat.prototype.format = function (obj, formattedData) {
      if (!obj) return ""; // Handle null or undefined values
      if (typeof obj === "number") {
          if (Number.isInteger(obj)) {
              return obj.toLocaleString('en-US'); // Format integer values
          } else {
              return obj.toLocaleString('en-US', {
                  minimumFractionDigits: 0,
                  maximumFractionDigits: 10
              }); // Format floating-point values
          }
      } else if (typeof obj === "string" && isValidNumber(obj) && !isPrecisionWithinLimit(obj)) {
          let [integerPart, decimalPart] = obj.split("."); // Split integer and decimal parts
          let formattedInt = integerPart.replace(/\B(?=(\d{3})+(?!\d))/g, ","); // Add thousand separators
          if (!decimalPart) return formattedInt; // Return if no decimal part
          let formattedDec = decimalPart.slice(0, 10).replace(/0+$/, ""); // Remove trailing zeros from decimals
          return formattedDec ? `${formattedInt}.${formattedDec}` : formattedInt;
      }
      return obj.toString();
  };
  
  CustomNumberFormat.prototype.parse = function (str) {
      return new GC.Spread.Formatter.GeneralFormatter().parse(str);
  };
  
  initSpread(spread);
  initEvent(spread);
  
  function initSpread(spread) {
      var sheet = spread.getActiveSheet();
      sheet.getCell(1, 1).formatter(new CustomNumberFormat());
  }
  
  function initEvent(spread) {
      spread.bind(GC.Spread.Sheets.Events.EditEnded, function (sender, args) {
          if (isValidNumber(args.editingText) && !isPrecisionWithinLimit(args.editingText) &&
              args.sheet.getFormatter(args.row, args.col)?.typeName === "customNumberFormat") {
              args.sheet.setFormatter(args.row, args.col, "@");
              args.sheet.setValue(args.row, args.col, args.editingText.toString());
              args.sheet.setFormatter(args.row, args.col, new CustomNumberFormat());
          }
      });
  }
  
  function isValidNumber(str) {
      return typeof str === 'string' && str.trim() !== '' && !isNaN(str) && isFinite(str);
  }
  
  function isPrecisionWithinLimit(str) {
      const num = Number(str);
      const significantDigits = str.replace(/^0\.|^0+|[^0-9]/g, '').length;
      return significantDigits <= 17;
  }

  GIF:

  Sample: https://jscodemine.mescius.io/share/bjPlBuwuA0uVXI51jJsqtw/?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"}

  Please note, if you’re only displaying the number without needing to use it in formulas in other cells, this solution will work fine. However, if the number is saved as a string, it might cause variations in formula results when used in other cells.

  References:

  EditEnded Event: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#editended

  setFormatter Method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setformatter

  Learn more about custom formatter: https://developer.mescius.com/spreadjs/demos/features/cells/formatter/custom-formatter/purejs

  Regards,
  
  Priyam