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?

Tibbitts

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. – Drying

I'm using Chrome browser, you any third party plugin that I can use to do such task? – Tibbitts

You can using plugin ngCsv - Export to CSV using AngularJS. Link here: ngmodules.org/modules/ng-csv – Trevortrevorr

@TaiNguyen ng-csv required ngSanitize which is not available in angular2. – Tibbitts

@Tibbitts You can using nodejs convert json to csv. Then angularjs2 call http post josn and get file scv. I think so – Trevortrevorr

Answer 1

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.

Terrenceterrene

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) – Broder

Answer 2

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

Botha

did you make it run on your project? – Litotes

Hi, Is it possible of auto wrapping of columns. – Scurf

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. – Botha

Works beautifully. Thank you! – Khano

how to center and wrap all cells of xls file (not only B2). is there any documentation we can read? – Fulmar

@luwojtaszek, I tried creating an excel using above method , It is creating an excel file but that I cannot open it says file corrupted. – Maladapted

@Botha I am also facing the same error as piyush. File created but corrupted. – Frydman

I did the same way and put data in place of json:Any but my file is crashing – Pollak

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. – Abandon

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 ? – Pollak

@Pollak - Can you please tell us what modifications did you do in order to make it work? – Abandon

modify line as const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' }); – Pollak

Thanks @Abandon .Can anyone help me for my Requirement ? – Pollak

@Pollak - 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: #19457837 – Abandon

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 – Pollak

@Pollak - Do you have an example for this? Can we have a look at the code? – Abandon

I want output like this user-images.githubusercontent.com/26056096/… . Please check once @Abandon – Pollak

@Pollak - Please post the two JSON/objects, or create a plunker or something like that. – Abandon

I am sending the array of objects data to exportAsExcelFile function, file exports but data is corrupted. Can you share data formation? – Sternmost

@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) – Actinomycosis

Is there a way to export only certain columns using this approach? – Errant

how can we make headers bold? – Shaynashayne

Just a word of caution when using XLSX: it is a massive library that cannot be tree-shaken. So if you want to include it, you need to weigh if it is worth incurring 20% or more of your final bundle size. – Manganate

Answer 3

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

Dulci

Thanks Man you saved my life. – Sneakbox

How to style cells? – Sternmost

@MohammadZeshan, basically there is a reference about formatting in an official package description. I would suggest creating a new question, because this is out of the main topic. – Dulci

Just a word of caution when using XLSX: it is a massive library that cannot be tree-shaken. So if you want to include it, you need to weigh if it is worth incurring 20% or more of your final bundle size. – Manganate

Answer 4

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

Monatomic

Answer 5

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

Sihon

Answer 6

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

Infantine

@RajRajeshwarSinghRathore as checked it's working fine for me plz try again and click on the button "Download XLS File" – Infantine

Yes, it's working now. Maybe some issue at that time. Thanks – Narcisanarcissism

if you already have the json in the right format, just use json_to_sheet instead of aoa_to_sheet – Benzoate

Just a word of caution when using XLSX: it is a massive library that cannot be tree-shaken. So if you want to include it, you need to weigh if it is worth incurring 20% or more of your final bundle size. – Manganate

Answer 7

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

• http://www.primefaces.org/primeng/#/datatableexport

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

Famed

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. – Metrology

Answer 8

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.

Willner