Interaction between cellDates and dateNF #653

New Issue

2017-05-13T09:56:11Z

mmancosu commented

2017-05-13 09:56:11 +00:00

(Migrated from github.com)

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?

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/](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 commented

2017-05-13 14:42:49 +00:00

(Migrated from github.com)

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

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: <img width="212" alt="datecode" src="https://cloud.githubusercontent.com/assets/6070939/26026371/b2b7cc86-37c7-11e7-9990-4cd47f4d1d66.png"> (XLSX technically has a date cell type but Excel generates XLSX files that solely use the number form) For example, with [sheetjs.xlsx](https://github.com/SheetJS/js-xlsx/files/998710/sheetjs.xlsx) in node: ```js > 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 ```

👍 3

SheetJSDev commented

2017-05-13 18:25:08 +00:00

(Migrated from github.com)

@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

👍 2

GreggOD commented

2017-07-27 11:20:34 +00:00

(Migrated from github.com)

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

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

Sign in to join this conversation.