javascript - How to Export JSON to CSV or Excel - Angular 2

Question

Say my json is like this:

var readyToExport = [
   {id: 1, name: 'a'},
   {id: 2, name: 'b'},
   {id: 3, name: 'c'}
];

How can I export this JSON into CSV or Excel file in Angular2?

The browser that I'm using is Chrome.

Maybe Angular2 is not relevant, however, is there any third party plugin that can be injected in Angular2 and perform this task?

Answer 1

I implemented excel export using these two libraries: file-server and xlsx.

You can add it to your existing project with:

npm install file-saver --save
npm install xlsx --save

ExcelService example:

import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

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

@Injectable()
export class ExcelService {

  constructor() { }

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

  private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
  }

}

You can find working example on my github: https://github.com/luwojtaszek/ngx-excel-export

[Styling the cells]

If you want to style the cells (f.e. add text wrapping, centering cell content, etc.) you can do this using xlsx and xlsx-style libraries.

1) Add required dependencies

npm install file-saver --save
npm install xlsx --save
npm install xlsx-style --save

2) Replace cpexcel.js file in xlsx-style dist directory.

Because of this bug: https://github.com/protobi/js-xlsx/issues/78 it's required to replace xlsx-style/dist/cpexcel.js with xlsx/dist/cpexcel.js in node_modules directory.

3) Implement ExcelService

import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';
import * as XLSXStyle from 'xlsx-style';

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

@Injectable()
export class ExcelService {

  constructor() { }

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    this.wrapAndCenterCell(worksheet.B2);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    // Use XLSXStyle instead of XLSX write function which property writes cell styles.
    const excelBuffer: any = XLSXStyle.write(workbook, { bookType: 'xlsx', type: 'buffer' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

  private wrapAndCenterCell(cell: XLSX.CellObject) {
    const wrapAndCenterCellStyle = { alignment: { wrapText: true, vertical: 'center', horizontal: 'center' } };
    this.setCellStyle(cell, wrapAndCenterCellStyle);
  }

  private setCellStyle(cell: XLSX.CellObject, style: {}) {
    cell.s = style;
  }

  private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
  }

}

Working example: https://github.com/luwojtaszek/ngx-excel-export/tree/xlsx-style

[UPDATE - 23.08.2018]

This works fine with the newest Angular 6.

yarn install xlsx --save

ExcelService example:

import {Injectable} from '@angular/core';
import * as XLSX from 'xlsx';

@Injectable()
export class ExcelService {

  constructor() {
  }

  static toExportFileName(excelFileName: string): string {
    return `${excelFileName}_export_${new Date().getTime()}.xlsx`;
  }

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    const workbook: XLSX.WorkBook = {Sheets: {'data': worksheet}, SheetNames: ['data']};
    XLSX.writeFile(workbook, ExcelService.toExportFileName(excelFileName));
  }
}

I updated my repo: https://github.com/luwojtaszek/ngx-excel-export