ExcelJS in Angular 13 : How to Add Multiple WorkSheets

In this post, you will learn how to add multiple worksheets to the ExcejJS-generated XLSX file. In an excel workbook, we can have multiple worksheets that can have multiple datasets, that can communicate data between each other.

This Angular post is compatible with Angular 4 upto latest versions, Angular 7, Angular 8, Angular 9, Angular 10, Angular 11, Angular 12 and Angular 13

We already discussed the detailed tutorial on creating the excel file from JSON data with custom styles on cells. The demo application also consists of merging of cells, header footer and image insert using base64 format.

You can check the previous detailed tutorial on how to add ExcelJS in Angular latest application.

In this guide, we will create another demo application to use ExcelJS in the Angular application to convert JSON data into an XLSX file. We will have multiple worksheets in the XLSX file generated.

How to Add Multiple WorkSheets using ExcelJS in Angular?

By following the below steps, you will be able able to create an XLSX file from JSON with multiple worksheets.

Step 1 – Create Angular Project

Step 2 – Install Required Packages

Step 3 – Update TS Config File

Step 4 – Update Component Class

Step 5 – See In Action

 

Step 1 – Create 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 .

 

 

Step 2 – Install Required Packages

Now, we’ll install some required packages in the Angular project to fulfil 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 [email protected]

IMPORTANT NOTE: Make sure you install the 1.12.1 version of exceljs, otherwise it will throw errors. As this library is created for node servers it causes strict typing issues on the client-side.

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 and its @types support

$ npm install --save file-saver
$ npm i --save-dev @types/file-saver

 

Step 3 – Update TS Config File

Afterwords, 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"
      ]
    }
  },

 

Step 4 – Update Component Class

Open the component class file to add data set for Worksheet 1 and 2. You will notive we have created two Worksheets worksheet and worksheet_2 that will load data from sheet_data_1 and sheet_data_2. You can append any number of worksheets are per requirnment.

Open the app.component.ts file and place below code in it:

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

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

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

  constructor() {}

  exportToExcel() {
    //Create a workbook with a worksheet
    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet('My WorkSheet 1');
    let worksheet_2 = workbook.addWorksheet('My WorkSheet 2');

    //Add Row and formatting
    worksheet.mergeCells('C1', 'F1');
    worksheet.getCell('C1').value = 'My Worksheet Title 1';
    worksheet_2.mergeCells('C1', 'F1');
    worksheet_2.getCell('C1').value = 'My Worksheet Title 2';

    // Add Header Rows
    worksheet.addRow(Object.keys(this.sheet_data_1[0]));
    worksheet_2.addRow(Object.keys(this.sheet_data_2[0]));

    // Adding Data with Conditional Formatting
    this.sheet_data_1.forEach((d: any) => {
      worksheet.addRow(Object.values(d));
    });

    worksheet.getRow(2).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: '58f359' },
    };

    this.sheet_data_2.forEach((d: any) => {
      worksheet_2.addRow(Object.values(d));
    });

    worksheet_2.getRow(2).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: '2bdfdf' },
    };

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

Step 5 – See In Action

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

We have successfully implemented the export to excel functionality in our Angular application using the ExcelJS library.

The ExcelJS library is a JavaScript library that provides an API for reading and writing Microsoft Excel files. It is designed to be used in NodeJS, but it can also be used in Client-Side apps or any other JavaScript environment.

Hope you enjoyed its tutorial, share it with your friends

Thanks for reading!

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments