Guide to ExcelJS: An Excel Workbook Manager

ExcelJS is a JavaScript package that acts as an Excel workbook manager. exceljs can read, manipulate and write spreadsheet data and styles to XLSX and JSON, according to its official documentation. It’s reverse-engineered from Excel spreadsheet files as a project.

It’s an extremely useful package that allows you to do the following:

• Creating a workbook.
• Creating a worksheet.
• Handling headers and footers.
• Setting frozen or split views.
• Setting auto filters.
• Data manipulation on rows and columns.
• Adding data validation.
• Adding styles.
• Inserting images to a workbook.

In addition, exceljs is frequently updated and available for free. This tutorial covers the step-by-step installation process and a few examples of some of the most basic and commonly used features. Also, code snippets will be provided as reference to the readers.

Let’s proceed to the next section and start installing exceljs.

 

How to Install ExcelJS

The recommended installation process is via npm, which mainly works for node.js projects. If you intend to use it directly within a browser for a project outside of node.js, you have to import the package using the official content delivery network (CDN) link. Let’s have a look at the installation process for both methods.

More on JavaScript8 Common JavasScript Data Structures

 

NPM

Run the following command to install npm in your project:

npm install exceljs

Then, import it as follows:

const ExcelJS = require('exceljs');

For a node.js that is more than 10 years old, you have to import using ES5 transpiled code. Also, you need to import a few polyfills, as well:

// polyfills required by exceljs
require('core-js/modules/es.promise');
require('core-js/modules/es.string.includes');
require('core-js/modules/es.object.assign');
require('core-js/modules/es.object.keys');
require('core-js/modules/es.symbol');
require('core-js/modules/es.symbol.async-iterator');
require('regenerator-runtime/runtime');

const ExcelJS = require('exceljs/dist/es5');

 

CDN

For using it directly on your browser, simply head over to the official CDN page for ExcelJS and copy the corresponding script tag based on your needs:

For example, you can use the following script tag inside your HTML file:

<script 
src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.3.0/exceljs.min.js" integrity="sha512-UnrKxsCMN9hFk7M56t4I4ckB4N/2HHi0w/7+B/1JsXIX3DmyBcsGpT3/BsuZMZf+6mAr0vP81syWtfynHJ69JA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>

 

FileSaver.js

Some of the features in exceljs are highly dependent on node.js built-in packages. For example, the save file functionality requires the fs module. This will throw an error when you call it within a non-node.js project.

Having said that, you can still utilize the file saving feature with just a few additional tricks. All you need to do is output the binary data as a buffer and save it using an external JavaScript library, such as FileSaver.js. Head over to the repository and copy the script tag based on your use cases.

Then, insert the script tag in your HTML as follows:

<script 
src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js" integrity="sha512-Qlv6VSKh1gDKGoJbnyA5RMXYcvnpIqhO++MhIM2fStMcGT9i2T//tSwYFlcyoRRDcDZ+TYHpH8azBBCyhpSeqw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>

 

How to Use ExcelJS

In this section, you will learn the fundamentals concepts and the basics behind exceljs.

 

Creating a New Workbook

Let’s start by creating a new workbook, as follows:

const workbook = new ExcelJS.Workbook();

 

Workbook Properties

Once you have a workbook, you can play around with some of its properties:

workbook.creator = 'Ng Wai Foong';
workbook.lastModifiedBy = 'Bot';
workbook.created = new Date(2021, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2021, 7, 27);

 

Adding a New Worksheet

Next, you can add a new worksheet to the workbook that you created earlier via the following code:

const worksheet = workbook.addWorksheet('New Sheet');

 

Worksheet Settings

You can initialize some configurations for your sheet as well by passing in an object:

// red tab colour
const worksheet = workbook.addWorksheet('New Sheet', {properties:{tabColor:{argb:'FFC0000'}}});

// hide grid lines
const worksheet = workbook.addWorksheet('New Sheet', {views: [{showGridLines: false}]});

// freeze first row and column
const worksheet = workbook.addWorksheet('New Sheet', {views:[{state: 'frozen', xSplit: 1, ySplit:1}]});

// pageSetup settings for A4 - landscape
const worksheet =  workbook.addWorksheet('New Sheet', {
  pageSetup:{paperSize: 9, orientation:'landscape'}
});

// headers and footers
const worksheet = workbook.addWorksheet('New Sheet', {
  headerFooter: {oddFooter: "Page &P of &N";, oddHeader: 'Odd Page'}
});

You can also set the configurations manually after you have initialized the worksheet object. For example, you can set a footer for odd pages, as follows:

//Set footer (default centered), result: "Page 2 of 16"
worksheet.headerFooter.oddFooter = "Page &P of &N";

&P and &N are part of the script commands. 

 

How to Set Auto Filters in ExcelJS

Moreover, there is support for auto filters as well. The following code snippet allows you to set an auto filter from A1 to C1:

worksheet.autoFilter = 'A1:C1';

You can use the following code instead, which is a lot more explicit compared to the standard form:

worksheet.autoFilter = {
 from: 'A1',
 to: 'C1',
}

 

Creating Columns

It’s good practice to set the columns with the corresponding mapping header and key.

worksheet.columns = [
 { header: 'Id', key: 'id' },
 { header: 'Name', key: 'name' },
 { header: 'Age', key: 'age' }
];

Header represents the text that will be displayed, while key represents the property name mapping when you add a new row using an object. Please note that the order is important, as it will affect the final output when you save the workbook as an excel file.

 

Accessing a Column

You can access each column individually via:

• key: The mapping key declared when creating the column (ID, name, age, etc.).
• letter: The corresponding letter for the column (A, B, C, etc.).
• column number: Index number (starts from one).

const idCol = worksheet.getColumn('id');
const nameCol = worksheet.getColumn('B');
const ageCol = worksheet.getColumn(3);

 

Iterating Each Cell

You can easily iterate all current cells in the column that is not empty:

ageCol.eachCell(function(cell, rowNumber) {

});

If you want to iterate over empty cells, simply set the includeEmpty property to true as follows:

dobCol.eachCell({ includeEmpty: true }, function(cell, rowNumber) {
});

 

How to Add a Row in ExcelJS

Once you have declared the corresponding columns, you can start to add data to it by calling the addRow function. There are multiple ways to add a row to the worksheet.

 

Adding a New Row Using Key-Value Object

For example, you can pass in key-value object that matched the header columns:

const row = worksheet.addRow({id: 1, name: 'John Doe', age: 35});

 

Adding a New Row Using Array

You can use an array as well to add new data. It will assign to the columns in order (A, B, C):

const row = worksheet.addRow([2, 'Mary Sue', 22]);

 

Adding Rows of Data

In addition, you can call addRows function and pass in an array of row objects instead. This allows you to add rows of data with just a single line of code:

const rows = [
 [3,'Alex','44'],
 {id:4, name: 'Margaret', age: 32}
];
worksheet.addRows(rows);

 

Adding Page Break

There is also a function called addPageBreak that adds a new page break below the row. You can call it as follows:

row.addPageBreak();

 

Iterating Each Row

You can easily go through each row by utilizing eachRow function on your worksheet:

worksheet.eachRow(function(row, rowNumber) {
});

 

Iterating Each Cell

Similar to column, you can iterate over each cell of the row via the following

row.eachCell(function(cell, colNumber) {
});

 

Metrics

There are a few built-in metrics for getting the number of rows and total number of cells:

const rowSize = row.cellCount;
const numValues = row.actualCellCount;

 

Data Validation

You can easily incorporate data validation to your worksheet programmatically via dataValidation. For example, you can set a dropdown with a list of values via the following code:

worksheet.getCell('A1').dataValidation = {
 type: 'list',
 allowBlank: true,
 formulae: ['"One,Two,Three,Four"']
};

Here’s another example that limits the input to be in between 2.5 and seven. You can add a tooltip message to guide the users by setting the prompt property.

worksheet.getCell('A1').dataValidation = {
 type: 'decimal',
 operator: 'between',
 allowBlank: true,
 showInputMessage: true,
 formulae: [2.5, 7],
 promptTitle: 'Decimal',
 prompt: 'The value must between 2.5 and 7'
};

 

Styling in ExcelJS

exceljs supports a rich set of styling and formatting for cells, rows and columns. It comes with the following properties:

• numFmt
• font
• alignment
• border
• fill

 

Formatting

You can easily format the number, as follows:

// display value as '1 3/5'
worksheet.getCell('A1').value = 1.6;
worksheet.getCell('A1').numFmt = '# ?/?';
// display value as '1.60%'
worksheet.getCell('B1').value = 0.016;
worksheet.getCell('B1').numFmt = '0.00%';

With the exception of numFmt, which accepts a string, the rest of the style takes in a JavaScript object.

 

Font

For example, you can set the font via the following code snippet:

worksheet.getCell('A1').font = {
 name: 'Arial Black',
 color: { argb: 'FF00FF00' },
 family: 2,
 size: 14,
 italic: true
};

 

Alignment

On the other hand, you can easily set the alignment to top right, as follows:

worksheet.getCell('A1').alignment = { vertical: 'top', horizontal: 'left' };
You can also set alignment for text wrapping and indentation via the following code:
// set cell wrap-text
worksheet.getCell('A1').alignment = { wrapText: true };
// set cell indent to 1
worksheet.getCell('A1').alignment = { indent: 1 };

 

Border

To style the border of a cell based on your desired color, use the following code instead:

worksheet.getCell('A1').border = {
 top: {style:'double', color: {argb:'FF00FF00'}},
 left: {style:'double', color: {argb:'FF00FF00'}},
 bottom: {style:'double', color: {argb:'FF00FF00'}},
 right: {style:'double', color: {argb:'FF00FF00'}}
};

The underlying style property accepts the following:

• thin
• dotted
• dashDot
• hair
• dashDotDot
• slantDashDot
• mediumDashed
• mediumDashDotDot
• mediumDashDot
• medium
• double
• thick

 

Fills

For filling a particular cell, simply pass in an object with the following property:

worksheet.getCell('A1').fill = {
 type: 'pattern',
 pattern:'darkTrellis',
 fgColor:{argb:'FFFFFF00'},
 bgColor:{argb:'FF0000FF'
};

fgColor refers to the foreground color, while bgColor refers to the back end color.

The complete list for pattern types is as follows:

• none
• solid
• darkGray
• mediumGray
• lightGray
• gray125
• gray0625
• darkHorizontal
• darkVertical
• darkDown
• darkUp
• darkGrid
• darkTrellis
• lightHorizontal
• lightVertical
• lightDown
• lightUp
• lightGrid
• lightTrellis

 

Inheritance

Please note that when you set a specific style to a row or column, exceljs will internally apply the same style to all existing cells in that row or column. Newly created cells will inherit the style as well.

As a result,it’s recommended to add cells before you set the style. The following code snippet illustrates an example of setting all the headers to bold after cells creation:

// arrayObj is an array of objects
worksheet.addRows(arrayObj);
worksheet.getRow(1).font = { bold: true };

 

File I/O

exceljs provides a few helper functions for reading and loading a file depending on the use cases. Generally, it’s categorized into:

• file
• stream
• buffer

Please note that File I/O related operations return a Promise and have to be called together with the await keyword. If you are using it inside a function, make sure to set the function to async.

 

File

Assuming that you have a local excel file, you can perform reading and writing as follows:

// file reading
await workbook.xlsx.readFile(filename);
// file writing
await workbook.xlsx.writeFile(filename);

 

Stream

If you have a stream of data, use the following code snippet:

// stream reading
await workbook.xlsx.read(stream);
// stream writing
await workbook.xlsx.write(stream);

 

Buffer

When dealing with buffer, you should call load and writeBuffer function instead:

// buffer reading
await workbook.xlsx.load(data);
// buffer writing
const buffer = await workbook.xlsx.writeBuffer();

Here’s an example of reading an uploaded excel file. fileList is a FileList object obtained from input tag. Simply instantiate a new binary large object (BLOB) and get the underlying buffer. Then, pass it to the load function as follows:

const blob = new Blob([fileList[0]], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' });
const buffer = await blob.arrayBuffer();

const workbook = new ExcelJS.Workbook();
await workbook.xlsx.load(buffer);

More on JavaScriptJavaScript Call Stacks: An Introduction

 

File I/O for a Non-Node.js Project

As mentioned earlier, the read and write file functions rely on the fs module, which will throw an error if you’re using it on a browser. In order to resolve this, simply call the writeBuffer function and save the output buffer data into a BLOB. Then, save it using FileSaver.js.

const blob = new Blob([fileList[0]], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' });
const buffer = await blob.arrayBuffer();

const workbook = new ExcelJS.Workbook();
await workbook.xlsx.load(buffer);

Make sure you import the appropriate script tag for FileSaver.js. Check the installation section for more information on this.