How to bind the cell's editor value into data source

How to bind the cell's editor value into data source

Posted by: pttrung811 on 31 March 2023, 12:50 am EST

• 

  pttrung811

  • Post Options:
  • Link
    Copy

  Posted 31 March 2023, 12:50 am EST - Updated 31 March 2023, 2:30 am EST

  Hi,

  When I input “1/1/1” into a cell with date format (14-Mar-12 for example), the cell’s editor value is a valid date (2001/1/1) and it will be painted with formatted date (1-Jan-01).

  But the data source always return an OADate value. How could I re-bind the editor’s value instead of OADate into the data source? With above example, it must be 2001/1/1.

  Thanks & Best Regards,

  Trung

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 3 April 2023, 12:45 pm EST

  Hello,

  This is by design SpreadJS uses javascript Date type to store date. However, Date type is only supported in the browser. That is why, the Date object is converted to OADate format that is supported by import export features. If like you like may convert the OADate format to a JS Date object. Please refer to the following code snippet and let me know if you face any issues.

  function oadateFromSlashFormat(slashDate) {
    var oadate = slashDate.match(/\d+/)[0]; // extract the number from the string
    var epoch = Date.parse("December 30, 1899"); // the OADate epoch
    var millisecondsPerDay = 24 * 60 * 60 * 1000;
    var daysSinceEpoch = parseFloat(oadate);
  
    var millisecondsSinceEpoch = daysSinceEpoch * millisecondsPerDay;
    var date = new Date(epoch + millisecondsSinceEpoch);
  
    return date;
  }
  
  var slashDate = "/OADate(36892)/"; // example date string
  var dateObject = oadateFromSlashFormat(slashDate);
  console.log(dateObject); // output: Mon Jan 01 2001 00:00:00 GMT-0800 (Pacific Standard Time)
  

  Regards,
  
  Avinash