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>
<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');
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>";
}
element.find("thead tr th").each(function () {
if (!$(this).hasClass("NoExport"))
tab_text = tab_text + "<td bgcolor='#87AFC6'>" +
$(this)[0].innerHTML + "</td>";
});
tab_text = tab_text + "</tr>";
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")) {
tab_text = tab_text + "<th>" + value + "</th>";
}
});
}
});
tab_text = tab_text + "</tr>";
});
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"))
{
$(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\./))
{
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
{
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