Introduction to ExcelJS

ExcelJS is a powerful library in the JavaScript ecosystem that enables you to create, read, and edit Excel (.xlsx) file formats. Whether you’re developing web applications, Node.js backends, or automated data processing scripts, ExcelJS provides a versatile API to work with Excel spreadsheets seamlessly.

Getting Started

First, let’s install ExcelJS:

npm install exceljs

Create a New Workbook

Creating a new workbook is straightforward with ExcelJS:

const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();

Add a Worksheet

Adding a worksheet to a workbook is easy:

const worksheet = workbook.addWorksheet('My Sheet');

Working with Columns and Rows

You can define columns and their properties, as well as add rows:

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 30 },
  { header: 'DOB', key: 'dob', width: 15 },
];

worksheet.addRow({ id: 1, name: 'John Doe', dob: '1980-01-01' });
worksheet.addRows([
  { id: 2, name: 'Jane Doe', dob: '1990-01-01' },
  { id: 3, name: 'Sam Smith', dob: '1975-01-01' },
]);

Formatting Cells

You can format cells by applying fonts, alignment, and borders:

const cell = worksheet.getCell('A1');
cell.font = { name: 'Arial', size: 12, bold: true };
cell.alignment = { vertical: 'middle', horizontal: 'center' };
cell.border = {
  top: { style: 'thin' },
  left: { style: 'thin' },
  bottom: { style: 'thin' },
  right: { style: 'thin' }
};

Reading Excel Files

ExcelJS also supports reading existing Excel files:

workbook.xlsx.readFile('path/to/excel/file.xlsx')
  .then(function() {
    const worksheet = workbook.getWorksheet('My Sheet');
    worksheet.eachRow({ includeEmpty: true }, function(row, rowNum) {
      console.log('Row ' + rowNum + ' = ' + JSON.stringify(row.values));
    });
  });

Writing to Excel Files

Finally, you can save your workbook to disk:

workbook.xlsx.writeFile('path/to/excel/file.xlsx')
  .then(function() {
    console.log('File written successfully');
  });

Example Application

Here is an example of a simple Node.js application that uses ExcelJS to read from one Excel file, process the data, and write to a new Excel file:

const ExcelJS = require('exceljs');

async function processExcel() {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile('input.xlsx');

  const worksheet = workbook.getWorksheet('Sheet1');

  worksheet.eachRow({ includeEmpty: true }, (row, rowNum) => {
    console.log(\`Row \${rowNum}: \`, JSON.stringify(row.values));
  });

  const newWorkbook = new ExcelJS.Workbook();
  const newWorksheet = newWorkbook.addWorksheet('Processed Data');

  newWorksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 30 },
    { header: 'DOB', key: 'dob', width: 15 },
    { header: 'Processed', key: 'processed', width: 20 }
  ];

  worksheet.eachRow({ includeEmpty: true }, (row, rowNum) => {
    if (rowNum > 1) {
      newWorksheet.addRow({
        id: row.getCell(1).value,
        name: row.getCell(2).value,
        dob: row.getCell(3).value,
        processed: true
      });
    }
  });

  await newWorkbook.xlsx.writeFile('output.xlsx');
  console.log('New file written successfully');
}

processExcel();

In this example, we read data from ‘input.xlsx’, process it, and write out the transformed data to ‘output.xlsx’. This demonstrates the flexibility and power of ExcelJS across common use cases.

Start leveraging ExcelJS today and enhance your JavaScript-based applications with efficient Excel file manipulations.

Hash: b9c5119e82466654777de41ecf006d85daf31cfd45c298a7b1b95a4d3b4ccec4