In this article explain we discuss how to export data to excel in Angular. We are going to show a demo of excel file download using ExcelJS and File Saver plugin. If you are an Angular developer and looking for export data to excel, then this post will help you more. Please access my previous article ASP.NET Core Web API with MongoDB CRUD- Beginner’s Guide.
When we develop any enterprise application using any technology, the end-user always needs the report data in an Excel file. In enterprise applications, the customer asked us to give a downloadable excel file using various filters.
Environment setup
- Node version 12.
- Angular CLI.
- file-saver npm module.
- ExcelJS npm module.
Creating New Angular Project and Configuration
Create a new Angular project using the below command and configure the export to excel.
ng new export-excel
Install ExcelJS Plugin
ExcelJS plugin is used to read and write excel file. It is rich in functionality. You can style rows and columns. Even you can add images to excel files. That’s why many people using this library. Install ExcelJS using the below command.
npm install exceljs --save
Install File Saver
FileSaver.js is the solution to saving files on the client-side and is perfect for web apps that generate files on the client. Install file saver plugin using the below command.
npm install file-saver --save
Import ExcelJS and File Saver Plugin
Open the app.component.ts file and import the ExcelJS and File Saver plugin using the below code.
import * as Excel from 'exceljs'; import * as fs from 'file-saver';
Create a Downloadable Excel File using Dummy Json Data
First, we need data in the JSON format. In our case we create dummy data(you can get the data from the server).
employees = [{
"name": "Akash Jain",
"designation": "Developer",
"Country": "India",
"email": "akash@gmail.com",
"age": 25
},
{
"name": "John Smith",
"designation": "Solution Architect",
"Country": "England",
"email": "john.smith@gmail.com",
"age": 36
},
{
"name": "Raghav Rana",
"designation": "Developer",
"Country": "India",
"email": "raghav.ran@hotmail.com",
"age": 33
},
{
"name": "Julie Roberts",
"designation": "Developer",
"Country": "USA",
"email": "juile.roberts@gmail.com",
"age": 36
},
{
"name": "Sundar Raghav",
"designation": "Solution Consultant",
"Country": "India",
"email": "sundar113@gmail.com",
"age": 38
},
{
"name": "Reba sen",
"designation": "IT Manager",
"Country": "Africa",
"email": "reba.sen@yahoo.com",
"age": 42
}
]Configuration on app.component.ts for export to excel
Let’s do all the required things that able to do convert export to excel in app.component.ts file.
import { Component } from '@angular/core';
import * as Excel from 'exceljs';
import * as fs from 'file-saver';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css']
})
export class AppComponent {
title = 'export-excel';
emprow: number =1;
employees = [{
"name": "Akash Jain",
"designation": "Developer",
"Country": "India",
"email": "akash@gmail.com",
"age": 25
},
{
"name": "John Smith",
"designation": "Solution Architect",
"Country": "England",
"email": "john.smith@gmail.com",
"age": 36
},
{
"name": "Raghav Rana",
"designation": "Developer",
"Country": "India",
"email": "raghav.ran@hotmail.com",
"age": 33
},
{
"name": "Julie Roberts",
"designation": "Developer",
"Country": "USA",
"email": "juile.roberts@gmail.com",
"age": 36
},
{
"name": "Sundar Raghav",
"designation": "Solution Consultant",
"Country": "India",
"email": "sundar113@gmail.com",
"age": 38
},
{
"name": "Reba sen",
"designation": "IT Manager",
"Country": "Africa",
"email": "reba.sen@yahoo.com",
"age": 42
}
]
downloadExcel() {
//create new excel work book
let workbook = new Excel.Workbook();
//add name to sheet
let worksheet = workbook.addWorksheet("Employee Data");
//add column name
let header = ["Name", "Designation","Country","Email","Age"]
let headerRow = worksheet.addRow(header);
//headerRow.font = { size: 14, bold: true };
for (let x1 of this.employees) {
let x2 = Object.keys(x1);
let temp = []
for (let y of x2) {
temp.push(x1[y])
}
worksheet.addRow(temp)
}
//set downloadable file name
let fname = "Employee Sheet"
//add data and file name and download
workbook.xlsx.writeBuffer().then((data) => {
let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
fs.saveAs(blob, fname + '-' + new Date().valueOf() + '.xlsx');
});
}
}Code Explanation
- Line no. 2-3: We import the require plugins that require to convert data to excel.
- Line no. 12-54: The employee dummy Json data (You can use data pick from server).
- Line no. 57 : We create a workbook excel workbook let workbook = new Excel.Workbook();
- Line no. 59 : From the workbook, we can create a new sheet using the addWorksheet() function like let worksheet = workbook.addWorksheet(“Employee Data”);
- Line no. 61 : Add column header using the addRow() function. In the JSON data, we added the columns.
- Line no. 64-71 : Now add the JSON data to the worksheet using the for loop.
- Line no. 73-79 : Set the file name and call the write function to create a downloadable excel file.
Create Button With Click Event in app.component.html
<div class="content" role="main">
<div class="container" style="width: 100%;">
<div class="panel panel-default">
<div class="panel-heading">Demo on Export to Excel</div>
<div class="panel-body">
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Designation</th>
<th scope="col">Country</th>
<th scope="col">Email</th>
<th scope="col">Age</th>
</tr>
</thead>
<tbody>
<tr *ngFor="let emp of employees; let ndx = index">
<th scope="row">{{ndx+1}}</th>
<td>{{emp.name}}</td>
<td>{{emp.designation}}</td>
<td>{{emp.Country}}</td>
<td>{{emp.email}}</td>
<td>{{emp.age}}</td>
</tr>
</tbody>
</table>
<button class="centre btn mc" (click)="downloadExcel()">Export to Excel</button>
</div>
</div>
</div>
</div>Code Explanation
- Line no. 6-27: We make a table that should display the JSON data in tabular format. Using For loop we iterate with employee Information.
- Line no. 28: We create a button that call the TS method downloadExcel()
That’s All. Let’s run the application using command ng s –o and see the result
You can see now, the JSON data is displayed in tabular format and Export to Excel button is there. Now click on this button and you can generate the Excel according to the below image.
What issue may come in ExcelJS and how to deal this ?
I got the below issue while using ExcelJS while run the application.
To overcome the above issue, open the tsconfig.base.json file and make add “node” in type as like below.
Conclusion
Leave behind your valuable queries and suggestions in the comment section below. Also, if you think this article helps you, do not forget to share this with your developer community. Happy Coding 🙂