Sheetjs: Interaction between cellDates and dateNF

[mmancosu]

I'm trying to parse some dates from a base64 string or a binary string.

The parsing configuration:

cellDates: false
dateNF: 'dd/mm/yyyy"

The data example:

COD,DES,DATE,NUM
01,ZEROONE,12/01/2009,1.2
02,ZEROTWO,16/02/2016,3

With base64 parsing type:

• cellDates seems to be ignored, cell datatype is always "d".
• dateNF ignored too, date output format is "mm/dd/yyyy"
  https://jsfiddle.net/fd30kLg6/1/

with binary parsing type:

• cellDates works fine, cell datatype "n" (and "d" with cellDates true)
• dateNF ignored, date output format is "mm/dd/yyyy"

Am I misunderstanding how cellDates and dateNF works?

[SheetJSDev]

Thanks for reporting @mmancosu ! Your understanding is correct, cellDates:true should be generating date cells with type "d", cellDates:false should be generating numeric cells, dateNF should override the default date format. Neither option has been applied to CSV, we'll fix it in the next push.

Background: cellDates is an option that mainly pertains to the XLS/XLSX/XLSB file formats. XLS and XLSB don't really have a native date type -- they store numbers and the "date" comes from the number format. For example, Saturday May 13 2017 is the number 42868:

(XLSX technically has a date cell type but Excel generates XLSX files that solely use the number form)

For example, with
sheetjs.xlsx in node:

> XLSX.readFile('sheetjs.xlsx').Sheets.SheetJS.C3
{ t: 'n', v: 41689.604166666664, w: '2/19/14' } // <-- default uses the number code
> XLSX.readFile('sheetjs.xlsx', {cellDates:true}).Sheets.SheetJS.C3
{ t: 'd', v: 2014-02-19T14:30:00.000Z, w: '2/19/14' } // <-- when cellDates is true, generate a date cell instead
> XLSX.readFile('sheetjs.xlsx', {dateNF:"dd/mm/yyyy"}).Sheets.SheetJS.C3
{ t: 'n', v: 41689.604166666664, w: '19/02/2014' } // <-- use the specified number format
> XLSX.readFile('sheetjs.xlsx', {dateNF:"dd/mm/yyyy", cellDates:true}).Sheets.SheetJS.C3
{ t: 'd', v: 2014-02-19T14:30:00.000Z, w: '19/02/2014' } // <-- use the specified number format

[SheetJSDev]

@mmancosu the current master should honor the cellDates/dateNF options for CSV. We'll ensure other formats also honor the options before closing the issue

[GreggOD]

@SheetJSDev
This was helpful! Thank you. I would like to someday devote some time to helping you guys make better documentation if thats an option?.. Give back to SheetJs a bit and get my company to give a donation once we have completed this project.