Working with Excel Files using Excel.js

Excel.js is a powerful Node.js library for reading, manipulating, and writing Excel files. It supports both XLSX and CSV formats, making it ideal for data processing applications.

Installation

To install Excel.js in your Node.js project, run the following command:

npm install exceljs

Once installed, you can start working with Excel files programmatically. All examples in this tutorial work with XLSX (Open XML Spreadsheet) files.

Working with Excel Cells

The getCell() method allows you to access individual cells by reference or coordinates. Here's how to read cell values:

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

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

// Different ways to access cells
const valueOne = ws.getCell('B1').value;     // Using cell reference
console.log('B1 value:', valueOne);

const valueTwo = ws.getCell(1, 1).value;     // Using row, column numbers
console.log('A1 value:', valueTwo);

const valueThree = ws.getRow(3).getCell(3).value;  // Using row then cell
console.log('C3 value:', valueThree);

B1 value: 2
A1 value: 10
C3 value: 12

Writing Data to Excel Files

You can create Excel files and populate them with data using various methods:

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

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

// Set individual cell values
ws.getCell('A1').value = 'Mukul Latiyan';
ws.getCell('B1').value = 'Software Developer';
ws.getCell('C1').value = new Date().toLocaleString();

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

// Write file to disk
wb.xlsx
   .writeFile(fileName)
   .then(() => {
      console.log('Excel file created successfully');
   })
   .catch(err => {
      console.log('Error:', err.message);
   });

Excel file created successfully

Reading Data from Excel Files

To read existing Excel files, use the readFile() method and iterate through columns with eachCell():

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

wb.xlsx.readFile(fileName).then(() => {
   const ws = wb.getWorksheet('My Sheet');
   
   // Read first column
   const c1 = ws.getColumn(1);
   console.log('Column 1 values:');
   c1.eachCell(c => {
      console.log(c.value);
   });

   console.log('Column 2 values:');
   const c2 = ws.getColumn(2);
   c2.eachCell(c => {
      console.log(c.value);
   });
}).catch(err => {
   console.log('Error:', err.message);
});

Column 1 values:
Mukul Latiyan
1
Column 2 values:
Software Developer
2

Working with Structured Columns

For structured data, define column headers with properties like width and key mappings:

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

// Define column headers with properties
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;

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

// Access columns by key, letter, or number
console.log('First name column:');
ws.getColumn('fn').eachCell((cell, rn) => {
   console.log(cell.value);
});

console.log('\nColumn count:', ws.actualColumnCount);

First name column:
First name
Mukul
Prince
Mayank

Column count: 4

Working with Rows

Excel.js provides methods to work with entire rows and iterate through multiple rows:

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

// Set up columns and data (same as previous example)
const headers = [
   { header: 'First name', key: 'fn', width: 15 },
   { header: 'Last name', key: 'ln', width: 15 },
   { header: 'Occupation', key: 'occ', width: 15 }
];

ws.columns = headers;
ws.addRow(['Mukul', 'Latiyan', 'Software Developer']);
ws.addRow(['Prince', 'Yadav', 'Driver']);

console.log('Total rows:', ws.actualRowCount);

// Iterate through all rows
let rows = ws.getRows(1, ws.actualRowCount) || [];
rows.forEach((row, index) => {
   console.log(`Row ${index + 1}:`);
   row.eachCell((cell) => {
      console.log('  ', cell.value);
   });
});

Total rows: 3
Row 1:
   First name
   Last name
   Occupation
Row 2:
   Mukul
   Latiyan
   Software Developer
Row 3:
   Prince
   Yadav
   Driver

Key Features

Conclusion

Excel.js provides comprehensive functionality for working with Excel files in Node.js applications. It supports reading, writing, and manipulating both cells and structured data, making it ideal for data processing tasks.