In this article, we will see how we can convert HTML table into excel file using pure javascript function and using jQuery plugin.
If you are looking to export jQuery Datatable into excel file you can refer the answer described here https://qawithexperts.com/questions/228/how-to-export-html-table-to-pdf-excel-in-jquery-datatable
Export HTML table to excel (xls) using JavaSript
Suppose below is our HTML table to be exported
<table id="tblToExport" cellspacing="0" cellpadding="0">
<tr>
<th>Customer Id</th>
<th>Name</th>
<th>Country</th>
</tr>
<tr>
<td>1</td>
<td>Benjamin Hammond</td>
<td>United States</td>
</tr>
<tr>
<td>2</td>
<td>Vikas Lalwani</td>
<td>India</td>
</tr>
<tr>
<td>3</td>
<td>Martin Mathews</td>
<td>France</td>
</tr>
<tr>
<td>4</td>
<td>Geoffry Schidner</td>
<td>Canada</td>
</tr>
</table>
<br />
<button id="btnExport" onclick="exportToExcel()"> Export to XLS</button>And here is the javascript function which takes HTML table and convert's it into excel file.
<script>
function exportToExcel(){
var htmls = "";
var uri = 'data:application/vnd.ms-excel;base64,';
var 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:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>';
var base64 = function(s) {
return window.btoa(unescape(encodeURIComponent(s)))
};
var format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
};
htmls = document.getElementById("tblToExport").innerHTML;
var ctx = {
worksheet : 'Worksheet',
table : htmls
}
var link = document.createElement("a");
link.download = "export.xls";
link.href = uri + base64(format(template, ctx));
link.click();
}
</script>That's it we are done, you can test it in your browser, output will be as below
Export HTML table to Excel using jQuery plugin
The above Javascript method is good if you want to export HTML table to excel which has few hundred rows, and you don't need to apply inline CSS in Excel, but what if we have thousand of HTML table rows, I think it is better to use jQuery plugin like Datatable and export jQuery datatable table into excel or you can use jQuery plugin table2excel.js to make everything easy.
Suppose we have same HTML table as above with little bit changed
<table id="resultsTable" data-tableName="Test Table 2" class="table table-bordered">
<thead>
<tr class="noExl">
<th>Not Geting exported</th>
<th>no getting exported either</th>
</tr>
<tr>
<th>Name(as Header)</th>
<th>Country(as header)</th>
</tr>
</thead>
<tbody>
<tr>
<td>Benjamin Hammond</td>
<td>United States</td>
</tr>
<tr>
<td>Vikas Lalwani</td>
<td>India</td>
</tr>
<tr>
<td>Martin Mathews</td>
<td>France</td>
</tr>
<tr>
<td>Geoffry Schidner</td>
<td>England</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="2">This footer spans 2 cells</td>
</tr>
</tfoot>
</table>
<div class="row">
<div class="col-md-2">
<a class="btn btn-default" id="exportNow">Export</a>
</div>
<div class="col-md-10"></div>
</div>jQuery and CSS files required to export this table(I am using bootstrap.css just to use some classes of it in HTML and need jQuery as we will use table2excel jQuery plugin which requires jQuery)
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<script src="https://cdn.rawgit.com/unconditional/jquery-table2excel/master/src/jquery.table2excel.js"></script>Complete HTML
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<script src="https://cdn.rawgit.com/unconditional/jquery-table2excel/master/src/jquery.table2excel.js"></script>
<table id="resultsTable" data-tableName="Test Table 2" class="table table-bordered">
<thead>
<tr class="noExl">
<th>Not Geting exported</th>
<th>no getting exported either</th>
</tr>
<tr>
<th>Name(as Header)</th>
<th>Country(as header)</th>
</tr>
</thead>
<tbody>
<tr>
<td>Benjamin Hammond</td>
<td>United States</td>
</tr>
<tr>
<td>Vikas Lalwani</td>
<td>India</td>
</tr>
<tr>
<td>Martin Mathews</td>
<td>France</td>
</tr>
<tr>
<td>Geoffry Schidner</td>
<td>England</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="2">This footer spans 2 cells</td>
</tr>
</tfoot>
</table>
<div class="row">
<div class="col-md-2">
<a class="btn btn-default" id="exportNow">Export</a>
</div>
<div class="col-md-10"></div>
</div>Now we just need to initialize & call plugin to export data to excel on button click
jQuery(document).ready(function() {
$('#exportNow').on('click', function(e){
e.preventDefault();
ResultsToTable();
});
function ResultsToTable(){
$("#resultsTable").table2excel({
exclude: ".noExl", //this class data should not be excluded in excel
filename: "Results"
});
}
});Executing the above script with HTML data gives output as below, here is the working fiddle
That's it, we are done, hope it helps you.
Buy us a coffee
Become a Patron