Breaking

Tuesday, December 10, 2019

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

create excel sheet using javascript

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.

7 comments:

  1. There are many online tools to do this, I was looking for it do using javascript code since long time and this code works totally fine! excellent ! Thanks

    ReplyDelete
  2. Does this code works on large data?

    ReplyDelete
  3. too much vitamin-a can also cause osteoporosis but aging is the number cause of it` los angeles web agency

    ReplyDelete
  4. I saw a lot of website but I think this one has got something special in it in it web design agency los angeles

    ReplyDelete
  5. i would love to enter my baby on a baby contest because she is very nice and talented,, los angeles web agency

    ReplyDelete
  6. Your article has proven useful to me. It’s very informative and you are obviously very knowledgeable in this area. You have opened my eyes to varying views on this topic with interesting and solid content. website design agency

    ReplyDelete

Featured Post

[Solved] How to get current location of user using javascript?(Example code)

How to get the current location of a user using javascript? You might think to get a user's location is a difficult task, but it&...

Popular Posts