While working on a web application, there are various instances where we would allow our users to download the data into their specific formats. One such requirement is to allow them to export the data in a spreadsheet (excel) or a CSV file.
This is a very common use case and hence I thought to create a step by step guide to accomplish this easily. We would be discussing the export into 2 main areas:
đ NOTE
I have created a repository on GitHub on this implementation
idris-rampurawala / ng-data-export
Demonstration of an export service that exports data to excel, csv in Angular 10
Export to Excel
The ability to export the data to excel not only gives a powerful feature for the users but also the ability to create an array of other related features to help our users better understand the data. So how do we start? Well, as you'd expect, we have an npm package to deal with it - xlsx (also known as sheetjs) đ
Installing dependencies
# installing xlsx package
$ npm install xlsx
# installing file-saver - a solution to saving files on the client-side
$ npm install file-saver
Creating an export service
One way of creating common functionalities in Angular is by creating a service for it. Hence, we create an export service which will have functions to export all types of information (excel and CSV for this post).
Using xlsx
xlsx
provides a very rich set of utilities to create or parse spreadsheets. For the sake of simplicity, we will concentrate on a couple of the utilities here.
1ïžâŁ Exporting an HTML table
If we want to export an HTML table
to excel, then it is pretty easy as xlsx
provides utility for it. Consider if we have a table đ
<!-- app.component.html -->
<table class="table table-sm" #userTable> <!-- we will make use of this angular var as element reference -->
<thead class="thead-dark">
<tr>
<th scope="col">#</th>
...
</tr>
</thead>
<tbody>
<tr *ngFor="let user of users">
<td scope="row">{{ user.id }}</td>
...
</tr>
<tr>
</tbody>
</table>
Now, we can just create a service
function to take this HTML element reference
and generate the excel from it (using <thead>
and <tbody>
).
/* export.service.ts */
import { Injectable, ElementRef } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';
const EXCEL_EXTENSION = '.xlsx';
@Injectable()
export class ExportService {
constructor() { }
/**
* Creates excel from the table element reference.
*
* @param element DOM table element reference.
* @param fileName filename to save as.
*/
public exportTableElmToExcel(element: ElementRef, fileName: string): void {
const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(element.nativeElement);
// generate workbook and add the worksheet
const workbook: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, ws, 'Sheet1');
// save to file
XLSX.writeFile(workbook, `${fileName}${EXCEL_EXTENSION}`);
}
...
}
And in component.ts
we just create a handler for the export button
to make an attempt to save the file as excel on the client machine.
/* app.component.ts */
import { Component, OnInit, ViewChild, ElementRef } from '@angular/core';
import { ExcelJson } from './interfaces/excel-json.interface';
import { ExportService } from './services/export.service';
...
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.scss']
})
export class AppComponent implements OnInit {
...
/* the table reference */
@ViewChild('userTable') userTable: ElementRef;
...
constructor(
private exportService: ExportService
) { }
ngOnInit(): void {
...
}
/**
* Function prepares data to pass to export service to create excel from Table DOM reference
*
*/
exportElmToExcel(): void {
this.exportService.exportTableElmToExcel(this.userTable, 'user_data');
}
...
}
That was pretty easy, isn't it? đ What if we want to export more complex data? đ Let's find out đ
2ïžâŁ Exporting more complex data
xlsx
provides various other utilities to customize the data in excel (using excel column name identifies A, B, C..
). For example, I had created a function to export the whole dashboard data to excel in one of my projects. Let's create a function in service
for the same.
/* export.service.ts */
...
/**
* Creates XLSX option from the Json data. Use this to customize the sheet by adding arbitrary rows and columns.
*
* @param json Json data to create xlsx.
* @param fileName filename to save as.
*/
public exportJsonToExcel(json: ExcelJson[], fileName: string): void {
// inserting first blank row
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(
json[0].data,
this.getOptions(json[0])
);
for (let i = 1, length = json.length; i < length; i++) {
// adding a dummy row for separation
XLSX.utils.sheet_add_json(
worksheet,
[{}],
this.getOptions(
{
data: [],
skipHeader: true
}, -1)
);
XLSX.utils.sheet_add_json(
worksheet,
json[i].data,
this.getOptions(json[i], -1)
);
}
const workbook: XLSX.WorkBook = { Sheets: { Sheet1: worksheet }, SheetNames: ['Sheet1'] };
// save to file
XLSX.writeFile(workbook, `${fileName}${EXCEL_EXTENSION}`);
}
/**
* Creates the XLSX option from the data.
*
* @param json Json data to create xlsx.
* @param origin XLSX option origin.
* @returns options XLSX options.
*/
private getOptions(json: ExcelJson, origin?: number): any {
// adding actual data
const options = {
skipHeader: true,
origin: -1,
header: []
};
options.skipHeader = json.skipHeader ? json.skipHeader : false;
if (!options.skipHeader && json.header && json.header.length) {
options.header = json.header;
}
if (origin) {
options.origin = origin ? origin : -1;
}
return options;
}
...
And in component.ts
, we create the data in xlsx
required format to pass to this service function
/* app.component.ts */
...
/**
* Function prepares data to pass to export service to create excel from Json
*
*/
exportToExcel(): void {
const edata: Array<ExcelJson> = [];
const udt: ExcelJson = {
data: [
{ A: 'User Data' }, // title
{ A: '#', B: 'First Name', C: 'Last Name', D: 'Handle' }, // table header
],
skipHeader: true
};
this.users.forEach(user => {
udt.data.push({
A: user.id,
B: user.firstName,
C: user.lastName,
D: user.handle
});
});
edata.push(udt);
// adding more data just to show "how we can keep on adding more data"
const bd = {
data: [
// chart title
{ A: 'Some more data', B: '' },
{ A: '#', B: 'First Name', C: 'Last Name', D: 'Handle' }, // table header
],
skipHeader: true
};
this.users.forEach(user => {
bd.data.push({
A: String(user.id),
B: user.firstName,
C: user.lastName,
D: user.handle
});
});
edata.push(bd);
this.exportService.exportJsonToExcel(edata, 'user_data_customized');
}
...
Explanation
Confused? đ Let me explain what we just did there.
-
xlsx
(or spreadsheets) have aworkbook
(that's an actual file) and in that, we can have multiplesheets
added. -
xlsx
provides a utility functionsheet_add_json()
to convert an array of objects to excel data with additional xlsx options. Hence, we just created a wrapper around it ourservice
by which we can pass multiple objects with different xlsx options. This way our export service handles the complexity and we are only required to create an array of objects to pass to it. -
xlsx
expects the array of objects in the form of{cell: value }
and hence{A: 'value'}
means that we want to put thisvalue
in the cell (column)A
of the excel. -
skipHeader
is to skip the auto-generated header from the objects being passed to the functionsheet_add_json()
-
origin: -1
is to append data to the bottom of worksheet starting on the first column - Also,
ExcelJson
is a custom interface (that I have created) to define the type of data that service function expects. It represents a valid object data forxlsx
.
For more information, please read the xlsx documentation and a sample implementation on github.
How do I style excel? đ§
xlsx
does not provide styling in its open-source version. You can opt for a pro version
for styling and dedicated support.
Alternatively, xlsx-style is a fork of xlsx
that provides styling on top of it.
One more a very popular alternative to xlsx
is ExcelJS. It has got styling included as well but provides lesser utilities in comparison to xlsx
.
Export to CSV
Now let's move on to the second part of export i.e. CSV.
Don't worry đ it's pretty easy. We just need to add a function to our export service
which accepts an array of objects along with a column header to create a CSV for it.
/* export.service.ts */
...
/**
* Saves the file on the client's machine via FileSaver library.
*
* @param buffer The data that need to be saved.
* @param fileName File name to save as.
* @param fileType File type to save as.
*/
private saveAsFile(buffer: any, fileName: string, fileType: string): void {
const data: Blob = new Blob([buffer], { type: fileType });
FileSaver.saveAs(data, fileName);
}
/**
* Creates an array of data to CSV. It will automatically generate a title row based on object keys.
*
* @param rows array of data to be converted to CSV.
* @param fileName filename to save as.
* @param columns array of object properties to convert to CSV. If skipped, then all object properties will be used for CSV.
*/
public exportToCsv(rows: object[], fileName: string, columns?: string[]): string {
if (!rows || !rows.length) {
return;
}
const separator = ',';
const keys = Object.keys(rows[0]).filter(k => {
if (columns?.length) {
return columns.includes(k);
} else {
return true;
}
});
const csvContent =
keys.join(separator) +
'\n' +
rows.map(row => {
return keys.map(k => {
let cell = row[k] === null || row[k] === undefined ? '' : row[k];
cell = cell instanceof Date
? cell.toLocaleString()
: cell.toString().replace(/"/g, '""');
if (cell.search(/("|,|\n)/g) >= 0) {
cell = `"${cell}"`;
}
return cell;
}).join(separator);
}).join('\n');
this.saveAsFile(csvContent, `${fileName}${CSV_EXTENSION}`, CSV_TYPE);
}
...
The code is pretty much self-explanatory đ€ where we check if any of the column's data present in the data passed, and generates a CSV from it. We can always change the delimiter from ,
to any other based on our requirement. file-saver package is required to save the file on the client's machine.
Well, that was pretty simple, isn't it? đ You check out my GitHub repo for a complete implementation of this post.
If you find this helpful or have any suggestions, feel free to comment. Also, do not forget to hit â€ïž or đŠ if you like my post.
See ya! until my next post đ
Top comments (13)
Thanks for the CSV code... You can also use the following code instead of depending on the FileSaver lib:
const blob = new Blob([csvContent], { type: 'text/csv'});
const a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = fileName;
a.click();
:) Thanks
can you share how to export html table to
copy
,pdf
,pagination
feature?How to change the sheetname in download CSV file?
Hey, you can find the same in the
export.service.ts
This one for export excel right? not for export CSV? Please check and let me sample example where I need to add this code in the following code if it is correct...
private saveAsFile(buffer: any, fileName: string, fileType: string): void {
const data: Blob = new Blob([buffer], { type: fileType });
FileSaver.saveAs(data, fileName);
}
/**
"${cell}"
; } return cell; }).join(separator); }).join('\n'); this.saveAsFile(csvContent,${fileName}${CSV_EXTENSION}
, CSV_TYPE); } }I want to give different name for file and tab in CSV.
You can pass the filename as mentioned in the function's
exportToCsv
dosctring. Also, CSV's won't have sheets in it ;)Thanks, you save my day
Hey, so how do I then make this excel sheet downloadable on the client's end? Basically, I want them to be able to click a button and have this excel sheet downloaded.
Hey, you can check out my GitHub repo of this implementation to accomplish your use case âșïž
how to protected the sheet in angular excel export
i'm using angular 13, this type error occurs. Any idea to how resolve? :/
what if when exporting large sets of data, it doesnt export everything just the header + 100 rows