使用 jQuery 和 html 导出到 CSV

Question

提问by GOK

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

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

How to determine whether Microsoft Excel or Open Office is installed on the system using jquery

如何使用jquery确定系统上是否安装了Microsoft Excel或Open Office

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

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

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

CSV：无法通过浏览器识别

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?

ODS & Excel：正在工作，但当系统安装了 excel 或 openoffice 时，我无法找到要生成哪一个？

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

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

enter image description here

在此处输入图片说明

Answer 1

回答by Terry Young

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 = '"\r\n"',

      // 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

截至 2017 年

Now uses HTML5 Bloband URLas the preferred method with Data URIas a fallback.

现在使用 HTML5Blob和URL作为Data URI后备的首选方法。

On Internet Explorer

在 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).

其他答案建议window.navigator.msSaveBlob; 但是，已知会导致 IE10/Window 7 和 IE11/Windows 10 崩溃。是否可以使用 Microsoft Edge 值得怀疑（请参阅Microsoft Edge 问题单 #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).

?在我第一次回答四年后，新的 IE 版本包括 IE10、IE11 和 Edge。他们都在微软发明的一个功能上崩溃（慢拍）。

Add navigator.msSaveBlobsupport at your own risk.

添加navigator.msSaveBlob支持的风险自负。

As of 2013

截至 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 URIwill not work, but is a helpful step. So:

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

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

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

Notes

笔记

You can style your link to look like a button. I'll leave this effort to you
IE has Data URI restrictions. See: Data URI scheme and Internet Explorer 9 Errors
About the "download" attribute, see these:

您可以设置链接的样式，使其看起来像一个按钮。我会把这个努力留给你
IE 有数据 URI 限制。请参阅：数据 URI 方案和 Internet Explorer 9 错误
关于“下载”属性，请参阅以下内容：

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)

Firefox 20+，Win/Mac（有效）
谷歌浏览器 26+，Win/Mac（有效）
Safari 6, Mac（有效，但文件名被忽略）
IE 9+（失败）

Content Encoding

内容编码

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

CSV 已正确导出，但在导入 Excel 时，字符ü打印为?¤. Excel 会错误地解释该值。

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

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

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

如果需要与 Excel 2007 兼容，请在每个数据值处添加 UTF-8 前缀。也可以看看：

Answer 2

回答by Action Dan

I am not sure if the above CSV generation code is so great as it appears to skip thcells and also did not appear to allow for commas in the value. So here is my CSV generation code that might be useful.

我不确定上面的 CSV 生成代码是否很棒，因为它似乎跳过了th单元格并且似乎也不允许在值中使用逗号。所以这是我可能有用的 CSV 生成代码。

It does assumeyou have the $tablevariable which is a jQuery object (eg. var $table = $('#yourtable');)

它确实假设你有$table一个 jQuery 对象的变量（例如。var $table = $('#yourtable');）

$rows = $table.find('tr');

var csvData = "";

for(var i=0;i<$rows.length;i++){
                var $cells = $($rows[i]).children('th,td'); //header or content cells

                for(var y=0;y<$cells.length;y++){
                    if(y>0){
                      csvData += ",";
                    }
                    var txt = ($($cells[y]).text()).toString().trim();
                    if(txt.indexOf(',')>=0 || txt.indexOf('\"')>=0 || txt.indexOf('\n')>=0){
                        txt = "\"" + txt.replace(/\"/g, "\"\"") + "\"";
                    }
                    csvData += txt;
                }
                csvData += '\n';
 }

Answer 3

回答by Du?an Ma?ar

A tiny update for @Terry Younganswer, i.e. add IE 10+ support

@Terry Young答案的小更新，即添加 IE 10+ 支持

if (window.navigator.msSaveOrOpenBlob) {
  // IE 10+
  var blob = new Blob([decodeURIComponent(encodeURI(csvString))], {
    type: 'text/csv;charset=' + document.characterSet
  });
  window.navigator.msSaveBlob(blob, filename);
} else {
  // actual real browsers
  //Data URI
  csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvData);

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

Answer 4

回答by Shahid wali

 <a id="export" role='button'>
        Click Here To Download Below Report
    </a>
    <table id="testbed_results" style="table-layout:fixed">
        <thead>
            <tr width="100%" style="color:white" bgcolor="#3195A9" id="tblHeader">
                <th>Name</th>
                <th>Date</th>
                <th>Speed</th>
                <th>Column2</th>
                <th>Interface</th>
                <th>Interface2</th>
                <th>Sub</th>
                <th>COmpany result</th>
                <th>company2</th>
                <th>Gen</th>
            </tr>
        </thead>
        <tbody>
            <tr id="samplerow">
                <td>hello</td>
                <td>100</td>
                <td>200</td>
                <td>300</td>
                <td>html2svc</td>
                <td>ajax</td>
                <td>200</td>
                <td>7</td>
                <td>8</td>
                <td>9</td>
            </tr>
            <tr>
                <td>hello</td>
                <td>100</td>
                <td>200</td>
                <td>300</td>
                <td>html2svc</td>
                <td>ajax</td>
                <td>200</td>
                <td>7</td>
                <td>8</td>
                <td>9</td>
            </tr>
        </tbody>
    </table>

    $(document).ready(function () {
        Html2CSV('testbed_results', 'myfilename','export');
    });



    function Html2CSV(tableId, filename,alinkButtonId) {
        var array = [];
        var headers = [];
        var arrayItem = [];
        var csvData = new Array();
        $('#' + tableId + ' th').each(function (index, item) {
            headers[index] = '"' + $(item).html() + '"';
        });
        csvData.push(headers);
        $('#' + tableId + ' tr').has('td').each(function () {

            $('td', $(this)).each(function (index, item) {
                arrayItem[index] = '"' + $(item).html() + '"';
            });
            array.push(arrayItem);
            csvData.push(arrayItem);
        });




        var fileName = filename + '.csv';
        var buffer = csvData.join("\n");
        var blob = new Blob([buffer], {
            "type": "text/csv;charset=utf8;"
        });
        var link = document.getElementById(alinkButton);

        if (link.download !== undefined) { // feature detection
            // Browsers that support HTML5 download attribute
            link.setAttribute("href", window.URL.createObjectURL(blob));
            link.setAttribute("download", fileName);
        }
        else if (navigator.msSaveBlob) { // IE 10+
            link.setAttribute("href", "#");
            link.addEventListener("click", function (event) {
                navigator.msSaveBlob(blob, fileName);
            }, false);
        }
        else {
            // it needs to implement server side export
            link.setAttribute("href", "http://www.example.com/export");
        }
    }

</script>

Answer 5

回答by Zlatin Zlatev

What if you have your data in CSV format and convert it to HTML for display on the web page? You may use the http://code.google.com/p/js-tables/plugin. Check this example http://code.google.com/p/js-tables/wiki/TableAs you are already using jQuery library I have assumed you are able to add other javascript toolkit libraries.

如果您有 CSV 格式的数据并将其转换为 HTML 以显示在网页上会怎样？您可以使用http://code.google.com/p/js-tables/插件。检查此示例http://code.google.com/p/js-tables/wiki/Table由于您已经在使用 jQuery 库，我假设您能够添加其他 javascript 工具包库。

If the data is in CSV format, you should be able to use the generic 'application/octetstream' mime type. All the 3 mime types you have tried are dependent on the software installed on the clients computer.

如果数据是 CSV 格式，您应该能够使用通用的“application/octetstream”mime 类型。您尝试过的所有 3 种 mime 类型都取决于客户端计算机上安装的软件。

Answer 6

回答by MervS

From what I understand, you have your data on a table and you want to create the CSV from that data. However, you have problem creating the CSV.

据我了解，您将数据放在一张表上，并且希望根据该数据创建 CSV。但是，您在创建 CSV 时遇到问题。

My thoughts would be to iterate and parse the contents of the table and generate a string from the parsed data. You can check How to convert JSON to CSV format and store in a variablefor an example. You are using jQuery in your example so that would not count as an external plugin. Then, you just have to serve the resulting string using window.openand use application/octetstreamas suggested.

我的想法是迭代和解析表的内容并从解析的数据生成一个字符串。您可以查看如何将 JSON 转换为 CSV 格式并存储在变量中作为示例。您在示例中使用了 jQuery，因此它不算作外部插件。然后，您只需按照建议使用window.open和使用来提供结果字符串application/octetstream。

使用 jQuery 和 html 导出到 CSV

提问by GOK

回答by Terry Young

Demo

演示

As of 2017

截至 2017 年

On Internet Explorer

在 Internet Explorer 上

As of 2013

截至 2013 年

Notes

笔记

Compatibility

兼容性

Content Encoding

内容编码

回答by Action Dan

回答by Du?an Ma?ar

回答by Shahid wali

回答by Zlatin Zlatev

回答by MervS

相关推荐

最近更新

标签

使用 jQuery 和 html 导出到 CSV

提问by GOK

回答by Terry Young

Demo

演示

As of 2017

截至 2017 年

On Internet Explorer

在 Internet Explorer 上

As of 2013

截至 2013 年

Notes

笔记

Compatibility

兼容性

Content Encoding

内容编码

回答by Action Dan

回答by Du?an Ma?ar

回答by Shahid wali

回答by Zlatin Zlatev

回答by MervS

相关推荐

jQuery 表格行可以展开和关闭吗？

当新节点插入 dom 时，是否会触发 jquery 事件？

jQuery 动态 select2 不触发更改事件

jQuery 的“keypress”不适用于 Chrome 中的某些键。如何解决？

相关推荐

最近更新

标签