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?

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
– S McCrohan, Commented Aug 27, 2016 at 3:36
I'm using Chrome browser, you any third party plugin that I can use to do such task? — Vicheanak
– Vicheanak, Commented Aug 27, 2016 at 3:40
You can using plugin ngCsv - Export to CSV using AngularJS. Link here: ngmodules.org/modules/ng-csv — Tai Nguyen
– Tai Nguyen, Commented Aug 27, 2016 at 3:48
@TaiNguyen ng-csv required ngSanitize which is not available in angular2. — Vicheanak
– Vicheanak, Commented Aug 27, 2016 at 4: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
– Tai Nguyen, Commented Aug 27, 2016 at 7:06

luwojtaszek · Accepted Answer · 2018-08-23 20:24:01Z

59

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

edited Aug 23, 2018 at 20:24

answered Jun 16, 2017 at 18:18

luwojtaszek

1,0051 gold badge11 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Joshua Fabillar Over a year ago

did you make it run on your project?

VSK Over a year ago

Hi, Is it possible of auto wrapping of columns.

luwojtaszek Over a year ago

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.

Piyush Kumar Over a year ago

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

Anuj Over a year ago

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

|

Community · Accepted Answer · 2017-05-23 12:10:30Z

17

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.

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Aug 27, 2016 at 5:24

qwelyt

7769 silver badges23 bronze badges

1 Comment

johan Over a year ago

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)

debugger · Accepted Answer · 2018-07-05 11:08:51Z

17

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

answered Jul 5, 2018 at 11:08

debugger

4804 silver badges11 bronze badges

3 Comments

Muhammad Zeshan Ghafoor Over a year ago

How to style cells?

debugger Over a year ago

@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.

jxn Over a year ago

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.

rlandster · Accepted Answer · 2018-11-12 13:16:56Z

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

edited Nov 12, 2018 at 13:16

rlandster

7,79516 gold badges67 silver badges109 bronze badges

answered Nov 12, 2018 at 13:10

Subham Debnath

1211 silver badge5 bronze badges

Comments

Marvin Delroy Aroza · Accepted Answer · 2020-07-06 08:14:55Z

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

Code Spy · Accepted Answer · 2018-09-20 05:10:47Z

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

answered Sep 20, 2018 at 5:10

Code Spy

9,9744 gold badges72 silver badges46 bronze badges

4 Comments

Code Spy Over a year ago

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

Raj Rajeshwar Singh Rathore Over a year ago

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

Sérgio S. Filho Over a year ago

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

jxn Over a year ago

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.

John Langford · Accepted Answer · 2017-06-08 12:48:37Z

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.

answered Jun 8, 2017 at 12:48

John Langford

1,4713 gold badges16 silver badges18 bronze badges

Comments

Community · Accepted Answer · 2017-09-11 00:23:50Z

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

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

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

edited Sep 11, 2017 at 0:23

CommunityBot

11 silver badge

answered Aug 27, 2016 at 6:00

Pardeep Jain

86.9k38 gold badges172 silver badges220 bronze badges

1 Comment

Fab Over a year ago

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.

Collectives™ on Stack Overflow

How to Export JSON to CSV or Excel - Angular 2

8 Answers 8

22 Comments

1 Comment

3 Comments

Comments

Comments

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

22 Comments

1 Comment

3 Comments

Comments

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related