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

New Issue

2017-10-18T15:05:09Z

laurentauthier commented

2017-10-18 15:05:09 +00:00

(Migrated from github.com)

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?

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 commented

2017-10-18 15:44:13 +00:00

(Migrated from github.com)

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

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

👍 2

SheetJSDev commented

2017-10-18 16:00:12 +00:00

(Migrated from github.com)

@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 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 commented

2017-10-18 16:11:23 +00:00

(Migrated from github.com)

@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 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 commented

2017-10-18 16:20:12 +00:00

(Migrated from github.com)

@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 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: <img width="640" alt="" src="https://user-images.githubusercontent.com/6070939/31729942-78ddb058-b3fe-11e7-8360-84982cbc6987.png"> 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 commented

2017-10-18 16:23:08 +00:00

(Migrated from github.com)

@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?

@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 commented

2017-10-19 08:13:58 +00:00

(Migrated from github.com)

![image](https://user-images.githubusercontent.com/1968429/31760806-32c6c772-b4b6-11e7-91ce-6610cd7133ca.png)

laurentauthier commented

2017-10-19 15:37:53 +00:00

(Migrated from github.com)

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 commented

2019-08-13 19:50:39 +00:00

(Migrated from github.com)

i have problem to:

i have problem to: ![Sem título](https://user-images.githubusercontent.com/9871536/62972461-7128b180-bdea-11e9-9782-5c9dc9703eaf.png) ![Sem título 2](https://user-images.githubusercontent.com/9871536/62972472-7554cf00-bdea-11e9-8f95-075eea6b2558.png)

danillo10 commented

2019-08-13 19:52:44 +00:00

(Migrated from github.com)

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

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 commented

2019-08-13 20:00:25 +00:00

(Migrated from github.com)

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

👍 14 🎉 3

igwtsagl70 commented

2019-09-18 15:41:15 +00:00

(Migrated from github.com)

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

😆 1

kaldaf commented

2019-11-16 18:29:37 +00:00

(Migrated from github.com)

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

😆 1

Riowaldy commented

2020-06-16 09:55:12 +00:00

(Migrated from github.com)

@danillo10 thank you so much. it works!

reviewher commented

2022-02-11 03:26:27 +00:00

(Migrated from github.com)

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.

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 commented

2022-07-14 14:53:46 +00:00

(Migrated from github.com)

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

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

Sign in to join this conversation.