JavaScript Export to Excel HTML Table with Input Tags
4.20/5 (4 votes)
This tip gives a brief of how to export a HTML table with input tags into Excel file using JavaScript.
Introduction
Recently, we faced a situation where we had a requirement to export the data from HTML table grid into an Excel file, i.e., whenever a user clicks on a button associated with the grid, all the data in the grid along with header and footer will get exported into an Excel file.
Most of the grid in our application had some input tags rendering in the table cells. We were using JQuery Datatable to render the grid throughout the application so that the user gets the functionalities like column sorting, etc. JQuery datatable provides the in-built functionality to export the grid into Excel file, but it was observed that it isn't used to export the value that used to assign to the input tag. Thus we needed a functionality where the grid will be exported with the value.
So we created a function that reads the value from all the input tags depending on the type of input tag. We achieved this by looping into each tr and td and depending upon the input tag fetching the value from that particular input field and then creating a separate HTML table string which is then used to export the HTML table using JQuery library.
Below is the code for the same along with the explanation of how to use it in different scenarios.
Using the Code
The code can be used in a simple way by just calling the function and passing the table DOM element as a parameter on click of a button or else on some events. In some cases, we don't have to export certain column or certain fields, so we use a set of custom defined class names that recognizes certain columns and input tags respectively. Below is the code for the same.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
// HTML Code
<input type="button" id="btnExport" value="Export" />
<table id="tblExportGrid" export-excel-filename="New Document">
<thead>
<th> First Column </th>
<th> Second Column </th>
<th class="NoExport"> Third Column </th>
</thead>
<tbody>
<tr>
<td><input type="text" value="First 1" /></td>
<td class="ExportLabelTD"> Second 1 </td>
<td class="NoExport"> Thrid 1 </td>
</tr>
<tr>
<td><input type="text" value="First 2" /></td>
<td class="ExportLabelTD"> Second 2 </td>
<td class="NoExport"> Thrid 2 </td>
</tr>
</tbody>
<tfoot>
<tr>
<td class="ExportValueTD"><input type="text" value="Foot First 1" /></td>
<td> Foot Second 1 </td>
<td class="NoExport"> Foot Thrid 1 </td>
</tr>
</tfoot>
</table>
// JavaScript Code
<script>
$("#btnExport").click(function(){
var $table = $("#tblExportGrid");
ExportHTMLTableToExcel($table);
});
function ExportHTMLTableToExcel($table) {
var tab_text = ""
var final_text = "";
var filename = $table.attr('export-excel-filename'); // attribute to be
// applied on Table tag
filename = isNullOrUndefinedWithEmpty(filename)? "Excel Document" : filename;
var index = $table.find("tbody tr").length;
if (Number(index) > 0) {
$.each($table, function (index, item) {
var element = $(item);
var headertext = $("#" + element[0].id).closest
(":has(label.HeaderLabel)").find('label').text().trim();
if (headertext == "") {
tab_text = "<table border='2px'><tr>";
}
else {
tab_text = "<table border='2px'><tr> " + headertext + "</tr><tr>";
}
// Create column header
element.find("thead tr th").each(function () {
if (!$(this).hasClass("NoExport"))
tab_text = tab_text + "<td bgcolor='#87AFC6'>" +
$(this)[0].innerHTML + "</td>";
});
//Close column header
tab_text = tab_text + "</tr>";
// Create body column
element.find(" tbody tr").each(function () {
tab_text = tab_text + "<tr>";
$(this).find("td").each(function () {
if ($(this).hasClass("ExportLabelTD"))
{
var value = $(this).html();
tab_text = tab_text + "<th>" + value + "</th>";
}
else {
$(this).find("input,select").each(function () {
var value = "";
if ($(this).prop("type") == 'select-one') {
value = $('option:selected', this).text();
} else {
value = $(this).val();
}
if (!$(this).closest("td").hasClass("NoExport") &&
!$(this).hasClass("NoExport")) { // NoExport is used for TD
// or tan input tag that not needs to be exported
tab_text = tab_text + "<th>" + value + "</th>";
}
});
}
});
tab_text = tab_text + "</tr>";
});
// Create colum footer
element.find("tfoot tr td").each(function () {
var colspan = $(this).attr("colspan");
var rowspan = $(this).attr("rowspan");
colspan = colspan == undefined ? 1 : colspan;
rowspan = rowspan == undefined ? 1 : rowspan;
if ($(this).hasClass("NoExport")) {
tab_text = tab_text + "";
}
else if ($(this).hasClass("ExportValueTD")) // Footer class that needs
// to be no td that have input tags
{
$(this).find("input,select").each(function () {
var value = "";
if ($(this).prop("type") == 'select-one') {
value = $('option:selected', this).text();
} else {
value = $(this).val();
}
if (!$(this).closest("td").hasClass("NoExport") &&
!$(this).hasClass("NoExport")) {
tab_text = tab_text + "<td colspan=" + colspan + "
rowspan=" + rowspan + ">" + value + "</th>";
}
});
}
else
tab_text = tab_text + "<td colspan=" + colspan + "
rowspan=" + rowspan + ">" + $(this).html() + "</td>";
});
tab_text = tab_text + "<tr></tr></table>";
if (index == 0) {
final_text = tab_text;
}
else {
final_text = final_text + tab_text;
}
});
var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");
if (msie > 0 || !!navigator.userAgent.match
(/Trident.*rv\:11\./)) // If Internet Explorer
{
txtArea1 = window.open();
txtArea1.document.open("txt/html", "replace");
txtArea1.document.write(final_text);
txtArea1.document.close();
txtArea1.focus();
sa = txtArea1.document.execCommand("SaveAs", true, filename+".xls");
return (sa);
}
else //other browser not tested on IE 11
{
//sa = window.open('data:application/vnd.ms-excel,' +
// encodeURIComponent(final_text));
var anchor = document.createElement('a');
anchor.setAttribute('href', 'data:application/vnd.ms-excel,' +
encodeURIComponent(final_text));
anchor.setAttribute('download', filename);
anchor.style.display = 'none';
document.body.appendChild(anchor);
anchor.click();
document.body.removeChild(anchor);
}
}
}
function isNullOrUndefinedWithEmpty(text){
if(text==undefined)
return true;
else if(text == null)
return true;
else if(text == null)
return true;
else
false;
}
</script>
//--------------------------------------------------------
Following are some of the class names with the descriptions that are used in the above code:
| Class Name | Description |
NoExport | Column or Input tag element that needs to be skipped while exporting Excel |
ExportLabelTD | <td> that doesn't contain any input tags, i.e., <td> with plain text |
ExportValueTD | <td> in footer that contains input tag |
History
- 21st October, 2019: Initial version