ExcelJS: A Comprehensive Guide to Working with Excel Files in Node.js

When it comes to handling Excel files in a programmatic way, whether for generating reports, manipulating data, or creating automated workflows, ExcelJS is one of the most powerful libraries available in the Node.js ecosystem. ExcelJS allows developers to read, write, and modify Excel files (both .xlsx and .xlsm formats) easily and efficiently.

This article will walk you through the key features of ExcelJS, how to get started with it, and answer some frequently asked questions.

---

What is ExcelJS?

ExcelJS is an open-source JavaScript library that makes working with Excel files (particularly .xlsx and .xlsm formats) in Node.js applications simple. With ExcelJS, you can read and write Excel files, manipulate their contents, and customize the formatting of cells, rows, and sheets. This makes it especially useful for developers who need to create or process spreadsheets dynamically in server-side JavaScript.

Some of the key features of ExcelJS include:

• Reading and writing .xlsx and .xlsm files
• Styling and formatting cells (font styles, colors, borders, etc.)
• Creating and managing worksheets (add/remove sheets, set headers)
• Managing data types (numbers, dates, booleans, formulas, etc.)
• Supports multiple sheets and their navigation
• Working with cell values, formulas, and conditional formatting

Whether you’re creating reports, processing large datasets, or exporting data to Excel files for users, ExcelJS simplifies these tasks in Node.js applications.

Key Features of ExcelJS

1. Reading Excel Files

ExcelJS allows you to read existing Excel files, extract data, and process it in your application. You can work with individual sheets and retrieve values from specific cells. Here’s a simple example of how to read an .xlsx file:

Example:

const ExcelJS = require('exceljs');

async function readExcelFile() {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile('example.xlsx');
  const worksheet = workbook.getWorksheet(1); // Get the first sheet

  // Access a specific cell
  const cell = worksheet.getCell('A1');
  console.log(cell.value);
}

readExcelFile();

In this example, we load an Excel file (example.xlsx), access the first worksheet, and then print the value of cell A1.

2. Writing Excel Files

You can create new Excel files or modify existing ones using ExcelJS. The library provides a simple API for setting cell values, formatting cells, and even adding new rows or columns. Here’s an example of creating a new Excel file:

Example:

const ExcelJS = require('exceljs');

async function createExcelFile() {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('MySheet'); // Create a new sheet

  // Add data to cells
  worksheet.getCell('A1').value = 'Hello';
  worksheet.getCell('B1').value = 'World';

  // Save the workbook to a file
  await workbook.xlsx.writeFile('output.xlsx');
}

createExcelFile();

This script creates a new workbook, adds a worksheet, and writes data into cells A1 and B1, then saves the file as output.xlsx.

3. Styling Cells

ExcelJS allows you to apply different styles to cells, such as changing font size, colors, borders, and more. This helps in formatting your reports or data outputs to make them more readable.

Example:

const ExcelJS = require('exceljs');

async function styleCells() {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('StyledSheet');

  // Apply style to a cell
  worksheet.getCell('A1').value = 'Styled Cell';
  worksheet.getCell('A1').font = { bold: true, size: 14 };
  worksheet.getCell('A1').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFF00' } };
  worksheet.getCell('A1').alignment = { horizontal: 'center' };
  worksheet.getCell('A1').border = {
    top: { style: 'thin' },
    left: { style: 'thin' },
    bottom: { style: 'thin' },
    right: { style: 'thin' }
  };

  // Save the workbook to a file
  await workbook.xlsx.writeFile('styled_output.xlsx');
}

styleCells();

This example shows how to apply bold text, a yellow background, centered text, and borders to cell A1.

4. Handling Formulas

ExcelJS can also be used to set formulas in cells. You can dynamically generate formulas based on the data in your spreadsheet.

Example:

const ExcelJS = require('exceljs');

async function addFormula() {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('FormulaSheet');

  worksheet.getCell('A1').value = 10;
  worksheet.getCell('A2').value = 20;
  worksheet.getCell('A3').formula = 'A1 + A2'; // Add a formula to cell A3

  // Save the workbook
  await workbook.xlsx.writeFile('formula_output.xlsx');
}

addFormula();

In this example, cell A3 contains a formula that sums the values of A1 and A2.

5. Managing Multiple Sheets

ExcelJS makes it easy to work with multiple worksheets within the same workbook. You can create, remove, or navigate between sheets.

Example:

const ExcelJS = require('exceljs');

async function multipleSheets() {
  const workbook = new ExcelJS.Workbook();
  const sheet1 = workbook.addWorksheet('Sheet1');
  const sheet2 = workbook.addWorksheet('Sheet2');

  sheet1.getCell('A1').value = 'Data for Sheet1';
  sheet2.getCell('A1').value = 'Data for Sheet2';

  await workbook.xlsx.writeFile('multi_sheet_output.xlsx');
}

multipleSheets();

This example demonstrates how to create two sheets and write data into them before saving the file.

---

Frequently Asked Questions (FAQs)

Q1: What file formats does ExcelJS support?

ExcelJS supports .xlsx and .xlsm file formats, both of which are standard Excel file types. It does not support the older .xls format natively.

Q2: Can I manipulate Excel charts with ExcelJS?

No, ExcelJS does not currently support creating or modifying Excel charts. However, you can work with cell data, which can then be used to create charts manually within Excel.

Q3: Is ExcelJS suitable for large datasets?

ExcelJS can handle relatively large files, but performance may degrade with extremely large datasets. For very large Excel files, consider breaking the data into smaller chunks or using other libraries that focus on performance optimization.

Q4: Does ExcelJS support password-protected files?

ExcelJS does not support reading or writing password-protected Excel files. You will need to remove the password protection manually or use another library to decrypt the file before working with it.

Q5: How do I install ExcelJS in my project?

You can install ExcelJS using npm or yarn:

npm install exceljs
# or
yarn add exceljs

Once installed, you can require the library and start working with Excel files in your Node.js application.

Q6: Can I use ExcelJS in the browser?

ExcelJS is primarily designed for Node.js environments, but it is possible to use it in the browser with some adjustments. However, performance may not be optimal when dealing with large Excel files in the browser.

---

Conclusion

ExcelJS is a powerful and flexible library for working with Excel files in Node.js. Whether you’re reading, writing, or manipulating Excel files, ExcelJS provides an easy-to-use API with rich functionality such as styling, formula management, and multi-sheet handling. It’s an excellent choice for server-side applications that require Excel report generation or data export.

By understanding its core features and methods, you can effectively automate Excel file handling in your Node.js applications, making it a versatile tool for many use cases.