SheetJS json_to_sheet在ISO 8601中以字符串形式写入日期 [英] SheetJS json_to_sheet is writing dates in ISO 8601 as strings

问题描述

我正在使用SheetJS库将角度材料数据表中的数据导出到excel.除了日期，Excel不会将其格式化或检测为日期以外，其他所有东西都工作正常.

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.

我有这样的JSON数据:

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
  }

如您所见，我有几个ISO 8601格式的日期和日期时间.

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

问题在于日期被作为字符串导出到excel文件，因此它们没有被格式化并且用户不能将它们作为适当的日期使用:

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

推荐答案

您需要将日期设置为Date对象.

You need to set the date as Date object.

我刚刚在ISO字符串中包装了一个新的Date构造函数.

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在ISO 8601中以字符串形式写入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！