angular material data table export to excel

Question

I am using angular material data table to display data in a tabular format. I need to include a functionality which exports tabular data to excel. I am not able to find any documents which will help me to export the data. Can you please let me know how to export data to excel in angular which uses angular material data table.

I tried using XLSX.utils and facing "Bad range (0): A1:A0 at check_ws" issue.

Location.component.html

<div class="example-container" #TABLE> 

  <mat-table #table [dataSource]="dataSource" matSort matSortActive="locationName" matSortDirection="asc" matSortDisableClear>
    <ng-container matColumnDef="locationName">
      <mat-header-cell *matHeaderCellDef mat-sort-header>Location Name </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.locationName}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="address">
      <mat-header-cell *matHeaderCellDef>Address </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.address}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="city">
      <mat-header-cell *matHeaderCellDef mat-sort-header> City </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.city}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="country">
      <mat-header-cell *matHeaderCellDef mat-sort-header>Country </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.country}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="zipcode">
      <mat-header-cell *matHeaderCellDef>ZipCode </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.zipcode}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="phone">
      <mat-header-cell *matHeaderCellDef>Phone </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.phone}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="timezone">
      <mat-header-cell *matHeaderCellDef> TimeZone </mat-header-cell>
      <mat-cell *matCellDef="let location"> {{location.timezone}} </mat-cell>
    </ng-container>
    <ng-container matColumnDef="action">
      <mat-header-cell *matHeaderCellDef> Action </mat-header-cell>
      <!-- <mat-cell *matCellDef="let location"> {{location.timezone}} </mat-cell> -->
      <mat-cell *matCellDef="let location">
      <a href ="#" class="btn Action-Tab" >Edit</a>&nbsp;&nbsp;
          <a href ="#" class="btn Action-Tab" >Delete</a>
        </mat-cell>
    </ng-container>
    <mat-header-row *matHeaderRowDef="displayedColumns"></mat-header-row>
    <mat-row *matRowDef="let row; columns: displayedColumns;">
    </mat-row>
  </mat-table>

  <mat-paginator [pageSizeOptions]="[10, 20, 50,100]"></mat-paginator>

</div>
<button mat-raised-button color="primary" (click)="ExportTOExcel()">Export as Excel</button>

Location.component.ts

import { Component, OnInit, OnDestroy , ViewChild,ElementRef} from '@angular/core';
import { ILocation } from '../../Ilocation';
import { LocationService } from '../../services/location.service';
import { DataTableResource } from 'angular5-data-table';
import { Subscription } from 'rxjs';
import {MatPaginator, MatSort, MatTableDataSource} from '@angular/material';
import {DataSource} from '@angular/cdk/table';
import * as XLSX from 'xlsx';
// import { CdkTableModule } from '@angular/cdk/table';

@Component({
  selector: 'app-location',
  templateUrl: './location.component.html',
  styleUrls: ['./location.component.css']
})
export class LocationComponent implements OnInit , OnDestroy{
  errorMessage: string;
  filterBy : string;
  locations: ILocation[];
  items : ILocation[]=[];
  itemCount :number = 0;
  subscription:Subscription;
  limits = [5, 10, 20, 80];
  tableResource : DataTableResource<ILocation>;
  displayedColumns = ['locationName', 'address', 'city', 'country','zipcode', 'phone','timezone','action'];
  // dataSource: MatTableDataSource<ILocation>;
  dataSource;
  @ViewChild(MatPaginator) paginator: MatPaginator;
  @ViewChild(MatSort) sort : MatSort;
  @ViewChild('TABLE',{ read: ElementRef }) table: ElementRef;

  constructor( private locationService: LocationService) {

   }

   applyFilter(filterValue: string) {
    filterValue = filterValue.trim(); // Remove whitespace
    filterValue = filterValue.toLowerCase(); // Datasource defaults to lowercase matches
    this.dataSource.filter = filterValue;
  }

  ngOnInit() {
    this.subscription = this.locationService.getLocations()
    .subscribe(locations =>{
       this.locations = locations;
       this.dataSource = new MatTableDataSource(locations);
       this.dataSource.sort = this.sort;
       this.dataSource.paginator = this.paginator;
       this.dataSource.table = this.table;
      },
      error => this.errorMessage = <any>error);           
  }

  ngOnDestroy(){
    this.subscription.unsubscribe();

  }

  ExportTOExcel()
  {
    console.log("export");
    this.table.nativeElement.style.background = "red";
    const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement);
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

    /* save to file */
    XLSX.writeFile(wb,'SheetJS.xlsx');
    console.log("exported");

  }

}

Answer 1

You can use xlsx for exporting table as excel.
usage
Execute npm i xlsx

HTML:

<div class="example-container mat-elevation-z8 " #TABLE>
  <table mat-table #table [dataSource]="dataSource">

    <!--- Note that these columns can be defined in any order.
          The actual rendered columns are set as a property on the row definition" -->

    <!-- Position Column -->
    <ng-container matColumnDef="position">
      <th mat-header-cell *matHeaderCellDef> No. </th>
      <td mat-cell *matCellDef="let element"> {{element.position}} </td>
      //..................................rest of the html
    <button mat-raised-button color="primary" (click)="exportAsExcel()">Export as Excel</button></div>

In your Component

import {Component,ViewChild, ElementRef} from '@angular/core';
 import * as XLSX from 'xlsx';
//......
    export class AppComponent  {
      @ViewChild('TABLE') table: ElementRef;
    exportAsExcel()
    {
      const ws: XLSX.WorkSheet=XLSX.utils.table_to_sheet(this.table.nativeElement);//converts a DOM TABLE element to a worksheet
      const wb: XLSX.WorkBook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

      /* save to file */
      XLSX.writeFile(wb, 'SheetJS.xlsx');

    }
    }

DEMO

Answer 2

It is a late reply but, you can use mat-table-exporter which utilizes xlsx sheetjs and provides paginated table export as excel, csv, json and txt formats.