Convert JSON to Excel using JavaScript

In previous tutorial, we have explained how to Convert Excel to JSON using JavaScript. In this tutorial, we will explain how to convert JSON into Excel File using JavaScript.

JSON (JavaScript Object Notation) is a lightweight data-interchange format and widely used in web application. It is plain text written in JavaScript object notation.

While working with APIs, mostly the available data is in JSON format and need to convert into desired format. As the Excel file type is popular file type that contains numerical data separated by rows and columns within a cell, we sometimes need to convert JSON object into Excel file.

So let’s proceed with tutorial to convert JSON object to Excel file using JavaScript. The file structure of this is following.

• index.php
• convert.js

Step1: Include Bootstrap, jQuery and XLSX plugin

As we will design convert page using Bootstrap, so we will include Bootstrap libraray files. We will include xlsx.full.min.js CDN file to handle JSON into Excel convert.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.2/xlsx.full.min.js"></script>

We will include convert.js file before body close tag to handle JSON object convert to Excel.

<script type="text/javascript" src="javascript/convert.js"></script>

Step2: Create Convert Page

We will create page with textarea and buttons to convert JSON data to Excel file.

<div class="conatiner mt-5">
	<br><br>
	<div class="form-group">
	<label for="csvData">Paste JSON Object Data:</label>
	<textarea id="josnData" class="form-control" rows="10">
[
{"name":"John", "age":30, "city":"New York"},
{"name":"Smith", "age":40, "city":"London"}
]
	</textarea>
	</div>		
	<button type="button" id="exportWorksheet" >Export Worksheet</button>
	<button type="button" id="exportWorksheetPlus" >Export Worksheet+</button>
</div>

Step3: Implement JSON Data to Excel Conversion

In convert.js file, we will call converion function on button click event. We will call function exportWorksheet() and exportWSPlus() on button click event.

$( document ).ready(function() {
	$("#exportWorksheet").click(function() {
		var josnData = $("#josnData").val();
		var jsonDataObject = eval(josnData);		
		exportWorksheet(jsonDataObject);
	});
	
	$("#exportWorksheetPlus").click(function() {
		var josnData = $("#josnData").val();
		var jsonDataObject = eval(josnData);		
		exportWSPlus(jsonDataObject);
	});
	
});

We will define convert functions to implement data conversion using XLSX libaray. We will define function exportWorksheet() and exportWSPlus().

function exportWorksheet(jsonObject) {
  var myFile = "myFile.xlsx";
  var myWorkSheet = XLSX.utils.json_to_sheet(jsonObject);
  var myWorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(myWorkBook, myWorkSheet, "myWorkSheet");
  XLSX.writeFile(myWorkBook, myFile);
}

function exportWSPlus(jsonObject) {
  var myFile = "myFilePlus.xlsx";
  var myWorkSheet = XLSX.utils.json_to_sheet(jsonObject);
  XLSX.utils.sheet_add_aoa(myWorkSheet, [["Your Mesage Goes Here"]], { origin: 0 });
  var merges = myWorkSheet['!merges'] = [{ s: 'A1', e: 'D1' }];
  var myWorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(myWorkBook, myWorkSheet, "myWorkSheet");
  XLSX.writeFile(myWorkBook, myFile);
}

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download