将XLSX列数据类型更改为Angular 5中的数字 [英] change XLSX column datatype to number in Angular 5

问题描述

1. 我正在使用 xlsx 库导出到excel("xlsx":"0.16.1" )

2. 我有三列带有千位分隔符的数字，所以当我导出该数据时，它将被视为字符串，但是我想要数字类型，这是导出到Excel文件的代码

1. i am using xlsx library for export to excel ("xlsx": "0.16.1")

2. i have three columns with thousand separator number, so when i export that data then it will consider as a string, but i want number type, here is code of Export to Excel file

public downloadExcel(json: any[], excelFileName: string, merges?: any): void {
    let worksheet: XLSX.WorkSheet;
    if (merges) {
        worksheet = XLSX.utils.aoa_to_sheet(json);
        worksheet['!merges'] = merges;
    } else {
        worksheet = XLSX.utils.json_to_sheet(json);
    }

    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };

    var fmt = '0'; 
    workbook.Sheets['data']['C2'].z = fmt;

    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
}

private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
        type: 'application/octet-stream'
    });
    FileSaver.saveAs(data, fileName + "_" + new Date().toLocaleDateString() + "_" + new Date().toLocaleTimeString() + EXCEL_EXTENSION);
}

所以我想更改 C，D，E，F 列数据类型，所以我可以直接求和该列，

so i want to change C,D,E,F column data type, so i can directly SUM that column,

推荐答案

在通过JSON生成Excel之前，通过从数字中删除Comma(，)并将其转换为数字来修改JSON，例如:

Before generating Excel from JSON, modify JSON by removing Comma(,) from numbers and convert them into numbers like:

parseInt('10,340'.replace(/,/g, ''))

这样，您将存储数字时不带逗号，因此Excel将自动将该列视为数字.

This way you will store the number without comma so Excel will consider that column as a number automatically.

如果您有任何疑问，请告诉我.

Let me know if you have any questions.

------------编辑------------

------------EDIT------------

您可以使用上面显示的原始编号替换逗号(，)，并使用列格式#，## 0.00 .这样，它将在每千个点显示逗号，但实际上会将其存储为数字，以便您可以执行所有算术运算

You can replace the comma(,) from the original number as shown above and use column formatting #,##0.00. This way it will display comma at every thousand points but actually it will store it as a number so you can do all arithmetic operations

这篇关于将XLSX列数据类型更改为Angular 5中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！