typescript Angular 6 - 格式化导出 Excel 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/52990621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Angular 6 - Formatting Export Excel Sheet
提问by Unicorn
i would like to apply styling in excel sheet to represent the data. Here is my code
我想在 Excel 表中应用样式来表示数据。这是我的代码
import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';
const EXCEL_TYPE = 'application/vnd.openxmlformats-
officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
@Injectable()
export class ExcelService {
constructor() { }
public exportAsExcelFile(json: any[], excelFileName: string): void {
var aoa = this.create2DArray(json, excelFileName);
const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet(aoa);
console.log('worksheet',worksheet);
const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
this.saveAsExcelFile(excelBuffer, excelFileName);
}
I am able to download the file along with data but not in formatted style.
我可以下载文件和数据，但不能以格式化的方式下载。
Formatted Excel-
格式化Excel-
Can anyone advice some piece of information on same.
任何人都可以建议一些相同的信息。
Thanks in advance.
提前致谢。
回答by anurag shahi
Hi you can style xlsx sheet using 'exceljs' library. You can style or customize your sheet as you want.
嗨，您可以使用 'exceljs' 库设置 xlsx 表的样式。您可以根据需要设计或自定义您的工作表。
Step 1: npm install exceljs, npm install file-saver
Step 2: import { Workbook } from 'exceljs'; import * as fs from 'file-saver';
第一步：npm install exceljs, npm install file-saver
步骤2：从'exceljs'导入{工作簿}；从“文件保护程序”导入 * 作为 fs；
Add these two variables in ts file where you want to use these
将这两个变量添加到 ts 文件中要使用这些变量的位置
const EXCEL_TYPE = 'application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
Step 3: Add below code in tsconfig.json
const EXCEL_TYPE = 'application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
第 3 步：在 tsconfig.json 中添加以下代码
"paths": {
"exceljs": [
"node_modules/exceljs/dist/exceljs.min"
]
}public exportAsExcelFile(json: any[], excelFileName: string, headersArray: any[]): void {
//Excel Title, Header, Data
const header = headersArray;
const data = json;
//Create workbook and worksheet
let workbook = new Workbook();
let worksheet = workbook.addWorksheet(excelFileName);
//Add Header Row
let headerRow = worksheet.addRow(header);
// Cell Style : Fill and Border
headerRow.eachCell((cell, number) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFFFFF00' },
bgColor: { argb: 'FF0000FF' }
}
cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
})
// Add Data and Conditional Formatting
data.forEach((element) => {
let eachRow = [];
headersArray.forEach((headers) => {
eachRow.push(element[headers])
})
if (element.isDeleted === "Y") {
let deletedRow = worksheet.addRow(eachRow);
deletedRow.eachCell((cell, number) => {
cell.font = { name: 'Calibri', family: 4, size: 11, bold: false, strike: true };
})
} else {
worksheet.addRow(eachRow);
}
})
worksheet.getColumn(3).width = 15;
worksheet.getColumn(4).width = 20;
worksheet.getColumn(5).width = 30;
worksheet.getColumn(6).width = 30;
worksheet.getColumn(7).width = 10;
worksheet.addRow([]);
//Generate Excel File with given name
workbook.xlsx.writeBuffer().then((data) => {
let blob = new Blob([data], { type: EXCEL_TYPE });
fs.saveAs(blob, excelFileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
})
}