[Solved] How to generate or create excel file using javascript? / How to convert JSON into Excel / How to convert array into Excel

How to generate/create excel file or push data into excel and download using javascript?

Most of the agencies work with a large amount of data and they need the ability to easily export and work with this data which we get from different sources like API or DB.

The best tool to do advance work with numeric data is Excel. 

with the help of the below code, we could easily process and download data into an excel sheet. and to make it easier we can put this code into a chrome extension which will generate excel within seconds and download it without any efforts

Before going on to how to create an excel file using javascript, you may like to read How to create a Google Chrome extension

Implementation time

We had a very simple set of requirements:

XLS  or XLSX (JSON to EXCEL)

Since CSV wasn't good enough, XLS or XLSX would be the way to go. XLSX would be preferable since it's spec is open-sourced and supported widely.

No Server

I’ve already downloaded and rendered the data that the user is about to download. Thus I decided to avoid doing another HTTP request and look to build the XLSX file on the client-side.

Minor bundle size

Doing stuff like this on the client means that they have to download more code, in order to build the file they’re about to download. While we’re utilizing load-on-demand through code-splitting, we still wanted to keep the bundle size hit low.

If you run the following code it'll download large data within seconds.

Sample code which converts JSON into an Excel file using javascript:

Save the following code into .html form.In the following example, we have taken dummy JSON data which we'll be converted into excel with the help of javascript. You can minify the code as per your needs. On click event of a Generate File button, data will be downloaded into an excel.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<div class='mydiv'>    

    <textarea id="txt" class='txtarea'>[{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]</textarea>

    <button class='gen_btn'>Generate File</button>

</div>

<style>

.txtarea{

    max-width:100%;

    min-height:200px;    

    display:block;

    width:100%;

}

.mydiv{

    padding:10px;

}

.gen_btn{

    padding:5px;

    background-color:#743ED9;

    color:white;

    font-family:arial;

    font-size:13px;

    border:2px solid black;

}

.gen_btn:hover{

    background-color:#9a64ff;

}

</style>

<script>

$(document).ready(function(){

    $('button').click(function(){

        var data = $('#txt').val();

        if(data == '')

            return;

        

        JSONToCSVConvertor(data, "Vehicle Report", true);

    });

});

function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {

    //If JSONData is not an object then JSON.parse will parse the JSON string in an Object

    var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;

    

    var CSV = '';    

    //Set Report title in first row or line

    

    CSV += ReportTitle + '\r\n\n';

    //This condition will generate the Label/Header

    if (ShowLabel) {

        var row = "";

        

        //This loop will extract the label from 1st index of on array

        for (var index in arrData[0]) {

            

            //Now convert each value to string and comma-seprated

            row += index + ',';

        }

        row = row.slice(0, -1);

        

        //append Label row with line break

        CSV += row + '\r\n';

    }

    

    //1st loop is to extract each row

    for (var i = 0; i < arrData.length; i++) {

        var row = "";

        

        //2nd loop will extract each column and convert it in string comma-seprated

        for (var index in arrData[i]) {

            row += '"' + arrData[i][index] + '",';

        }

        row.slice(0, row.length - 1);

        

        //add a line break after each row

        CSV += row + '\r\n';

    }

    if (CSV == '') {        

        alert("Invalid data");

        return;

    }   

    

    //Generate a file name

    var fileName = "MyReport_";

    //this will remove the blank-spaces from the title and replace it with an underscore

    fileName += ReportTitle.replace(/ /g,"_");   

    

    //Initialize file format you want csv or xls

    var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);

    

    // Now the little tricky part.

    // you can use either>> window.open(uri);

    // but this will not work in some browsers

    // or you will not get the correct file extension    

    

    //this trick will generate a temp <a /> tag

    var link = document.createElement("a");    

    link.href = uri;

    

    //set the visibility hidden so it will not effect on your web-layout

    link.style = "visibility:hidden";

    link.download = fileName + ".csv";

    

    //this part will append the anchor tag and remove it after automatic click

    document.body.appendChild(link);

    link.click();

    document.body.removeChild(link);

}

</script>

If you are having input data into an array you can convert that data into a JSON object and pass it into

JSONToCSVConvertor() function.

We build this pretty swiftly, but we are quite confident with it. But do feel free to leave any suggestions, comments or questions in the comments below.