How to Export JSON Response Data in Angular 7

Question

I have JSON Response coming from API. I want to export it to excel sheet in CSV format.

JSON Response:

[
  {
    "id": 13,
    "context": "http://tempurl.org",
    "details": [
      {
        "name": "test1"
      },
      {
        "name": "test2"
      }
    ],
    "username": "testuser1",
    "custName": "cap1"
  },
  {
    "id": 14,
    "context": "http://tempurl.org",
    "details": [],
    "username": "testuser2",
    "custName": "cap2"
  }
]

Here is a data of CSV that we need to get once it opens on export button click:

id      context               username        custName      name
13      http://tempurl.org    testuser1       cap1          test1
13      http://tempurl.org    testuser1       cap1          test2
14      http://tempurl.org    testuser2       cap2          

Previously I was using gridApi that was doing the csv download but it only works for a visible table whose data we want to export. But not for JSON as in my case.

There is no proper tutorial for the latest angular 7 that cab provide me the insight on how to do this.

Answer 1

You have to create JSON as you want it in excel. and then install xlsx and file-saver in your app.

npm install xlsx

npm install file-saver

After this use below service this service have two methods one for export JSON to excel and second for table to excel.

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 exportJsonAsExcelFile(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: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }
  public exportTableAsExcelFile(table: HTMLElement, excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.table_to_sheet(table);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }
  private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], { type: EXCEL_TYPE });
    FileSaver.saveAs(data, fileName + new Date().getTime() + EXCEL_EXTENSION);
  }
}

Answer 2

You can use combine your data to csv content and download it use URL.createObjectURL

download() {
    let fileName = 'download.csv';
    let columnNames = ["id", "context", "username", "custName"];
    let header = columnNames.join(',');

    let csv = header;
    csv += '\r\n';

    this.data.map(c => {
      csv += [c["id"], c["context"], c["username"], c["custName"]].join(',');
      csv += '\r\n';
    })

    var blob = new Blob([csv], { type: "text/csv;charset=utf-8;" });

    var link = document.createElement("a");
    if (link.download !== undefined) {
      var url = URL.createObjectURL(blob);
      link.setAttribute("href", url);
      link.setAttribute("download", fileName);
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }
  }

Demo : https://stackblitz.com/edit/angular-download-csv2