Exporting Multiple Tables to a Single Excel Sheet with ExcelJS in Angular and Node.js

Ever struggled with managing reports that contain multiple tables?

Imagine having to download and juggle separate files — not ideal! This blog post will show you how to create a single, consolidated Excel sheet containing all your tables using ExcelJS in both Angular and Node.js.

We’ll break it down into easy-to-understand steps and even build a mini-project to solidify your learning!

What is ExcelJS?

Think of ExcelJS as a magic wand for your web applications. It’s a JavaScript library that lets you create and manipulate Excel files directly in your code. You can add data, format cells, and even generate charts — all without relying on external software.

Using ExcelJS in Angular and Node.js

The beauty of ExcelJS is its versatility. It can be used on both the client-side (Angular) and server-side (Node.js) of your application.

Here’s what we’ll need:

• Node.js and npm (Node Package Manager): If you don’t have them installed already, head over to https://nodejs.org/en to download and set them up.

npm install exceljs --save

Mini-Project 1: Student Data Export in Angular

import { Component } from '@angular/core';
import * as ExcelJS from 'exceljs/dist/exceljs.min.js';
import { saveAs } from 'file-saver';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.scss']
})
export class AppComponent {
  studentData = [
  { name: "Muskan", RollNo: 101, Grade: "A+", Class: 10 },
  { name: "John", RollNo: 102, Grade: "A", Class: 10 },
  { name: "Emily", RollNo: 103, Grade: "B+", Class: 10 },
  { name: "Michael", RollNo: 104, Grade: "A", Class: 10 },
  { name: "Sophia", RollNo: 105, Grade: "A+", Class: 10 },
  { name: "James", RollNo: 106, Grade: "B", Class: 10 },
  { name: "Emma", RollNo: 107, Grade: "A+", Class: 10 },
  { name: "William", RollNo: 108, Grade: "A", Class: 10 },
  { name: "Olivia", RollNo: 109, Grade: "B+", Class: 10 },
  { name: "Alexander", RollNo: 110, Grade: "A", Class: 10 },
  { name: "Ava", RollNo: 111, Grade: "A+", Class: 10 },
  { name: "Henry", RollNo: 112, Grade: "B", Class: 10 },
  { name: "Ethan", RollNo: 113, Grade: "A+", Class: 10 }
];

teachersData = [
  { name: "Mr. Smith", TeacherNo: 201, Subject: "Maths" },
  { name: "Ms. Johnson", TeacherNo: 202, Subject: "Science" },
  { name: "Mr. Brown", TeacherNo: 203, Subject: "History" },
  { name: "Ms. Martinez", TeacherNo: 204, Subject: "English" },
  { name: "Mr. Wilson", TeacherNo: 205, Subject: "Art" },
  { name: "Ms. Taylor", TeacherNo: 206, Subject: "Physical Education" },
  { name: "Mr. Anderson", TeacherNo: 207, Subject: "Music" },
  { name: "Ms. Thomas", TeacherNo: 208, Subject: "Geography" }
];

subjectData = [
  { subjectName: "Maths", No: 1 },
  { subjectName: "Science", No: 2 },
  { subjectName: "History", No: 3 },
  { subjectName: "English", No: 4 }
];t

  allData = { 'Student Data': this.studentData, 'Teacher Data': this.teachersData, 'Subject Data': this.subjectData };

  constructor() {}

  exportToExcel(data: any, fileName: string): void {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet 1');

    const boldStringRow = worksheet.addRow(['TotalData']);
    boldStringRow.font = { bold: true };

    worksheet.addRow([]);

    Object.keys(data).forEach(sectionKey => {
      const headerRow = worksheet.addRow([sectionKey]);
      headerRow.font = { bold: true };

      const sectionData = data[sectionKey];

      const tableHeader = worksheet.addRow(Object.keys(sectionData[0]));
      tableHeader.eachCell((cell, colNumber) => {
        cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFFF00' } };
        cell.font = { bold: true };
        worksheet.getColumn(colNumber).width = 25;
      });

      sectionData.forEach((item: any) => {
        const rowData = Object.values(item);
        const row = worksheet.addRow(rowData);
        row.eachCell((cell, colNumber) => {
          cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
          if (colNumber >= 2 && colNumber <= 4) {
            cell.alignment = { horizontal: 'center' };
          }
        });
      });

      worksheet.addRow([]);
    });

    workbook.xlsx.writeBuffer().then(buffer => {
      const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      saveAs(blob, fileName);
    });
  }
}

Result:

In your Angular component, you can define these arrays (studentData, teachersData, subjectData) as properties of the component class. You can then use these properties in your template or methods to display or manipulate the data as needed.

Let’s break down the provided code -

This function, named `exportToExcel`, is designed to take in some data along with a file name and export it to an Excel file. It utilizes the ExcelJS library for handling Excel-related operations.

First, it initializes a new Excel workbook and adds a worksheet named ‘Sheet 1’. Then, it adds a row with the label ‘TotalData’, making the text bold, which likely serves as a header for the entire dataset.

After adding an empty row, it iterates over the keys of the input data object, presumably representing different sections of data. For each section, it adds a bold header row with the section’s key. Then, it extracts the data corresponding to that section and adds another row with the column headers based on the keys of the first item in that section’s data.

For each cell in the header row, it sets specific formatting, including border styles, background color, and bold font. Additionally, it adjusts the column width to 25 units.

Next, it iterates through each item in the section’s data, adding a row for each item. For each cell in these rows, it sets border styles, and if the cell’s column number falls within a certain range (presumably columns 2 to 4), it horizontally centers the cell’s content.

Finally, it adds an empty row after each section’s data for better visual separation. Once all data has been processed, it writes the workbook to a buffer in the Excel file format. This buffer is then converted into a Blob object and saved as a file using the `saveAs` function, with the provided file name. The file type is specified as an Excel spreadsheet.

Mini-Project 2: Export in NodeJS

const ExcelJS = require('exceljs');
const { saveAs } = require('file-saver');

function exportToExcel(data, fileName) {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet 1');

    const boldStringRow = worksheet.addRow(['TotalData']);
    boldStringRow.font = { bold: true };

    worksheet.addRow([]);

    Object.keys(data).forEach(sectionKey => {
      const headerRow = worksheet.addRow([sectionKey]);
      headerRow.font = { bold: true };

      const sectionData = data[sectionKey];

      const tableHeader = worksheet.addRow(Object.keys(sectionData[0]));
      tableHeader.eachCell((cell, colNumber) => {
        cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFFF00' } };
        cell.font = { bold: true };
        worksheet.getColumn(colNumber).width = 25;
      });

      sectionData.forEach(item => {
        const rowData = Object.values(item);
        const row = worksheet.addRow(rowData);
        row.eachCell((cell, colNumber) => {
          cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
          if (colNumber >= 2 && colNumber <= 4) {
            cell.alignment = { horizontal: 'center' };
          }
        });
      });

      worksheet.addRow([]);
    });

    workbook.xlsx.writeBuffer().then(buffer => {
      const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      saveAs(blob, fileName);
    });
}

const data = {
  "studentData": [
    { name: "Muskan", RollNo: 101, Grade: "A+", Class: 10 },
    { name: "John", RollNo: 102, Grade: "A", Class: 10 },
    { name: "Emily", RollNo: 103, Grade: "B+", Class: 10 },
    { name: "Michael", RollNo: 104, Grade: "A", Class: 10 },
    { name: "Sophia", RollNo: 105, Grade: "A+", Class: 10 },
    { name: "James", RollNo: 106, Grade: "B", Class: 10 },
    { name: "Emma", RollNo: 107, Grade: "A+", Class: 10 }
  ],
  "TeachersData": [
    { name: "Mr. Smith", TeacherNo: 201, Subject: "Maths" },
    { name: "Ms. Johnson", TeacherNo: 202, Subject: "Science" },
    { name: "Mr. Brown", TeacherNo: 203, Subject: "History" },
    { name: "Ms. Martinez", TeacherNo: 204, Subject: "English" },
    { name: "Mr. Wilson", TeacherNo: 205, Subject: "Art" }
  ],
  "SubjectData": [
    { subjectName: "Maths", No: 1 },
    { subjectName: "Science", No: 2 },
    { subjectName: "History", No: 3 },
    { subjectName: "English", No: 4 }
  ]
};

const fileName = 'output.xlsx';
exportToExcel(data, fileName);

Result:

Formatting options available in ExcelJS:

1. Font:
— Set font properties.
— Example: `cell.font = { bold: true }`

2. Fill:
— Apply fill color.
— Example: `cell.fill = { type: ‘pattern’, pattern: ‘solid’, fgColor: { argb: ‘FFFF0000’ } }`

3. Borders:
— Define border styles.
— Example: `cell.border = { top: { style: ‘thin’ }, bottom: { style: ‘thin’ } }`

4. Alignment:
— Align cell contents.
— Example: `cell.alignment = { horizontal: ‘center’, vertical: ‘middle’ }`

5. Number Format:
— Format cell values.
— Example: `cell.numFmt = ‘0.00%’`

6. Protection:
— Lock or hide cells.
— Example: `worksheet.getCell(‘A1’).protection = { locked: true }`

7. Background Image:
— Set worksheet background.
— Example: `worksheet.background = { image: ‘image.png’ }`

8. Column and Row Width/Height:
— Adjust dimensions.
— Example: `worksheet.getColumn(1).width = 20`

There are many options available to format your excel sheet from your application.

Conclusion:

By combining Angular for the front-end and Node.js for the back-end, developers can create detailed Excel files quickly. This helps improve user experience and simplifies data handling, making it a useful tool for web apps needing advanced data exporting features.