How to Export Data to Excel Tutorial

Angular 11/12 export data excel sheet format example. In this tutorial, we will comprehensively learn how to export excel sheet file in Angular 11/12 app.

This Angular tutorial is compatible with version 4+ including latest version 12, 11, 10, 9, 8 ,7, 6 & 5.

This tutorial guide will help in implementing Angular 12 PrimeNG Data Table with Export to Excel step by step .

 

How to Export Data In Excel Sheet Using Angular 12

Simply follow the below given steps for exporting the data in excel format in Angular 12 app

  • Step 1 – Create a New Angular App
  • Step 2 – Install bootstrap and primeng Library
  • Step 3 – Add Code on App.Module.ts File
  • Step 4 – Add Code on View File
  • Step 5 – Add Code On app.Component ts File
  • Step 6 – Start the Angular App

Step 1 – Create a New Angular App

Firstly, we open the terminal and execute the following command on it to install angular app:

ng new export-data-app

Step 2 – Install the Bootstrap and Primeng Library

Secondly, we need to install NPM package called ng-bootstrap/ng-bootstrap. It is done for implementing bootstrap carousel in Angular 11 app. Therfore, we can install the packages by executing the following commands on the terminal:

npm install jquery --save
ng add @ng-bootstrap/ng-bootstrap
npm install primeng --save 
npm install primeicons --save  
npm install @angular/cdk --save 
npm i file-saver npm i xlsx 
npm install primeflex --save

Step 3 – Adding Code on App.Module.ts File

Further, we have to visit src/app directory and open app.module.ts file. Afterwards, add the following lines of into app.module.ts file:

...
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
import { TableModule } from 'primeng/table';
import { DropdownModule } from 'primeng/dropdown';
import { ButtonModule } from 'primeng/button';
 
@NgModule({
 ...
  imports: [
   ...
    BrowserAnimationsModule,
    TableModule,
    DropdownModule,
    ButtonModule
  ],
  ...

Step 4 – Adding Code on View File

Fourth step is that we create bootstrap carousel in angular 11 app. Hence, visit src/app/ and app.component.html and update the following code into it:

<p-table [value]="sales">
    <ng-template pTemplate="caption">
        <div class="p-d-flex">
            
            <button type="button" pButton pRipple icon="pi pi-file-excel" (click)="exportExcel()" class="p-button-success p-mr-2"></button>
            
        </div>
    </ng-template>
    <ng-template pTemplate="header">
        <tr>
             
            <th pSortableColumn="brand">Brand <p-sortIcon field="brand"></p-sortIcon></th>
            <th>Last Year Sale</th>
            <th>This Year Sale</th>
            <th>Last Year Profit</th>
            <th>This Year Profit</th>
        </tr>
         
    </ng-template>
    <ng-template pTemplate="body" let-sale>
        <tr>
            <td>{{sale.brand}}</td>
            <td>{{sale.lastYearSale}}</td>
            <td>{{sale.thisYearSale}}</td>
            <td>{{sale.lastYearProfit}}</td>
            <td>{{sale.thisYearProfit}}</td>
        </tr>
    </ng-template>
</p-table>

Step 5 – Adding Code On app.Component ts File

In addition, we have to move to next step. In this, we visit the src/app directory and open app.component.ts. Then add the following code into component.ts file:

...
export class AppComponent {
 ...
  ngOnInit() {
    this.sales = [
      { brand: 'Apple', lastYearSale: '51%', thisYearSale: '40%', lastYearProfit: '$54,406.00', thisYearProfit: '$43,342' },
      { brand: 'Samsung', lastYearSale: '83%', thisYearSale: '96%', lastYearProfit: '$423,132', thisYearProfit: '$312,122' },
      { brand: 'Microsoft', lastYearSale: '38%', thisYearSale: '5%', lastYearProfit: '$12,321', thisYearProfit: '$8,500' },
      { brand: 'Philips', lastYearSale: '49%', thisYearSale: '22%', lastYearProfit: '$745,232', thisYearProfit: '$650,323,' },
      { brand: 'Song', lastYearSale: '17%', thisYearSale: '79%', lastYearProfit: '$643,242', thisYearProfit: '500,332' },
      { brand: 'LG', lastYearSale: '52%', thisYearSale: ' 65%', lastYearProfit: '$421,132', thisYearProfit: '$150,005' },
      { brand: 'Sharp', lastYearSale: '82%', thisYearSale: '12%', lastYearProfit: '$131,211', thisYearProfit: '$100,214' },
      { brand: 'Panasonic', lastYearSale: '44%', thisYearSale: '45%', lastYearProfit: '$66,442', thisYearProfit: '$53,322' },
      { brand: 'HTC', lastYearSale: '90%', thisYearSale: '56%', lastYearProfit: '$765,442', thisYearProfit: '$296,232' },
      { brand: 'Toshiba', lastYearSale: '75%', thisYearSale: '54%', lastYearProfit: '$21,212', thisYearProfit: '$12,533' }
  ];
   
}
 
//excel button click functionality
exportExcel() {
  import("xlsx").then(xlsx => {
      const worksheet = xlsx.utils.json_to_sheet(this.sales); // Sale Data
      const workbook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
      const excelBuffer: any = xlsx.write(workbook, { bookType: 'xlsx', type: 'array' });
      this.saveAsExcelFile(excelBuffer, "sales");
  });
}
saveAsExcelFile(buffer: any, fileName: string): void {
  import("file-saver").then(FileSaver => {
    let EXCEL_TYPE =
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
    let EXCEL_EXTENSION = ".xlsx";
    const data: Blob = new Blob([buffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(
      data,
      fileName + "_export_" + new Date().getTime() + EXCEL_EXTENSION
    );
  });
}
 
}

Step 6 – Starting the Angular App

Finally, we reach the end step of this tutorial where you execute the following command on terminal to start angular bootstrap carousel app:

ng serve

Conclusion

So friends, we hope that you all have well understood the concept and steps of this tutorial. We have tried our best to keep it easy and simple. Please do share with your friends if you liked it.

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *