Search code examples
javascriptjsonsheetjs

Sheetjs - Sheet add Json ignores date format

I have a JSON that contains some data and I want to export it to Excel. Problem is that I can not achieve the correct date format when saved to Excel. The date format that I want to achieve is something like that 2020-07-30 07:31:45

I followed the advise from this post but it didn't really help: sheetjs

The JSON:

{
  "source": "internal",
  "account": "Test",
  "posted_at": new Date("2020-09-25T07:11:19.0000000")
  "content": "some content"
}

My code:

ws = XLSX.utils.aoa_to_sheet([[formattedQuery]]);

XLSX.utils.sheet_add_json(ws, json, { origin: -1, display: true }, { cellDates: true, dateNF: 'YYYYMMDD HH:mm:ss' });

var workbook = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(workbook, ws, filename.substring(0, 29));

XLSX.writeFile(workbook, filename);

Date as saved on Excel: enter image description here

Solution

  • Looks like you added the date options in a separate config object they should be merged with the origin and display options for the sheet_add_json method invocation. Like this:

    XLSX.utils.sheet_add_json(ws, json, { origin: -1, display: true, cellDates: true, dateNF: 'YYYYMMDD hh:mm:ss' })