How to parse date which are having OADate format?

How to parse date which are having OADate format?

Posted by: 16pa1a0576 on 24 November 2022, 4:59 pm EST

• 

  16pa1a0576

  • Post Options:
  • Link
    Copy

  Posted 24 November 2022, 4:59 pm EST

  Hi,
  
  I am trying to parse an Excel file which is imported in our application using ExcelIO library.

  excelIo.open(
  
  file,
  
  (json) => {})

  Something like above. In that excel file we have dates(MM/dd/yyyy) as well for which when i found TYPE as integer in microsoft excel. When i tried to display that value , i was getting it as /OADate(…)/. How to resolve this? How to convert this to MM/dd/yyyy format?

  Thanks

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 27 November 2022, 11:54 pm EST

  Hi,

  The reason you are getting /OADate(…)/ is because excel uses OA Date format for exporting date values and spreadjs converts these values to Date type of javascript. Spreadjs internally converts these values to Date type while importing excel file. You may write custom code to convert OA Date value to Date type value.

  You may use editCell command to set /OADate(…)/ values and then format them.
  
  Kindly refer to the code snippet which shows how OADate formate date string in converted to Date type of javascript.

  // returns date object from oa string
  function fromOaToDate(oaString) {
      let splits = oaString.split('(');
      let value = splits[1].split(')');
  
      value = Number.parseFloat(value);
  
      var days = parseInt(value);
      var ms = Math.abs((value - days) * 8.64e7);
      return new Date(1899, 11, 30 + days, 0, 0, 0, ms);
  }

  Sample: https://codesandbox.io/s/solution-forked-ul4jyl?file=/index.html

  Doc reference:

  editCell command: https://www.grapecity.com/spreadjs/api/modules/GC.Spread.Sheets.Commands#editcell

  sheet.setFormatter: https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setformatter

  Regards,

  Ankit