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!

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

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

Answer 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.

Answer 4

xlsx file data in JSON object with date format you can try with below example

reader.onload = function(e) {
    var data = e.target.result;
    var workbook = XLSX.read(data, {
        header: 1,
        raw: false,
        dateNF: 'dd/MM/yyyy'
    });
    for (var i = 0; i < workbook.SheetNames.length; i++) {
        var XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[i]], {
            header: 0,
            raw: false
        });
        Console.log(XL_row_object;)
        break;
    }
};
reader.readAsArrayBuffer(files);

Answer 5

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

  }

Answer 6

Had the same issue, applied the same code as @Charles G

onFileChange(event) {
        this.file = event.target.files ? event.target.files[0] : null;

        var data = new Uint8Array(this.file)
        
        const  reader = new FileReader();

        if (data) {

            reader.onload = (e) => {
                /* Parse data */
                console.log('Parse data');
                const bstr = e.target.result;
                const wb = XLSX.read(bstr, {type:'binary',cellText:false,cellDates:true});
                /* Get first worksheet */
                console.log('Get first worksheet');
                const wsname = wb.SheetNames[0];
                const ws = wb.Sheets[wsname];
                /* Convert array of arrays */
                console.log('Convert array of arrays');
                const data = XLSX.utils.sheet_to_json(ws, {header:1,raw:false,dateNF:'yyyy-mm-dd'});
                console.log(JSON.stringify(data));
            }

            reader.readAsBinaryString(this.file);
        }
    }

Answer 7

If reading data by cell. For example. worksheet['A1'].v

Instead of .v use .w to get the raw data. So use worksheet['A1'].w

Wasted a lot of time figuring it out in my project.

Answer 8

const element = document.getElementById('excel-table');
const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(element);

in "ws" date format like a this json

{
  "t": "n",
  "v": 44933.000555555554,
  "z": "m/d/yy"
}

so we know date have type (t) number (n) and z is "m/d/yy"

Object.keys(ws).forEach(cell => {
      const cellValue = ws[cell];

      if (cellValue.t === 'n' && cellValue.z === 'm/d/yy') {
        const excelDate = cellValue.v;
        const jsDate = new Date((excelDate - (25567 + 1)) * 86400 * 1000);
        const formattedDate = `${String(jsDate.getDate()).padStart(2, '0')}.${String(jsDate.getMonth() + 1).padStart(2, '0')}.${jsDate.getFullYear()}`;
        ws[cell].v = formattedDate;
        ws[cell].t = 's';
      }
    });

and do const wb: XLSX.WorkBook = XLSX.utils.book_new();...