Read and Write Excel Files in Node.js Using XLSX

Working with Excel files is a common task in modern web and backend applications. Common examples are like generating reports, managing financial data, or importing large datasets, handling Excel files efficiently can save a lot of time and effort. Developers often need a way to read and write Excel files in Node.js to automate these repetitive tasks. In this guide, we will walk you through everything you need to know about read and write excel files in node.js using xlsx.

Why Use Excel in Node.js

Excel remains one of the most widely used formats for storing and sharing data. Integrating Excel with Node.js can help you:

Automate repetitive data import/export operation.
Generate reports directly from your application and allowing user to download.
Import or export large datasets without manual effort.
Preserve uniformity and minimize mistakes in company processes.

By offering a user-friendly interface for programmatically reading, editing, and creating Excel files, Node.js in conjunction with the XLSX package streamlines these operations.

Module Installation

For managing excel read and write operation, you need xlsx package. The xlsx pakcage provides verity of methods which is used to perform operation like reading excel file content, converting excel data to json, writing object to excel file and much more.

Before working with read and write excel files with node.js, you need to install the XLSX package. Open your terminal and run:

npm i xlsx

You can skip this installation if xlsx package is already installed into your node.js application.

Reading Excel File in Node.js

Reading Excel files in Node.js is straightforward with XLSX. Let’s explore different examples:

const XLSX = require('xlsx');
const workbook = XLSX.readFile('data.xlsx');
const sheetName = workbook.SheetNames[0];
const sheet = workbook.Sheets[sheetName];
const data = XLSX.utils.sheet_to_json(sheet);
console.log(data);

Reading Specific Columns

While reading any Excel file in Node.js using the XLSX package, you can specify which columns to load. This helps system to prevent loading garbage data and taking un-neccesary space which reduces processing time.

const XLSX = require('xlsx');
const workbook = XLSX.readFile('employees.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet, { header: ['Name', 'Department', 'Salary'] });
console.log(data);

As per this example, it will only loads and print Name, Department and Salary columns from provided excel file.

Reading Excel from a File Buffer

Sometimes, you need to read excel files which are uploaded by user instead of your server’s file. This type of requirement is common for import process for example, in e-commerce platform where user can upload their products instead of manually adding them one by one.

const XLSX = require('xlsx');
const fs = require('fs');
const fileBuffer = fs.readFileSync('products.xlsx');
const workbook = XLSX.read(fileBuffer, { type: 'buffer' });
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet);
console.log(data);

Above example, will load data into memory without storing it on server to reduce storage usage.

Writing Excel File in Node.js

In most of today’s applications, developers provide users with a way to export their data locally. Users can use the Excel format for backups, samples, or reports, and non-technical users often prefer Excel as the export format.

Let’s take an simple example to write excel file using xlsx in node.js. For simpler understanding, you will use static data.

const XLSX = require('xlsx');
const data = [
  { Name: 'John Doe', Age: 28, Department: 'Sales' },
  { Name: 'Jane Smith', Age: 32, Department: 'HR' }
];
const worksheet = XLSX.utils.json_to_sheet(data);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, 'Employees');
XLSX.writeFile(workbook, 'employees.xlsx');

This code creates a new Excel file with employee data and saves it as employees.xlsx.

Appending Data to Existing Excel File

For sharing data real-time to outside application shared excel file is used. In that case, you have to append new data into existing excel file.

const XLSX = require('xlsx');
const workbook = XLSX.readFile('employees.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet);
data.push({ Name: 'Mike Johnson', Age: 29, Department: 'Marketing' });
const newSheet = XLSX.utils.json_to_sheet(data);
XLSX.utils.book_append_sheet(workbook, newSheet, 'Updated');
XLSX.writeFile(workbook, 'employees_updated.xlsx');

Here, it will load existing file and merge new data then write it to same or new file as per you requirement.

Conclusion

Using the XLSX package, you can efficiently read and write Excel files in Node.js to automate workflows, manage large datasets, and generate reports without manual effort. Whether it’s reading uploaded files, appending new data, or creating Excel files with multiple sheets, Node.js provides a reliable and scalable solution.

With help of xlsx package, you can read and write to multiple different sheets but for simple integration above example will work. You can check out our detailed guide on Top 20 Node.js Packages to explore essential tools and libraries that can enhance your Node.js projects and simplify development.

Codewolfy