angular 7 How to export ngx-datatable content to a excel sheet?

Question

I have a pb with ngx-datatable and export excel. I try to use fileSaver, with angular 7. I implement a button for export and do this on it :

var blob = new Blob([document.getElementById("exportable").innerText], {
  type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
  //type: "application/vnd.ms-excel;charset=utf-8"
});

then saveAs(blob, "Report.xls");

"exportable" is the id of ngx-datatable :

<ngx-datatable id="exportable" #table
               class="mytable"
               [rows]="rows"
               [columns]="columns">

but I obtain a xls file white one column and all in like this :

do you know how to do to obtain a excel file presentation like my ngx-datatable ?

Thanks !

Answer 1

I finally found this solution, that works for me. I post here for users looking for this : my exportService :

const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

@Injectable({
  providedIn: 'root'
})
export class ExportService {

  constructor( private notifService: NotifService) { }

  public exportAsExcelFile(rows: any[], excelFileName: string): void {
    if (rows.length > 0) {
      const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(rows);
      const workbook: XLSX.WorkBook = {Sheets: {'Compte-rendu': worksheet}, SheetNames: ['Compte-rendu']};
      const excelBuffer: any = XLSX.write(workbook, {bookType: 'xlsx', type: 'array'});
      this.saveAsExcelFile(excelBuffer, excelFileName);
    }else{
      this.notifService.message('Aucune ligne à exporter...');
    }
  }
  private saveAsExcelFile(buffer: any, baseFileName: string): void {
    const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});
    FileSaver.saveAs(data, baseFileName + '_' + this.getDateFormat(new Date())  + EXCEL_EXTENSION);
  }

  private getDateFormat(date: Date): string {
    return formatDate(date, 'yyyyMMdd_HHmmss', 'en-US');
  }
}

Bye !

Answer 2

I have a better PRACTICAL solution for this. Also works with ANGULAR 10+

In most of the cases, the data is coming from API.

So, it would be great if we convert the data to CSV directly. Yes, It is possible...

Step 1 : npm install @ctrl/ngx-csv

Step 2 : module.ts

@NgModule({
 declarations: [
  ...
  ...
  ...
 ],
 imports: [
   ...
   CsvModule,
   ...
 ]
})

Step 3 : component.html

<a csvLink [data]="yourDataObject">CSV</a>

Hope you find this solution helpful :)

Answer 3

Here is something that worked really well for me. I have an object that feeds data to the Ngx-Datatable called 'temp', and reference that so any items currently showing in the datatable (if filter applied or not) is then exported to excel.

Install XLSX Package npm i xlsx --save

Component.ts

  exportexcel(): void
  {
    /* pass here the data source */
    
    const ws: XLSX.WorkSheet =XLSX.utils.json_to_sheet(this.temp);
 
    /* generate workbook and add the worksheet */
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
 
    /* save to file */  
    XLSX.writeFile(wb, 'FileName.xlsx');
 
  }

    <button (click)="exportexcel()">Export to Excel</button>