使用jQuery和html导出到CSV [英] Export to CSV using jQuery and html
问题描述
我有一个表格数据，我需要导出到csv而不使用任何外部插件或api。我使用 window.open 方法传递mime类型，但面临如下问题：
关于下载属性，请参阅：
测试
我测试的浏览器包括：
- Firefox 20+，Win / Mac（工作）
- Google Chrome 26+，Win / Mac（工作
- Safari 6，Mac（文件名问题未解决，但未生成JavaScript错误，文件内容完好无损）
- IE 9+我说更多）
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 Open Office 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 ms 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.
Here's the demo first, then explanations afterwards:
$(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 "Col1"</td>
<td>row5 "Col2"</td>
<td>row5 "Col3"</td>
</tr>
<tr>
<td>row6 "Col1"</td>
<td>row6 "Col2"</td>
<td>row6 "Col3"</td>
</tr>
</table>
</div>
Updates since 2017
Added use of HTML5 Blob and URL as the preferred method, and use of Data URI as a fallback.
I see other answers suggesting window.navigator.msSaveBlob
However, I am still reluctant to update my code to add support for it, simply because it crashes in IE10 on Window 7 and IE11 on Windows 10 (for me at least). It actually worked in Microsoft Edge though (but ironically crashed for another user: Microsoft Edge issue ticket #10396033).
The simple act of calling this in Microsoft's own Developer Tools / Console causes the browser to crash.
navigator.msSaveBlob(new Blob(["hello"], {type: "text/plain"}), "test.txt");
It's been 4 years since my first answer, 3 versions came out (IE10, 11, Edge), and Microsoft manages to have all of them crash on a function they invented (slow claps)
So, I am not including that in the solution.
But I am adding this warning though: add navigator.msSaveBlob support at your own risk.
Answer since 2013
I am curious as to why you cannot implement a server-side solution for this. Is there a reason?
But for the fun of it, here is my attempt on a client-side solution.
Just dumping the HTML as a Data URI will not work in this case. You will need to first convert the table contents into a valid CSV formatted string. (This is actually the easier part.)
But even after that, the next challenge is how to download it. For some reason I could not get the window.open approach to work in Firefox. So instead I used a different approach: <a href="{Data URI here}">
With the <a> tag, we have the option to assign a default file name using the download attribute, which is currently only feasible in Firefox and Google Chrome. Since it is just an attribute, it degrades gracefully.
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:
Tests
Browsers I've tested with include:
- Firefox 20+, Win/Mac (works)
- Google Chrome 26+, Win/Mac (works)
- Safari 6, Mac (filename issue unresolved but no javascript errors produced, file contents are intact)
- IE 9+ (fails by design, need I say more)
这篇关于使用jQuery和html导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
