Written by

Jayakumar Balasubramaniam

Share

  

Category

see all categories
intranet as a service

How to Convert JSON data into Excel (Code Walkthrough)

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

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

alasql

Figure: 1

By using alasql you could even create 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 packges 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 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 2-2

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 excel file with two sheets in it each sheet holds each 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 convert JSON data into excel and download with single/multiple excel document from 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’re stuck up in the middle I am available in the comments to help you out.

Jayakumar Balasubramaniam

Written by Jayakumar Balasubramaniam

A technology evangelist working on Microsoft 365, SharePoint & Azure. Gets my hands dirty with latest and greatest of SharePoint, Azure & front-end frameworks. Currently working with “Hubfly – A unified digital workplace” as a Product Engineer. Getting in to the depth of the domain & technology, with my eyes for perfection, ensures that the product releases go right every time.