Menu Close
  • Home
  • Export Data to Excel in Angular- Beginner’s Guide
    •

Export Data to Excel in Angular- Beginner’s Guide

admin 0 Comments
Post Views: 2,808

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.

#Find Source Code

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": "[email protected]",
    "age": 25
  },
  {
    "name": "John Smith",
    "designation": "Solution Architect",
    "Country": "England",
    "email": "[email protected]",
    "age": 36
  },
  {
    "name": "Raghav Rana",
    "designation": "Developer",
    "Country": "India",
    "email": "[email protected]",
    "age": 33
  },
  {
    "name": "Julie Roberts",
    "designation": "Developer",
    "Country": "USA",
    "email": "[email protected]",
    "age": 36
  },
  {
    "name": "Sundar Raghav",
    "designation": "Solution Consultant",
    "Country": "India",
    "email": "[email protected]",
    "age": 38
  },
  {
    "name": "Reba sen",
    "designation": "IT Manager",
    "Country": "Africa",
    "email": "[email protected]",
    "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": "[email protected]",
    "age": 25
  },
  {
    "name": "John Smith",
    "designation": "Solution Architect",
    "Country": "England",
    "email": "[email protected]",
    "age": 36
  },
  {
    "name": "Raghav Rana",
    "designation": "Developer",
    "Country": "India",
    "email": "[email protected]",
    "age": 33
  },
  {
    "name": "Julie Roberts",
    "designation": "Developer",
    "Country": "USA",
    "email": "[email protected]",
    "age": 36
  },
  {
    "name": "Sundar Raghav",
    "designation": "Solution Consultant",
    "Country": "India",
    "email": "[email protected]",
    "age": 38
  },
  {
    "name": "Reba sen",
    "designation": "IT Manager",
    "Country": "Africa",
    "email": "[email protected]",
    "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

export-to-excel

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.

export-to-excel-demo

What issue may come in ExcelJS and how to deal this ?

I got the below issue while using ExcelJS while run the application.

export-to-excel-excejs-error

To overcome the above issue, open the tsconfig.base.json file and make add “node” in type as like below.

export-to-excel-excejs-resolved

#Find Source Code

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 🙂

Tagged ,

Leave a Reply

Your email address will not be published.