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
– loganfsmyth, Commented Jan 1, 2015 at 21:44
yes it displays as exponential but that file is uploaded by client. i dont have much of a choice — raju
– raju, Commented Jan 2, 2015 at 3:25

SheetJS · Accepted Answer · 2015-01-02 18:06:59Z

13

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 2, 2015 at 18:06

answered Jan 2, 2015 at 0:26

SheetJS

23k12 gold badges68 silver badges76 bronze badges

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

4 Comments

raju Over a year ago

my client uploads the file and asking him to do that before uploading would not be right.

SheetJS Over a year ago

@raju updated response to show how to proactively fix the file

Murali Krishna Over a year ago

what is w here? why can't I find it.

lone_worrior Over a year ago

@MuraliKrishna That's because it was there in old version.I'll a new answer for it.

lone_worrior · Accepted Answer · 2020-06-11 14:14:48Z

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

answered Jun 11, 2020 at 14:14

lone_worrior

2422 silver badges16 bronze badges

Comments

Parth Tagadiya · Accepted Answer · 2024-02-29 12:24:00Z

2

By default sheet_to_csv takes the formatted numbers.

To avoid the formatted value and to take raw inputs (original values) you have to add a 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 }
              );

edited Feb 29, 2024 at 12:24

answered Jul 7, 2021 at 11:53

Parth Tagadiya

1747 bronze badges

1 Comment

Elha Over a year ago

This worked for me, but I used: {raw: true} instead of: { rawNumbers: true }

Irving · Accepted Answer · 2020-08-24 18:14:53Z

1

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

answered Aug 24, 2020 at 18:14

Irving

1,2822 gold badges17 silver badges29 bronze badges

Comments

Evan Schumacher · Accepted Answer · 2024-12-19 23:29:02Z

0

Pulling from a couple of the above comments, I fixed it with the following:

const exponentialFormatRegex = /^[+-]?\d+(\.\d+)?e[+-]?\d+$/i;
Object.keys(workSheet).forEach(function(s) {
  if (
    workSheet[s].w && // If the cell has a formatted value
    workSheet[s].t === 'n' && // If the cell is a number
    exponentialFormatRegex.test(workSheet[s].w) // If the formatted value is in scientific notation
  ) {
    // Set the formatted value to the raw value
    workSheet[s].w = workSheet[s].v;
  }
});

answered Dec 19, 2024 at 23:29

Evan Schumacher

1

Collectives™ on Stack Overflow

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

5 Answers 5

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related