How to correctly format date to yyyy-mm-dd from xlsx to csv #3013

New Issue

MKottenhagen · 2023-10-17T06:51:43Z

MKottenhagen commented

2023-10-17 06:51:43 +00:00

When converting excel files to csv files we would like for all our dates to be formatted the same, like yyyy-mm-dd.
We tried doing this using the dateNF option, but this seems to only work for dates formatted like dd/mm/yyyy.

Is it possible for dateNF to work with other formats? For instance dd-mm-yyyy or dd.mm.yyyy
And if this is not possible with dateNF, is their another way we could do this using sheetjs?

When converting excel files to csv files we would like for all our dates to be formatted the same, like yyyy-mm-dd. We tried doing this using the dateNF option, but this seems to only work for dates formatted like dd/mm/yyyy. Is it possible for dateNF to work with other formats? For instance dd-mm-yyyy or dd.mm.yyyy And if this is not possible with dateNF, is their another way we could do this using sheetjs?

sheetjs commented

2023-10-17 07:05:53 +00:00

dateNF solves a very specific ambiguity with Excel. Number format 14 must be interpreted according to the computer regional settings. Other formats are specified in a locale-independent way.

If you need to override the format for all date cells:

pass the options cellDates: true, cellNF: true, cellText: false to the read or readFile function
loop over every cell and manually set the z number format property of each date cell to the desired format.

dateNF solves a very specific ambiguity with Excel. Number format 14 must be interpreted according to the computer regional settings. Other formats are specified in a locale-independent way. If you need to override the format for all date cells: 1) pass the options `cellDates: true, cellNF: true, cellText: false` to the `read` or `readFile` function 2) loop over every cell and manually set the `z` number format property of each date cell to the desired format.

sheetjs closed this issue

2023-10-17 07:05:54 +00:00

sheetjs commented

2023-10-20 16:48:51 +00:00

#3019 (comment) related comment with a code snippet for looping over every cell in a sparse worksheet and wiping the z number format for each date cell:

const range = XLSX.utils.decode_range(worksheet["!ref"]);
for(let R = range.s.r; R <= range.e.r; ++R) {
  for(let C = range.s.c; C <= range.e.c; ++C) {
    const addr = XLSX.utils.encode_cell({c:C,r:R});
    if(!worksheet[addr] || worksheet[addr].t != "d") continue;
    delete worksheet[addr].z;
  }
}

For a dense worksheet (if you pass dense: true to read or readFile) the loop is

worksheet["!data"].forEach(row => { row.forEach(cell => {
  if(!cell || cell.t != "d") return;
  delete cell.z;
}); });

https://git.sheetjs.com/sheetjs/sheetjs/issues/3019#issuecomment-10781 related comment with a code snippet for looping over every cell in a sparse worksheet and wiping the `z` number format for each date cell: ```js const range = XLSX.utils.decode_range(worksheet["!ref"]); for(let R = range.s.r; R <= range.e.r; ++R) { for(let C = range.s.c; C <= range.e.c; ++C) { const addr = XLSX.utils.encode_cell({c:C,r:R}); if(!worksheet[addr] || worksheet[addr].t != "d") continue; delete worksheet[addr].z; } } ``` For a dense worksheet (if you pass `dense: true` to `read` or `readFile`) the loop is ```js worksheet["!data"].forEach(row => { row.forEach(cell => { if(!cell || cell.t != "d") return; delete cell.z; }); }); ```

Sign in to join this conversation.