angular 11 export excel with customize header
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);
})
}
Run code snippet