Angular 13 ExcelJS Project to Export JSON Data to Colorful Excel (.XLSX) File and Download it Using FileSaver.js in TypeScript

ng new sampleapp

cd sampleapp

npm i exceljs

npm i file-saver

Now add the below code inside the tsconfig.json file of your project

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

"paths": {

"exceljs": [

"node_modules/exceljs/dist/exceljs.min"

]

}

app.component.html

import { Component } from '@angular/core';
import * as XLSX from 'xlsx';
import { ExportExcelService } from './services/export-excel.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent {
  title = 'angular-app';

  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);
  }
}

import { Component } from '@angular/core';

import * as XLSX from 'xlsx';

import { ExportExcelService } from './services/export-excel.service';

@Component({

selector: 'app-root',

templateUrl: './app.component.html',

styleUrls: ['./app.component.css']

})

export class AppComponent {

title = 'angular-app';

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);

}

Now create the service by executing the below command as shown below

$ ng generate service services/export-excel

1	$ ng generate service services/export-excel

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

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


  constructor() { }

  exportExcel(excelData: { title: any; headers: any; data: any; }) {

    //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({
      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: any) => {
      let row = worksheet.addRow(d);

      let sales = row.getCell(6);
      let color = 'FF99FF99'

      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');
    })

  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

import { Injectable } from '@angular/core';

import { Workbook } from 'exceljs';

import * as fs from 'file-saver';

@Injectable({

providedIn: 'root'

})

export class ExportExcelService {

constructor() { }

exportExcel(excelData: { title: any; headers: any; data: any; }) {

//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({

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: any) => {

let row = worksheet.addRow(d);

let sales = row.getCell(6);

let color = 'FF99FF99'

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');

})

}

Related posts:

You Might Also Like

Angular Project to Build Chipotle Nutritional Calculator in Browser Using HTML5 & CSS3

Angular 13 Copy to Clipboard Example to Copy Text inside TextArea & Input Fields Using ngx-copypaste Module in TypeScript

Angular 13 xlsx Example to Export JSON Data to Excel (.XLSX) and Download it Using FileSaver.js in Browser Using TypeScript

Leave a Reply Cancel reply