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.

You can have a look to this [library](https://github.com/kauegimenes/jsonexport) — Julien METRAL, Apr 16 '19 at 08:50
You can also check this answer: https://stackoverflow.com/questions/51487689/angular-5-how-to-export-data-to-csv-file — Grégory Elhaimer, Apr 16 '19 at 08:51

score 3 · Answer 1 · answered Apr 16 '19 at 08:51

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);
  }
}

score 2 · Answer 2 · answered Apr 16 '19 at 09:11

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

wentjun · Answer 3 · 2019-04-16T09:02:43.293

There is no need to install any external libraries or dependencies. All these could be done with Vanilla JavaScript/TypeScript on your component.ts.

The first line in your CSV file will always be the header.

The subsequent lines will represent each line of data on the CSV line. Therefore, formattedData represents the processing of yourData to its individual lines, and each 'line' is represented by an array.

After which, you use Array.join() to convert the array to the respective string value, separated by a comma.

this.yourData = [{"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"}];

exportData() {
  let currentRow = [];
  const csvHeader = ['id', 'context', 'username', 'custName', 'name'];

  const formattedData = this.yourData.map(row => {
    // do the rest here
    return currentRow;
  });
  let csvContent = 'data:text/csv;charset=utf-8,';

  formattedData.unshift(csvHeader);
  formattedData.forEach(rowArray => {
    const row = rowArray.join(',');
    csvContent += row + '\r\n';
  });

  const encodedUri = encodeURI(csvContent);
  window.open(encodedUri);

}

How to Export JSON Response Data in Angular 7

3 Answers3