Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Export JSON to CSV or Excel - Angular 2

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?

like image 418
Vicheanak Avatar asked Aug 27 '16 03:08

Vicheanak


People also ask

How do I download a CSV file in angular 6?

The ->download() function sets headers so that the file will be automatically downloaded. When you fetch this data with an AJAX call (which is what HttpClient does) you simply get the binary data returned (which is what you see in your Response tab in Chrome developer tools). Show activity on this post.


Video Answer


2 Answers

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

like image 133
luwojtaszek Avatar answered Sep 29 '22 17:09

luwojtaszek


You can use XLSX library for Angular2+.

Following the guide provided there:

public export() {
    const readyToExport = [
      {id: 1, name: 'a'},
      {id: 2, name: 'b'},
      {id: 3, name: 'c'}
    ];

    const workBook = XLSX.utils.book_new(); // create a new blank book
    const workSheet = XLSX.utils.json_to_sheet(readyToExport);

    XLSX.utils.book_append_sheet(workBook, workSheet, 'data'); // add the worksheet to the book
    XLSX.writeFile(workBook, 'temp.xlsx'); // initiate a file download in browser
  }

Tested with Angular 5.2.0 and XLSX 0.13.1

like image 39
debugger Avatar answered Sep 29 '22 16:09

debugger