Format date with SheetJS

Question

Thank you in advance for taking a look at this question! I am trying to use SheetJS to read a .xlsx file but am having some trouble with a column of dates that is formatted via Excel as Custom "yyyy/mm/dd hh:mm:ss". All of these dates show up as large float values.

Here is the code to read the .xlsx file on upload:

uploadWorkbook(e) {
    let reader = new FileReader();
    reader.readAsBinaryString(e.target.files[0]);
    reader.onload = (e) => {
        let data = e.target.result;
        let workbook = XLSX.read(data, {type: 'binary'});
        let first_sheet_name = workbook.SheetNames[0];
        let worksheet = workbook.Sheets[first_sheet_name];
        let jsonObj = XLSX.utils.sheet_to_json(worksheet);
        console.log(jsonObj);
    }
}

As an example, the first object's date value is 43395.29775462963. I would even be okay with formatting all cells as strings if this is possible. Any help would be greatly appreciated!

Thanks everyone!

Solution 1

So, I figured out that passing the raw option when converting the sheet to JSON outputs all cells as a string. So it would be:

let jsonObj = XLSX.utils.sheet_to_json(worksheet, {raw: false});

Solution 2

This is my way:

 const target = e.target.result;
 const wb = XLSX.read(target,{type:'binary',cellText:false,cellDates:true});
 const wsname = wb.SheetNames[0];
 const ws = wb.Sheets[wsname];
 const data = XLSX.utils.sheet_to_json(ws, {header:1,raw:false,dateNF:'yyyy-mm-dd'});

Solution 3

I'm not pretty sure if back then these flags existed, but currently (December 2019), you can add the flag "cellDates" in order to have it as a date instead of a number.

const workbook = XLSX.read( data, {
    ...
    cellDates: true,
});

And this is better since the data type is now a date in a date format instead of a string in a date format.

Solution 4

e.target.result was giving me undefined for data. Below solution worked for me. Check this out.

 onFileUpload($event) {
    let reader = new FileReader();
    reader.readAsBinaryString($event.target.files[0]);
    reader.onload = (e) => {
      const target = reader.result;
      const wb = XLSX.read(target, { type: 'binary', cellText: false, cellDates: true });
      const wsname = wb.SheetNames[0];
      const ws = wb.Sheets[wsname];
      const data = XLSX.utils.sheet_to_json(ws, { header: 0, raw: false, dateNF: 'yyyy-mm-dd HH:mm:ss' });

      console.log(JSON.stringify(data));
    }

  }

View more solutions

Comments

[Evan]

I use {raw: false} for sheet_to_json function, but the data ''12/21/1989" turns to be"12/12/89" and "12/21/2019" turns to be "12/2119". Auto trims the year part. Could you give some tips to fix it?

[ΔO 'delta zero']

Nice, this is what I've been looking for!

[greenie-beans]

i'm having a similar issue, not sure of a fix either.

[Rohan Shenoy]

Did u get a solution to this @greenie @Evan?

[Rohan Shenoy]

Tried this but I'm getting date in format mm/dd/yy instead of mm/dd/yyyy

[Dipen Bhikadya]

I think this is a better solution to handle date fields.

[Akhila]

What about the time zone? I'm getting the date of next day than in the sheet @Leon

[sfarzoso]

Best solution, should be accepted answer

[Hemanth B]

Same issue, it does not work

[Alex Szücs]

For those who want use dots: {dateNF:'yyyy"."mm"."dd'} ref: github.com/SheetJS/sheetjs/issues/718#issuecomment-313465344