Date format changing when converting xlsx to json

Question

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}]}

That's because Excel stores dates and times as a number of days since an epoch, midnight on 30 December 1899 (IIRC). — phuzi, Jul 07 '20 at 08:28
See https://stackoverflow.com/questions/15549823/oadate-to-milliseconds-timestamp-in-javascript for a way to convert this number to a JavaScript Date. — phuzi, Jul 07 '20 at 08:29
Does this answer your question? [OADate to Milliseconds timestamp in Javascript](https://stackoverflow.com/questions/15549823/oadate-to-milliseconds-timestamp-in-javascript) — phuzi, Jul 07 '20 at 08:29

Owais Ahmed Khan · Accepted Answer · 2020-07-07T08:24:51.943

4

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);
}

edited Jul 07 '20 at 08:24

answered Jul 07 '20 at 08:10

Owais Ahmed Khan

218
1
4
12

Tried it! but no success :( Thanks for your response :) – Usama Tariq Jul 07 '20 at 08:13
Yes, i got the date but in long format, is there any way to format this as given in my excel sheet. Thank you for help! much appreciated :) – Usama Tariq Jul 07 '20 at 08:32

score 0 · Answer 2 · answered Jul 07 '20 at 08:11

0

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

answered Jul 07 '20 at 08:11

Zim84

3,404
2
35
40

Actually i used the code from this website https://stackblitz.com/edit/xlsx-to-json?file=src%2Fapp%2Fapp.component.ts It is returning the correct date value if i try running the example given in this website. – Usama Tariq Jul 07 '20 at 08:19

Date format changing when converting xlsx to json

2 Answers2