How to implement Angular excel generator in Angular 11|12 ?

In this tutorial, we’ll learn to export data to excel in Angular using the ngx-pappaparse library. We will view data in Angular excel or angular csv files. The ngx-papaparse library allows us to export data in a spreadsheet of file format XLSX or CVS. The CSV file format is compatible with Excel and Google Sheets.

Prerequisites:
To create and run an Angular project, We need nodejs and Angular CLI already install in our system, if not then check how to install nodejs and angular in Ubuntu. To install the Angular CLI, open a terminal and run the following command:

npm install -g @angular/cli

Setting up and configure Angular excel project

Let create our angular project and install ngx-papaparse for exporting data to excel in Angular 11|12.

ng new angularExcelApps
npm i ngx-papaparse --save

Once we have created our project and install papaparse library which allows angular to export our data to excel format. The papaparse library is an open-source CSV library which is a fast and powerful CSV parser for the browser that supports web workers and streaming large files. Converts CSV to JSON and JSON to CSV.

In a real application, we are retrieving our data source from API remote server, to demonstrate Angular export our data to excel, we have some dummy data. We will retrieve data using the HTTP client module in the same way if the data source is remote. Let add our dummy data, in the assets folder create a data folder and students.json.

[
    {
        "name": "Will Smith",
        "gender": "Male",
        "country": "USA"
    },
    {
        "name": "Jackline Joy",
        "gender": "Female",
        "country": "Sri Lanak"
    },
    {
        "name": "Alu Arjun",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Kavitha Kumar",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "John Snow",
        "gender": "Male",
        "country": "United Kingdom"
    },
    {
        "name": "Priya kanana",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "Shri Devi",
        "gender": "Female",
        "country": "Sri Lanka"
    },
    {
        "name": "Richard Roy",
        "gender": "Male",
        "country": "France"
    },
    {
        "name": "Sonu Nigam",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Priya Dutt",
        "gender": "Female",
        "country": "USA"
    },
    {
        "name": "Will Smith",
        "gender": "Male",
        "country": "USA"
    },
    {
        "name": "Jackline Joy",
        "gender": "Female",
        "country": "Sri Lanak"
    },
    {
        "name": "Alu Arjun",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Kavitha Kumar",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "John Snow",
        "gender": "Male",
        "country": "United Kingdom"
    },
    {
        "name": "Priya kanana",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "Shri Devi",
        "gender": "Female",
        "country": "Sri Lanka"
    },
    {
        "name": "Richard Roy",
        "gender": "Male",
        "country": "France"
    },
    {
        "name": "Sonu Nigam",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Priya Dutt",
        "gender": "Female",
        "country": "USA"
    },
    {
        "name": "Will Smith",
        "gender": "Male",
        "country": "USA"
    },
    {
        "name": "Jackline Joy",
        "gender": "Female",
        "country": "Sri Lanak"
    },
    {
        "name": "Alu Arjun",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Kavitha Kumar",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "John Snow",
        "gender": "Male",
        "country": "United Kingdom"
    },
    {
        "name": "Priya kanana",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "Shri Devi",
        "gender": "Female",
        "country": "Sri Lanka"
    },
    {
        "name": "Richard Roy",
        "gender": "Male",
        "country": "France"
    },
    {
        "name": "Sonu Nigam",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Priya Dutt",
        "gender": "Female",
        "country": "USA"
    },
    {
        "name": "Will Smith",
        "gender": "Male",
        "country": "USA"
    },
    {
        "name": "Jackline Joy",
        "gender": "Female",
        "country": "Sri Lanak"
    },
    {
        "name": "Alu Arjun",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Kavitha Kumar",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "John Snow",
        "gender": "Male",
        "country": "United Kingdom"
    },
    {
        "name": "Priya kanana",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "Shri Devi",
        "gender": "Female",
        "country": "Sri Lanka"
    },
    {
        "name": "Richard Roy",
        "gender": "Male",
        "country": "France"
    },
    {
        "name": "Sonu Nigam",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Priya Dutt",
        "gender": "Female",
        "country": "USA"
    },
    {
        "name": "Will Smith",
        "gender": "Male",
        "country": "USA"
    },
    {
        "name": "Jackline Joy",
        "gender": "Female",
        "country": "Sri Lanak"
    },
    {
        "name": "Alu Arjun",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Kavitha Kumar",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "John Snow",
        "gender": "Male",
        "country": "United Kingdom"
    },
    {
        "name": "Priya kanana",
        "gender": "Female",
        "country": "India"
    },
    {
        "name": "Shri Devi",
        "gender": "Female",
        "country": "Sri Lanka"
    },
    {
        "name": "Richard Roy",
        "gender": "Male",
        "country": "France"
    },
    {
        "name": "Sonu Nigam",
        "gender": "Male",
        "country": "India"
    },
    {
        "name": "Priya Dutt",
        "gender": "Female",
        "country": "USA"
    }
]

We need to have models for our student data, let create a folder name called model in the app/models and add a file called student.ts file. Edit app/models/student.ts file.

export interface IStudent {
    name: string;
    gender: string;
    country: string;
}


We have to import the HTTClient module for HTTP GET requests to retrieve our student data. Edit app.module.ts file.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
import { HttpClientModule } from '@angular/common/http';

@NgModule({
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    HttpClientModule,
    BrowserAnimationsModule
  ],
  providers: [],
  bootstrap: [AppComponent]
})

Example of exporting data to excel in Angular 10

First, we will retrieve our dummy data and create our Angular table. In the previous article, we have discussed how to create a table in angular using angular material and regular HTML tables.
We’ll demonstrate Here’s a quick screenshot of what we’ll be building.

Angular excel generator

How Angular excel generator works ?

We need to create an excel service or provider, which handles all activities for the conversion of JSON objects of table data to CSV. Let first generate our excel service by running the following command.

ng generate service services/excel

The above command will create an excel.service.ts file in the services folder. Let’s edit the excel service file as.

import { Injectable } from '@angular/core';
import { Papa } from 'ngx-papaparse';
import { IStudent } from '../model/student';

@Injectable({
  providedIn: 'root'
})
export class ExcelService {

  constructor(private papa: Papa) { }

  excelDownload(students: IStudent[]): void {
    const excelObject = this.mapData(students);
    console.log(excelObject);
    const csv = this.papa.unparse({ data: excelObject });
    console.log(csv);

    const csvData = new Blob([csv], { type: 'text/csv;charset=utf-8;' });

    let csvURL = null;

    if (navigator.msSaveBlob) {
      csvURL = navigator.msSaveBlob(csvData, 'export.xlsx');
    } else {
      csvURL = window.URL.createObjectURL(csvData);
    }

    // For Browser
    const tempLink = document.createElement('a');
    tempLink.href = csvURL;
    tempLink.setAttribute('download', 'export.xlsx');
    tempLink.click();
  }

  // tslint:disable-next-line: typedef
  private mapData(data: IStudent[]) {
    return data.map((item) => {
      return {
        Name: item.name,
        Gender: item.gender,
        Country: item.country
      };
    });
  }
}

The mapData method will allow us to have capital letters to our table header and return the same javascript object. The papaparser unparse method will remove all field names and return only the value of the Javascript object. We are creating a new Blob object to create CVS from the Javascript object.

    const csvData = new Blob([csv], { type: 'text/csv;charset=utf-8;' });

Blob stands for “Binary Large Object”, it’s an opaque representation of a chunk of bytes. Web Browsers implement a Blob object, which is responsible for holding data. The Blob object represents is a file-like object of immutable, raw data; they can be read as text or binary data or converted into a ReadableStream so its methods can be used for processing the data.

Edit our component to retrieve and create a Table

Let first retrieve our students’ JSON data and display the data in table formats. Edit app.component.ts file.

import { HttpClient } from '@angular/common/http';
import { Component, OnInit } from '@angular/core';
import { IStudent } from './model/student';
import { ExcelService } from './services/excel.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.scss']
})
export class AppComponent implements OnInit {
  students: IStudent[];

  constructor(private http: HttpClient, private excelService: ExcelService) { }

  ngOnInit(): void {
    this.http.get('./assets/data/students.json').subscribe((response: IStudent[]) => {
      this.students = response;
    });
  }

  exportToExcel(): void {
    this.excelService.excelDownload(this.students);
  }
}

In the app-component template, we have a button called export to excel. When we click on this button, it will be called our excel service to convert our data to excel format and download the file to the client-side.

<router-outlet></router-outlet>
<div style="margin:0 30px;">
    <h2 style="text-align: center;">Angular datatable export to Excel</h2>
    <button style="float: right; margin-bottom: 10px;" (click)="exportToExcel()">Export to Excel</button>
    <table>
        <tr>
            <th>S.no</th>
            <th>Name</th>
            <th>Gender</th>
            <th>Country</th>
        </tr>
        <tr *ngFor="let student of students; index as i">
            <td>{{ i }}</td>
            <td>{{ student.name }}</td>
            <td>{{ student.gender }}</td>
            <td>{{ student.country }}</td>
        </tr>
    </table>
</div>

Edit app.component.scss file to apply style for our table.

table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 100%;
  }
  
  th {
    background-color: red;
    border: 1px solid red;
  }
  td {
    border: 1px solid #dddddd;
  }
  td, th {
    text-align: left;
    padding: 8px;
  }
  
  tr:nth-child(even) {
    background-color: #dddddd;
  }

Conclusion
In this tutorial, we have completed our angular project of exporting data to excel in Angular 10 using papaParser library. For mobile applications, we need other file libraries to download our files on mobile devices. But this is no issue with a web application.

Related posts


Leave a Comment

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

Scroll to Top