32

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?

Francesco Borzi
  • 37,945
  • 31
  • 126
  • 195
Vicheanak
  • 5,746
  • 15
  • 57
  • 95
  • There are several ways to export to CSV or Excel; the fact that you're using Angular is probably irrelevant. More important is going to be what browser you're using, as client-side creation of files is not very uniform across browsers. – S McCrohan Aug 27 '16 at 03:36
  • I'm using Chrome browser, you any third party plugin that I can use to do such task? – Vicheanak Aug 27 '16 at 03:40
  • You can using plugin ngCsv - Export to CSV using AngularJS. Link here: http://ngmodules.org/modules/ng-csv – Tai Nguyen Aug 27 '16 at 03:48
  • @TaiNguyen ng-csv required ngSanitize which is not available in angular2. – Vicheanak Aug 27 '16 at 04:33
  • @Vicheanak You can using nodejs convert json to csv. Then angularjs2 call http post josn and get file scv. I think so – Tai Nguyen Aug 27 '16 at 07:06

8 Answers8

55

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

luwojtaszek
  • 875
  • 1
  • 8
  • 8
  • did you make it run on your project? – Joshua Fabillar Aug 01 '17 at 07:57
  • Hi, Is it possible of auto wrapping of columns. – VSK Aug 23 '17 at 13:25
  • You can do this using xlsx and xlsx-style libraries. I updated my post with the instruction. You can also find working example on my GitHub. – luwojtaszek Aug 28 '17 at 13:28
  • Works beautifully. Thank you! – zisha Nov 17 '17 at 18:56
  • how to center and wrap all cells of xls file (not only B2). is there any documentation we can read? – Noah13 Jan 01 '18 at 02:17
  • 4
    @luwojtaszek, I tried creating an excel using above method , It is creating an excel file but that I cannot open it says file corrupted. – Piyush Kumar Mar 08 '18 at 12:17
  • @luwojtaszek I am also facing the same error as piyush. File created but corrupted. – Aakash Thakur Mar 13 '18 at 09:32
  • I did the same way and put data in place of json:Any but my file is crashing – Anuj Apr 04 '18 at 12:28
  • I can create and download the file, but I cannot open it, because of a problem regarding the content of the file. If I click 'Yes' in the dialog box (which asks me to confirm if I want to recover the file), the file is opening, but it is empty. – decebal Apr 04 '18 at 13:18
  • I did the same way and put data in place of json:Any but my file is crashing.Got solution with some modification . I need to add some more data on first part of excel . How can i do that ? – Anuj Apr 05 '18 at 05:49
  • @Anuj - Can you please tell us what modifications did you do in order to make it work? – decebal Apr 05 '18 at 06:50
  • 3
    modify line as const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' }); – Anuj Apr 05 '18 at 06:57
  • @Anuj - Thank you! – decebal Apr 05 '18 at 07:34
  • Thanks @decebal .Can anyone help me for my Requirement ? – Anuj Apr 05 '18 at 12:20
  • @Anuj - I guess you need to alter the JSON in order to add some more data on first part of the excel. If so, please see this post: https://stackoverflow.com/questions/19457337/how-to-add-a-property-at-the-beginning-of-an-object-in-javascript – decebal Apr 05 '18 at 13:31
  • Thanks for the hind . I tried to add second json with first one and use only value not key but it is adding header 0,1,2... and so on with 1-1 letter in each excel box – Anuj Apr 06 '18 at 07:23
  • @Anuj - Do you have an example for this? Can we have a look at the code? – decebal Apr 10 '18 at 13:21
  • I want output like this https://user-images.githubusercontent.com/26056096/38581064-512f215e-3d29-11e8-99a7-9a376ecdf8ed.png . Please check once @decebal – Anuj Apr 11 '18 at 07:32
  • @Anuj - Please post the two JSON/objects, or create a plunker or something like that. – decebal Apr 12 '18 at 08:07
  • I am sending the array of objects data to exportAsExcelFile function, file exports but data is corrupted. Can you share data formation? – Muhammad Zeshan Ghafoor Nov 29 '18 at 07:34
  • @uwojtaszek are there ways to format cells? I found out that resetting a cell like this: worksheet.C2.t = 'd'; will format it as a date. But I am struggling with formatting numbers (thousand separator, number of decimal etc) – azakgaim Feb 20 '19 at 19:46
  • Is there a way to export only certain columns using this approach? – ljs Apr 16 '19 at 23:40
16

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

debugger
  • 307
  • 2
  • 8
14

The fact that you are using Angular isn't all that important, though it does open up for some more libs.

You basically have two options.

  1. Write your own json2csv converter, which isn't all that hard. You already have the JSON, which you can turn to JS objects, and then just iterate over every object and get the correct field for the current column.
  2. You can use a lib like https://github.com/zemirco/json2csv which does it for you.

Also, this SO question probably answers your question How to convert JSON to CSV format and store in a variable

CSV is the basic format for Excel-like programs. Don't go messing with xls(x) unless you really have to. It will make your brain hurt.

Community
  • 1
  • 1
qwelyt
  • 726
  • 9
  • 21
  • You can export to XLS using a back-end that generates the XLS for you. eg. For PHP there is PHPExcel (Won't hurt your brain so much as trying to do it directly from the browser) – johan May 16 '17 at 11:59
3

This is the right way i think... worked for me! took a json array

downloadFile(data: any, filename:string) {
    const replacer = (key, value) => value === null ? '' : value;
    const header = Object.keys(data[0]);
    let csv = data.map(row => header.map(fieldName => JSON.stringify(row[fieldName],replacer)).join(','));
    csv.unshift(header.join(','));
    let csvArray = csv.join('\r\n');
    var blob = new Blob([csvArray], {type: 'text/csv' })
    saveAs(blob, filename + ".csv");
}
rlandster
  • 6,310
  • 12
  • 50
  • 76
Subham Debnath
  • 51
  • 5
3

You can export from JSON to CSV using this simple code. This code solve the many basic issues like, problems with the separator, custom heading, skip empty column and add - in place of the empty data for a particular column. Refer this github link to solve all the issue regarding CSV export in Angular.

https://github.com/marvin-aroza/Angular-csv-export

Consider this as you JSON data

jsonData : any = [{
    name : 'Berlin',
    age : '45',
    country : 'Spain',
    phone : '896514326'
  },
  {
    name : 'Professor',
    age : '42',
    country : 'spain'
  },
  {
    name : 'Tokyo',
    age : '35',
    phone : '854668244'
  },
  {
    name : 'Helsinki',
    phone : '35863297'
  }];

You can download you csv using these functions

exportCsv() {
    this.downloadFile(this.jsonData);
  }

  downloadFile(data, filename = 'data') {
    let arrHeader = ["name", "age", "country", "phone"];
    let csvData = this.ConvertToCSV(data, arrHeader);
    console.log(csvData)
    let blob = new Blob(['\ufeff' + csvData], { type: 'text/csv;charset=utf-8;' });
    let dwldLink = document.createElement("a");
    let url = URL.createObjectURL(blob);
    let isSafariBrowser = navigator.userAgent.indexOf('Safari') != -1 && navigator.userAgent.indexOf('Chrome') == -1;
    if (isSafariBrowser) {  //if Safari open in new window to save file with random filename.
      dwldLink.setAttribute("target", "_blank");
    }
    dwldLink.setAttribute("href", url);
    dwldLink.setAttribute("download", "sample.csv");
    dwldLink.style.visibility = "hidden";
    document.body.appendChild(dwldLink);
    dwldLink.click();
    document.body.removeChild(dwldLink);
  }

And to edit the format of the CSV you can add this function

ConvertToCSV(objArray, headerList) {
    console.log(objArray);
    console.log(headerList);
    let array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
    let str = '';
    let row = 'S.No,';

    let newHeaders = ["Name", "Age", "Country", "Phone"];

    for (let index in newHeaders) {
      row += newHeaders[index] + ',';
    }
    row = row.slice(0, -1);
    str += row + '\r\n';
    for (let i = 0; i < array.length; i++) {
      let line = (i + 1) + '';
      for (let index in headerList) {
        let head = headerList[index];

        line += ',' + this.strRep(array[i][head]);
      }
      str += line + '\r\n';
    }
    return str;
  }

In case of values with comma, You can use this function to remove the comma and consider it as one single value

strRep(data) {
    if(typeof data == "string") {
      let newData = data.replace(/,/g, " ");
       return newData;
    }
    else if(typeof data == "undefined") {
      return "-";
    }
    else if(typeof data == "number") {
      return  data.toString();
    }
    else {
      return data;
    }
  }
Marvin Delroy Aroza
  • 61
  • 3
2

Use the XLSX library to convert JSON into XLS file and Download

Working Demo

Source link

Method

Include library

<script type="text/javascript" src="//unpkg.com/xlsx/dist/xlsx.full.min.js"></script>

JavaScript Code

    var createXLSLFormatObj = [];

    /* XLS Head Columns */
    var xlsHeader = ["EmployeeID", "Full Name"];

    /* XLS Rows Data */
    var xlsRows = [{
            "EmployeeID": "EMP001",
            "FullName": "Jolly"
        },
        {
            "EmployeeID": "EMP002",
            "FullName": "Macias"
        },
        {
            "EmployeeID": "EMP003",
            "FullName": "Lucian"
        },
        {
            "EmployeeID": "EMP004",
            "FullName": "Blaze"
        },
        {
            "EmployeeID": "EMP005",
            "FullName": "Blossom"
        },
        {
            "EmployeeID": "EMP006",
            "FullName": "Kerry"
        },
        {
            "EmployeeID": "EMP007",
            "FullName": "Adele"
        },
        {
            "EmployeeID": "EMP008",
            "FullName": "Freaky"
        },
        {
            "EmployeeID": "EMP009",
            "FullName": "Brooke"
        },
        {
            "EmployeeID": "EMP010",
            "FullName": "FreakyJolly.Com"
        }
    ];


    createXLSLFormatObj.push(xlsHeader);
    $.each(xlsRows, function(index, value) {
        var innerRowData = [];
        $("tbody").append('<tr><td>' + value.EmployeeID + '</td><td>' + value.FullName + '</td></tr>');
        $.each(value, function(ind, val) {

            innerRowData.push(val);
        });
        createXLSLFormatObj.push(innerRowData);
    });


    /* File Name */
    var filename = "FreakyJSON_To_XLS.xlsx";

    /* Sheet Name */
    var ws_name = "FreakySheet";

    if (typeof console !== 'undefined') console.log(new Date());
    var wb = XLSX.utils.book_new(),
        ws = XLSX.utils.aoa_to_sheet(createXLSLFormatObj);

    /* Add worksheet to workbook */
    XLSX.utils.book_append_sheet(wb, ws, ws_name);

    /* Write workbook and Download */
    if (typeof console !== 'undefined') console.log(new Date());
    XLSX.writeFile(wb, filename);
    if (typeof console !== 'undefined') console.log(new Date());

You can refer this code to use in ANgular 2 Component

Code Spy
  • 7,520
  • 3
  • 57
  • 39
0

You can export your JSON to CSV format using primeng based on angular2, apart from CSV format there are too many optoin availabel to apply on JSON,

for converting your JSON into CSV format see here

Updated link https://www.primefaces.org/primeng/#/datatable/export

Community
  • 1
  • 1
Pardeep Jain
  • 71,130
  • 29
  • 141
  • 199
  • Primeng DataTable Export doesn't export template columns and is reason I'm looking for another solution. Also can't sort on template columns either. – Fab Oct 10 '17 at 13:09
0

I used the angular2-csv library for this: https://www.npmjs.com/package/angular2-csv

This worked very well for me with one exception. There is a known issue (https://github.com/javiertelioz/angular2-csv/issues/10) with the BOM character getting inserted at the start of the file which causes a garbage character to display with my version of Excel.

John Langford
  • 881
  • 2
  • 10
  • 13