SheetJS json_to_sheet is writing dates in ISO 8601 as strings

Question

I'm using the SheetJS library to export data in my angular material datatable to excel. Everything is working fine except for the dates, which are not being formatted or detected as dates by excel.

I have JSON data like this:

{
    "id": 21658,
    "lector_nombre": "Test",
    "plataforma_codigo": "F10",
    "plataforma_descripcion": "BLOQUE",
    "created_at": "2020-02-27T16:53:32.7",
    "fecha_ult_revision": "2020-02-25T00:00:00",
    "pasos_ahora": 0,
    "pasos_ciclo": 1000,
    "pasos_ptes": 1000,
    "ubicacion_1": "",
    "ubicacion_2": "",
    "estado": true,
    "fecha_sig_revision": "2021-02-25T00:00:00",
    "codigo_mantenimiento": null
  }

I have several dates and datetimes in ISO 8601 format as you can see.

The problem is that the dates are being exported as strings to the excel file, therefore they are not formatted and the user cannot work with them as proper dates:

This is the code where I am managing the exporting process:

import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

@Injectable({
  providedIn: 'root'
})
export class ExportxlsService {
  fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
  fileExtension = '.xlsx';
  constructor() {}

  public exportExcel(jsonData: any[], fileName: string): void {
    console.log(JSON.stringify(jsonData, null, 2));
    const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData, { cellDates: true, dateNF: 'YYYYMMDD HH:mm:ss' });
    const wb: XLSX.WorkBook = { Sheets: { data: ws }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    this.saveExcelFile(excelBuffer, fileName);
  }

  private saveExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], { type: this.fileType });
    FileSaver.saveAs(data, fileName + this.fileExtension);
  }
}

Not really. I finally decided to transform the format of all the date properties with moment.js before calling the exportExcel method. For each row, something like `{...row, created_at: moment(row.created_at).format('DD-MM-YYYY HH:mm:ss')...etc ` — Ernesto G, Sep 09 '20 at 15:19
Yes. i notice if the data is Date object, then cellDates will treat accordingly — Zaiman Noris, Sep 15 '20 at 17:14

score 3 · Accepted Answer · edited Jan 21 '21 at 20:15

You need to set the date as Date object.

I just wrapped the ISO string an new Date constructor.


{
    "id": 21658,
    "lector_nombre": "Test",
    "plataforma_codigo": "F10",
    "plataforma_descripcion": "BLOQUE",
    "created_at": new Date("2020-02-27T16:53:32.7"),
    "fecha_ult_revision": "2020-02-25T00:00:00",
    "pasos_ahora": 0,
    "pasos_ciclo": 1000,
    "pasos_ptes": 1000,
    "ubicacion_1": "",
    "ubicacion_2": "",
    "estado": true,
    "fecha_sig_revision": "2021-02-25T00:00:00",
    "codigo_mantenimiento": null
  }

SheetJS json_to_sheet is writing dates in ISO 8601 as strings

1 Answers1

Linked