Menu
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.
<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>
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.
655
right-click anywhere in the table and select 'copy whole table' start up a spreadsheet application such as LibreOffice Calc. paste into the spreadsheet (select appropriate separator character as needed) save/export the spreadsheet as CSV.
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 "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>Now uses HTML5 Blob and URL as the preferred method with Data URI as a fallback.
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.msSaveBlobsupport at your own risk.
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:
window.open approach would not work in Firefox, so I used <a href="{Data URI here}">.<a> tag's download attribute, which only works in Firefox and Google Chrome. Since it is just an attribute, it degrades gracefully.About the "download" attribute, see these:
Browsers testing includes:
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:
166
I am not sure if the above CSV generation code is so great as it appears to skip th cells and also did not appear to allow for commas in the value. So here is my CSV generation code that might be useful.
It does assume you have the $table variable which is a jQuery object (eg. 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';
}
<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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
