Comma's disappearing when editing cells on SpreadJs after import file

Comma's disappearing when editing cells on SpreadJs after import file

Posted by: thuan.tu on 3 March 2024, 9:42 pm EST

• 

  thuan.tu

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 3 March 2024, 9:42 pm EST - Updated 3 March 2024, 9:45 pm EST

  Hi support team!
  
  We are using spread sheet designer for my project. After import file Excel_example_Revenue.xlsx (attached below) , We detected a problem. When we enter a number with a comma (for example: 1,234) into a cell (e.g. D17), this cell auto formats data input to 1234 (missing comma). We tested this error again on MS excel and it did not appear. We assume that SpreadJs automatically reformatted the cells in the spreadsheet. Can you tell us what happened and how we fixed it? Thanks!Excel_example_Revenue.zip

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 4 March 2024, 10:24 pm EST - Updated 4 March 2024, 10:30 pm EST

  Hi,

  As per my understanding, the thousands separator comma(,) does not appear in cell D17 when the xlsx file is imported into SpreadJS.

  We tried to replicate the issue at our end. However, we were unable to replicate the issue at our end. Kindly refer to the attached GIF.

  Could you please provide us with a steps/video showing to replicate the issue? It would help us to replicate the issue and find its root cause.

  

  Regards

• 

  thuan.tu

  • Post Options:
  • Link
    Copy

  Posted 5 March 2024, 1:12 pm EST

  Try opening the Excel_example_Revenue.xlsx file on both MS Excel and SpreadJS, then enter it in cell D17 on both tools. MS Excel will retain the user input format but spreadJS will not (comma(,) disappearing).
  
  You can see our attached file: comma-issue.zip

• 

  thuan.tu

  • Post Options:
  • Link
    Copy

  Posted 5 March 2024, 1:15 pm EST

  We know that the comma (,) thousands separator does not appear in cell D17. But the behavior of SpreadJS and MS Excel is different when we enter numeric value with comma (,).

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 6 March 2024, 3:48 am EST

  Hi,

  Currently, we are investigating the issue. We will inform you about our findings as soon as possible.

  Regards

• 

  thuan.tu

  • Post Options:
  • Link
    Copy

  Posted 6 March 2024, 2:32 pm EST

  Thanks a lot!

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 6 March 2024, 5:35 pm EST

  Hi,

  Thanks for providing us with the videos of the issue.

  After investigating the issue, we found that if a cell has General format applied explicitly then entering value 1,234 does not automatically change its format to Number. In the .xlsx file that you have shared, cell D17 has General format applied explicitly, that is why even after entering value 1,234 in the cell it shows the cell value as 1234 and does not change its format to Number.

  We have escalated this issue to the dev for further investigation. The internal tracking ID for this is SJS-23152. We will keep you updated on the progress.

  As a workaround, you can handle the EditEnding event and check if a cell has General format applied explicitly. If so then remove the General format so that the format can be decided implicitly as per the value entered in the cell.

  Kindly refer to the attached sample.

  sample: https://jscodemine.grapecity.com/share/GJHAykk5_EWNpKChURcTKw/?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"}

  You can import the .xlsx file and enter the value 1,234 in the cell D17.

  Regards

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 17 March 2024, 7:46 pm EST - Updated 17 March 2024, 7:51 pm EST

  Hi,

  The mentioned behavior is a known difference between Excel and SpreadJS. When a value(e.g. 1,234) is entered in a cell in SpreadJS, the auto-formatter policy will try to parse the entered string to a value, and then save the auto-formatter to the cell, but the cell’s formatter is still undefined.

  It essentially means that in SpreadJS, if the cell has a formatter, the formatter will not be changed by inputting behavior. If the cell didn’t have a formatter, Spreadjs will generate an auto-formatter to the cell style(not set as formatter for the cell). Kindly refer to the attached GIF.

  But in Excel, when you perform the same action, a formatter will be applied to the cell directly.

  As a component, SpreadJS does not pollute the user’s data (the cell style is a kind of user data). The mentioned behavior is by design. You may use the workaround that we provided you in our previous response.

  

  Regards

• 

  thuan.tu

  • Post Options:
  • Link
    Copy

  Posted 17 March 2024, 8:29 pm EST

  Thanks chandan.rawat so much!