How To Export Data to Excel in Angular

#angular #excel

Data export to Excel is a common task in modern business-facing apps and of course we have npm packages to help us do just that. An amazing one to consider is Angular Material Table Exporter. But there are a big prerequisite to use it. Your data has to be rendered using Material Table. If you are OK with it, then follow the docs for the mat-table-exporter package for a painless integration - it works like a charm. One gotcha that you may face is the bundle size. If you follow the default integration steps and add MatTableExporterModule to shared.module.ts, your bundle size may gain 1.5Mb. Of course you can lazy-load it, move to the server completely, or use the method below.

Meet excel-export Service

We will be using only one fairly low-level dependency - xlsx and go from there. Let’s make sure we have the latter installed:

npm i xlsx

Now we have access to a plethora of methods and options provided by this awesome package which will be integrated into excel-export.service.ts.

TLDR: Here’s what the service looks like:

// excel-export.service.ts

import { utils as XLSXUtils, writeFile } from 'xlsx';
import { WorkBook, WorkSheet } from 'xlsx/types';

import { Injectable } from '@angular/core';

export interface IExportAsExcelProps {
  readonly data: any[];
  readonly fileName: string;
  readonly sheetName?: string;
  readonly header?: string[];
  readonly table?: HTMLElement;
}

@Injectable({
  providedIn: 'root'
})
export class ExcelExportService {
  fileExtension = '.xlsx';

  public exportAsExcel({
    data,
    fileName,
    sheetName = 'Data',
    header = [],
    table
  }: IExportAsExcelProps): void {
    let wb: WorkBook;

    if (table) {
      wb = XLSXUtils.table_to_book(table);
    } else {
      const ws: WorkSheet = XLSXUtils.json_to_sheet(data, { header });
      wb = XLSXUtils.book_new();
      XLSXUtils.book_append_sheet(wb, ws, sheetName);
    }

    writeFile(wb, `${fileName}${this.fileExtension}`);
  }
}

What’s going on above?

First, official xlsx docs tell you to import everything from xlsx:

import * as XLSX from 'xlsx';

It works, but my personal preference is to import individual methods, interfaces, types, and not pull the whole library along. Hence the adjusted import declarations:

import { utils as XLSXUtils, writeFile } from 'xlsx';
import { WorkBook, WorkSheet } from 'xlsx/types';

We will have only one public method exportAsExcel that takes the following props: data, fileName, sheetName, header, table with the following interface:

export interface IExportAsExcelProps {
  readonly data: any[];
  readonly fileName: string;
  readonly sheetName?: string;
  readonly header?: string[];
  readonly table?: HTMLElement;
}

The data has to be in JSON format to make json_to_sheet util method happy. Read more about it in the docs: json_to_sheet

If you prefer to grab the DOM’s <table> Element and convert its contents into Excel doc, just pass the desired HTMLElement through and our service will use table_to_book method. More info on that in the docs: table_to_book

Well, fileName and sheetName are self-explanatory, se we are left with the last optional prop: header.

This is an array of keys from your data Object that controls the column order. If you don’t explicitly pass it, xlsx defaults to Object.keys. Read more on header: https://github.com/SheetJS/sheetjs#array-of-objects-input

I believe this is all. Just massage the data you want to send down or play with the <table> contents and you have a working Export as Excel service you can call whenever you need it.