Javascript 如何使用javascript将json数据导出到excel文件？

how to export json data to excel file using javascript?

提问by Sunil Kumar

I have json data which needs to be exported to an excel sheet. I have tried

我有需要导出到 Excel 工作表的 json 数据。我试过了

window.open('data:application/vnd.ms-excel,' + encodeURIComponent( $('#tableId').html()));

But this works for html table only.

但这仅适用于 html 表。

回答by iniravpatel

$(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);
}

.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;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.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>

Click Hereto watch It work in JSfiddle.

单击此处观看它在 JSfiddle 中的工作。

回答by iwayankit

this solution works fine with large JSON data, IE error issues, hope this will help

此解决方案适用于大型 JSON 数据、IE 错误问题，希望这会有所帮助

$(document).ready(function() {
  $("#button1").on('click', function(e) {
    var url = "";
    $.ajax({
      type: "GET",
      url: url,
      success: function(result) {
        DownloadJsonData(result, "NewFile", true);
      },
      error: function(result) {
        alert('Error ');
      }
    });
  });

  function DownloadJsonData(JSONData, FileTitle, 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 = '';
    //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 = FileTitle + (new Date());
    var blob = new Blob([CSV], {
      type: 'text/csv;charset=utf-8;'
    });
    if (navigator.msSaveBlob) { // IE 10+
      navigator.msSaveBlob(blob, filename);
    } else {
      var link = document.createElement("a");
      if (link.download !== undefined) { // feature detection
        // Browsers that support HTML5 download attribute
        var url = URL.createObjectURL(blob);
        link.setAttribute("href", url);
        link.style = "visibility:hidden";
        link.download = filename + ".csv";
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      }
    }
  }
})

.flex {
  display: flex;
  justify-content: center;
}

.flex-item+.flex-item {
  margin-left: 10px;
}

.button {
  border-radius: 4px;
  background-color: #f4511e;
  border: none;
  color: #FFFFFF;
  text-align: center;
  font-size: 28px;
  padding: 20px;
  width: 100%;
  transition: all 0.5s;
  cursor: pointer;
  margin: 15px;
  margin-left: 25px;
}

.button span {
  cursor: pointer;
  display: inline-block;
  position: relative;
  transition: 0.5s;
}

.button span:after {
  content: '
<!-- <!DOCTYPE html> -->
<html>

<head>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>

<body>
  <h1 clas="flex">Please Click on a Button of requested Data</h1>
  <div class="flex">
    <button id="button1" class="button"><span>Click Me </span></button>
  </div>
</body>

</html>
bb';
  position: absolute;
  opacity: 0;
  top: 0;
  right: -20px;
  transition: 0.5s;
}

.button:hover span {
  padding-right: 25px;
}

.button:hover span:after {
  opacity: 1;
  right: 0;
}

<script>
$(document).ready(function() {
  $('button').click(function() {
    var data = $('[id="xyz"]').val();
    if (data == '')
      return;

    JSONToCSVConvertor(data, "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 = "";
  //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>

回答by bviale

If the csv format fits your need, then it can be easily done like in this topic.

如果 csv 格式符合您的需要，则可以像本主题一样轻松完成。

Otherwise, you will have to parse manually your JSON to the 'XML' format of a Excel file (because I don't know any library doing this). In this case, you will have to study the Excel file format, which is explained in this MSDN blog.

否则，您将不得不手动将 JSON 解析为 Excel 文件的“XML”格式（因为我不知道有任何库这样做）。在这种情况下，您必须研究 Excel 文件格式，此MSDN 博客中对此进行了解释。

Cheers

干杯

回答by Florian Ntibarigobeka

@niravpatel9898's original answer works well for me, however the actual download to CSV causes this error in excel while parsing the JSON data I have: Some features might be lost if you save this workbook in the comma-delimited (.csv) format. To preserve these features, save it in an Excel file format.Is there any way to get rid of this warning and ensure the data parses and downloads to Excel correctly?

@niravpatel9898 的原始答案对我来说效果很好，但是实际下载到 CSV 会在解析我拥有的 JSON 数据时在 excel 中导致此错误： 如果您以逗号分隔 (.csv) 格式保存此工作簿，则某些功能可能会丢失。要保留这些功能，请将其保存为 Excel 文件格式。有什么办法可以消除此警告并确保数据正确解析并下载到 Excel 中？

When I try using changing the code to output to .xls or when I use the solution suggested by @iwayankit, it does not seem to work. Below is the original code by @niravpatel9898 that I am trying to work with:

当我尝试使用更改代码以输出到 .xls 或使用@iwayankit 建议的解决方案时，它似乎不起作用。以下是我正在尝试使用的@niravpatel9898 的原始代码：

##代码##