Search code examples
angularsheetjs

SheetJS json_to_sheet is writing dates in ISO 8601 as strings

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:

enter image description here

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);
  }
}
Solution

  • 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
  }