Date in XLSX file not parsing correctly in SheetJs

Question

I am trying to read a XLSX file using sheetjs node-module with a column having dates. After parsing I got data in incorrect format

File data is : 2/17/2020

But after xlsx read it gives me 2/17/20. It is changing the year format. My requirement is to get the data as it is.

Sample Code:

var workbook = XLSX.readFile('a.xlsx', {
  sheetRows: 10
});
    
var data = XLSX.utils.sheet_to_json(workbook.Sheets['Sheet1'], {
  header: 1,
  defval: '',
  blankrows: true,
  raw: false
});

Answer 1

There's a solution presented here using streams but can equally work with readFile. The problem that was at play in the issue is that the value for the dateNF option in sheet_to_json needs some escape characters.

E.g.:

const XLSX = require('xlsx');
const filename = './Book4.xlsx';

const readOpts = { // <--- need these settings in readFile options
  cellText:false, 
  cellDates:true
};

const jsonOpts = {
  header: 1,
  defval: '',
  blankrows: true,
  raw: false,
  dateNF: 'd"/"m"/"yyyy' // <--- need dateNF in sheet_to_json options (note the escape chars)
}

const workbook = XLSX.readFile(filename, readOpts);
const worksheet = workbook.Sheets['Sheet1'];
const json = XLSX.utils.sheet_to_json(worksheet, jsonOpts);

console.log(json);

For this input:

Will output:

[
  [ 'v1', 'v2', 'today', 'formatted' ],
  [ '1', 'a', '14/8/2021', '14/8/2021' ]
]

Without the dateNF: 'd"/"m","/yyyy' you get same problem as you describe in the question:

[
  [ 'v1', 'v2', 'today', 'formatted' ],
  [ '1', 'a', '8/14/21', '8/14/21' ]
]

The two potential unwanted side effects are:

1. use of the cellText and cellDates options in readFile
2. note my custom format of yyyy^mmm^dd in the input - the dateNF setting overrides any custom setting