Welcome folks today in this blog post we will be exporting html5 table to excel file and download it using xlsx library in typescript. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new angular project using the below command as shown below
ng new angularproject
cd angularproject
And now you need to install the library using the npm command as shown below
npm i xlsx
And now you want to edit the app.component.html file of your angular project and copy paste the code as shown below
app.component.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<div style=" margin: auto; width: 50%;"> <button (click)="exportexcel()">Export to Excel</button> <table id="excel-table"> <tr> <th>Id</th> <th>Name</th> <th>Username</th> <th>Email</th> </tr> <tr *ngFor="let item of userList"> <td>{{item.id}}</td> <td>{{item.name}}</td> <td>{{item.username}}</td> <td>{{item.email}}</td> </tr> </table> </div> |
As you can see inside this html code we have the button to export the html table to excel file and also we have defined a onclick event listener and we are calling the exportexcel() method. And also we have defined the table inside which we are using the ngFor directive to loop through all the records from the json object And here we are displaying the id,name,username and email of the user.
Now we need to edit the app.component.ts file of your angular project as shown below
app.component.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
import { Component } from '@angular/core'; import * as XLSX from 'xlsx'; @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { title = 'angular-app'; fileName= 'ExcelSheet.xlsx'; userList = [ { "id": 1, "name": "Leanne Graham", "username": "Bret", "email": "Sincere@april.biz" }, { "id": 2, "name": "Ervin Howell", "username": "Antonette", "email": "Shanna@melissa.tv" }, { "id": 3, "name": "Clementine Bauch", "username": "Samantha", "email": "Nathan@yesenia.net" }, { "id": 4, "name": "Patricia Lebsack", "username": "Karianne", "email": "Julianne.OConner@kory.org" }, { "id": 5, "name": "Chelsey Dietrich", "username": "Kamren", "email": "Lucio_Hettinger@annie.ca" } ] } |
As you can see we have defined the usersList array of json objects and we have four properties about the user and now we are displaying this userList array inside the html.
And now we will be defining the method to export the user data present inside the html5 table as shown below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
exportexcel(): void { /* pass here the table id */ let element = document.getElementById('excel-table'); const ws: XLSX.WorkSheet =XLSX.utils.table_to_sheet(element); /* generate workbook and add the worksheet */ const wb: XLSX.WorkBook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, 'Sheet1'); /* save to file */ XLSX.writeFile(wb, this.fileName); } |
As you can see inside this function we are getting the reference of the table using it’s id. And then we are converting the table to excel sheet file using the XLSX Module that we are importing at the top of the file. And then we are appending the data inside the new sheet using the append_sheet() method. And then we are using the writeFile() method to save the excel file.
