How To Convert JSON Data Into Excel In Angular

Introduction

There are lots of online tools to convert JSON data into Excel. However, if you would like to develop a custom solution for your company here is a simple way to do so with Angular4.

Implementation

The Excel conversion of JSON data can be done in Angular 4 by using the below NPM packages

• alasql (https://www.npmjs.com/package/alasql)
• xlsx (https://www.npmjs.com/package/xlsx)

The npm package xlsx plays the supportive role for alasql. If we didn’t include the xlsx package you might see the error “Please include the xlsx.js library” error in your browser console window like below,

Figure: 1

By using alasql you could even create an Excel document with multiple sheets in Angular 4 by passing multiple JSON objects

Step 1

Go to your package.json file and add the packages  alasql and xlsx like below

"alasql": "0.4.5",
"xlsx": "0.13.0"

 

Code Snippet: 1

Now we have to install the newly added npm packages into our project, to do so you can use any of the below steps

1. If you are using Visual Studio, right-click the JSON file and click on “Restore Packages” option. This will automatically install the missed npm packages.

2. If you are using any terminal window, just install the above packages by using the below command.
   

   npm install alasql
   npm install xlsx

   Code Snippet: 2

Step 2

Now, we have to include some reference scripts directly to your index file where you have referred the polyfills required for Angular.

Code Snippet: 3

Step 3

Next step is to include to import the package alasql just like importing all other packages in your app.module.ts file like below,

import * as alasql from 'alasql';

 

Code Snippet: 4

That’s it, now you are ready to convert the JSON data into Excel data. You can query the JSON object just like querying SQL tables, please find a sample below

Example

Here I have created two JSON array objects and created an Excel file with two sheets in it. Each sheet holds each a JSON object accordingly. Also, we can pass in different options to format the Excel sheet like below,

let DataForSheet1: any[] = [];
        DataForSheet1.push({ FName: "John", LName: "Kennedy" });
        DataForSheet1.push({ FName: "Ajay", LName: "Kumar" });
        DataForSheet1.push({ FName: "Deepak", LName: "Reddy" });
        DataForSheet1.push({ FName: "Kiran", LName: "Kumar" });

        let DataForSheet2: any[] = [];
        DataForSheet2.push({ Name: "John", Mark: 100 });
        DataForSheet2.push({ Name: "Ajay", LName: 85 });
        DataForSheet2.push({ Name: "Deepak", LName: 70 });
        DataForSheet2.push({ Name: "Kiran", LName: 90 });

        var opts = [{ sheeitd: "Sheet 1", header: true }, { sheeitd: "Sheet 2", header: true }]

        alasql("SELECT INTO XLSX ('Students.xlsx',?) FROM ?", [opts, [DataForSheet1, DataForSheet2]]);
    //This piece of code will download excel sheet with two sheets

        alasql("SELECT TOP 2 Name as [Student Name], Mark as [Subject Marks] INTO XLSX ('Marks.xlsx',{headers:true}) FROM ?", [DataForSheet2]);
    //This piece of code will download excel sheet with one sheet, and if you notice here we could change the column name also.

Code Snippet: 5

Summary

In this article, we have discussed how to convert JSON data into Excel and download with single/multiple Excel documents from the Angular application. Still, we could do more formatting with the same Excel using the alasql and xlsx packages. I will explain those in my next article.

If you have any questions/issues about this article, please let me know in the comments.