Working with Excel Files using Excel.js

In order to work with the Excel.js, you need to first install the package as a dependency in your project. To install Excel.js in your project, you need to run the following command −

npm install exceljs

Once you run the above command, Excel.js will be installed as a dependency in your project.

In all the examples used in this tutorial, we will work with "xlsx" files that are open XML spreadsheet files used in Microsoft Excel.

Working on ExcelJS Cells

In the example shown below, we are working with Excel sheet cells. To get a reference of a particular cell, we can use the getCell function.

Consider the code shown below.

// Read Simple Cell Values

const Excel = require('exceljs');
const wb = new Excel.Workbook();
const ws = wb.addWorksheet('My Sheet');

ws.addRows([
   [10, 2, 3, 4, 5],
   [6, 11, 8, 9, 10],
   [10, 11, 12, 14, 15],
   [16, 17, 18, 13, 20]]
);

const valueOne = ws.getCell('B1').value;
console.log(valueOne);

const valueTwo = ws.getCell(1, 1).value;
console.log(valueTwo);

const valueThree = ws.getRow(3).getCell(3).value;
console.log(valueThree);

Here, I am adding data to a worksheet and then reading that data from the cell with the help of the getCell function.

There are a few important points to note about this example, the first being that we are generating a new workbook that is generated with the code snippet shown below.

const wb = new Excel.Workbook();

Now, we need to add a new worksheet to this excel file, and we do that with the code snippet shown below.

const ws = wb.addWorksheet('My Sheet');

Now, as the next step, we add the data to the worksheet with the help of addRows, and then we make use of the getCell() function to extract the values of different cells.

Output

When you run this code, it will produce the following output −

2
10
12

Writing to an Excel file

In this example, we will write data to an Excel file. Consider the code shown below.

const Excel = require('exceljs');
const fileName = 'myexcel.xlsx';

const wb = new Excel.Workbook();
const ws = wb.addWorksheet('My Sheet');

ws.getCell('A1').value = 'Mukul Latiyan';
ws.getCell('B1').value = 'Software Developer';
ws.getCell('C1').value = new Date().toLocaleString();

const r3 = ws.getRow(3);
r3.values = [1, 2, 3, 4];

wb.xlsx
   .writeFile(fileName)
   .then(() => {
      console.log('file created');
   })
   .catch(err => {
      console.log(err.message);
   });

In this example, we are using excel.js to create an Excel file named "myexcel.js". Then we add a new sheet to the Excel file with the name "My Sheet". Finally, we are inserting different values at different cell locations of "My Sheet".

In the first example, we added values to one cell at a time and then we added values using an entire row and finally we inserted an array with different integers as values.

Here, once we add the values to the sheet, the next step is to be able to create the file in our machine, and for that we are making use of the writeFile() method.

When you run this code, it will create an Excel file called "myexcel.xlsx" and in that file, you will have all the data that we have inserted.

Reading Data from an Excel File

In this example, we will try to read the data from an excel file, and the first step in order to do so is to make use of the readFile() method and then we need to get the name of the sheet, and then simply make use of the getColumn() method. When we are able to get a particular method, the next step is to simply iterate over that column, and we can do that with the help of the eachCell() method.

Consider the code shown below.

const Excel = require('exceljs');
const wb = new Excel.Workbook();
const fileName = 'myexcel.xlsx';

wb.xlsx.readFile(fileName).then(() => {

   const ws = wb.getWorksheet('My Sheet');
   c1.eachCell(c => {
      console.log(c.value);
   });

   const c2 = ws.getColumn(2);
   c2.eachCell(c => {
      console.log(c.value);
   });
}).catch(err => {
   console.log(err.message);
});

The eachCell() method will help us to iterate over the cell values of a particular column and then we can print the values of each of these cells.

In the above example, we are making use of the eachCell() function on two columns, i.e., "column 1" and "column 2", and then we are printing all the values that are present in these columns.

Output

When you run this code, it will produce the following output −

Mukul Latiyan
1
Software Developer
2

Working with Columns

Now let's understand a more complex example where we will add an array of JSON data into columns, and then will also add multiple rows and then make use of the getColumn() and eachCell() method to get the values for each of the cell of a column.

Consider the code shown below.

const Excel = require('exceljs');

const wb = new Excel.Workbook();
const ws = wb.addWorksheet('My Sheet');

const headers = [{
   header: 'First name',
   key: 'fn',
   width: 15
},
{
   header: 'Last name',
   key: 'ln',
   width: 15
},
{
   header: 'Occupation',
   key: 'occ',
   width: 15
},
{
   header: 'Salary',
   key: 'sl',
   width: 15
},
]

ws.columns = headers;

ws.addRow(['Mukul', 'Latiyan', 'Software Developer', 1230]);
ws.addRow(['Prince', 'Yadav', 'Driver', 980]);
ws.addRow(['Mayank', 'Agarwal', 'Maali', 770]);

ws.getColumn('fn').eachCell((cell, rn) => {
   console.log(cell.value);
});

console.log('--------------');

ws.getColumn('B').eachCell((cell, rn) => {
   console.log(cell.value);
});

console.log('--------------');

ws.getColumn(3).eachCell((cell, rn) => {
   console.log(cell.value);
});

console.log('--------------');
console.log(`There are ${ws.actualColumnCount} columns`);

In this code, we created an array named headers, in which we have different objects and in each object we are defining the header, key and width, as these fields will define the properties of a particular cell, and then we are assigning this header array to columns.

After that, we are adding multiple rows to these columns and then later on, we are accessing each column and then making use of the eachCell() method to fetch value from a particular cell.

Output

When you run this code, it will produce the following output on the terminal −

First name
Mukul
Prince
Mayank
--------------
Last name
Latiyan
Yadav
Agarwal
--------------
Occupation
Software Developer
Driver
Maali
--------------
There are 4 columns

Working with Rows

Now let's see how to use the row specific methods on an Excel. Consider the code shown below.

const Excel = require('exceljs');

const wb = new Excel.Workbook();
const ws = wb.addWorksheet('My Sheet');

const headers = [{
   header: 'First name',
   key: 'fn',
   width: 15
},
{
   header: 'Last name',
   key: 'ln',
   width: 15
},
{
   header: 'Occupation',
   key: 'occ',
   width: 15
},
{
   header: 'Salary',
   key: 'sl',
   width: 15
},
]

ws.columns = headers;

ws.addRow(['Mukul', 'Latiyan', 'Software Developer', 1230]);
ws.addRow(['Prince', 'Yadav', 'Driver', 980]);
ws.addRow(['Mayank', 'Agarwal', 'Maali', 770]);

console.log(`There are ${ws.actualRowCount} rows`);

let rows = ws.getRows(1, 4).values();

for (let row of rows) {
	row.eachCell((cell, cn) => {
    	console.log(cell.value);
	});
	console.log('--');
}

Output

When you run this code, it will produce the following output on the terminal −

There are 4 rows
First name
Last name
Occupation
Salary
--
Mukul
Latiyan
Software Developer
1230
--
Prince
Yadav
Driver
980
--
Mayank
Agarwal
Maali
770
--

Conclusion

In this tutorial, we learned how we can work with excel files to extract values from cells, rows and columns with the help of Excel.js