how to avoid large numbers from converting to Exponential in nodejs excel file read

Question

I am want to read excel file having phone numbers stored as numbers but when I read the file using SheetJS/js-xlsx (npm install xlsx), All the large phone numbers are converted to strings like

9.19972E+11 

919971692474  --> 9.19972E+11

My code is

var workbook = XLSX.readFile(req.files.fileName.path);
var sheet_name_list = workbook.SheetNames;
var csvFile = XLSX.utils.sheet_to_csv(workbook.Sheets[sheet_name_list[0]]);
console.log(csvFile2);

console output is

customer_phone,product_name
9.19972E+13,"Red Belly Shoes,"

Is there any way I can avoid such conversion?

Answer 1

The number 919971692474 is normally displayed as 9.19972E+11 in Excel. To force it to display the full number you have to set the number format to 0 (right click, format cell, choose custom type '0'). And when you do that, the full number is displayed. If you don't set a format in excel, the xlsx module uses the "General" format and that number format displays the phone number as an exponential.

If the file is incorrect, you can override the CSV formatting by deleting the w key and adding a z key corresponding to the desired number format. For example, to change cell A2:

var sheet = workbook.Sheets[workbook.SheetNames[0]];
delete sheet.A2.w;
sheet.A2.z = '0';

If you want to do this for all number cells, just loop:

Object.keys(sheet).forEach(function(s) {
    if(sheet[s].w) {
        delete sheet[s].w;
        sheet[s].z = '0';
    }
});

Answer 2

It seems in later versions w is not there. That's how it could be done in recent versions.

const ws = XLSX.utils.json_to_sheet(data);

Object.keys(ws).forEach(function(s) {
    if(ws[s].t === 'n') {
        ws[s].z = '0';
        ws[s].t = 's';
    }
});

const csv = XLSX.utils.sheet_to_csv(ws);