How to specify a different format date dd/mm/yyyy for Excel export?

[laurentauthier]

I tried this:
var ws = XLSX.utils.table_to_sheet(document.getElementById(id), {dateNF:'d/m/yy'});
instead of:
var ws = XLSX.utils.table_to_sheet(document.getElementById(id));
No effect. date result is still inversed (formatted in html table with dd/mm/yyyy)
04/10/2017 in html table is written 10/04/2017 in excel file.
It seems to does not support french format...
Do you have any idea to resolv my issue?

[laurentauthier]

dateNF:'mm/dd/yyyy;@' seems to resolv my problem but I don't understand why cell format is mixed with Standard and date format.
Please help, I'm lost!
And cellDate has no effect
var ws = XLSX.utils.table_to_sheet(document.getElementById(id), {dateNF:'mm/dd/yyyy;@',cellDates:true });

[SheetJSDev]

@laurentauthier can you share a small HTML table and the result of XLSX.utils.table_to_sheet(document.getElementById(id)); ? Wondering whether it is an ingress or egress issue

[laurentauthier]

@SheetJSDev with dateNF:'mm/dd/yyyy;@' it seems to be ok: 04/10/2017 (4 october) is written 04/10/2017 in xlsx file with date format.
But 31/01/2017 is written with standard format with spaces before and after (due to comments in html I suppose). I don't understand why.
I'm french and I use an Excel french version.

[SheetJSDev]

@laurentauthier the ingress part (which takes the cell text and generates a date) uses the browser Date constructor to parse the date string: https://github.com/SheetJS/js-xlsx/blob/master/bits/20_jsutils.js#L129 . I suspect that the browser parsing implicitly assumes the format is "m/d/y". Here's what I get on Windows 7 Chrome under French (France) Locale:

Can you check the two commands against your browser (new Date("04/10/2017") and new Date("31/01/2017")) and see whether you get the same results?

[laurentauthier]

@SheetJSDev export as csv:
,ADM - administratif 2016,-, 31/01/2017 , ...
,ADM - Administratif 2017,-,04/10/2017, , ...

when there is some whitespaces, dates are not formatted.
How to specify to trim values?

[laurentauthier]

[laurentauthier]

In fact,I do not understand why specify dateNF:'mm/dd/yyyy;@' to format dd/mm/yyyy in excel file... How to do? To resolv I specify raw:true and let Excel do the conversion. But I need to trim values... (see another issue about this problem)

[danillo10]

i have problem to:

[danillo10]

this.viewExportar = false;
const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement, {dateNF:'mm/dd/yyyy;@',cellDates:true});
const wb: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

/* save to file */
XLSX.writeFile(wb, "relatorio_CP-"+moment().format()+".xlsx");

[danillo10]

Solved with: const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement, {dateNF:'mm/dd/yyyy;@',cellDates:true, raw: true});

[igwtsagl70]

Muchas gracias danillo10, ¿De dónde sacaste la referencia mil disculpas?, me interesaría profundizar más sobre esta librería, muchas gracias

[kaldaf]

Thanks a lot @danillo10 your answer solved my same problem.

[Riowaldy]

@danillo10 thank you so much. it works!

[reviewher]

Overriding single cell date formats is described in the docs https://github.com/SheetJS/sheetjs#number-formats by setting the z property.

As @danillo10 wrote, pass the option cellDates: true to recover date cells.

[msd96]

What about this code?

function setExcelDateFormat(theWS,theCols,frmtTxt){
  for(h=0;h<theCols.length;h++){
    var colNum = XLSX.utils.decode_col(theCols[h]);
    var range = XLSX.utils.decode_range(theWS['!ref']);
    for(i=range.s.r+1;i<range.e.r+1;i++){
      var ref = XLSX.utils.encode_cell({r:i, c:colNum});
      if(!theWS[ref]) continue;
      theWS[ref].v = theWS[ref].v;
      theWS[ref].t = 'd';
      theWS[ref].z = frmtTxt;
    }
  }
  return theWS;
}

[greident]

i answer in stackOverflow
https://stackoverflow.com/questions/53163552/format-date-with-sheetjs/76894466#76894466

const element = document.getElementById('excel-table');
const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(element);

in "ws" date format like a this json

{
  "t": "n",
  "v": 44933.000555555554,
  "z": "m/d/yy"
}

so we know date have type (t) number (n) and z is "m/d/yy"

Object.keys(ws).forEach(cell => {
      const cellValue = ws[cell];

      if (cellValue.t === 'n' && cellValue.z === 'm/d/yy') {
        const excelDate = cellValue.v;
        const jsDate = new Date((excelDate - (25567 + 1)) * 86400 * 1000);
        const formattedDate = `${String(jsDate.getDate()).padStart(2, '0')}.${String(jsDate.getMonth() + 1).padStart(2, '0')}.${jsDate.getFullYear()}`;
        ws[cell].v = formattedDate;
        ws[cell].t = 's';
      }
    });

and do const wb: XLSX.WorkBook = XLSX.utils.book_new();...

[0xluc]

Solved with: const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement, {dateNF:'mm/dd/yyyy;@',cellDates:true, raw: true});

Thank you!