使用 jQuery 和 HTML 导出到 CSV [英] Export to CSV using jQuery and HTML

查看：22
本文介绍了使用 jQuery 和 HTML 导出到 CSV的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表格数据，我需要在不使用任何外部插件或 API 的情况下将其导出到 csv.我使用了传递 MIME 类型的 window.open 方法，但遇到了如下问题:

如何使用jQuery确定系统上是否安装了Microsoft Excel或OpenOffice

代码应该独立于系统上安装的内容，即 OpenOffice 或 Microsoft Excel.我相信 CSV 是可以在两个编辑器中显示的格式.

代码

 <头><script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script><脚本类型=文本/JavaScript">$(document).ready(function(){$("#btnExport").click(function(e) {var msg = GetMimeTypes();//开发办公室window.open('data:application/vnd.oasis.opendocument.spreadsheet,' + $('#dvData').html());//MS-Excelwindow.open('data:application/vnd.ms-excel,' + $('#dvData').html());//CSVwindow.open('data:application/csv,charset=utf-8,' + $('#dvData').html());e.preventDefault();});});函数 GetMimeTypes() {var message = "";//Internet Explorer 支持 mimeTypes 集合，但它始终为空如果 (navigator.mimeTypes && navigator.mimeTypes.length > 0) {var mimes = navigator.mimeTypes;for (var i=0; i "；+ mimes[i].type + "</b>: "+ mimes[i].description + "
";}}别的 {message = "您的浏览器不支持此功能";//对不起！}返回(消息)；}<身体><div id="dvData"><表格><tr><th>第一列</th><th>第二列</th><第3列</tr><tr><td>row1 Col1</td><td>row1 Col2</td><td>row1 Col3</td></tr><tr><td>row2 Col1</td><td>row2 Col2</td><td>row2 Col3</td></tr><tr><td>row3 Col1</td><td>row3 Col2</td><td>row3 Col3</td></tr>

<br/><输入类型=按钮"id=btnExport"值="将表格数据导出到 Excel/>

错误

CSV:浏览器无法识别

ODS &Excel: 正在运行，但是当系统安装了 excel 或 openoffice 时，我无法找到要生成哪一个?

IE 版本 8:它完全不起作用，在新窗口中打开，如下图所示.

解决方案

Demo

请参阅下面的说明.

$(document).ready(function() {函数 exportTableToCSV($table, 文件名) {var $rows = $table.find('tr:has(td)'),//不太可能由键盘输入的临时分隔符//这是为了避免不小心拆分实际内容tmpColDelim = String.fromCharCode(11),//垂直制表符tmpRowDelim = String.fromCharCode(0),//空字符//CSV 格式的实际分隔符colDelim = '","',rowDelim = '"
"',//从表格中抓取文本到 CSV 格式的字符串中csv = '"' + $rows.map(function(i, row) {var $row = $(row),$cols = $row.find('td');返回 $cols.map(function(j, col) {var $col = $(col),文本 = $col.text();return text.replace(/"/g, '""');//转义双引号}).get().join(tmpColDelim);}).get().join(tmpRowDelim).split(tmpRowDelim).join(rowDelim).split(tmpColDelim).join(colDelim) + '"';//故意假"，见下面的评论如果(假&& window.navigator.msSaveBlob){var blob = new Blob([decodeURIComponent(csv)], {类型:'文本/csv；字符集=utf8'});//在 IE 10、IE 11 和 Microsoft Edge 中崩溃//参见 MS Edge 问题 #10396033//因此，故意的假"//这里只是为了完整性//删除false"，风险自负window.navigator.msSaveBlob(blob, 文件名);} else if (window.Blob && window.URL) {//HTML5 Blobvar blob = new Blob([csv], {类型:'text/csv;charset=utf-8'});var csvUrl = URL.createObjectURL(blob);$(这个).attr({'下载':文件名，'href': csvUrl});} 别的 {//数据URIvar csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);$(这个).attr({'下载':文件名，'href': csvData,'目标':'_blank'});}}//这必须是一个超链接$(".export").on('click', function(event) {//CSVvar args = [$('#dvData>table'), 'export.csv'];exportTableToCSV.apply(this, args);//如果是 CSV，则不要执行 event.preventDefault() 或返回 false//我们实际上需要这是一个典型的超链接});});

a.export,a.出口:访问{显示:内联块；文字装饰:无；颜色:#000；背景颜色:#ddd;边框:1px 实心 #ccc；填充:8px；}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script><a href="#" class="export">将表格数据导出到 Excel</a><div id="dvData"><表格><tr><th>第一列</th><th>第二列</th><第3列</tr><tr><td>row1 Col1</td><td>row1 Col2</td><td>row1 Col3</td></tr><tr><td>row2 Col1</td><td>row2 Col2</td><td>row2 Col3</td></tr><tr><td>row3 Col1</td><td>row3 Col2</td><td>row3 Col3</td></tr><tr><td>row4 'Col1'</td><td>row4 'Col2'</td><td>row4 'Col3'</td></tr><tr><td>row5 &quot;Col1&quot;</td><td>row5 &quot;Col2&quot;</td><td>row5 &Col3"</td></tr><tr><td>row6 "Col1"</td><td>row6 "Col2"</td><td>row6 "Col3"</td></tr>

<小时>

截至 2017 年

现在使用 HTML5 Blob 和 URL 作为首选方法，Data URI 作为后备.

在 Internet Explorer 上

其他答案建议 window.navigator.msSaveBlob；但是，它会崩溃 IE10/Window 7 和 IE11/Windows 10.是否使用 Microsoft Edge 是值得怀疑的(请参阅 Microsoft Edge 问题票#10396033).

仅仅在微软自己的开发者工具/控制台中调用它会导致浏览器崩溃:

navigator.msSaveBlob(new Blob(["hello"], {type: "text/plain"}), "test.txt");

在我第一次回答四年后，新的 IE 版本包括 IE10、IE11 和 Edge.他们都在微软发明的一个函数上崩溃了(慢拍).

<块引用>

添加 navigator.msSaveBlob 支持，风险自负.

<小时>

截至 2013 年

通常这将使用服务器端解决方案来执行，但这是我对客户端解决方案的尝试.简单地将 HTML 转储为 Data URI 是行不通的，但这是一个有用的步骤.所以:

  1. 将表格内容转换为有效的 CSV 格式字符串.(这是最简单的部分.)
  2. 强制浏览器下载它.window.open 方法在 Firefox 中不起作用，所以我使用了 <a href="{Data URI here}">.
  3. 使用 标记的 download 属性指定默认文件名，该属性仅适用于 Firefox 和 Google Chrome.由于它只是一个属性，所以它会优雅地降级.

<小时>

注意事项

兼容性

浏览器测试包括:

  • Firefox 20+，Win/Mac(有效)
  • Google Chrome 26+，Win/Mac(有效)
  • Safari 6、Mac(有效，但忽略文件名)
  • IE 9+(失败)

内容编码

CSV 导出正确，但导入 Excel 时，字符 ü 打印为 ä.Excel 错误地解释了该值.

引入 var csv = 'ufeff'; 然后 Excel 2013+ 正确解释这些值.

如果您需要与 Excel 2007 兼容，请在每个数据值处添加 UTF-8 前缀.另见:

I have a tabular data which I need to export to csv without using any external plugin or API. I used the window.open method passing the MIME types but faced issues like below:

How to determine whether Microsoft Excel or OpenOffice is installed on the system using jQuery

The code should be independent of the fact that what is being installed on the system, i.e. OpenOffice or Microsoft Excel. I believe CSV is the format which can be expected to show in both the editors.

CODE

    <html>

<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>  

<script type="text/JavaScript">
$(document).ready(function(){
    $("#btnExport").click(function(e) {
        var msg = GetMimeTypes();
        //OpenOffice
        window.open('data:application/vnd.oasis.opendocument.spreadsheet,' + $('#dvData').html());
        //MS-Excel
        window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
        //CSV
        window.open('data:application/csv,charset=utf-8,' + $('#dvData').html());
        e.preventDefault();
    });
});

function GetMimeTypes () {
    var message = "";
        // Internet Explorer supports the mimeTypes collection, but it is always empty
    if (navigator.mimeTypes && navigator.mimeTypes.length > 0) {
        var mimes = navigator.mimeTypes;
        for (var i=0; i < mimes.length; i++) {
            message += "<b>" + mimes[i].type + "</b> : " + mimes[i].description + "<br />";
        }
    }
    else {
        message = "Your browser does not support this ";
       //sorry!
    }

    return ( message);
}
</script>

</head>
<body>
<div id="dvData">
<table>
    <tr>
        <th>Column One </th>
        <th>Column Two</th>
        <th>Column Three</th>
    </tr>
    <tr>
        <td>row1 Col1</td>
        <td>row1 Col2</td>
        <td>row1 Col3</td>
   </tr>
   <tr>
        <td>row2 Col1</td>
        <td>row2 Col2</td>
        <td>row2 Col3</td>
   </tr>
      <tr>
        <td>row3 Col1</td>
        <td>row3 Col2</td>
        <td>row3 Col3</td>  
   </tr>
</table>
</div>
<br/>
<input type="button" id="btnExport" value=" Export Table data into Excel " />

</body>

Errors

CSV: Unrecognised over the browsers

ODS & Excel: is working but I am not able to find which one to generate when system is having an excel installed or openoffice installed?

IE version 8 : it is totally not working, opens in a new window and as below screenshot.

解决方案

Demo

See below for an explanation.

$(document).ready(function() {

  function exportTableToCSV($table, filename) {

    var $rows = $table.find('tr:has(td)'),

      // Temporary delimiter characters unlikely to be typed by keyboard
      // This is to avoid accidentally splitting the actual contents
      tmpColDelim = String.fromCharCode(11), // vertical tab character
      tmpRowDelim = String.fromCharCode(0), // null character

      // actual delimiter characters for CSV format
      colDelim = '","',
      rowDelim = '"
"',

      // Grab text from table into CSV formatted string
      csv = '"' + $rows.map(function(i, row) {
        var $row = $(row),
          $cols = $row.find('td');

        return $cols.map(function(j, col) {
          var $col = $(col),
            text = $col.text();

          return text.replace(/"/g, '""'); // escape double quotes

        }).get().join(tmpColDelim);

      }).get().join(tmpRowDelim)
      .split(tmpRowDelim).join(rowDelim)
      .split(tmpColDelim).join(colDelim) + '"';

    // Deliberate 'false', see comment below
    if (false && window.navigator.msSaveBlob) {

      var blob = new Blob([decodeURIComponent(csv)], {
        type: 'text/csv;charset=utf8'
      });

      // Crashes in IE 10, IE 11 and Microsoft Edge
      // See MS Edge Issue #10396033
      // Hence, the deliberate 'false'
      // This is here just for completeness
      // Remove the 'false' at your own risk
      window.navigator.msSaveBlob(blob, filename);

    } else if (window.Blob && window.URL) {
      // HTML5 Blob        
      var blob = new Blob([csv], {
        type: 'text/csv;charset=utf-8'
      });
      var csvUrl = URL.createObjectURL(blob);

      $(this)
        .attr({
          'download': filename,
          'href': csvUrl
        });
    } else {
      // Data URI
      var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

      $(this)
        .attr({
          'download': filename,
          'href': csvData,
          'target': '_blank'
        });
    }
  }

  // This must be a hyperlink
  $(".export").on('click', function(event) {
    // CSV
    var args = [$('#dvData>table'), 'export.csv'];

    exportTableToCSV.apply(this, args);

    // If CSV, don't do event.preventDefault() or return false
    // We actually need this to be a typical hyperlink
  });
});

a.export,
a.export:visited {
  display: inline-block;
  text-decoration: none;
  color: #000;
  background-color: #ddd;
  border: 1px solid #ccc;
  padding: 8px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<a href="#" class="export">Export Table data into Excel</a>
<div id="dvData">
  <table>
    <tr>
      <th>Column One</th>
      <th>Column Two</th>
      <th>Column Three</th>
    </tr>
    <tr>
      <td>row1 Col1</td>
      <td>row1 Col2</td>
      <td>row1 Col3</td>
    </tr>
    <tr>
      <td>row2 Col1</td>
      <td>row2 Col2</td>
      <td>row2 Col3</td>
    </tr>
    <tr>
      <td>row3 Col1</td>
      <td>row3 Col2</td>
      <td>row3 Col3</td>
    </tr>
    <tr>
      <td>row4 'Col1'</td>
      <td>row4 'Col2'</td>
      <td>row4 'Col3'</td>
    </tr>
    <tr>
      <td>row5 &quot;Col1&quot;</td>
      <td>row5 &quot;Col2&quot;</td>
      <td>row5 &quot;Col3&quot;</td>
    </tr>
    <tr>
      <td>row6 "Col1"</td>
      <td>row6 "Col2"</td>
      <td>row6 "Col3"</td>
    </tr>
  </table>
</div>

As of 2017

Now uses HTML5 Blob and URL as the preferred method with Data URI as a fallback.

On Internet Explorer

Other answers suggest window.navigator.msSaveBlob; however, it is known to crash IE10/Window 7 and IE11/Windows 10. Whether it works using Microsoft Edge is dubious (see Microsoft Edge issue ticket #10396033).

Merely calling this in Microsoft's own Developer Tools / Console causes the browser to crash:

navigator.msSaveBlob(new Blob(["hello"], {type: "text/plain"}), "test.txt");


Four years after my first answer, new IE versions include IE10, IE11, and Edge. They all crash on a function that Microsoft invented (slow clap).

Add navigator.msSaveBlob support at your own risk.

As of 2013

Typically this would be performed using a server-side solution, but this is my attempt at a client-side solution. Simply dumping HTML as a Data URI will not work, but is a helpful step. So:

  1. Convert the table contents into a valid CSV formatted string. (This is the easy part.)
  2. Force the browser to download it. The window.open approach would not work in Firefox, so I used <a href="{Data URI here}">.
  3. Assign a default file name using the <a> tag's download attribute, which only works in Firefox and Google Chrome. Since it is just an attribute, it degrades gracefully.

Notes

Compatibility

Browsers testing includes:

  • Firefox 20+, Win/Mac (works)
  • Google Chrome 26+, Win/Mac (works)
  • Safari 6, Mac (works, but filename is ignored)
  • IE 9+ (fails)

Content Encoding

The CSV is exported correctly, but when imported into Excel, the character ü is printed out as ä. Excel interprets the value incorrectly.

Introduce var csv = 'ufeff'; and then Excel 2013+ interprets the values correctly.

If you need compatibility with Excel 2007, add UTF-8 prefixes at each data value. See also:

这篇关于使用 jQuery 和 HTML 导出到 CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
前端开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆