Format of data is incorrect after Input

Format of data is incorrect after Input

Posted by: hieu.van.nguyen on 20 July 2023, 11:20 pm EST

• 

  hieu.van.nguyen

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 20 July 2023, 11:20 pm EST - Updated 20 July 2023, 11:25 pm EST

  Hi Grapecity support team,
  
  I have an issue with the value input in the cell with the “general” format, when I input the value “Aug 8” to that cell and press enter, the text display is “45146” while the value when I focus that cell is “8/8/2023”. How can it display the correct value I input? (currently, I’m using version 16.1.3)
  
  Best Regard.
  
  

  

  990×422 31.8 KB

  

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 21 July 2023, 3:32 am EST

  Hi,

  Thanks for sharing the images with us. We were able to reproduce the issue at our end. We have escalated it to the concerned dev team for further investigation. The internal tracking id is: SJS-19355. We will let you know when there is an update from the dev team on this.

  As a workaround, you could handle the EditEnded event, check if the typed value matches the specified date format and then set the formatter of the cell. Kindly refer to the following code snippet and the sample:

  spread.bind(GC.Spread.Sheets.Events.EditEnded, function (e, args) {
      let formatter = args.sheet.getFormatter(args.row, args.col);
      let cellValue = args.sheet.getValue(args.row, args.col);
  
      if (toString.call(cellValue) === "[object Date]" && formatter && formatter.toUpperCase() === "GENERAL" ||
          (toString.call(cellValue) === "[object Date]" && !formatter)) {
          if (args.editingText.match(/^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s\d{2}$/)) {
  
              args.sheet.setFormatter(args.row, args.col, "m/d/yyyy");
          }
      }
  });

  Sample: https://jscodemine.grapecity.com/share/zxZaTZmDukiLNclNLDnycw/?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["%2Fsrc%2Fapp.js"%2C"%2Fpackage.json"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Regards,

  Ankit

• 

  hieu.van.nguyen

  • Post Options:
  • Link
    Copy

  Posted 21 July 2023, 4:39 am EST

  Hi,
  
  Thanks for your suggestion, I will try it while waiting for the dev team to investigate and solve the issue.

  Regards,
  
  Hieu.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 25 July 2023, 11:18 pm EST

  Hi Hieu,

  The developers have informed us that this is not a built-in support case, and you could use below code to apply formatter to preselected Formatters:

  var cultureInfo = GC.Spread.Common.CultureManager.getCultureInfo();
  cultureInfo.DateTimeFormat.preselectedFormatters.unshift('MMM dd')

  Sample: https://jscodemine.grapecity.com/share/OGiz3TZckUisfeyfXWddog/?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["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Reference:

  getCultureInfo method: https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Common.CultureManager#getcultureinfo

  Please let us know if you face any problems.

  Regards,

  Ankit

• 

  hieu.van.nguyen

  • Post Options:
  • Link
    Copy

  Posted 26 July 2023, 3:03 am EST

  Hi Ankit,
  
  Thanks for your support and the dev team. This solution can solve my issue, and if there is any problem, I will let you know.

  Regards,

  Hieu