I am reading an excel file using SheetJs, but the issue is it's converting long numbers like 3577888990098 to exponential like 3.52E+12.

This question is not duplicate, because:

File columns can be random and system won't know which ones are numbers and which ones are Strings (Alphabetical) or both.

So how to get rid of this?

Here is my code:

function handleFileSelect(evt) {
    var files = evt.target.files;
    var i, f;
    //Loop through files
    for (i = 0, f = files[i]; i != files.length; ++i) {
        var name = f.name;
        const reader = new FileReader();
        reader.onload = (evt) => {
            /* Parse data */
            const bstr = evt.target.result;
            const wb = XLSX.read(bstr, {type:'binary'});
            /* Get first worksheet */
            const wsname = wb.SheetNames[0];
            const ws = wb.Sheets[wsname];
            /* Convert array of arrays */
            const data = XLSX.utils.sheet_to_csv(ws, {header:1});
            /* Update state */
            console.log("DATA>>>"+data);
        };
        reader.readAsBinaryString(f);
    }
}

I am reading an excel file using SheetJs, but the issue is it's converting long numbers like 3577888990098 to exponential like 3.52E+12.

This question is not duplicate, because:

File columns can be random and system won't know which ones are numbers and which ones are Strings (Alphabetical) or both.

So how to get rid of this?

Here is my code:

function handleFileSelect(evt) {
    var files = evt.target.files;
    var i, f;
    //Loop through files
    for (i = 0, f = files[i]; i != files.length; ++i) {
        var name = f.name;
        const reader = new FileReader();
        reader.onload = (evt) => {
            /* Parse data */
            const bstr = evt.target.result;
            const wb = XLSX.read(bstr, {type:'binary'});
            /* Get first worksheet */
            const wsname = wb.SheetNames[0];
            const ws = wb.Sheets[wsname];
            /* Convert array of arrays */
            const data = XLSX.utils.sheet_to_csv(ws, {header:1});
            /* Update state */
            console.log("DATA>>>"+data);
        };
        reader.readAsBinaryString(f);
    }
}

• javascript
• jquery

3 Answers 3

By default sheet_to_csv take the formatted numbers.

To avoid the formatted value and to take raw inputs (original values) you have to add parameter in sheet_to_csv method you have to set rawNumbers to true

Try this code

const data = XLSX.utils.sheet_to_csv(worksheet, { rawNumbers: true });

How about using toFixed() for only the Number objects

var x = 3577888990098;
var y = '3577888990098';

if(typeof(x) === 'number') {
    x = x.toFixed(); // it will not convert it to exponential.
}

I have resolved this by:

//Assuming there will not be any "+" or "E+" in any values of sheet.
if(cell_data[cell_count].indexOf('E+') !== -1 || cell_data[cell_count].indexOf('+') !== -1) {
    console.log(cell_data[cell_count] +" IS EXPONENTIAL");
    fixedNumber = Number(cell_data[cell_count]) // it will not convert exponential to number.
    onerow.push(fixedNumber);
}else{
    onerow.push(cell_data[cell_count]);
}

As there is no JS function to check if value if Exponential.