Here, we will learn about how can we read data from JSON using API and download it in excel format in Angular 7. We will first read the data from the JSON API and then download the data in excel format using Angular 7.
Prerequisites
- Basic knowledge of Angular 7
- Node JS must be installed
- Angular CLI must be installed
- An editor like Visual Studio Code
Let’s get started.
Create a new project from the terminal.
Open the newly created project in the Visual Studio Code.
Install the packages required for the operation
npm install file-saver --save npm install xlsx --save
Create a service named excel-services.service.ts file for the operation using the terminal
ng g service ./services/excel-services
Install the bootstrap and add it in angular.json file
npm install bootstrap
Now we are all done with installation and setup.
Open the excel-services.service.ts file and add the code in it.
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({
providedIn: 'root'
})
export class ExcelServicesService {
constructor() { }
public exportAsExcelFile(json: any[], excelFileName: string): void {
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
this.saveAsExcelFile(excelBuffer, excelFileName);
}
private saveAsExcelFile(buffer: any, fileName: string): void {
const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});
FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
}
}
Code for app.component.ts file
import { Component, OnInit } from '@angular/core';
import { ExcelServicesService } from './services/excel-services.service';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css']
})
export class AppComponent implements OnInit {
title = 'excel-upload-download';
ngOnInit(){
}
excel=[];
constructor(private excelService:ExcelServicesService,private http: HttpClient){
this.getJSON().subscribe(data => {
data.forEach(row => {
this.excel.push(row);
});
});
}
exportAsXLSX():void {
this.excelService.exportAsExcelFile(this.excel, 'sample');
}
public getJSON(): Observable<any> {
return this.http.get('https://api.myjson.com/bins/zg8of');
}
}
Open app.component.html file and add the code in it.
<h3>Generate and Download Excel File in Angular 7</h3>
<div class="container">
<button (click)="exportAsXLSX()" class="btn btn-info mb-4 mt-2">Download Excel</button>
<table class="table table-striped table-bordered table-hover">
<tr>
<th>Name</th>
<th>Month</th>
<th>Sales</th>
<th>Percentage</th>
</tr>
<tr *ngFor="let item of excel">
<td>{{item.Name}}</td>
<td>{{item.Month}}</td>
<td>{{item.Sales_Figure}}</td>
<td>{{item.Perc}}</td>
</tr>
</table>
</div>
Code for app.module.ts file
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { HttpClientModule } from '@angular/common/http';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
@NgModule({
declarations: [
AppComponent
],
imports: [
BrowserModule,
AppRoutingModule,
HttpClientModule
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }
Open the index.html file present at root folder and add the font awesome reference in it.
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>ExcelUploadDownload</title> <base href="/"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="icon" type="image/x-icon" href="favicon.ico"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> </head> <body> <app-root></app-root> </body> </html>
Output:
Hi Can i add multiple tables in excel using multiple sheets… code i wanted to know
Hu, will it work if we have some formula aapied at the first column ????
Hey! You can try to use ZetExcel.com. It is very helpful and useful.