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.
Top comments (1)
Is it possible to export dropdown with the options in excel?