Code2night
  • Home
  • Blogs
  • Tutorial
  • Post Blog
  • Tools
    • Json Beautifier
    • Html Beautifier
  • Members
    • Register
    • Login
  1. Home
  2. Blogpost
13 Apr
2023

Export to Excel in Angular using ExcelJS?

by Shubham Batra

231

Download Attachment

Welcome to Code2Night! In this blog post, we will explore how to export data from an Angular application to Excel using the ExcelJS library. Excel is a popular file format used for storing and analyzing large amounts of data, and ExcelJS is an excellent library for creating and manipulating Excel files programmatically.

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

ng new 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

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

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

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

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


@Injectable({
  providedIn: 'root'
})
export class ExportExcelService {
  dataForExcel = [];
  constructor() { }

  exportExcel(excelData: any, arrayData: any) {
    debugger
    arrayData.forEach((row: any) => {
      excelData.data.push(Object.values(row))
    })

    //Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers
    const data = excelData.data;
    const logoBase64 = "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==";

    //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: logoBase64,
      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';
      if (+sales < 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 the Service code, now we will import our service in the App component and pass the Header, Title, and Data using the JSON object

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

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

  dataForExcel = [];

  empPerformance = [
    { ID: 10011, NAME: "A", DEPARTMENT: "Sales", MONTH: "Jan", YEAR: 2020, SALES: 13212, CHANGE: 102, LEADS: 305 },
    { ID: 10012, NAME: "A", DEPARTMENT: "Sales", MONTH: "Feb", YEAR: 2020, SALES: 23234, CHANGE: 20, LEADS: 4403 },
    { ID: 10013, NAME: "A", DEPARTMENT: "Sales", MONTH: "Mar", YEAR: 2020, SALES: 54234, CHANGE: 405, LEADS: 3045 },
    { ID: 10014, NAME: "A", DEPARTMENT: "Sales", MONTH: "Apr", YEAR: 2020, SALES: 23335, CHANGE: 302, LEADS: 2034 },
    { ID: 10015, NAME: "A", DEPARTMENT: "Sales", MONTH: "May", YEAR: 2020, SALES: 45535, CHANGE: 201, LEADS: 102 },
  ];

  constructor(public ete: ExportExcelService) { }

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

    debugger
    this.ete.exportExcel(reportData, this.empPerformance);
  }
}

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

<style>
  .container {
    height: 200px;
    position: relative;
    border: 3px solid green;
  }

  .vertical-center {
    margin: 0;
    position: absolute;
    top: 50%;
    -ms-transform: translateY(-50%);
    transform: translateY(-50%);
  }

  .button {
    background-color: #4CAF50;
    /* Green */
    border: none;
    color: white;
    padding: 15px 32px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 16px;
  }
</style>

<div class="container" role="main">
  <div class="vertical-center">
    <H1>Export To Excel</H1>
    <button (click)="exportToExcel()" class="button">Export The Report</button>
  </div>
</div>
 
<router-outlet></router-outlet>

Now you can run your Angular project by hitting
ng serve --open
 OUTPUT :
angular


  • |
  • Export to Excel in Angular 8 using ExcelJS? , How To Export To Excel In Angular , Create Excel from JSON in Angular 9/8 using Exceljs Tutorial with Example

Comments

Follow Us On Social Media - Like Us On Facebook

Best Sellers

product 1

Hand Hug Bracelet For Women Men Cuff Bangle Adjustable Lover Couple Bracelets

Can be given as a gift to your family, relatives, or friends

Buy $15.99
product 1

Teddy bear hug bear plush toy bear cub

Can be given as a gift to your family, relatives, or friends


Buy $49.99

Tags

LinkedinLogin
LinkedinProfile
GetLinkedinProfile
C#
Aspnet
MVC
Linkedin
ITextSharp
Export to Pdf
AspNet Core
AspNet
View to Pdf in Aspnet
Model Validation In ASPNET Core MVC 60
Model Validation
Model Validation In ASPNET Core MVC
Model Validation In ASPNET
Image Compression in AspNet
Compress Image in c#
AspNet MVC
Thank you for Downloading....!

Subscribe for more tutorials

Support our team

Continue with Downloading

Welcome To Code2night, A common place for sharing your programming knowledge,Blogs and Videos

  • Panipat
  • info@Code2night.com

Links

  • Home
  • Blogs
  • Tutorial
  • Post Blog

Popular Tags

Copyright © 2023 by Code2night. All Rights Reserved

  • Home
  • Blog
  • Login
  • SignUp
  • Contact
  • Terms & Conditions
  • Refund Policy
  • About Us
  • Privacy Policy
  • Json Beautifier