jQuery 将 html 表导出到 csv

export html table to csv

提问by sam

I am trying to add a feature of csv download option in my website. It should convert the html table present in the website in to csv content and make it downloadable. Ive been searching through internet for a good plugin and found some usefule ones like http://www.dev-skills.com/export-html-table-to-csv-file/But it uses php script to do the download part. I was wondering if there is a pure javascript library available to do this feature using server side softwares like node.js without the use of php??

我正在尝试在我的网站中添加 csv 下载选项的功能。它应该将网站中存在的 html 表转换为 csv 内容并使其可下载。我一直在互联网上搜索一个好的插件，发现了一些有用的插件，比如http://www.dev-skills.com/export-html-table-to-csv-file/但它使用 php 脚本来完成下载部分。我想知道是否有一个纯 javascript 库可用于使用 node.js 等服务器端软件而不使用 php 来执行此功能？

回答by melancia

Using just jQuery, vanilla Javascript, and the table2CSVlibrary:

使用 just jQuery、 vanillaJavascript和table2CSV库：

export-to-html-table-as-csv-file-using-jquery

导出到 html-table-as-csv-file-using-jquery

Put this code into a script to be loaded in the headsection:

将此代码放入要在该head部分加载的脚本中：

 $(document).ready(function () {
    $('table').each(function () {
        var $table = $(this);

        var $button = $("<button type='button'>");
        $button.text("Export to spreadsheet");
        $button.insertAfter($table);

        $button.click(function () {
            var csv = $table.table2CSV({
                delivery: 'value'
            });
            window.location.href = 'data:text/csv;charset=UTF-8,' 
            + encodeURIComponent(csv);
        });
    });
})

Notes:

笔记：

Requires jQueryand table2CSV: Add script references to both libraries before the script above.

需要jQuery和table2CSV：在上述脚本之前添加对这两个库的脚本引用。

The tableselector is used as an example, and can be adjusted to suit your needs.

该table选择作为一个例子，可以调整，以满足您的需求。

It only works in browsers with full Data URIsupport: Firefox, Chrome and Opera, not in IE, which only supports Data URIsfor embedding binary image data into a page.

它仅适用于完全Data URI支持的浏览器：Firefox、Chrome 和 Opera，不适用于 IE，后者仅支持Data URIs将二进制图像数据嵌入到页面中。

For full browser compatibility you would have to use a slightly different approach that requires a server side script to echothe CSV.

为了完全兼容浏览器，您将不得不使用一种稍微不同的方法，该方法需要一个服务器端脚本到echoCSV。

回答by Calumah

Should work on every modern browser and without jQuery or any dependency, here my implementation :

应该适用于所有现代浏览器，无需 jQuery 或任何依赖项，这里是我的实现：

// Quick and simple export target #table_id into a csv
function download_table_as_csv(table_id) {
    // Select rows from table_id
    var rows = document.querySelectorAll('table#' + table_id + ' tr');
    // Construct csv
    var csv = [];
    for (var i = 0; i < rows.length; i++) {
        var row = [], cols = rows[i].querySelectorAll('td, th');
        for (var j = 0; j < cols.length; j++) {
            // Clean innertext to remove multiple spaces and jumpline (break csv)
            var data = cols[j].innerText.replace(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/gm, ' ')
            // Escape double-quote with double-double-quote (see https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv)
            data = data.replace(/"/g, '""');
            // Push escaped string
            row.push('"' + data + '"');
        }
        csv.push(row.join(';'));
    }
    var csv_string = csv.join('\n');
    // Download it
    var filename = 'export_' + table_id + '_' + new Date().toLocaleDateString() + '.csv';
    var link = document.createElement('a');
    link.style.display = 'none';
    link.setAttribute('target', '_blank');
    link.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(csv_string));
    link.setAttribute('download', filename);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}

Then add your download button/link :

然后添加您的下载按钮/链接：

<a href="#" onclick="download_table_as_csv('my_id_table_to_export');">Download as CSV</a>

CSV file is timedated and compatible with default Excel format.

CSV 文件带有时间限制并与默认 Excel 格式兼容。

回答by Buddhika Ariyaratne

There is a very easy free and open source solution at http://jordiburgos.com/post/2014/excellentexport-javascript-export-to-excel-csv.html

http://jordiburgos.com/post/2014/excellentexport-javascript-export-to-excel-csv.html 上有一个非常简单的免费开源解决方案

First download the javascript file and sample files from https://github.com/jmaister/excellentexport/releases/tag/v1.4

首先从https://github.com/jmaister/excellentexport/releases/tag/v1.4下载javascript文件和示例文件

The html page looks like below.

html 页面如下所示。

Make sure the the javascript file is in the same folder or change the path of the script in the html file accordingly.

确保 javascript 文件在同一文件夹中或相应地更改 html 文件中脚本的路径。

<html>
<head>
    <title>Export to excel test</title>
    <script src="excellentexport.js"></script>
    <style>
        table, tr, td {
            border: 1px black solid;
        }
    </style>
</head>
<body>
    <h1>ExcellentExport.js</h1>

    Check on <a href="http://jordiburgos.com">jordiburgos.com</a> and  <a href="https://github.com/jmaister/excellentexport">GitHub</a>.

    <h3>Test page</h3>

    <br/>

    <a download="somedata.xls" href="#" onclick="return ExcellentExport.excel(this, 'datatable', 'Sheet Name Here');">Export to Excel</a>
    <br/>

    <a download="somedata.csv" href="#" onclick="return ExcellentExport.csv(this, 'datatable');">Export to CSV</a>
    <br/>

    <table id="datatable">
        <tr>
            <th>Column 1</th>
            <th>Column "cool" 2</th>
            <th>Column 3</th>
        </tr>
        <tr>
            <td>100,111</td>
            <td>200</td>
            <td>300</td>
        </tr>
        <tr>
            <td>400</td>
            <td>500</td>
            <td>600</td>
        </tr>
        <tr>
            <td>Text</td>
            <td>More text</td>
            <td>Text with
                new line</td>
        </tr>
    </table>

</body>

It is very easy to use this as I have tried most of the other methods.

使用它非常容易，因为我已经尝试了大多数其他方法。

回答by Italo Borssatto

You don't need PHP script on server side. Do that in the client side only, in browsers that accept Data URIs:

您不需要服务器端的 PHP 脚本。仅在客户端执行此操作，在接受数据 URI 的浏览器中：

data:application/csv;charset=utf-8,content_encoded_as_url

The Data URI will be something like:

数据 URI 将类似于：

data:application/csv;charset=utf-8,Col1%2CCol2%2CCol3%0AVal1%2CVal2%2CVal3%0AVal11%2CVal22%2CVal33%0AVal111%2CVal222%2CVal333

You can call this URI by:

您可以通过以下方式调用此 URI：

• using window.open
• or setting the window.location
• or by the href of an anchor
• by adding the download attribute it will work in chrome, still have to test in IE.

• 使用 window.open
• 或设置 window.location
• 或通过锚点的 href
• 通过添加下载属性，它可以在 chrome 中工作，仍然需要在 IE 中进行测试。

To test, simply copy the URIs above and paste in your browser address bar. Or test the anchor below in a HTML page:

要进行测试，只需复制上面的 URI 并粘贴到您的浏览器地址栏中。或者在 HTML 页面中测试以下锚点：

<a download="somedata.csv" href="data:application/csv;charset=utf-8,Col1%2CCol2%2CCol3%0AVal1%2CVal2%2CVal3%0AVal11%2CVal22%2CVal33%0AVal111%2CVal222%2CVal333">Example</a>

To create the content, getting the values from the table, you can use table2CSVmentioned by MelanciaUKand do:

要创建内容，从表中获取值，您可以使用MelanciaUK提到的table2CSV并执行以下操作：

var csv = $table.table2CSV({delivery:'value'});
window.location.href = 'data:application/csv;charset=UTF-8,' + encodeURIComponent(csv);

回答by jzhou

I found there is a library for this. See example here:

我发现有一个图书馆。请参阅此处的示例：

https://editor.datatables.net/examples/extensions/exportButtons.html

https://editor.datatables.net/examples/extensions/exportButtons.html

In addition to the above code, the following Javascript library files are loaded for use in this example:

除了上述代码之外，还加载了以下 Javascript 库文件以用于本示例：

In HTML, include following scripts:

在 HTML 中，包括以下脚本：

jquery.dataTables.min.js   
dataTables.editor.min.js   
dataTables.select.min.js
dataTables.buttons.min.js  
jszip.min.js    
pdfmake.min.js
vfs_fonts.js  
buttons.html5.min.js    
buttons.print.min.js

Enable buttons by adding scripts like:

通过添加如下脚本来启用按钮：

<script>
$(document).ready( function () {
    $('#table-arrays').DataTable({
        dom: '<"top"Blf>rt<"bottom"ip>',
        buttons: ['copy', 'excel', 'csv', 'pdf', 'print'],
        select: true,
    });
} );
</script>

For some reason, the excel export results in corrupted file, but can be repaired. Alternatively, disable excel and use csv export.

出于某种原因，excel 导出导致文件损坏，但可以修复。或者，禁用 excel 并使用 csv 导出。

回答by Roger Perez

Used the answer above, but altered it for my needs.

使用了上面的答案，但根据我的需要对其进行了更改。

I used the following function and imported to my REACTfile where I needed to download the csv file.

我使用了以下函数并导入到我需要下载 csv 文件的REACT文件中。

I had a spantag within my thelements. Added comments to what most functions/methods do.

span我的th元素中有一个标签。添加了对大多数函数/方法所做的评论。

import { tableToCSV, downloadCSV } from './../Helpers/exportToCSV';


export function tableToCSV(){
  let tableHeaders = Array.from(document.querySelectorAll('th'))
    .map(item => {
      // title = splits elem tags on '\n',
      // then filter out blank "" that appears in array.
      // ex ["Timestamp", "[Full time]", ""]
      let title = item.innerText.split("\n").filter(str => (str !== 0)).join(" ")
      return title
    }).join(",")

  const rows = Array.from(document.querySelectorAll('tr'))
  .reduce((arr, currRow) => {
    // if tr tag contains th tag.
    // if null return array.
    if (currRow.querySelector('th')) return arr

    // concats individual cells into csv format row.
    const cells = Array.from(currRow.querySelectorAll('td'))
      .map(item => item.innerText)
      .join(',')
    return arr.concat([cells])
  }, [])

return tableHeaders + '\n' + rows.join('\n')
}

export function downloadCSV(csv){
  const csvFile = new Blob([csv], { type: 'text/csv' })
  const downloadLink =  document.createElement('a')
  // sets the name for the download file
  downloadLink.download = `CSV-${currentDateUSWritten()}.csv`
  // sets the url to the window URL created from csv file above
  downloadLink.href = window.URL.createObjectURL(csvFile)
  // creates link, but does not display it.
  downloadLink.style.display = 'none'
  // add link to body so click function below works
  document.body.appendChild(downloadLink)

  downloadLink.click()
}

When user click export to csv it trigger the following function in react.

当用户单击导出到 csv 时，它会在反应中触发以下功能。

  handleExport = (e) => {
    e.preventDefault();
    const csv = tableToCSV()
    return downloadCSV(csv)
  }

Example html table elems.

示例 html 表元素。

  <table id="datatable">
        <tbody>
          <tr id="tableHeader" className="t-header">
            <th>Timestamp
              <span className="block">full time</span></th>
            <th>current rate
              <span className="block">alt view</span>
            </th>
            <th>Battery Voltage
              <span className="block">current voltage
              </span>
            </th>
            <th>Temperature 1
              <span className="block">[C]</span>
            </th>
            <th>Temperature 2
              <span className="block">[C]</span>
            </th>
            <th>Time & Date </th>
          </tr>

        </tbody>
        <tbody>
          {this.renderData()}
        </tbody>
      </table>
    </div>

回答by dabeng

(1)This is the native javascript solution for this issue. It works on most of modern browsers.

(1)这是针对这个问题的原生javascript解决方案。它适用于大多数现代浏览器。

function export2csv() {
  let data = "";
  const tableData = [];
  const rows = document.querySelectorAll("table tr");
  for (const row of rows) {
    const rowData = [];
    for (const [index, column] of row.querySelectorAll("th, td").entries()) {
      // To retain the commas in the "Description" column, we can enclose those fields in quotation marks.
      if ((index + 1) % 3 === 0) {
        rowData.push('"' + column.innerText + '"');
      } else {
        rowData.push(column.innerText);
      }
    }
    tableData.push(rowData.join(","));
  }
  data += tableData.join("\n");
  const a = document.createElement("a");
  a.href = URL.createObjectURL(new Blob([data], { type: "text/csv" }));
  a.setAttribute("download", "data.csv");
  document.body.appendChild(a);
  a.click();
  document.body.removeChild(a);
}

table {
  border-collapse: collapse;
}

td, th {
  border: 1px solid #aaa;
  padding: 0.5rem;
  text-align: left;
}

td {
  font-size: 0.875rem;
}

.btn-group {
  padding: 1rem 0;
}

button {
  background-color: #fff;
  border: 1px solid #000;
  margin-top: 0.5rem;
  border-radius: 3px;
  padding: 0.5rem 1rem;
  font-size: 1rem;
}

button:hover {
  cursor: pointer;
  background-color: #000;
  color: #fff;
}

<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Author</th>
      <th>Description</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>jQuery</td>
      <td>John Resig</td>
      <td>The Write Less, Do More, JavaScript Library.</td>
    </tr>
    <tr>
      <td>React</td>
      <td>Jordan Walke</td>
      <td>React makes it painless to create interactive UIs.</td>
    </tr>
    <tr>
      <td>Vue.js</td>
      <td>Yuxi You</td>
      <td>The Progressive JavaScript Framework.</td>
    </tr>
  </tbody>
</table>

<div class="btn-group">
  <button onclick="export2csv()">csv</button>
</div>

(2) If you want a pure javascript library, FileSaver.jscould help you save the code snippets for triggering file download. Besides, FileSaver.js will not be responsible for constructing content for exporting. You have to construct the content by yourself in the format you want.

(2) 如果你想要一个纯javascript库，FileSaver.js可以帮你保存触发文件下载的代码片段。此外，FileSaver.js 将不负责构建导出内容。您必须以您想要的格式自行构建内容。

回答by MorningZ

I used Calumah's function posted above, but I did run into an issue with his code as poisted.

我使用了上面发布的 Calumah 的函数，但我确实遇到了他的代码问题。

The rows are joined with a semicolon

行用分号连接

csv.push(row.join(';'));

but the link generated has "text/csv" as the content type

但生成的链接的内容类型为“text/csv”

Maybe in Windows that isn't a problem, but in Excel for Mac that throws things off. I changed the array join to a comma and it worked perfect.

也许在 Windows 中这不是问题，但在 Excel for Mac 中却会出现问题。我将数组连接更改为逗号，并且效果很好。