Skip to content

WebNinjaDeveloper.com

Programming Tutorials




Menu
  • Home
  • Youtube Channel
  • PDF Invoice Generator
Menu

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

Posted on March 23, 2023

 

 

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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

 

 

JavaScript
1
2
3
4
5
6
7
8
9
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

 

 

JavaScript
1
2
3
4
5
6
7
8
// 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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
// 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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
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

 

 

JavaScript
1
2
3
4
5
6
7
8
// 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

 

 

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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

 

 

JavaScript
1
2
3
4
5
6
7
// 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

 

 

JavaScript
1
2
3
4
5
6
7
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.

 

 

 

Recent Posts

  • Node.js Express Project to Remove Background of Images Using Rembg & Formidable Library in Browser
  • Node.js Tutorial to Remove Background From Image Using Rembg & Sharp Library in Command Line
  • Python 3 Flask Project to Remove Background of Multiple Images Using Rembg Library in Browser
  • Python 3 Rembg Library Script to Bulk Process Multiple Images and Remove Background in Command Line
  • Python 3 Rembg Library Script to Remove Background From Image in Command Line
  • Angular
  • Bunjs
  • C#
  • Deno
  • django
  • Electronjs
  • java
  • javascript
  • Koajs
  • kotlin
  • Laravel
  • meteorjs
  • Nestjs
  • Nextjs
  • Nodejs
  • PHP
  • Python
  • React
  • ReactNative
  • Svelte
  • Tutorials
  • Vuejs




©2023 WebNinjaDeveloper.com | Design: Newspaperly WordPress Theme