Create Excel from JSON in Angular 9/8 using Exceljs Tutorial with Example

In this Angular 9/8 tutorial, we will learn How to export JSON data into XLSX / Excel file using the Excel Js library. Using ExcelJs we can create custom formated and styled XLS files using JSON data in Angular Applications.

In modern applications, we can display data in many forms like sometimes we represent in the form of graphical charts, tabular data tables, lists, etc. But due to many reasons, we may want to export the data getting used in such cases for applying some formula, information manipulation, sharing reports. The most preferred way of sharing information is done using Excel sheets due to the many features and capabilities we all know.

Here we will implement export to Excel functionally in an Angular application with the help of the ExcelJS library. This is an awesome library to convert JSON data in formatted and customized Excel files.

How to Download XLSX from JSON using ExcelJS on Angular Application?

You can follow these steps to quickly download formatted JSON into the EXCEL file on client-application

Step 1 – Create Angular Application

Step 2 – Install ExcelJS and File-Saver

Step 3 – Configure tsconfig.json

Step 4 – Create & Update Excel Export Service

Step 5 – Create and Save XLSX File-Saver

Step 6 – See In Action

[lwptoc]

 

What is ExcelJS?

ExcelJS is the Javascript library used to read, manipulate, and write spreadsheet data and styles to XLSX and JSON. We can easily create XLSX files with formatted headers, footers, cells, and feed any customized data including textual, images, etc. It is an active and community-driven library with lots of features.

 

Features of ExcelJS

Following is the list of some awesome feature of Excel JS:

• View, Add, Remove, Access Worksheets, and also set the Worksheet States like hidden, visible, veryHidden.
• Page setup includes orientation, Margins, height, width, Print area.
• Headers and Footers configuration with custom style, font size, color, etc.
• Worksheet Views to Freeze & Split sections.
• Handling individual cells or Merging the group of cells.
• Cell level data validation support.
• Tables and Comments on worksheets
• Styling includes fonts, fill, border, alignment, size, gradient fills, etc.
• Images can be added using the addImage() function.
• File I/O for Reading, Writing of CSV & XLSX.
• ES5 Support, can be used in simple HTML to reactive applications like Angular, React, and NodeJS.

 

Let’s get started with the implementation of ExcelJS in the Angular 9/8 application project…

 

Create a new Angular Project

Let’s first create a new Angular project using Angular CLI tool by executing the following command

$ ng new angular-export-to-excel
? Would you like to add Angular routing? No
? Which stylesheet format would you like to use? SCSS

Move inside the project

$ cd angular-export-to-excel

If you have Visual Studio Code installed, open the project by hitting

$ code .

 

Install Required Packages

Now, we’ll install some required packages in the Angular project to fulfill the export to excel functionality

Install ExcelJs

Run the following command to install the ExcelJs package which is the main player to deal with Excel format related functionalities we want to achieve

$ npm install --save exceljs@1.12.0

 

Install FileSaver

FileSave.js is a lifesaver we must say when dealing with operations related to saving files on the disk. It is mainly used on client-side applications to save large files in web applications.

Run the following command to install the file-saver package

$ npm install --save file-saver

 

Configure tsconfig.json

Now, open the tsconfig.json file at the project root, then add the “paths” property under the “compilerOptions” with "exceljs" library location.

"compilerOptions": {

     ...

    "paths": {
      "exceljs": [
        "node_modules/exceljs/dist/exceljs.min"
      ]
    }
  },

Create a Service for ExcelJs

Now we’ll create a new Service to keep Excel-related methods in one place. Run the following command to generate service in the services folder

$ ng generate service services/export-excel

This will create the ExportExcelService under the services folder.

 

Update the ExportExcelService

Now open the services/ export-excel.service.ts file and make the following changes:

Import the exceljs and file-saver files

import { Workbook } from 'exceljs';
import * as fs from 'file-saver';

 

Also import the mylogo.js file.

import * as logo from './mylogo.js';

To add Logo image in the Excel file, we will keep the base64 format in the services/mylogo.js file

export const imgBase64 = "data:image/png;base64,iVBORw0KGgoA.....VORK5CYII=";

You can convert any image to base64 using any online tools that vary easily.

 

The exportExcel() method will be called from the component, with an object having Title, Header Arraya, and Data for excel file.

Get Title, Header, and Data

//Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers
    const data = excelData.data;

Create a Workbook with Worksheet

//Create a workbook with a worksheet
    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet('Sales Data');

Adding Title

//Add Row and formatting
    worksheet.mergeCells('C1', 'F4');
    let titleRow = worksheet.getCell('C1');
    titleRow.value = title
    titleRow.font = {
      name: 'Calibri',
      size: 16,
      underline: 'single',
      bold: true,
      color: { argb: '0085A3' }
    }
    titleRow.alignment = { vertical: 'middle', horizontal: 'center' }

 

Adding Date

// Date
    worksheet.mergeCells('G1:H4');
    let d = new Date();
    let date = d.getDate() + '-' + d.getMonth() + '-' + d.getFullYear();
    let dateCell = worksheet.getCell('G1');
    dateCell.value = date;
    dateCell.font = {
      name: 'Calibri',
      size: 12,
      bold: true
    }
    dateCell.alignment = { vertical: 'middle', horizontal: 'center' }

 

Adding Logo Image

//Add Image
    let myLogoImage = workbook.addImage({
      base64: logo.imgBase64,
      extension: 'png',
    });
    worksheet.mergeCells('A1:B4');
    worksheet.addImage(myLogoImage, 'A1:B4');

 

Adding Excel header

//Adding Header Row
    let headerRow = worksheet.addRow(header);
    headerRow.eachCell((cell, number) => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: '4167B8' },
        bgColor: { argb: '' }
      }
      cell.font = {
        bold: true,
        color: { argb: 'FFFFFF' },
        size: 12
      }
    })

 

Formating Cells Conditionally

// Adding Data with Conditional Formatting
    data.forEach(d => {
      let row = worksheet.addRow(d);

      let sales = row.getCell(6);
      let color = 'FF99FF99';
      if (+sales.value < 200000) {
        color = 'FF9999'
      }

      sales.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: color }
      }
    }
    );

 

Adding Footer Text

//Footer Row
    let footerRow = worksheet.addRow(['Employee Sales Report Generated from example.com at ' + date]);
    footerRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFB050' }
    };

 

Saving and Exporting the Excel File

//Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      fs.saveAs(blob, title + '.xlsx');
    })

 

Finally, the complete ExportExcelService in the export-excel.service.ts will look like this

import { Injectable } from '@angular/core';
import { Workbook } from 'exceljs';
import * as fs from 'file-saver';
import * as logo from './mylogo.js';

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


  constructor() { }

  exportExcel(excelData) {

    //Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers
    const data = excelData.data;

    //Create a workbook with a worksheet
    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet('Sales Data');


    //Add Row and formatting
    worksheet.mergeCells('C1', 'F4');
    let titleRow = worksheet.getCell('C1');
    titleRow.value = title
    titleRow.font = {
      name: 'Calibri',
      size: 16,
      underline: 'single',
      bold: true,
      color: { argb: '0085A3' }
    }
    titleRow.alignment = { vertical: 'middle', horizontal: 'center' }

    // Date
    worksheet.mergeCells('G1:H4');
    let d = new Date();
    let date = d.getDate() + '-' + d.getMonth() + '-' + d.getFullYear();
    let dateCell = worksheet.getCell('G1');
    dateCell.value = date;
    dateCell.font = {
      name: 'Calibri',
      size: 12,
      bold: true
    }
    dateCell.alignment = { vertical: 'middle', horizontal: 'center' }

    //Add Image
    let myLogoImage = workbook.addImage({
      base64: logo.imgBase64,
      extension: 'png',
    });
    worksheet.mergeCells('A1:B4');
    worksheet.addImage(myLogoImage, 'A1:B4');

    //Blank Row 
    worksheet.addRow([]);

    //Adding Header Row
    let headerRow = worksheet.addRow(header);
    headerRow.eachCell((cell, number) => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: '4167B8' },
        bgColor: { argb: '' }
      }
      cell.font = {
        bold: true,
        color: { argb: 'FFFFFF' },
        size: 12
      }
    })

    // Adding Data with Conditional Formatting
    data.forEach(d => {
      let row = worksheet.addRow(d);

      let sales = row.getCell(6);
      let color = 'FF99FF99';
      if (+sales.value < 200000) {
        color = 'FF9999'
      }

      sales.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: color }
      }
    }
    );

    worksheet.getColumn(3).width = 20;
    worksheet.addRow([]);

    //Footer Row
    let footerRow = worksheet.addRow(['Employee Sales Report Generated from example.com at ' + date]);
    footerRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFB050' }
    };

    //Merge Cells
    worksheet.mergeCells(`A${footerRow.number}:F${footerRow.number}`);

    //Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      fs.saveAs(blob, title + '.xlsx');
    })

  }
}

 

Calling in Component

We are done with Service code, now we will import out service in the App component and pass Header, Title, and Data using JSON object

export class AppComponent {
  title = 'angular-export-to-excel';

  dataForExcel = [];

  empPerformance = [
    { ID: 10011, NAME: "A", DEPARTMENT: "Sales", MONTH: "Jan", YEAR: 2020, SALES: 132412, CHANGE: 12, LEADS: 35 },
    { ID: 10012, NAME: "A", DEPARTMENT: "Sales", MONTH: "Feb", YEAR: 2020, SALES: 232324, CHANGE: 2, LEADS: 443 },
    { ID: 10013, NAME: "A", DEPARTMENT: "Sales", MONTH: "Mar", YEAR: 2020, SALES: 542234, CHANGE: 45, LEADS: 345 },
    { ID: 10014, NAME: "A", DEPARTMENT: "Sales", MONTH: "Apr", YEAR: 2020, SALES: 223335, CHANGE: 32, LEADS: 234 },
    { ID: 10015, NAME: "A", DEPARTMENT: "Sales", MONTH: "May", YEAR: 2020, SALES: 455535, CHANGE: 21, LEADS: 12 },
  ];

  constructor(public ete: ExportExcelService) { }

  exportToExcel() {

    this.empPerformance.forEach((row: any) => {
      this.dataForExcel.push(Object.values(row))
    })

    let reportData = {
      title: 'Employee Sales Report - Jan 2020',
      data: this.dataForExcel,
      headers: Object.keys(this.empPerformance[0])
    }

    this.ete.exportExcel(reportData);
  }
}

In the App template just add a button to call this method

<button (click)="exportToExcel()">Export The Report</button>

Now you can run your Angular project by hitting

$ ng serve --open

 

Conclusion

That’s it we have successfully implemented the export to excel functionally in our Angular application using the exceljs and file-saver modules. We can easily format the excel file while adding images in it. there are also number features available to explore.

Hope you enjoyed its tutorial, share it with your friends

Thanks for reading!