In this article, we saw how to export the excel file of the table. In these article, we generate an Excel file on the client-side.
Client Side Download Excel: It’s helpful to reduce the API call on the server and then download.
In this article we use 2 libraries “ExcelJS” & “FileSaver”. Let’s start to create a new project then install these modules.
ng new excel-generate
“excel-generate” you need to write your application’s name. Then it will take some time to create the project. After successfully installing that, you need to go to their directory. For example “cd excel-generate”. To Run angular applications, it required to run “ng serve”.
After we need to install “ExcelJS” & “FileSaver”
npm i exceljs
npm i file-saver
After install module. we create service for download the excel file we generate. below command run to create service.
ng generate service excel
We need to import “exceljs” & “file-saver”
import { Workbook } from 'exceljs';
import * as fs from 'file-saver';
Display below full code for the excel service.
import { Injectable } from '@angular/core';
import { Workbook } from 'exceljs';
import * as fs from 'file-saver';
@Injectable({
providedIn: 'root'
})
export class ExcelService {
constructor() {
}
async generateExcel() {
// Excel Title, Header, Data
const title = 'Yearly Social Sharing Education For Betterment';
const header = ['Year', 'Month', 'Facebook', 'Reddit', 'LinkedIn', 'Instagram'];
const data = [
[2019, 1, '50', '20', '25', '20'],
[2019, 2, '80', '20', '25', '20'],
[2019, 3, '120', '20', '25', '20'],
[2019, 4, '75', '20', '25', '20'],
[2019, 5, '60', '20', '25', '20'],
[2019, 6, '80', '20', '25', '20'],
[2019, 7, '95', '20', '25', '20'],
[2019, 8, '55', '20', '25', '20'],
[2019, 9, '45', '20', '25', '20'],
[2019, 10, '80', '20', '25', '20'],
[2019, 11, '90', '20', '25', '20'],
[2019, 12, '110', '20', '25', '20'],
];
// Create workbook and worksheet
const workbook = new Workbook();
const worksheet = workbook.addWorksheet('Sharing Data');
// Add Row and formatting
const titleRow = worksheet.addRow([title]);
titleRow.font = { name: 'Corbel', family: 4, size: 16, underline: 'double', bold: true };
worksheet.addRow([]);
const subTitleRow = worksheet.addRow(['Date : 06-09-2020']);
worksheet.mergeCells('A1:D2');
// Blank Row
worksheet.addRow([]);
// Add Header Row
const 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(d => {
const row = worksheet.addRow(d);
const qty = row.getCell(5);
let color = 'FF99FF99';
if (+qty.value < 500) {
color = 'FF9999';
}
qty.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: color }
};
}
);
worksheet.getColumn(3).width = 30;
worksheet.getColumn(4).width = 30;
worksheet.addRow([]);
// Footer Row
const footerRow = worksheet.addRow(['This is system generated excel sheet.']);
footerRow.getCell(1).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFCCFFE5' }
};
footerRow.getCell(1).border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
// Merge Cells
worksheet.mergeCells(`A${footerRow.number}:F${footerRow.number}`);
// Generate Excel File with given name
workbook.xlsx.writeBuffer().then((data: any) => {
const blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
fs.saveAs(blob, 'SocialShare.xlsx');
});
}
}
After creating an excel service. Let’s create app.component.ts for social sharing reports in excel. Display in the below code.
Below is the full embeded code export Excel file in Angular using “ExcelJS” & “FileSaver”. you can easily used in your application.
Example