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?

You'd either have to check if `sheet_to_csv` has any support for number formatting, or process the sheet yourself beforehand to convert the numbers to strings. — loganfsmyth, Jan 01 '15 at 21:44
yes it displays as exponential but that file is uploaded by client. i dont have much of a choice — raju, Jan 02 '15 at 03:25

SheetJS · Accepted Answer · 2015-01-02T18:06:59.013

12

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

edited Jan 02 '15 at 18:06

answered Jan 02 '15 at 00:26

SheetJS

22,470
12
65
75

my client uploads the file and asking him to do that before uploading would not be right. – raju Jan 02 '15 at 11:47
@raju updated response to show how to proactively fix the file – SheetJS Jan 02 '15 at 18:07
what is w here? why can't I find it. – Murali Krishna Mar 05 '20 at 18:51
1

@MuraliKrishna That's because it was there in old version.I'll a new answer for it. – lone_worrior Jun 11 '20 at 14:13

score 2 · Answer 2 · answered Jul 07 '21 at 11:53

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

var csvFile = XLSX.utils.sheet_to_csv(workbook.Sheets[sheet_name_list[0]], { rawNumbers: true });

score 1 · Answer 3 · answered Jun 11 '20 at 14:14

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

score 1 · Answer 4 · answered Aug 24 '20 at 18:14

Using sheet[s].z = '0'; works in removing the scientific notation, but it also removes any decimal places you might want to retain. From the readme:

The cell.w formatted text for each cell is produced from cell.v and cell.z format.

I was able to remove the scientific notation by explicitly setting the value of w instead of letting xlsx calculate it for me:

if (cell.t === 'n') {
  cell.w = cell.v;
}

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

4 Answers4

By default `sheet_to_csv` take the formatted numbers.

Linked

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

4 Answers4

By default sheet_to_csv take the formatted numbers.

Linked

By default `sheet_to_csv` take the formatted numbers.