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
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
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
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.
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
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
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
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
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
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
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
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.