Skip to main content

Export to Excel in Angular 8 using ExcelJS

Two important open source libraries available to read/write an excel in the client-side web applications are :

Both libraries are used to read, manipulate and write spreadsheet data and styles to XLSX and JSON.

Initially, I had used XLSX & XLSX-style libraries to create and format an excel from JSON but the adverse point of this open-source library is it doesn’t provide any feature to add an image in excel (add image feature is available in pro version), which was the big requirement for my excel.

Later on, I found ExcelJS, which is really easy to use, provide almost all the features of Excel, and the main thing is it also provide add image feature.

Export to Excel in Angular 8 using ExcelJS

Environment Setup

Create a Angular 8 Project

Use below command to create a new Angular 6 project with Angular CLI.

ng new angular-exceljs-example

Install ExcelJS Library

npm install --save exceljs@1.12.0

Important

The latest version of 

exceljs
 (version 
3.4.0
 while writing the article) uses the 
@types/node@^10.12.0
 while angular 8 uses 
@types/node@~8.9.4
. Because of this, you might face the following error while compiling an application :

ERROR in node_modules/exceljs/index.d.ts:1661:34 - error TS2503: Cannot find namespace 'NodeJS'.
dictionary: Buffer | NodeJS.TypedArray | DataView | ArrayBuffer; // deflate/inflate only, empty dictionary by default

We can fix this by upgrading the 

@types/nodes
 to 
10.12.0
 but because angular 8 doesn’t use this version I will not recommend doing so, So until we required any specific feature which is released in latest 
exceljs
 version, I will recommend you to use 
exceljs@1.12.0
 for angular 8 applications.

If you find any issue in angular 8 with 

exceljs
, Refer to the comment section below which contains some useful solutions. However, if you don’t find any solution please add your issue in new comment.

Update tsconfig.js

ExcelJS is generally used for server side web applications in node. Here we want to use it in client side Angular application. for that we need to set 

compilerOptions
 in 
tsconfig.json
 as shown below :

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

If you are using Angular 4 or 5 you need to use the following path in 

tsconfig.json

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

In Linux, It is not able to find 

exceljs.browser
 or 
exceljs.min
 from 
tsconfig.js
 So remove the path from 
tsconfig
 and import ExcelJS like this:

import * as Excel from "exceljs/dist/exceljs.min.js";
import * as ExcelProper from "exceljs";
let workbook: ExcelProper.Workbook = new Excel.Workbook();

Install file-saver

FileSaver.js is the solution to saving files on the client-side and is perfect for web apps that need to generate files, or for saving sensitive information that shouldn’t be sent to an external server.

It implements the 

saveAs()
 FileSaver interface in browsers that do not natively support it.

Install file-saver library using following command

npm install --save file-saver

Generate Excel with Formatting

Environment setup is done. Now lets start to build an excel.

We will create a separate service in our project called 

excel.service.ts
,  you can create it using below command

ng generate service excel

Import ExcelJS and FileSaver

In 

excel.service.ts
, add the following import statements.

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

Create a separate method and data varibales.

In 

excel.service.ts
, We will create a separate method called 
generateExcel()
.

In this method, I have created some data variables as below, We will export these data in excel sheet.

Note: You can pass data from the component as a parameter in 

generateExcel()
 and generate a dynamic excel sheet.

const title = 'Car Sell Report';
const header = ["Year", "Month", "Make", "Model", "Quantity", "Pct"]
const data = [
[2007, 1, "Volkswagen ", "Volkswagen Passat", 1267, 10],
[2007, 1, "Toyota ", "Toyota Rav4", 819, 6.5],
[2007, 1, "Toyota ", "Toyota Avensis", 787, 6.2],
[2007, 1, "Volkswagen ", "Volkswagen Golf", 720, 5.7],
[2007, 1, "Toyota ", "Toyota Corolla", 691, 5.4],
...
];

Create Workbook and Add Worksheet

Create a new workbook and add a new worksheet using 

addWorksheet()
 method of Workbook.

let workbook = new Workbook();
let worksheet = workbook.addWorksheet('Car Data');

Add Row and format the fonts.

We will use 

addRow()
 method of worksheet object. to add a row in a worksheet. as below,

// Add new row
let titleRow = worksheet.addRow([title]);
// Set font, size and style in title row.
titleRow.font = { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true };
// Blank Row
worksheet.addRow([]);
//Add row with current date
let subTitleRow = worksheet.addRow(['Date : ' + this.datePipe.transform(new Date(), 'medium')]);

Add Image in worksheet

To add an image in excel we need to add base64 of an image. I have saved the base64 of logo image in a separate file called 
carlogo.js
as below.
export const logoBase64 = "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAFKCAMAAADcyF29AAABxVBMVEVHcEwJCAggFxEBAQE2KyQAAAA0LScAAAAAAAA1LysXEQ0EBAQFBAMDAwMLCQgGBQUFBAOEQhUHBwZjQSuScFoVFRZvNAx5NghcOyaudU8yMDBrNhOiViMZFhXEdD3Ef0+4ZzFISUdSJwliMA6BPA6lVR8CAgEDAgQPDhANDgsKCQoVFhPObi4SERS2VxcE.......";
To use it in generateExcel() method, We need to import carlogo.jsin excel.service.ts as below,
import * as logoFile from './carlogo.js';
Now, add an image in a worksheet as below,
let logo = workbook.addImage({
base64: logoFile.logoBase64,
extension: 'png',
});
worksheet.addImage(logo, 'E1:F3');
workbook.addImage(image)
 creates an image object and returns the image id, that image id we will use to place image in the worksheet using 
worksheet.addImage(imageId, cellRange)
. The coordinates calculated from the range will cover from the top-left of the first cell to the bottom right of the second.

Merge Cells

We can merge cells using 

worklist.mergeCells(cellRange)
 method, as below,

worksheet.mergeCells('A1:D2');

The coordinates calculated from the range will cover from the top-left of the first cell to the bottom right of the second.

Add Data with Header & Conditional Formatting

We will add a header row for car data records with a background color, as below,

//Add Header Row
let headerRow = worksheet.addRow(header);
// Cell Style : Fill and Border
headerRow.eachCell((cell, number) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFFFFF00' },
bgColor: { argb: 'FF0000FF' }
}
cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
});

ExcelJS directly doesn’t support conditional formatting, but we can achieve this functionality by assigning style based on required condition in angular, as below,

// Add Data and Conditional Formatting
data.forEach(d => {
let row = worksheet.addRow(d);
let qty = row.getCell(5);
let color = 'FF99FF99';
if (+qty.value < 500) {
color = 'FF9999'
}
qty.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: color }
}
}
);

Note: Using 

worklist.addRows(records: any[])
 method we can add multiple rows. as an example,

worksheet.addRows(data);

But in our example, we want to perform cell styling based on a conditional check, So we have saved the individual row using 

worklist.addRow(record)
 method.

Same way, you can add other rows like footer and additional information.

Export file using FileSaver

Now our workbook is ready to export. We can export it using 

saveFile()
 method of file-saver, as shown below

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

Final Code Review

Note : I have added 

Generate Excel
 button in 
AppComponent
 which will call 
genetateExcel()
 method of 
excel.service.ts
.

tsconfig.js
app.component.ts
app.component.html
excel.service.ts
{
"compileOnSave": false,
"compilerOptions": {
"baseUrl": "./",
"outDir": "./dist/out-tsc",
"sourceMap": true,
"declaration": false,
"moduleResolution": "node",
"emitDecoratorMetadata": true,
"experimentalDecorators": true,
"target": "es5",
"paths": {
"exceljs": [
"node_modules/exceljs/dist/exceljs.min"
]
},
"typeRoots": [
"node_modules/@types"
],
"lib": [
"es2017",
"dom"
]
}
}







Comments

Post a Comment

Popular posts from this blog

4 Ways to Communicate Across Browser Tabs in Realtime

1. Local Storage Events You might have already used LocalStorage, which is accessible across Tabs within the same application origin. But do you know that it also supports events? You can use this feature to communicate across Browser Tabs, where other Tabs will receive the event once the storage is updated. For example, let’s say in one Tab, we execute the following JavaScript code. window.localStorage.setItem("loggedIn", "true"); The other Tabs which listen to the event will receive it, as shown below. window.addEventListener('storage', (event) => { if (event.storageArea != localStorage) return; if (event.key === 'loggedIn') { // Do something with event.newValue } }); 2. Broadcast Channel API The Broadcast Channel API allows communication between Tabs, Windows, Frames, Iframes, and  Web Workers . One Tab can create and post to a channel as follows. const channel = new BroadcastChannel('app-data'); channel.postMessage(data); And oth...
Post a Comment
Read more

Certbot SSL configuration in ubuntu

  Introduction Let’s Encrypt is a Certificate Authority (CA) that provides an easy way to obtain and install free  TLS/SSL certificates , thereby enabling encrypted HTTPS on web servers. It simplifies the process by providing a software client, Certbot, that attempts to automate most (if not all) of the required steps. Currently, the entire process of obtaining and installing a certificate is fully automated on both Apache and Nginx. In this tutorial, you will use Certbot to obtain a free SSL certificate for Apache on Ubuntu 18.04 and set up your certificate to renew automatically. This tutorial will use a separate Apache virtual host file instead of the default configuration file.  We recommend  creating new Apache virtual host files for each domain because it helps to avoid common mistakes and maintains the default files as a fallback configuration. Prerequisites To follow this tutorial, you will need: One Ubuntu 18.04 server set up by following this  initial ...
Post a Comment
Read more

Working with Node.js streams

  Introduction Streams are one of the major features that most Node.js applications rely on, especially when handling HTTP requests, reading/writing files, and making socket communications. Streams are very predictable since we can always expect data, error, and end events when using streams. This article will teach Node developers how to use streams to efficiently handle large amounts of data. This is a typical real-world challenge faced by Node developers when they have to deal with a large data source, and it may not be feasible to process this data all at once. This article will cover the following topics: Types of streams When to adopt Node.js streams Batching Composing streams in Node.js Transforming data with transform streams Piping streams Error handling Node.js streams Types of streams The following are four main types of streams in Node.js: Readable streams: The readable stream is responsible for reading data from a source file Writable streams: The writable stream is re...
Post a Comment
Read more