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

问题描述

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

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

   public downloadExcel(json: any[], excelFileName: string, merges?: any): void {让工作表:XLSX.WorkSheet；如果(合并){工作表 = XLSX.utils.aoa_to_sheet(json);工作表['!merges'] = 合并；} 别的 {工作表 = XLSX.utils.json_to_sheet(json);}const 工作簿: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);}私人 saveAsExcelFile(buffer: any, fileName: string): void {常量数据:Blob = new Blob([buffer], {类型:'应用程序/八位字节流'});FileSaver.saveAs(data, fileName + "_" + new Date().toLocaleDateString() + "_" + new Date().toLocaleTimeString() + EXCEL_EXTENSION);}

<块引用>

所以我想改变 C,D,E,F 列的数据类型，这样我就可以直接对那列求和，

解决方案

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

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

这样您将不带逗号存储数字，以便 Excel 会自动将该列视为数字.

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

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

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

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

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

解决方案

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

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

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

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屋！