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.
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? SCSSMove inside the project
$ cd angular-export-to-excelIf 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 exceljs@1.12.1IMPORTANT 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!
Leave a Reply