In this short article and video, I will show you how to work with XLSX in Node.js.

First, what is an XLSX file, it is a Microsoft Excel Open XML Spreadsheet created by Microsoft Excel. It stores the data as a compressed Zip file, which also contains a bunch of other files used to open the document. Now once we got that out of the way, let me show you how to work with it.

The first thing you will need to do is to install the XLSX package using NPM if you don’t know what NPM is, then check this video out right here.

Installing the package XLSX from NPM

npm i xlsx

Importing dependencies

const XLSX = require("xlsx");

Reading the file into memory

const workbook = XLSX.readFile("file-example.xlsx");

Converting the XLSX to JSON so it's easier to work with

let worksheets = {};
for (const sheetName of workbook.SheetNames) {
    worksheets[sheetName] = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
}

This line is optional and only used for the debugging

console.log("json:\n", JSON.stringify(worksheets.Sheet1), "\n\n");

Modifying the XLSX file

worksheets.Sheet1.push({
    "First Name": "Bob",
    "Last Name": "Bob",
    "Gender": "Male",
    "Country": "United States",
    "Age": 35,
    "Date": "22/09/2020",
    "Id": 1600,
    "New Row": "test"
});

Updating the XLSX file

XLSX.utils.sheet_add_json(workbook.Sheets["Sheet1"], worksheets.Sheet1)
XLSX.writeFile(workbook, "file-example.xlsx");

Creating a new XLSX file

const newBook = XLSX.utils.book_new();
const newSheet = XLSX.utils.json_to_sheet(worksheets.Sheet1);
XLSX.utils.book_append_sheet(newBook, newSheet, "Sheet1");
XLSX.writeFile(newBook,"new-book.xlsx");

If you would rather want a video version of this article:

#xlsx #node.js #excel #nodejs #xlsx file