Convert JSON to EXCEL

Asked

Viewed 6,016 times

4

I carry out a consultation ajax that returns me a Json with object array, how can I transform this json in excel?

Example of my json:

[
    {idcancelamento: "383", idcliente: "2409", emailcancelamento: "1", financeiro: "1", suporte: "1"},
    {idcancelamento: "384", idcliente: "3457", emailcancelamento: "1", financeiro: "1", suporte: null}
]

I tried with this function but everything is on the same line separated by ,

  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);
}
javascript json excel xls

3 answers

7


There are some solutions on the international site with pure Javascript. I use (and recommend) the library alasql.

Example

var jsonArray = [{
    idcancelamento: "383",
    idcliente: "2409",
    emailcancelamento: "1",
    financeiro: "1",
    suporte: "1"
  },
  {
    idcancelamento: "384",
    idcliente: "3457",
    emailcancelamento: "1",
    financeiro: "1",
    suporte: null
  }
];

alasql('SELECT * INTO XLSX("arquivo.xlsx", {headers: true}) FROM ?', [jsonArray]);
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.11.6/xlsx.core.min.js"></script>
<script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>

The main difference from alasql to pure scripts is that it works well with nested objects in JSON.

2

I had the same problem as you, to solve this, just replace the separator from , (COMMA) to ; (POINT AND COMMA) at these points:

//responsavel pelo cabeçalho do excel
for (var index in arrData[0]) {
   row += index + ';';
}

And this point, where it goes interacting key and array value:

for (var index in arrData[i]) {
   row += arrData[i][index] + ';';
}

-1

Browser other questions tagged javascript json excel xls

You are not signed in. Login or sign up in order to post.