Reading excel in javascript is converting long numbers to Exponential

Question

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

desertnaut · Accepted Answer · 2022-08-04 10:42:11Z

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

edited Aug 4, 2022 at 10:42

desertnaut

60.8k32 gold badges155 silver badges183 bronze badges

answered Jul 7, 2021 at 11:48

Parth Tagadiya

1747 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rahul_sann · Accepted Answer · 2017-10-25 07:37:00Z

2

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

answered Oct 25, 2017 at 7:37

rahul_sann

3143 silver badges9 bronze badges

Comments

Noman Ali · Accepted Answer · 2017-10-25 07:59:51Z

0

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.

answered Oct 25, 2017 at 7:59

Noman Ali

3,37014 gold badges48 silver badges79 bronze badges

Collectives™ on Stack Overflow

Reading excel in javascript is converting long numbers to Exponential

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related