Convert JSON data to Excel with JavaScript

Question

I know there are a lot of answers to these questions, but my requirement is a bit different. Let me explain what I have tried.

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);

}

The out put i am getting from this is

CLASSIC_bn_0 CLASSIC_en_0 CLASSIC_en_1 CLASSIC_en_10 CLASSIC_en_11
1             2            28           undefined     undefined 
1             2            28           undefined     undefined 

But I am expecting the output in another way, that is it should be represented column-wise rather than row-wise.

CLASSIC_bn_0   1   1
CLASSIC_en_0   2   2 
CLASSIC_en_1   28  28
CLASSIC_en_10  28  28
CLASSIC_en_11  undefined undefined          

Kindly help me with this as I am struck from the past one day.
Thank you.

Answer 1

What about simple changing of axes and a bit of array magic to omit string ugly concatenation ?

var JSONData = [{ "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 }]
JSONToCSVConvertor(JSONData, "Cars list", 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
    var cols = [];
    if (ShowLabel) {
        var col = [];

        //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
            col.push(index);
        }
        cols.push(col);
    }

    //1st loop is to extract each row
    for (var i = 0; i < arrData.length; i++) {
        var col = [];
        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData[i]) {
            col.push(arrData[i][index]);
        }
        cols.push(col);
    }

    if (!cols.length) {
        alert("Invalid data");
        return;
    }

    var table = "<TABLE border=1>";
    var col = 0, loop = true;
    while(loop) {
        table += "<TR><TD>"
        var row = [];
        for(var r in cols) {
            loop &= (col < cols[r].length - 1);
            row.push(cols[r][col]);
        }
        CSV += '"' + row.join('","') + '"\r\n';
        table += row.join("</TD><TD>") + "</TD></TR>";
        col++;
    }
    table += "</TABLE>";
    console.log(CSV)
    document.body.innerHTML = table;
}