Node.js xlsx-populate Example to Read & Write Excel Files | Encrypt & Style Excel Files in Javascript

Welcome folks today in this blog post we will be reading and writing excel files in node.js in command line And also we will be seeing how to encrypt and style excel files using the xlsx-populate library. All the full source code of the application is shown below.

Get Started

In order to get started you need to make a new node.js project using the below command as shown below

npm init -y

npm i xlsx-populate

And after that you need to make the index.js file and copy paste the following code

Writing Excel Files with Data

Now we will look at a basic example how we can write the excel data of users and save it inside the users.xlsx file as shown below

index.js

const XlsxPopulate = require('xlsx-populate');
 
// Load a new blank workbook
XlsxPopulate.fromBlankAsync()

    .then(workbook => {
        
        // Modify the workbook.
        workbook.sheet("Sheet1").cell("A1").value("Name");
        workbook.sheet("Sheet1").cell("B1").value("Age");
        workbook.sheet("Sheet1").cell("C1").value("Country");
        workbook.sheet("Sheet1").cell("A2").value("John");
        workbook.sheet("Sheet1").cell("B2").value("25");
        workbook.sheet("Sheet1").cell("C2").value("USA");
        workbook.sheet("Sheet1").cell("A3").value("Steve");
        workbook.sheet("Sheet1").cell("B3").value("45");
        workbook.sheet("Sheet1").cell("C3").value("NZ");

 
        // Write to file.
        return workbook.toFileAsync("./users.xlsx");
});

const XlsxPopulate = require('xlsx-populate');

// Load a new blank workbook

XlsxPopulate.fromBlankAsync()

.then(workbook => {

// Modify the workbook.

workbook.sheet("Sheet1").cell("A1").value("Name");

workbook.sheet("Sheet1").cell("B1").value("Age");

workbook.sheet("Sheet1").cell("C1").value("Country");

workbook.sheet("Sheet1").cell("A2").value("John");

workbook.sheet("Sheet1").cell("B2").value("25");

workbook.sheet("Sheet1").cell("C2").value("USA");

workbook.sheet("Sheet1").cell("A3").value("Steve");

workbook.sheet("Sheet1").cell("B3").value("45");

workbook.sheet("Sheet1").cell("C3").value("NZ");

// Write to file.

return workbook.toFileAsync("./users.xlsx");

});

As you can see we are importing the xlsx-populate library at the top and then we are using the fromBlankAsync() method to create a new excel workbook and inside it we are first of all getting the active sheet named Sheet1 and then we are inserting the data at the particular cell position using the cell() method and then we are inserting the value using the value() method. And lastly we are saving the excel file using the toFileAsync() method

Reading & Parsing Excel File Data

Now we will be looking at a basic example where we will be reading and parsing the specific cell data using the cell() method as shown below

index.js

const XlsxPopulate = require('xlsx-populate');
 
 
// Load an existing workbook
XlsxPopulate.fromFileAsync("./users.xlsx")
    .then(workbook => {
        // Modify the workbook.
        const value = workbook.sheet("Sheet1").cell("A1").value();
 
        // Log the value.
        console.log(value);
});

const XlsxPopulate = require('xlsx-populate');

// Load an existing workbook

XlsxPopulate.fromFileAsync("./users.xlsx")

.then(workbook => {

// Modify the workbook.

const value = workbook.sheet("Sheet1").cell("A1").value();

// Log the value.

console.log(value);

});

As you can see we are using the cell() method and we are passing the cell position which is A1 and then we are getting the value using the value() method. And then we are printing it inside the console.

Writing & Reading Excel File Using Ranges

Now guys we will be looking at ranges how we can use them to insert multiple values inside the excel files and read them also at once as shown below

index.js

const XlsxPopulate = require('xlsx-populate');
 
// Load a new blank workbook
XlsxPopulate.fromBlankAsync()

    .then(workbook => {
        
        workbook.sheet(0).cell("A1").value([
            [1, 2, 3],
            [4, 5, 6],
            [7, 8, 9]
        ]);

 
        // Write to file.
        return workbook.toFileAsync("./users2.xlsx");
});

const XlsxPopulate = require('xlsx-populate');

// Load a new blank workbook

XlsxPopulate.fromBlankAsync()

.then(workbook => {

workbook.sheet(0).cell("A1").value([

[1, 2, 3],

[4, 5, 6],

[7, 8, 9]

]);

// Write to file.

return workbook.toFileAsync("./users2.xlsx");

});

As you can see we are setting the active sheet which is 0 and then we are passing the cell number and also we are passing the 2d array of values.

const XlsxPopulate = require('xlsx-populate');
 
// Load an existing workbook
XlsxPopulate.fromFileAsync("./users.xlsx")
    .then(workbook => {
        // Modify the workbook.
        const values = workbook.sheet("Sheet1").usedRange().value();
        console.log(values)
    });

const XlsxPopulate = require('xlsx-populate');

// Load an existing workbook

XlsxPopulate.fromFileAsync("./users.xlsx")

.then(workbook => {

// Modify the workbook.

const values = workbook.sheet("Sheet1").usedRange().value();

console.log(values)

});

As you can see we are reading all the cell values from the excel file using the usedRange() method and then we are using the value() method to get the values in the form of the 2d array as shown below

Managing Sheets in Excel File

Now we can manage sheets in excel file by providing the crud operations for sheets as shown below

index.js

// Get sheet by index
const sheet1 = workbook.sheet(0);
 
// Get sheet by name
const sheet2 = workbook.sheet("Sheet2");
 
// Get all sheets as an array
const sheets = workbook.sheets();

// Get sheet by index

const sheet1 = workbook.sheet(0);

// Get sheet by name

const sheet2 = workbook.sheet("Sheet2");

// Get all sheets as an array

const sheets = workbook.sheets();

You can even add sheets inside the excel file as shown below

// Add a new sheet named 'New 1' at the end of the workbook
const newSheet1 = workbook.addSheet('New 1');
 
// Add a new sheet named 'New 2' at index 1 (0-based)
const newSheet2 = workbook.addSheet('New 2', 1);
 
// Add a new sheet named 'New 3' before the sheet named 'Sheet1'
const newSheet3 = workbook.addSheet('New 3', 'Sheet1');
 
// Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.
const sheet = workbook.sheet('Sheet1');
const newSheet4 = workbook.addSheet('New 4', sheet);

// Add a new sheet named 'New 1' at the end of the workbook

const newSheet1 = workbook.addSheet('New 1');

// Add a new sheet named 'New 2' at index 1 (0-based)

const newSheet2 = workbook.addSheet('New 2', 1);

// Add a new sheet named 'New 3' before the sheet named 'Sheet1'

const newSheet3 = workbook.addSheet('New 3', 'Sheet1');

// Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.

const sheet = workbook.sheet('Sheet1');

const newSheet4 = workbook.addSheet('New 4', sheet);

You can rename sheets inside the excel file as shown below

const sheet = workbook.sheet(0).name("new sheet name");
 

// Move 'Sheet1' to the end
workbook.moveSheet("Sheet1");
 
// Move 'Sheet1' to index 2
workbook.moveSheet("Sheet1", 2);
 
// Move 'Sheet1' before 'Sheet2'
workbook.moveSheet("Sheet1", "Sheet2");

const sheet = workbook.sheet(0).name("new sheet name");

// Move 'Sheet1' to the end

workbook.moveSheet("Sheet1");

// Move 'Sheet1' to index 2

workbook.moveSheet("Sheet1", 2);

// Move 'Sheet1' before 'Sheet2'

workbook.moveSheet("Sheet1", "Sheet2");

We can even delete specific sheets inside the excel file as shown below

// Delete 'Sheet1'
workbook.deleteSheet("Sheet1");
 
// Delete sheet with index 2
workbook.deleteSheet(2);
 
// Delete from sheet reference
workbook.sheet(0).delete();

// Delete 'Sheet1'

workbook.deleteSheet("Sheet1");

// Delete sheet with index 2

workbook.deleteSheet(2);

// Delete from sheet reference

workbook.sheet(0).delete();

Styling the Excel Sheet

Now we will be styling the excel sheet using the basic example as shown below

index.js

const XlsxPopulate = require("xlsx-populate");

// Load an existing workbook
XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {
  // Modify the workbook.
  const range = workbook.sheet("Sheet1").usedRange();
  range.style({
    bold: true,
    italic: [
      [true, false],
      [false, true],
    ],
    underline: (cell, ri, ci, range) => Math.random() > 0.5,
    fill:{
        type: "pattern",
        pattern: "darkDown",
        foreground: {
            rgb: "ff0000"
        },
        background: {
            theme: 3,
            tint: 0.4
        }
    }
  });

  return workbook.toFileAsync("style.xlsx");
});

const XlsxPopulate = require("xlsx-populate");

// Load an existing workbook

XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {

// Modify the workbook.

const range = workbook.sheet("Sheet1").usedRange();

range.style({

bold: true,

italic: [

[true, false],

[false, true],

underline: (cell, ri, ci, range) => Math.random() > 0.5,

fill:{

type: "pattern",

pattern: "darkDown",

foreground: {

rgb: "ff0000"

background: {

theme: 3,

tint: 0.4

}

});

return workbook.toFileAsync("style.xlsx");

});

As you can see we are using the style() method and inside it we are passing multiple styles including the bold and underline as you can see below. And also we are changing the background color of the excel cell values.

Inserting Hyperlinks in Excel Sheet

Now we can even insert hyperlinks inside the excel cell values as shown below

// Load an existing workbook
XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {
  // Modify the workbook.
  const hyperlink = workbook.sheet(0).cell("A1").style({underline:true}).hyperlink("https://google.com");

  return workbook.toFileAsync("hyperlink.xlsx");
});

// Load an existing workbook

XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {

// Modify the workbook.

const hyperlink = workbook.sheet(0).cell("A1").style({underline:true}).hyperlink("https://google.com");

return workbook.toFileAsync("hyperlink.xlsx");

});

Encrypting Excel Sheet With Password

Now we will be encrypting excel documents with password using the below example

index.js

const XlsxPopulate = require("xlsx-populate");

// Load an existing workbook
XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {
  // Modify the workbook.
  workbook.toFileAsync("./password.xlsx", { password: "S3cret!" });
});

const XlsxPopulate = require("xlsx-populate");

// Load an existing workbook

XlsxPopulate.fromFileAsync("./users.xlsx").then((workbook) => {

// Modify the workbook.

workbook.toFileAsync("./password.xlsx", { password: "S3cret!" });

});

As you can see we are passing a second argument and inside it we are providing the password to the excel file.