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.
This question is related to
jquery
html
csv
export
export-to-csv
The answer is
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/Table As you are already using jQuery library I have assumed you are able to add other javascript toolkit libraries.
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.
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 tiny update for @Terry Young answer, i.e. add IE 10+ support
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'
});
}
<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>
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.
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 variable for 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.open and use application/octetstream as suggested.
Source: Stackoverflow.com