Welcome to %s forums

<No. 1 Community for developers, by developers/>

Login Register

How to export JSON data to excel using Angular?

Get support on Angular & Typescript related technology.
Post Reply
admin
Site Admin
Posts: 45

How to export JSON data to excel using Angular?

Post by admin » Fri Aug 23, 2019 4:35 pm

For this create a service as services/excel.service.ts

Code: Select all

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()
export class ExcelService {

  constructor() { }

  public exportAsExcelFile(json: any[], user: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(
      user
      , { skipHeader: true });
    XLSX.utils.sheet_add_json(worksheet, json, { origin: `A${user.length + 2}` });
    console.log('worksheet', worksheet);
    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);
  }
}
app.component.css

Code: Select all

p {
  font-family: Lato;
}
td {
  border: 1px solid black;
}
app.component.html

Code: Select all

<h1>Sample Heading</h1>
<p>Sub Heading</p>

<button (click)="exportAsXLSX()"><i class="fa fa-file-excel-o" style="font-size:48px;color:blue"></i></button>
<table>
	<tr>
		<td>Eid</td>
		<td>Ename</td>
		<td>Esal</td>
	</tr>
	<tr *ngFor="let item of data">
		<td>{{item.eid}}</td>
		<td>{{item.ename}}</td>
		<td>{{item.esal}}</td>
	</tr>
</table>
app.component.ts

Code: Select all

import { Component } from '@angular/core';
import { ExcelService } from './services/excel.service';
@Component({
  selector: 'my-app',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent {
  name = 'Angular';
  data: any = [{
    eid: 'e101',
    ename: 'ravi',
    esal: 1000
  },
  {
    eid: 'e102',
    ename: 'ram',
    esal: 2000
  },
  {
    eid: 'e103',
    ename: 'rajesh',
    esal: 3000
  }];

  user: any = [{ key: 'Name', data: 'Aish' }, { key: 'Age', data: 10 }, { key: 'Gender', data: 'Female' }];

  constructor(private excelService: ExcelService) {

  }
  exportAsXLSX(): void {
    this.excelService.exportAsExcelFile(this.data, this.user, 'sample');
  }
}
app.module.ts

Code: Select all

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { FormsModule } from '@angular/forms';

import { AppComponent } from './app.component';

import { ExcelService } from './services/excel.service';

@NgModule({
  imports:      [ BrowserModule, FormsModule ],
  declarations: [ AppComponent ],
  bootstrap:    [ AppComponent ],
  providers: [ExcelService]
})
export class AppModule { }
Created a demo here https://stackblitz.com/edit/angular-exp ... cussioncom

Post Reply