Cell Range Formatting Issue on SpreadJS

Cell Range Formatting Issue on SpreadJS

Posted by: srayaprolu on 21 August 2025, 1:38 pm EST

• 

  srayaprolu

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 August 2025, 1:38 pm EST

  Hi,

  I am using SpreadJS 18.1.4 version

  I’m trying to apply number and date formatting to ranges of cells in SpreadJS using the .formatter() method. My approach is as follows:

  javascript

  [code]// For number columns
  
  sheet.getRange(row, column, rowCount, colCount).formatter(‘#,##0.0000;-#,##0.0000’);

  // For date columns
  
  sheet.getRange(row, column, rowCount, colCount).formatter(‘MM/dd/YYYY’);[/code]

  The formatting is visually applied to the range of cells, but in the SpreadJS Designer toolbar (and after exporting to Excel), the cells display Custom format instead of Number or Date type, as shown in the screenshots below (screenshot-1)

  Is there a way to ensure the formatted columns are recognized as Number and Date types (not Custom) in the Designer and Excel export?

  Any suggestions or best practices for this in SpreadJS would be appreciated!

  Used this documentation to set the formatting types. - https://developer.mescius.com/spreadjs/docs/features/cells/cellformat/BasicFormat

  Thank you.

  
  
  

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 22 August 2025, 2:53 am EST - Updated 22 August 2025, 2:58 am EST

  Hi,

  From my understanding, you are applying number and date formatting, which works as expected but appears as Custom instead of Number or Date in the Designer.

  This behavior is expected and by design. When a format is applied that already exists in the Format Cells dialog, it is recognized and displayed as Number or Date. However, if the applied format is not available in the Format Cells dialog, it is treated as Custom. Please refer to the attached GIF—when I use a format available in the Format Cells dialog, it shows as Date or Number. But when I use a format not listed there, it displays as Custom. Both SpreadJS and Excel behave the same way.

  GIF:

  Regards,
  
  Priyam

• 

  srayaprolu

  • Post Options:
  • Link
    Copy

  Posted 22 August 2025, 12:56 pm EST - Updated 22 August 2025, 1:02 pm EST

  Thanks for replying, I applied exact some format like what you’ve tried.

  For numbers - sheet.getRange(row, column, rowCount, colCount).formatter(‘#,##0.0000") (“instead of 2 decimals, I want to have 4 decimals”)

  Why do i still see values being left inclines, altough format box says its a number? I added how it gets shown on excel upon exporting. Please suggest any changes

  

  2890×1376 537 KB

  

  2886×1468 565 KB

• 

  srayaprolu

  • Post Options:
  • Link
    Copy

  Posted 22 August 2025, 2:48 pm EST

  If you see screenshot from excel file, the values which I have highlighted as not having comma (,) On the contrary on SpreadJS Designer is showing commas and value in number format, but aligned towards left.

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 25 August 2025, 4:35 am EST - Updated 25 August 2025, 4:41 am EST

  Hi,

  From my understanding, you are applying a number format, but the values are aligning to the left instead of the right, and the exported Excel file is not displaying commas for the numbers.

  It looks like you are using:

  sheet.getRange(row, column, rowCount, colCount).formatter(‘#,##0.0000")

  where the formatter string is incorrect (‘#,##0.0000"). When corrected to:
  
  “#,##0.0000”

  it works as expected on my end—the numbers align to the right and commas appear correctly in the exported Excel file. Please refer to the attached GIF and sample.

  GIF:

  Sample: purejs.zip

  To assist you further, could you provide a minimal working sample along with the steps to reproduce the issue, or modify the attached sample to replicate it? This will help me investigate in more detail. Additionally, sharing a GIF or video of the behavior would also be very helpful.

  Regards,
  
  Priyam

• 

  srayaprolu

  • Post Options:
  • Link
    Copy

  Posted 26 August 2025, 4:27 am EST

  thanks for reaching back, my old code had typo. But thanks for showing it to me again. I am trying to achieve this behavior.

  If numbers with more than 4 decimals, are encountered i want them to be limited to 4 decimals.
  
  If numbers with no decimals are encountered I want them to limited to 2 decimals, like in excel.

  I am trying to achieve this by sheet.getRange(row, column, rowCount, colCount).formatter(‘#,##0.00##‘), where formatted value is getting identified as Custom, where as I want it to be shown as number format. Do you have any idea on how to achieve this with formatter pattern? Or should I be adding custom JS code on top to count how many values are there after decimal and apply .formatter(‘#,##0.00’) and .formatter(‘#,##0.0000’) accordingly?

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 26 August 2025, 5:57 am EST - Updated 26 August 2025, 6:02 am EST

  Hi,

  I understand your requirement—you want to format numbers so that:

  • Values without decimals display 2 decimal places.
  • Values with more than 4 decimals are rounded/limited to 4 decimals.
  • Ideally, the format should be recognized as Number instead of Custom in SpreadJS/Excel.

  The first two points can be achieved using the string format #,##0.00##. However, for the third point, it will still appear as Custom because that format is not available in the Format Cells dialog in either Excel or SpreadJS Designer—this is expected behavior by design (see attached “Steps.gif”).

  Gif:

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

  If you want the format to show as Number instead of Custom, you’ll need to use #,##0.0000 or #,##0.00. Since both can’t be applied at the same time in one cell, you’ll need to handle this via the ValueChanged event. In that event, you can check whether the value has decimals or more than 4 decimals, and then apply the correct format dynamically using the setFormatter method:

  spread.bind(GC.Spread.Sheets.Events.ValueChanged, (e, info) => {
      let value = info.newValue;
      // process value to check decimals
      if (value % 1 === 0) {
          // no decimals → 2 decimals
          info.sheet.setFormatter(info.row, info.col, '#,##0.00');
      } else {
          // has decimals → limit to 4 decimals
          info.sheet.setFormatter(info.row, info.col, '#,##0.0000');
      }
  });

  Regards,
  
  Priyam

• 

  srayaprolu

  • Post Options:
  • Link
    Copy

  Posted 2 September 2025, 9:47 am EST

  Thank you for responding, it happens to be issue from TypeORM library which I am using. This thread can be marked resolved.