ExcelJS

ExcelJS is a powerful JavaScript library used to read, manipulate, and write Excel files. It allows developers to work with Excel data in .xlsx or .csv formats programmatically.

This is extremely useful when generating reports, importing/exporting data, or working with spreadsheets in JavaScript/Node.js environments.

In this tutorial, we will cover:

1. What is ExcelJS?
2. Installation and Setup
3. Creating a New Workbook and Worksheet
4. Adding Data to a Worksheet
5. Reading Data from a Worksheet
6. Styling Cells and Formatting Data
7. Working with Formulas
8. Saving Excel Files
9. Reading from Existing Excel Files
10. Exporting Excel Files to CSV
11. Examples and Use Cases

Let’s explore each of these with examples.

1. What is ExcelJS?

ExcelJS is a JavaScript library that allows you to work with Excel files in both Node.js and browser environments. With ExcelJS, you can:

• Create and manipulate Excel workbooks.
• Add data to worksheets.
• Apply styles to cells.
• Work with formulas and functions.
• Read existing Excel files.
• Export Excel data to CSV format.

It supports .xlsx files natively and has extensive features for handling Excel data.

2. Installation and Setup

Installation

To use ExcelJS, you need to install it using npm or yarn.

npm install exceljs

or

yarn add exceljs

Importing ExcelJS

Once installed, you can import the library in your project:

const ExcelJS = require('exceljs');

// ES6 import (if supported)
import ExcelJS from 'exceljs';

3. Creating a New Workbook and Worksheet

A workbook is the top-level structure of an Excel file, and it can contain multiple worksheets. Let’s create a workbook and add a worksheet.

Example: Creating a Workbook and Worksheet

const ExcelJS = require('exceljs');

// Create a new workbook
const workbook = new ExcelJS.Workbook();

// Add a new worksheet
const worksheet = workbook.addWorksheet('My Sheet');

// Set column headers for the worksheet
worksheet.columns = [
    { header: 'ID', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 },
    { header: 'Age', key: 'age', width: 10 },
];

console.log('Worksheet created successfully!');

In this example:

• We created a workbook using new ExcelJS.Workbook().
• We added a worksheet named “My Sheet” using workbook.addWorksheet().
• We defined columns for the worksheet with headers (ID, Name, Age) and specified the column widths.

4. Adding Data to a Worksheet

You can add data to a worksheet in ExcelJS by using worksheet.addRow() or by assigning values directly to cells.

Example 1: Adding Rows to the Worksheet

// Add data as rows
worksheet.addRow({ id: 1, name: 'John Doe', age: 30 });
worksheet.addRow({ id: 2, name: 'Jane Smith', age: 25 });

console.log('Data added to the worksheet!');

Example 2: Adding Data to Individual Cells

// Add data to specific cells
worksheet.getCell('A4').value = 3;
worksheet.getCell('B4').value = 'Alice Green';
worksheet.getCell('C4').value = 29;

console.log('Data added to individual cells!');

In these examples:

• worksheet.addRow() is used to add rows of data in one go.
• worksheet.getCell() is used to target specific cells and assign values directly.

5. Reading Data from a Worksheet

Once data has been added to a worksheet, you can read it by accessing rows or individual cells.

Example: Reading Data from a Worksheet

// Loop through rows and print data
worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
    console.log(`Row ${rowNumber}: ${row.values}`);
});

In this example:

• worksheet.eachRow() is used to loop through each row of the worksheet. The includeEmpty: true option ensures that empty rows are also considered.
• The data in each row is accessed using row.values, which returns an array of cell values.

6. Styling Cells and Formatting Data

ExcelJS allows you to style cells by changing fonts, backgrounds, borders, and alignment.

Example: Styling a Cell

const headerCell = worksheet.getCell('A1');

// Set font, fill, and alignment
headerCell.font = { name: 'Arial', family: 4, size: 12, bold: true };
headerCell.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFFF00' },  // Yellow background
};
headerCell.alignment = { vertical: 'middle', horizontal: 'center' };

console.log('Cell styling applied!');

In this example:

• font is used to change the font style.
• fill is used to set a solid background color.
• alignment adjusts the vertical and horizontal alignment of the cell content.

7. Working with Formulas

You can use Excel formulas within cells, and ExcelJS will handle the calculation when the file is opened in Excel.

Example: Adding a Formula to a Cell

worksheet.getCell('D2').value = { formula: 'B2+C2', result: 55 };

console.log('Formula added to the worksheet!');

In this example:

• The value of cell D2 is set to the formula B2 + C2, and the result is pre-calculated as 55. The result will automatically update when the Excel file is opened.

8. Saving Excel Files

Once you've populated your workbook, you can save it as an .xlsx file.

Example: Saving a Workbook

// Write the workbook to a file
workbook.xlsx.writeFile('myWorkbook.xlsx')
    .then(() => {
        console.log('Workbook saved successfully!');
    })
    .catch((error) => {
        console.error('Error saving workbook:', error);
    });

In this example:

• workbook.xlsx.writeFile() writes the workbook to a file called myWorkbook.xlsx.

9. Reading from Existing Excel Files

ExcelJS can also read data from existing Excel files.

Example: Reading an Excel File

// Load an existing workbook
workbook.xlsx.readFile('existingWorkbook.xlsx')
    .then(() => {
        const worksheet = workbook.getWorksheet(1);

        // Read data from specific cells
        const name = worksheet.getCell('B2').value;
        const age = worksheet.getCell('C2').value;

        console.log(`Name: ${name}, Age: ${age}`);
    })
    .catch((error) => {
        console.error('Error reading workbook:', error);
    });

In this example:

• workbook.xlsx.readFile() is used to load an existing Excel file.
• Once the workbook is loaded, you can access worksheets and read data from specific cells.

10. Exporting Excel Files to CSV

ExcelJS also allows you to export worksheets to CSV format.

Example: Exporting a Worksheet to CSV

// Export the worksheet to a CSV file
worksheet.csv.writeFile('myWorksheet.csv')
    .then(() => {
        console.log('Worksheet exported to CSV successfully!');
    })
    .catch((error) => {
        console.error('Error exporting to CSV:', error);
    });

In this example:

• worksheet.csv.writeFile() exports the worksheet data to a file named myWorksheet.csv.

11. Examples and Use Cases

Example 1: Generating a Sales Report

const salesData = [
    { id: 1, product: 'Laptop', quantity: 2, price: 1200 },
    { id: 2, product: 'Phone', quantity: 5, price: 600 },
    { id: 3, product: 'Tablet', quantity: 3, price: 300 },
];

// Add column headers
worksheet.columns = [
    { header: 'ID', key: 'id', width: 10 },
    { header: 'Product', key: 'product', width: 20 },
    { header: 'Quantity', key: 'quantity', width: 10 },
    { header: 'Price', key: 'price', width: 15 },
    { header: 'Total', key: 'total', width: 15, formula: 'C2*D2' },  // Adding a formula for total
];

// Add sales data
salesData.forEach(sale => {
    worksheet.addRow({
        id: sale.id,
        product: sale.product,
        quantity: sale.quantity,
        price: sale.price,
        total: sale.quantity * sale.price,  // Calculate total
    });
});

// Save the workbook
workbook.xlsx.writeFile('salesReport.xlsx')
    .then(() => console.log('Sales report generated successfully!'));

Example 2: Reading and Summing Data from a

Worksheet

// Load the sales report
workbook.xlsx.readFile('salesReport.xlsx')
    .then(() => {
        const worksheet = workbook.getWorksheet(1);

        // Calculate total sales by summing the total column
        let totalSales = 0;
        worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
            if (rowNumber > 1) {  // Skip header row
                totalSales += row.getCell('E').value;
            }
        });

        console.log(`Total Sales: $${totalSales}`);
    });

Conclusion

ExcelJS is a powerful library for working with Excel files in JavaScript/Node.js.

In this tutorial, we covered:

• Creating workbooks and worksheets.
• Adding and reading data from worksheets.
• Styling cells and working with formulas.
• Saving and reading Excel files.
• Exporting worksheets to CSV format.