使用jQuery和html导出为CSV [英] Export to CSV using jQuery and html
问题描述
我有一个表格数据，我需要导出到csv而不使用任何外部插件或api。我使用传递mime类型的 window.open 方法，但遇到下列问题：
）。
在Microsoft自己的开发工具/控制台中调用此简单行为导致浏览器崩溃。 >
navigator.msSaveBlob（new Blob（[hello]，{type：text / plain}），test.txt ）;
自从我第一次回答以来，已经有4年了，出现了3个版本（IE10,11，Edge）微软设法使所有这些都发生在他们发明的功能上（慢拍）
所以，我不包括在解决方案
但是，我添加了此警告：添加 navigator.msSaveBlob 支持您自担风险。 >
自2013年以来的答案
我是好奇，为什么你不能实现一个服务器端解决方案。有理由吗？
但是为了乐趣，这里是我尝试使用客户端解决方案。
在这种情况下，将HTML转换为数据URI将不起作用。您将需要首先将表格内容转换为有效的CSV格式的字符串。 （这实际上是更容易的部分。）
但即使如此，下一个挑战是如何下载它。由于某些原因，我无法获得在Firefox中使用的 window.open 方法。所以我使用了一种不同的方法：< a href ={Data URI here}>
使用< a> 标签，我们可以选择使用下载属性指定默认文件名，目前仅在Firefox和Google Chrome中可行。由于它只是一个属性，它会优雅地降级。
注释
- 您可以将链接的样式看起来像一个按钮。我会留下你的努力
- IE具有数据URI限制。请参阅：数据URI方案和Internet Explorer 9错误
-
关于下载属性，请参阅以下内容：
测试
我测试过的浏览器包含：
- Firefox 20+，Win / Mac（作品）
- Google Chrome 26+，Win / Mac（
works ） - 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屋！
