SheetJs - Date is exported as text #2825

Closed
opened 2022-11-12 04:56:07 +00:00 by bkarv · 2 comments

Using SheetJS in Angular to export array of arrays to Excel. The issue I am having is SheetJs is not recognising my date data and instead treating it as a text value. Only when you double click on the value in excel it then recognises and converts to date.

I have tried setting the format. Even tested with a dummy value of New Date() but still no luck. Any ideas how I can make this field a date field? Big thanks in advance.

Code and screenshots below.

var arrayOfArrays = [["ID1","DESC","2022-10-26"],["ID2","DESC","2022-12-12"]]

var ws = XLSX.utils.aoa_to_sheet(arrayOfArrays,{dateNF: 'dd/mm/yyyy', cellDates: true});
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws,"Data");
var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});
function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

FileSaver.saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), filename)    

Screen shot of behaviour attached

Using SheetJS in Angular to export array of arrays to Excel. The issue I am having is SheetJs is not recognising my date data and instead treating it as a text value. Only when you double click on the value in excel it then recognises and converts to date. I have tried setting the format. Even tested with a dummy value of New Date() but still no luck. Any ideas how I can make this field a date field? Big thanks in advance. Code and screenshots below. ``` var arrayOfArrays = [["ID1","DESC","2022-10-26"],["ID2","DESC","2022-12-12"]] var ws = XLSX.utils.aoa_to_sheet(arrayOfArrays,{dateNF: 'dd/mm/yyyy', cellDates: true}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws,"Data"); var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'}); function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } FileSaver.saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), filename) ``` Screen shot of behaviour attached
Owner

You are passing strings, so aoa_to_sheet generates string cells.

Try passing actual date objects:

var arrayOfArrays = [
  ["ID1","DESC",new Date("2022-10-26 00:00:00")],
  ["ID2","DESC",new Date("2022-12-12 00:00:00")]
];

var ws = XLSX.utils.aoa_to_sheet(arrayOfArrays,{dateNF: 'dd/mm/yyyy', cellDates: true});
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws,"Data");
XLSX.writeFile(wb, "issue2825.xlsx");

Live example: https://jsfiddle.net/wqfg09ms/

You are passing strings, so `aoa_to_sheet` generates string cells. Try passing actual date objects: ```js var arrayOfArrays = [ ["ID1","DESC",new Date("2022-10-26 00:00:00")], ["ID2","DESC",new Date("2022-12-12 00:00:00")] ]; var ws = XLSX.utils.aoa_to_sheet(arrayOfArrays,{dateNF: 'dd/mm/yyyy', cellDates: true}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws,"Data"); XLSX.writeFile(wb, "issue2825.xlsx"); ``` Live example: https://jsfiddle.net/wqfg09ms/
Author

Thanks so much for the prompt response we initially tried this but got a string. But after seeing your fiddle working correctly and realised our function that generates the array of array was concatting the date values and converting to string. After fixing this it now works!

Thanks again for such a great and important package!

Thanks so much for the prompt response we initially tried this but got a string. But after seeing your fiddle working correctly and realised our function that generates the array of array was concatting the date values and converting to string. After fixing this it now works! Thanks again for such a great and important package!
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2825
No description provided.