Sheetjs: export date-time format cell

Created on 26 Mar 2018  路  1Comment  路  Source: SheetJS/sheetjs

I was using below api to generate the excel from js object.
var ws2 = XLSX.utils.json_to_sheet(dataForSheet, {skipHeader: true});
The json object have date column and date-time column . But in the exported excel, the datetime column can not bee seen unless you double-click it.
How can this issue be solved?
I tried the dateNF property like below, but does not work, it will affect the date column as well.
var ws2 = XLSX.utils.json_to_sheet(dataForSheet, {skipHeader: true,dateNF: 'YYYYMMDD HH:mm:ss'});

I also post this question in SO.
https://stackoverflow.com/questions/49483419/how-to-make-the-datetime-column-shown-correctly-on-the-ms-excel

Most helpful comment

You have to manually set the column width to 17 characters (the format yyyymmdd hh:mm:ss generates 17-character values):

if(!ws['!cols']) ws['!cols'] = [];
ws['!cols'][/* column index for date */] = { wch: 17 };

https://jsfiddle.net/sheetjs/9xneL9wh/ is a sample fiddle:

var ws = XLSX.utils.json_to_sheet([[new Date()]], {skipHeader:true, dateNF: 'YYYYMMDD HH:mm:ss'});
if(!ws['!cols']) ws['!cols'] = [];
ws['!cols'][0] = { wch: 17 };
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "issue1052");
XLSX.writeFile(wb, "issue1052.xlsx");

>All comments

You have to manually set the column width to 17 characters (the format yyyymmdd hh:mm:ss generates 17-character values):

if(!ws['!cols']) ws['!cols'] = [];
ws['!cols'][/* column index for date */] = { wch: 17 };

https://jsfiddle.net/sheetjs/9xneL9wh/ is a sample fiddle:

var ws = XLSX.utils.json_to_sheet([[new Date()]], {skipHeader:true, dateNF: 'YYYYMMDD HH:mm:ss'});
if(!ws['!cols']) ws['!cols'] = [];
ws['!cols'][0] = { wch: 17 };
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "issue1052");
XLSX.writeFile(wb, "issue1052.xlsx");
Was this page helpful?
0 / 5 - 0 ratings

Related issues

gustavosimil picture gustavosimil  路  3Comments

Alex0007 picture Alex0007  路  3Comments

eyalcohen4 picture eyalcohen4  路  3Comments

jamesbillinger picture jamesbillinger  路  4Comments

jamespan0 picture jamespan0  路  3Comments