Read and Write Excel File in Node.js

ExcelJS is a JavaScript library that provides a convenient solution for working with '.xlsx' files, allowing you to read, manipulate, and save data programmatically using Node.js.

In this tutorial, we'll walk through the steps for reading from and writing to Excel files in Node.js using ExcelJS. You'll learn how to initialize a workbook, read data from existing Excel files, dynamically build new worksheets, and save updates. Each section includes practical examples to help you apply the concepts in real-world scenarios.

Prerequisite

• You should have a basic understanding of JavaScript
• You should have a basic knowledge of Nodejs.

Setting Up the Environment

Let's set up the project structure and install the necessary library. Follow these steps to prepare your Node.js environment:

Step 1: Create a New Directory

Start by creating a new directory for the project. We'll name it excel-read-write, but feel free to name it as you like.

mkdir excel-read-write
cd excel-read-write

Step 2: Initialize a Node.js project

Run the following command to initialize a new Node.js project with a default package.json file:

npm init -y

Step 3: Install the exceljs Library

To work with Excel files, install the exceljs library.

npm install exceljs

Step 4: Create an Application File

For ease, create a file called app.js where we'll write our code.

touch app.js

Reading Data from an Excel File

Let's start by reading data from an existing Excel file. In this scenario, suppose you have an Excel file named country.xlsx that contains a list of countries and their populations. Our goal is to load this file, read its content, and display each row's data.

Let's start writing our app.js file.

Step 1: Import ExcelJS and Initialize the Workbook

Begin by importing exceljs and creating a new Workbook instance.

const ExcelJS = require("exceljs");
const workbook = new ExcelJS.Workbook();

Step 2: Read the File and Access Data

We will use the following data. You can save the data as country.xlsx.

country.xlsx

Country	Population
Nigeria	200 million
Ghana	10 million
Senegal	5 million

Use 'workbook.xlsx.readFile()' to load 'country.xlsx'. After loading, you can access individual worksheets by index or name.

workbook.xlsx.readFile("country.xlsx").then(() => {
    const worksheet = workbook.getWorksheet(1); // Get the first sheet
    // Iterate through each row in the worksheet
    worksheet.eachRow((row, rowNumber) => {
		  console.log(`Row ${rowNumber}: ${row.values}`);
	 });
});

Output

Writing Data to a New Excel File

Now that we've covered reading data, let's explore how to write data to a new Excel file. Suppose you want to create a report summarizing employee roles, salaries, and years of experience.

• Create a New Worksheet: Use 'workbook.addWorksheet()' to create a new worksheet.
• Define Columns and Add Rows: Set up columns with headers and keys. Each row can then be added as an object with key-value pairs.
• Save the File: Use 'writeFile()' to save the workbook to a new file, such as 'output.xlsx'.

Here's the code to set this up in app.js:

const ExcelJS = require("exceljs");
const workbook = new ExcelJS.Workbook();

// Add a new worksheet named "Sheet1"
const worksheet = workbook.addWorksheet("Sheet1");

// Define columns for the worksheet
worksheet.columns = [
{ header: "Job", key: "job", width: 10 },
{ header: "Salary", key: "salary", width: 10 },
{ header: "Years", key: "years", width: 5 },
];

// Add rows to the worksheet
worksheet.addRow({ job: "Engineer", salary: 50000, years: 5 });
worksheet.addRow({ job: "Designer", salary: 40000, years: 3 });
worksheet.addRow({ job: "Manager", salary: 60000, years: 10 });

// Save the workbook to a new file
workbook.xlsx.writeFile("output.xlsx").then(() => {

console.log("File created successfully");

});

Here's a breakdown of the key steps:

• addWorksheet("Sheet1") creates a new worksheet named "Sheet1".
• worksheet.columns defines the headers and keys. Each key maps to a column header, while width sets the column width in the file.
• addRow() adds individual rows based on the specified keys.

After running this code, you'll have a file called output.xlsx with a new worksheet named Sheet1 containing your specified data.

Output

Complete Program

To summarize, here's the complete code that reads data from country.xlsx and writes new data to output.xlsx.

const ExcelJS = require("exceljs");

// Create a new Workbook
const workbook = new ExcelJS.Workbook();

// Read existing data
workbook.xlsx.readFile("country.xlsx").then(() => {
   const worksheet = workbook.getWorksheet(1); // Get the first sheet
   worksheet.eachRow((row, rowNumber) => {
       console.log(`Row ${rowNumber}: ${row.values}`);
   });
});

// Add new data to a different worksheet
const newWorkbook = new ExcelJS.Workbook();
const newWorksheet = newWorkbook.addWorksheet("Sheet1");

// Define columns
newWorksheet.columns = [
   { header: "Job", key: "job", width: 10 },
   { header: "Salary", key: "salary", width: 10 },
   { header: "Years", key: "years", width: 5 },
];

// Insert rows
newWorksheet.addRow({ job: "Engineer", salary: 50000, years: 5 });
newWorksheet.addRow({ job: "Designer", salary: 40000, years: 3 });
newWorksheet.addRow({ job: "Manager", salary: 60000, years: 10 });

// Save new workbook
newWorkbook.xlsx.writeFile("output.xlsx").then(() => {
   console.log("File created successfully");
});

Conclusion

In this tutorial, we covered how to read and write Excel files in Node.js using the 'exceljs' library. You learned how to initialize a workbook, read data from an existing Excel file, and dynamically add and configure worksheets, columns, and rows. We also walked through the process of saving updates to a new Excel file.

Working with Excel files in Node.js opens up numerous possibilities for handling data, such as generating reports, processing data from external systems, or exporting results from your application. By leveraging 'exceljs', you can build powerful data-driven applications and simplify data exchange between systems. This tutorial provides a foundation for working with Excel in Node.js, which you can extend to suit more complex use cases.