Skip to main content

Angular

Angular is a JS library for building user interfaces.

This demo tries to cover common Angular data flow ideas and strategies. Angular and TypeScript familiarity is assumed.

SheetJS plays nice with each version of Angular.

Other demos cover general Angular deployments, including:

danger

Angular tooling uses native NodeJS modules. There are a number of issues when trying to run Angular projects with different NodeJS versions. These issues should be directed to the Angular project.

Installation

The "Frameworks" section covers installation with pnpm and other package managers.

The library can be imported directly from JS or TS code with:

import { read, utils, writeFile } from 'xlsx';

Internal State

The various SheetJS APIs work with various data shapes. The preferred state depends on the application.

Array of Objects

Typically, some users will create a spreadsheet with source data that should be loaded into the site. This sheet will have known columns. For example, our presidents sheet has "Name" / "Index" columns:

`pres.xlsx` data

This naturally maps to an array of typed objects, as in the TS example below:

import { read, utils } from 'xlsx';

interface President {
Name: string;
Index: number;
}

const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer();
const wb = read(f);
const data = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);
console.log(data);

data will be an array of objects:

[
{ Name: "Bill Clinton", Index: 42 },
{ Name: "GeorgeW Bush", Index: 43 },
{ Name: "Barack Obama", Index: 44 },
{ Name: "Donald Trump", Index: 45 },
{ Name: "Joseph Biden", Index: 46 }
]

A component will typically loop over the data using *ngFor. The following example generates a TABLE with a row for each President:

src/app/app.component.ts
import { Component } from '@angular/core';
import { read, utils, writeFileXLSX } from 'xlsx';

interface President { Name: string; Index: number };

@Component({
selector: 'app-root',
template: `
<div class="content" role="main"><table>
<thead><th>Name</th><th>Index</th></thead>
<tbody>
<tr *ngFor="let row of rows">
<td>{{row.Name}}</td>
<td>{{row.Index}}</td>
</tr>
</tbody><tfoot>
<button (click)="onSave()">Export XLSX</button>
</tfoot>
</table></div>
`
})
export class AppComponent {
rows: President[] = [ { Name: "SheetJS", Index: 0 }];
ngOnInit(): void { (async() => {
/* Download from https://sheetjs.com/pres.numbers */
const f = await fetch("https://sheetjs.com/pres.numbers");
const ab = await f.arrayBuffer();

/* parse workbook */
const wb = read(ab);

/* update data */
this.rows = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);

})(); }
/* get state data and export to XLSX */
onSave(): void {
const ws = utils.json_to_sheet(this.rows);
const wb = utils.book_new();
utils.book_append_sheet(wb, ws, "Data");
writeFileXLSX(wb, "SheetJSAngularAoO.xlsx");
}
}

HTML

The main disadvantage of the Array of Objects approach is the specific nature of the columns. For more general use, passing around an Array of Arrays works. However, this does not handle merge cells well!

The sheet_to_html function generates HTML that is aware of merges and other worksheet features. The generated HTML does not contain any <script> tags, and should therefore be safe to pass to an innerHTML-bound variable, but the DomSanitizer approach is strongly recommended:

src/app/app.component.ts
import { Component, ElementRef, ViewChild } from '@angular/core';
import { DomSanitizer, SafeHtml } from '@angular/platform-browser';
import { read, utils, writeFileXLSX } from 'xlsx';

@Component({
selector: 'app-root',
template: `<div class="content" role="main" [innerHTML]="html" #tableau></div>
<button (click)="onSave()">Export XLSX</button>`
})
export class AppComponent {
constructor(private sanitizer: DomSanitizer) {}
html: SafeHtml = "";
@ViewChild('tableau') tabeller!: ElementRef<HTMLDivElement>;
ngOnInit(): void { (async() => {
/* Download from https://sheetjs.com/pres.numbers */
const f = await fetch("https://sheetjs.com/pres.numbers");
const ab = await f.arrayBuffer();

/* parse workbook */
const wb = read(ab);

/* update data */
const h = utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
this.html = this.sanitizer.bypassSecurityTrustHtml(h);
})(); }
/* get live table and export to XLSX */
onSave(): void {
const elt = this.tabeller.nativeElement.getElementsByTagName("TABLE")[0];
const wb = utils.table_to_book(elt);
writeFileXLSX(wb, "SheetJSAngularHTML.xlsx");
}
}

Rows and Columns

Some data grids and UI components split worksheet state in two parts: an array of column attribute objects and an array of row objects. The former is used to generate column headings and for indexing into the row objects.

The safest approach is to use an array of arrays for state and to generate column objects that map to A1-Style column headers.

ngx-datatable uses prop as the key and name for the column label:

/* rows are generated with a simple array of arrays */
this.rows = utils.sheet_to_json(worksheet, { header: 1 });

/* column objects are generated based on the worksheet range */
const range = utils.decode_range(ws["!ref"]||"A1");
this.columns = Array.from({ length: range.e.c + 1 }, (_, i) => ({
/* for an array of arrays, the keys are "0", "1", "2", ... */
prop: String(i),
/* column labels: encode_col translates 0 -> "A", 1 -> "B", 2 -> "C", ... */
name: XLSX.utils.encode_col(i)
}));

Older Versions

danger

This demo is included for legacy deployments. There are incompatibilities with different NodeJS and other ecosystem versions. Issues should be raised with Google and the Angular team.

The newest versions of NodeJS will not work with older Angular projects!

caution

The Angular tooling provides no easy way to switch between versions!

This is a known Angular problem

To work around this, SheetJSAngular.zip is a skeleton project designed to play nice with each Angular version.

Strategies

Internal State

This demo uses an array of arrays as the internal state:

export class SheetJSComponent {
data: any[][] = [ [1, 2], [3, 4] ];
// ...
}

Nested ngFor in a template can loop across the rows and cells:

<table class="sjs-table">
<tr *ngFor="let row of data">
<td *ngFor="let val of row">{{val}}</td>
</tr>
</table>

Reading Data

For legacy deployments, the best ingress is a standard HTML INPUT file element:

<input type="file" (change)="onFileChange($event)" multiple="false" />

In the component, the event is a standard file event. Using a FileReader has broad support compared to the modern Blob#arrayBuffer approach:

  onFileChange(evt: any) {
/* wire up file reader */
const target: DataTransfer = <DataTransfer>(evt.target);
if (target.files.length !== 1) throw new Error('Cannot use multiple files');
const reader: FileReader = new FileReader();
reader.onload = (e: any) => {
/* read workbook */
const ab: ArrayBuffer = e.target.result;
const wb: WorkBook = read(ab);

/* grab first sheet */
const wsname: string = wb.SheetNames[0];
const ws: WorkSheet = wb.Sheets[wsname];

/* save data */
this.data = <AOA>(utils.sheet_to_json(ws, {header: 1}));
};
reader.readAsArrayBuffer(target.files[0]);
}

Writing Data

The demo uses an HTML5 button in the template:

<button (click)="export()">Export!</button>

In the component, aoa_to_sheet is used to generate the worksheet:

  export(): void {
/* generate worksheet */
const ws: WorkSheet = utils.aoa_to_sheet(this.data);

/* generate workbook and add the worksheet */
const wb: WorkBook = utils.book_new();
utils.book_append_sheet(wb, ws, 'Sheet1');

/* save to file */
writeFile(wb, "SheetJS.xlsx");
}

SystemJS

The default angular-cli configuration requires no additional configuration.

Some deployments use the SystemJS loader, which does require configuration. The SystemJS demo describe the required settings.

Legacy Demo

0) Download and unzip SheetJSAngular.zip:

curl -LO https://docs.sheetjs.com/angular/SheetJSAngular.zip
unzip SheetJSAngular.zip
cd SheetJSAngular

1) Download the files for the desired Angular version:

curl -o package.json -L https://docs.sheetjs.com/angular/versions/package.json-ng2
curl -o src/polyfills.ts -L https://docs.sheetjs.com/angular/versions/polyfills.ts-ng2

2) install project and dependencies:

npm i
npm i -S https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz

3) start a local server with

npm run serve

The traditional site URL is http://localhost:4200/ . Open the page with a web browser and open the console. In the "Elements" tab, the app-root element will have an ng-version attribute.

4) build the app with

npm run build