如何避免在Node.js Excel文件读取中将大量数字转换为指数 [英] how to avoid large numbers from converting to Exponential in nodejs excel file read

问题描述

我想读取将电话号码存储为数字的excel文件，但是当我使用SheetJS/js-xlsx(npm install xlsx)读取文件时，所有大电话号码都会转换为类似

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

我的代码是

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

控制台输出为

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

有什么办法可以避免这种转换吗?

Is there any way I can avoid such conversion?

推荐答案

数字919971692474在Excel中通常显示为9.19972E + 11.要强制其显示完整的数字，您必须将数字格式设置为0(右键单击，设置单元格格式，选择自定义类型"0").然后，您会看到完整的数字.如果您未在excel中设置格式，则xlsx模块将使用常规"格式，并且该数字格式会将电话号码显示为指数.

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.

如果文件不正确，则可以通过删除w键并添加与所需数字格式相对应的z键来覆盖CSV格式.例如，要更改单元格A2:

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

这篇关于如何避免在Node.js Excel文件读取中将大量数字转换为指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！