SheetJs converting date automatically angular 7

Question

I'm using sheetjs in angular 7. And I'm exporting excel from my json. But excel converting my date to 02/29/2019 format. But, I'm sending "29.02.2019". I'm sending as string, excel still converting. I also send "29.02.2019." by adding "." character at end of date, but it still convert to "02/29/2019." . How can I achieve showing by my format?

import * as XLSX from 'xlsx';

exportExcelFromJson(jsonData): void {
    const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();

    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
    XLSX.writeFile(wb, 'MyExcel.xlsx');
}

score 3 · Accepted Answer · answered Mar 11 '19 at 13:39

3

Add the dateNF option to your call as described in the documentation: https://docs.sheetjs.com/#array-of-objects-input

so such a call should work:

const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData, {dateNF:"dd.MM.yyyy"});

answered Mar 11 '19 at 13:39

Chirag Patel

334
2
12

as in no change to date structure or is it throwing an error? – Chirag Patel Mar 11 '19 at 14:00
can you try setting the cellDates option to true. – Chirag Patel Mar 11 '19 at 14:03
2

Thanks. `dateNF` worked now. For working `dateNF` , json property should be written as Date. I wrote as string and then it's not worked. But excel converting whatever you write string or date. @ChiragPatel – realist Mar 11 '19 at 14:32
I am using same code, but in my case I am expecting that date should be right aligned and It should be visible as a dat currently it is visible as General. How can we achieve that? Code is same as question asked. – Maulik Patel Feb 09 '21 at 12:46

SheetJs converting date automatically angular 7

1 Answers1