javascript - Date format changing when converting xlsx to json - Stack Overflow

admin管理员组

文章数量:1332896

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

Share Improve this question Follow asked Jul 7, 2020 at 8:02 Usama TariqUsama Tariq 10511 gold badge33 silver badges77 bronze badges 3

• 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

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 6

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

本文标签： javascriptDate format changing when converting xlsx to jsonStack Overflow