Export Data to Excel in Angular- Beginner’s Guide

Export Data to Excel in Angular- Beginner’s Guide

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 our previous article Angular 10 with ASP.NET Web API-CRUD Operations.

Find source code in GitHub

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.

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": "akash@gmail.com",
    "age": 25
  },
  {
    "name": "John Smith",
    "designation": "Solution Architect",
    "Country": "England",
    "email": "john.smith@gmail.com",
    "age": 36
  },
  {
    "name": "Raghav Rana",
    "designation": "Developer",
    "Country": "India",
    "email": "raghav.ran@hotmail.com",
    "age": 33
  },
  {
    "name": "Julie Roberts",
    "designation": "Developer",
    "Country": "USA",
    "email": "juile.roberts@gmail.com",
    "age": 36
  },
  {
    "name": "Sundar Raghav",
    "designation": "Solution Consultant",
    "Country": "India",
    "email": "sundar113@gmail.com",
    "age": 38
  },
  {
    "name": "Reba sen",
    "designation": "IT Manager",
    "Country": "Africa",
    "email": "reba.sen@yahoo.com",
    "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": "akash@gmail.com",
    "age": 25
  },
  {
    "name": "John Smith",
    "designation": "Solution Architect",
    "Country": "England",
    "email": "john.smith@gmail.com",
    "age": 36
  },
  {
    "name": "Raghav Rana",
    "designation": "Developer",
    "Country": "India",
    "email": "raghav.ran@hotmail.com",
    "age": 33
  },
  {
    "name": "Julie Roberts",
    "designation": "Developer",
    "Country": "USA",
    "email": "juile.roberts@gmail.com",
    "age": 36
  },
  {
    "name": "Sundar Raghav",
    "designation": "Solution Consultant",
    "Country": "India",
    "email": "sundar113@gmail.com",
    "age": 38
  },
  {
    "name": "Reba sen",
    "designation": "IT Manager",
    "Country": "Africa",
    "email": "reba.sen@yahoo.com",
    "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

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.

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

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

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

Source Code

Click to get Source Code

Summary

In this article we discussed how to export data into excel in Angular . This post will be very helpful if you are looking for a downloadable excel file using Angular. The ExcelJS plugin offers styling rows and columns and even you can add images too. It had many other functionalities too. You can check it out using the below link.

For more details about ExcelJs visit this link.

Latest Post

Export data into Excel in ASP.NET MVC
Export-data-into-Excel

In this example we learn how to export the data into MS Excel in ASP.NET MVC without using any 3rd Read more

Angular 10 with ASP.NET Web API-CRUD Operations
Angular-10-with-ASP.NET-Web-API-CRUD-Operations

Angular CRUD operation using ASP .NET Web API with Entity Framework Code first approach in SQL Server. Beginners guide to Read more

How to create custom pipes in Angular
How to create custom pipes in Angular

A pipe takes in data as input and transforms it to a desired output. Simply you can say Pipes are Read more

How to use angular material in angular
How to use angular material in angular

UI component infrastructure and Material Design components for mobile and desktop Angular web applications.

Export data into Excel in ASP.NET MVC

Export data into Excel in ASP.NET MVC

In this article we learn how to export the data into MS Excel in ASP.NET MVC without using any 3rd party libraries. If you are new to ASP.NET MVC to learn the step by step process please follow the ASP.NET MVC Series.


We take an example to print the dummy data into a ASP .NET MVC page and there we apply to export data into MS Excel.

  • Create a ASP.NET MVC application using Visual Studio.
  • We select as Empty project with ASP.NET MVC
  • Create a Controller inside the controllers folder, let’s name as “CustomerController.cs”
  • Add a customer model class in Models folder.
  • In the “CustomerController” we create a dummy list that contains multiple customers. You can connect with database and fetch the real-time data.
Customer Model Class
Customer Model Class
Customer Controller
Customer Controller
Code Explanation
  • We take a dummy data to return the customer details inside the view page.
  • GetCustomer() holds the customer information.
  • When Index() action method is called it collect the customer information and send to the view.
  • We use the scaffolding technique to generate the list of HTML using customer model class
Customer View
Customer View Page
Code Explanation
  • The customer information is printed using scaffold.
  • We create here a Export button and when it click it generate the Excel.
  • The view screen look like below;
Customer Information
Create Export Post method to convert the data into Excel

As we already discuss we are not using any 3rd party libraries to export excel, here we can simply GridView control of the System.Web.UI.WebControls namespace that is part of ASP.NET Web Form and it do conversion of MS Excel following below steps.

CoreSpider- Export to Excel in ASP.NET MVC
Code Explanation
  • On Export() we fetch the data from customer list.
  • Using help of GridView control of the System.Web.UI.WebControls namespace append the customer data into Gridview.
  • Rendering the GridView conent using HtmlTextWriter and StringWriter.

Everything is okay now, so let’s run the application and see the output like below;

CoreSpider.com - Export Data to Excel
CoreSpider.com – Export Data to Excel

You can see like above image we do code for export data into excel format in ASP.NET MVC without using any 3rd part library, we just use the Grid-view Controls that are present in System.Web.UI.WebControls.

Conclusion

So far this article we discuss how to export the data into MS Excel in ASP .NET MVC without using any 3rd party libraries. If have any suggestion about this article please give your feedback.

How to use angular material in angular
How to use angular material in angular

UI component infrastructure and Material Design components for mobile and desktop Angular web applications.

How to create custom pipes in Angular
How to create custom pipes in Angular

A pipe takes in data as input and transforms it to a desired output. Simply you can say Pipes are Read more

How to read external JSON file in Jquery AJAX
Javascript-Jquery-Ajax

This article explain how to read external json file. There are many ways to store JSON data in our application. Read more

Export data into PDF in ASP.NET MVC
Export-Data-into-PDF-SERVICE

In this example we use Dapper ORM to fetch the data from database and using the data we export into Read more

Pin It on Pinterest