I have made a code to convert HTML table data into an excel file and it works fine. It creates the file successfully
So what's the problem
When I try to open the excel file it shows an error that says.
Excel cannot open the file filename.xlsx because the file format or the file extension not valid.
Below is the code that I am using
HTML Code
<table id="tblexportData" class="table">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Age</th>
<th>Mobile</th>
</tr>
</thead>
<tbody>
<tr>
<td>Robert</td>
<td>robert@gmail.com</td>
<td>26</td>
<td>9999999999</td>
</tr>
<tr>
<td>Michael</td>
<td>michael@gmail.com</td>
<td>24</td>
<td>9999999999</td>
</tr>
<tr>
<td>Julie</td>
<td>julie@gmail.com</td>
<td>36</td>
<td>9999999999</td>
</tr>
<tr>
<td>Trevor</td>
<td>trevor@gmail.com</td>
<td>28</td>
<td>9999999999</td>
</tr>
</tbody>
</table>
<button onclick="exportToExcel('tblexportData', 'user-data')" class="btn btn-success example-screen">Export Table Data To Excel File</button>
Javascript Code
function exportToExcel(tableID, filename = ''){
var downloadurl;
var dataFileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
var tableSelect = document.getElementById(tableID);
var tableHTMLData = tableSelect.outerHTML.replace(/ /g, '%20');
console.log(tableSelect.outerHTML);
console.log(tableSelect.outerHTML.replace(/ /g, '%20'));
// Specify file name
filename = filename?filename+'.xlsx':'export_excel_data.xlsx';
// Create download link element
downloadurl = document.createElement("a");
document.body.appendChild(downloadurl);
if(navigator.msSaveOrOpenBlob){
console.log('msSave if');
var blob = new Blob(['\ufeff', tableHTMLData], {
type: dataFileType
});
navigator.msSaveOrOpenBlob( blob, filename);
}else{
console.log('msSave else');
// Create a link to the file
downloadurl.href = 'data:' + dataFileType + ', ' + tableHTMLData;
// Setting the file name
downloadurl.download = filename;
//triggering the function
downloadurl.click();
}
}
Note: - I have tried application/vnd.ms-excel for xls and it is working but I is not working with application/vnd.openxmlformats-officedocument.spreadsheetml.sheet i.e. for xlsx.
if you are using kendo-UI it native support export to excel export. kendo Excell
if not, I'm using this method:
$scope.exportData = function () {
debugger;
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet ><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayRightToLeft/><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
if ($scope.mororList)
var table = document.getElementById("Grid");
else
var table = document.getElementById("OtherGrid");
var filters = $('.k-header .k-grid-toolbar').remove();
var newtable = table.childNodes[1].innerHTML + table.childNodes[2].innerHTML
newtable = "<table style='direction:rtl'>" + newtable + "</table>";
$('.k-rtl .k-grid .k-widget').after(filters);
var ctx = { worksheet: name || 'Worksheet', table: newtable };
var url = uri + base64(format(template, ctx));
var a = document.createElement('a');
a.href = url;
a.download = 'excelName.xls';
a.target = "_blank";
document.body.appendChild(a);
a.onclick = "aclickfn";
a.click();
document.body.removeChild(a);
setTimeout(function () {
}, 200);
};