Write a Date column with js-xlsx

Question

Using js-xlsx I did several attempts to write a XLSX file with a Date column, such as the one below. Each time, the date is not rendered as I expect when I open the XLSX file with Excel 2010: it is rendered as the number of days from a certain origin. Is there a way to render a date directly, without having to format the column in Excel ?

const XLSX = require("xlsx");

var wb = { 
    Sheets: { 
        Sheet1: { 
            '!ref': 'A1:A2', 
            A1: { t: 'd', v: new Date('13 April 2014 00:00 UTC'), w: '2014-04-13' }, 
            A2: { t: 'd', v: new Date('13 April 2014 00:00 UTC'), w: '2014-04-13' }
        } 
    },
    SheetNames: [ 'Sheet1' ] 
  }

XLSX.writeFile(wb, "wbDate.xlsx", {cellDates: true});

Edit: possible answer

Finally I get some dates as follows:

const XLSX = require("xlsx");

var wb = { 
    Sheets: { 
        Sheet1: { 
            '!ref': 'A1:B2', 
            A1: { t: 'd', v: '2016-06-21' }, 
            A2: { t: 'd', v: '2016-11-22' },
            B1: { t: 'n', v: 42542, z: 'm/d/yy' }, 
            B2: { t: 'n', v: 42696, z: 'm/d/yy' }
        } 
    },
    SheetNames: [ 'Sheet1' ] 
  }

XLSX.writeFile(wb, "wbDate.xlsx", {cellDates: false});

This gives (Windows 7, Excel 2010, locale time French Belgium):

Oddly, the format is given as m/d/yy, but the output is rendered as d/m/yy (or dd/mm/yy?). I have not managed to get another format (but this one is nice for me).

score 2 · Answer 1 · answered Dec 27 '16 at 10:15

There is a long discussion and explanation of various ways to format dates in this issue on GitHub:

https://github.com/SheetJS/js-xlsx/issues/126

It is an open issue so all of the problems are probably not resolved yet.

Keep in mind that the format (days since the epoch) is not the only issue. Another issue is that the start of the epoch is actually counted from a different day on different systems and that must also be taken into account.

Write a Date column with js-xlsx

Edit: possible answer

1 Answers1