How to avoid big numbers from converting to Exponential file in nodejs excel file
I want to read an excel file with phone numbers stored as numbers, but when I read the file using SheetJS / js-xlsx (npm install xlsx) all large phone numbers are converted to strings like
9.19972E+11
919971692474 --> 9.19972E+11
My code
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
customer_phone,product_name
9.19972E+13,"Red Belly Shoes,"
Is there a way to avoid this conversion?
source to share
The number 919971692474 is usually displayed as 9.19972E + 11 in Excel. To make it display the full number, you have to set the number format to 0 (right click, format cell, custom type selection "0"). And when you do, the full number is displayed. If you don't specify the format in excel, the xlsx module uses the "General" format and this number format displays the phone number as an exponent.
If the file is wrong, you can override the CSV formatting by removing the key w
and adding a key z
corresponding to the number format you want. 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 numeric cells, just loop:
Object.keys(sheet).forEach(function(s) {
if(sheet[s].w) {
delete sheet[s].w;
sheet[s].z = '0';
}
});
source to share