logo标签列表

如何使用JavaScript将JSON数据导出到Excel文件？

javascriptjqueryhtmljsonexport-to-excel
8

我有一个需要导出到Excel表格的JSON数据。我已经尝试过：

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

但是这只适用于 HTML 表格。
4个回答
20

$(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>

点击这里 在 JSFiddle 中查看它的运行。

3
这个解决方案的问题在于数据通过 URL 传递，而 URL 的最大长度为2083个字符。 - Pier
这种方法能否为Excel数据添加样式？ - Vivek Gondliya
@VivekGondliya 不是。 - iniravpatel
这个解决方案需要使用Blob来下载大文件，请在上面的解决方案中添加以下代码：var blob = new Blob([CSV], { type: 'text/csv;charset=utf-8;' }); - ankitkhandelwal185
@iwayankit，你能否更新一下答案，我会接受它的。 - iniravpatel
@niravpatel9898 好的。 - ankitkhandelwal185
4

这个解决方案在处理大量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: '\00bb';
  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;
}
<!-- <!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>

我想重命名标题，有什么建议吗？ - PayBash
1
如果csv格式符合您的需求，那么可以像这个主题中那样轻松完成。
否则，您将不得不手动解析JSON并将其转换为Excel文件的“XML”格式（因为我不知道任何库可以做到这一点）。在这种情况下，您将不得不学习Excel文件格式，该格式在MSDN博客中有所解释。
干杯
1
因为我不知道有任何库可以做到这一点。对于那些经过这里的人，我猜类似这个的东西会起作用。 - freeeman
-1

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

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

<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>
嗨，弗洛里安，这不是答案，只是一些带有冗余内容的评论。=> 踩。无论如何，您的问题似乎是导入CSV文件时出现的标准Excel对话框，这与CSV生成问题不符。其他答案/评论包括使用xlsx-export库。 Excel中的警告来自后台所做的更改或仅是任何csv文件的标准警告。问候 :) - Andreas Covidiot
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接