I am trying to read data form xlsx file and converting it into json, but the date column value is changing Here is the screenshot:Screenshot of my excel file from which i am reading data

Here is the code for reading data from excel file and converting into JSON:

onBasicUpload(event){
let workBook = null;
let jsonData = null;
const reader = new FileReader();
const file = event.files[0];
console.log(file,"file is here");

reader.onload = (event) => {
  const data = reader.result;
  workBook = xlsx.read(data, { type: 'binary' });
  jsonData = workBook.SheetNames.reduce((initial, name) => {
    const sheet = workBook.Sheets[name];
    initial[name] = xlsx.utils.sheet_to_json(sheet);
    console.log(jsonData,"jsonDAta");
    
    return initial;
  }, {});
  const dataString = JSON.stringify(jsonData);
  console.log(dataString,"stringify data");
  this.jsonArr = JSON.parse(dataString)
  console.log(this.jsonArr,"parsed json");
  console.log(Object.keys(this.jsonArr['data'][0]))
  
}
reader.readAsBinaryString(file);
}

It is returning me this: DOCDT is the value of the date being returned. {"data":[{"DOCNO":"001","NETAMOUNT":30000,"IREF1":"50","IREF2":"100","DOCDT":43989},{"DOCNO":2,"NETAMOUNT":40000,"IREF1":40,"IREF2":90,"DOCDT":43989}]}

I am trying to read data form xlsx file and converting it into json, but the date column value is changing Here is the screenshot:Screenshot of my excel file from which i am reading data

Here is the code for reading data from excel file and converting into JSON:

onBasicUpload(event){
let workBook = null;
let jsonData = null;
const reader = new FileReader();
const file = event.files[0];
console.log(file,"file is here");

reader.onload = (event) => {
  const data = reader.result;
  workBook = xlsx.read(data, { type: 'binary' });
  jsonData = workBook.SheetNames.reduce((initial, name) => {
    const sheet = workBook.Sheets[name];
    initial[name] = xlsx.utils.sheet_to_json(sheet);
    console.log(jsonData,"jsonDAta");
    
    return initial;
  }, {});
  const dataString = JSON.stringify(jsonData);
  console.log(dataString,"stringify data");
  this.jsonArr = JSON.parse(dataString)
  console.log(this.jsonArr,"parsed json");
  console.log(Object.keys(this.jsonArr['data'][0]))
  
}
reader.readAsBinaryString(file);
}

It is returning me this: DOCDT is the value of the date being returned. {"data":[{"DOCNO":"001","NETAMOUNT":30000,"IREF1":"50","IREF2":"100","DOCDT":43989},{"DOCNO":2,"NETAMOUNT":40000,"IREF1":40,"IREF2":90,"DOCDT":43989}]}

• javascript
• json
• angular
• primeng

• That's because Excel stores dates and times as a number of days since an epoch, midnight on 30 December 1899 (IIRC). – phuzi Commented Jul 7, 2020 at 8:28
• See stackoverflow./questions/15549823/… for a way to convert this number to a JavaScript Date. – phuzi Commented Jul 7, 2020 at 8:29
• Does this answer your question? OADate to Milliseconds timestamp in Javascript – phuzi Commented Jul 7, 2020 at 8:29

2 Answers 2

Try using this,

onBasicUpload(event){
let workBook = null;
let jsonData = null;
const reader = new FileReader();
const file = event.files[0];
console.log(file,"file is here");

reader.onload = (event) => {
  const data = reader.result;
  workBook = xlsx.read(data, { type: 'binary' , cellDates: true });
  jsonData = workBook.SheetNames.reduce((initial, name) => {
    const sheet = workBook.Sheets[name];
    initial[name] = xlsx.utils.sheet_to_json(sheet);
    console.log(jsonData,"jsonDAta");
    
    return initial;
  }, {});
  const dataString = JSON.stringify(jsonData);
  console.log(dataString,"stringify data");
  this.jsonArr = JSON.parse(dataString)
  console.log(this.jsonArr,"parsed json");
  console.log(Object.keys(this.jsonArr['data'][0]))
  
}
reader.readAsBinaryString(file);
}

Dates are storred as a number within XLSX Files (See here or here). What you got is what is storred, so you got the number back.

According to the first reference, dates are storred as of January 1 1900. All you need to do now is have a function, that converts this number back to a date. Pure math.

Luckily, a quick SO search revealed, that this has been done before: Converting Excel Date Serial Number to Date using Javascript