Format date with SheetJS

sivs picture sivs · Nov 6, 2018 · Viewed 19.6k times · Source

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

sivs picture sivs · Nov 6, 2018

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