Introduction
This article gives an explanation about how to export an angular js table in an excel using jquery plugin with bootstrap 4 in asp.net web forms as well as also show how to bind an angular js table.
In my previous article, I explained the angular-js table with bootstrap 4 in asp.net web forms and how to upload a file using angular js as well as how to add new row dynamically in HTML table using angular js and in this article, I'll explain how to export angular js table in excel file using table2excel jQuery plugin.
Requirement
1) Explain what is table2excel plugin?
2) Explain how to bind data in the angular js table with bootsrap4?
3) Explain how to export an angular js table in an excel sheet?
Implementation
So, let's start with table2excel plugin and learn what is table2excel plugin as well as how to use this plugin for export angular js table in the excel sheet.
What is the table2excel plugin?
table2excel plugin is a JQuery plugin that is allowed to export HTML or angular js table in an excel sheet. The JQuery plugin table2excel also provides features like include or exclude images, links, as well as inputs fields, are available in the table while export that HTML or angular js table in an excel sheet, by default it will include all the images, links and input fields available in the table during export excel sheet.
This plugin also allows us to preserves the font color as well as the background color of the table. This plugin also provides a property "exclude" to exclude any specified row or data from the table during the export excel sheet.
You can download this plugin from the GitHub or you can create and use this plugin by writing the following code as shown in the script below and save the file with name table2excel.js
Script for table2excel plugin
(function ($, window, document, undefined) { var pluginName = "table2excel", defaults = { exclude: ".noExl", name: "Table2Excel", filename: "table2excel", fileext: ".xls", exclude_img: true, exclude_links: true, exclude_inputs: true }; // The actual plugin constructor function Plugin(element, options) { this.element = element; // jQuery has an extend method which merges the contents of two or // more objects, storing the result in the first object. The first object // is generally empty as we don't want to alter the default options for // future instances of the plugin // this.settings = $.extend({}, defaults, options); this._defaults = defaults; this._name = pluginName; this.init(); } Plugin.prototype = { init: function () { var e = this; var utf8Heading = "<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">"; e.template = { head: "<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\">" + utf8Heading + "<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>", sheet: { head: "<x:ExcelWorksheet><x:Name>", tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>" }, mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>", table: { head: "<table>", tail: "</table>" }, foot: "</body></html>" }; e.tableRows = []; // get contents of table except for exclude $(e.element).each(function (i, o) { var tempRows = ""; $(o).find("tr").not(e.settings.exclude).each(function (i, p) { tempRows += "<tr>"; $(p).find("td,th").not(e.settings.exclude).each(function (i, q) { // p did not exist, I corrected var rc = { rows: $(this).attr("rowspan"), cols: $(this).attr("colspan"), flag: $(q).find(e.settings.exclude) }; if (rc.flag.length > 0) { tempRows += "<td> </td>"; // exclude it!! } else { if (rc.rows & rc.cols) { tempRows += "<td>" + $(q).html() + "</td>"; } else { tempRows += "<td"; if (rc.rows > 0) { tempRows += " rowspan=\'" + rc.rows + "\' "; } if (rc.cols > 0) { tempRows += " colspan=\'" + rc.cols + "\' "; } tempRows += "/>" + $(q).html() + "</td>"; } } }); tempRows += "</tr>"; console.log(tempRows); }); // exclude img tags if (e.settings.exclude_img) { tempRows = exclude_img(tempRows); } // exclude link tags if (e.settings.exclude_links) { tempRows = exclude_links(tempRows); } // exclude input tags if (e.settings.exclude_inputs) { tempRows = exclude_inputs(tempRows); } e.tableRows.push(tempRows); }); e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName); }, tableToExcel: function (table, name, sheetName) { var e = this, fullTemplate = "", i, link, a; e.format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }); }; sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName; e.ctx = { worksheet: name || "Worksheet", table: table, sheetName: sheetName }; fullTemplate = e.template.head; if ($.isArray(table)) { for (i in table) { //fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail; fullTemplate += e.template.sheet.head + sheetName + i + e.template.sheet.tail; } } fullTemplate += e.template.mid; if ($.isArray(table)) { for (i in table) { fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail; } } fullTemplate += e.template.foot; for (i in table) { e.ctx["table" + i] = table[i]; } delete e.ctx.table; var isIE = /*@cc_on!@*/false || !!document.documentMode; // this works with IE10 and IE11 both :) //if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // this works ONLY with IE 11!!! if (isIE) { if (typeof Blob !== "undefined") { //use blobs if we can fullTemplate = e.format(fullTemplate, e.ctx); // with this, works with IE fullTemplate = [fullTemplate]; //convert to array var blob1 = new Blob(fullTemplate, { type: "text/html" }); window.navigator.msSaveBlob(blob1, getFileName(e.settings)); } else { //otherwise use the iframe and save //requires a blank iframe on page called txtArea1 txtArea1.document.open("text/html", "replace"); txtArea1.document.write(e.format(fullTemplate, e.ctx)); txtArea1.document.close(); txtArea1.focus(); sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings)); } } else { var blob = new Blob([e.format(fullTemplate, e.ctx)], { type: "application/vnd.ms-excel" }); window.URL = window.URL || window.webkitURL; link = window.URL.createObjectURL(blob); a = document.createElement("a"); a.download = getFileName(e.settings); a.href = link; document.body.appendChild(a); a.click(); document.body.removeChild(a); } return true; } }; function getFileName(settings) { return (settings.filename ? settings.filename : "table2excel"); } // Removes all img tags function exclude_img(string) { var _patt = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i; return string.replace(/<img[^>]*>/gi, function myFunction(x) { var res = _patt.exec(x); if (res !== null && res.length >= 2) { return res[2]; } else { return ""; } }); } // Removes all link tags function exclude_links(string) { return string.replace(/<a[^>]*>|<\/a>/gi, ""); } // Removes input params function exclude_inputs(string) { var _patt = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i; return string.replace(/<input[^>]*>|<\/input>/gi, function myFunction(x) { var res = _patt.exec(x); if (res !== null && res.length >= 2) { return res[2]; } else { return ""; } }); } $.fn[pluginName] = function (options) { var e = this; e.each(function () { if (!$.data(e, "plugin_" + pluginName)) { $.data(e, "plugin_" + pluginName, new Plugin(this, options)); } }); // chain jQuery functions return e; }; })(jQuery, window, document);
Now, let's create and bind an HTML table using an angular js directive.
Create and bind HTML table using angular js
Let's create and bind a simple HTML table using angular js in asp.net webform using the bootstrap 4.
Here, I'll show directly how to bind HTML table using angular js with bootstrap, for detailed information about how to create angular js table in asp.net web forms with master page concept, I have published my article about the same, you can visit that article "AngularJs Table with Bootstrap 4 in ASP.NET Web Forms" where I explained all the steps with detailed information.
So, First, you have to include jquery.min.js, as well as table2excel.js file in your project and then put the following lines in the master page of asp.net web forms application to link that javascript with your application.
Masterpage.Master
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script> <script src="scripts/table2excel.js" type="text/javascript"></script>
Now, You have to write the following javascript in the same file for download or export angular js HTML table in excel sheet.
<script type="text/javascript"> $(function () { $("#btnExporttoExcel").click(function () { $("#tblEmployee").table2excel({ filename: "Employee.xls", exclude_img: true, exclude_links: true, exclude_inputs: true }); }); }); </script>
Explanation
As you can see in the javascript above here, we have called a javascript function on click of btnExporttoExcel, we will use this button for export data in the excel sheet. Then we have called function table2excel for tblEmployee where tblEmployee is an angular js HTML table and we've passed parameters filename for the name of excel sheet, exclude image, links and inputs parameters for excluding image, links and inputs while export excels sheets.
Now, you have to write the following code in Default.aspx file, and create and bind HTML table using angular js.
Default.aspx
<div class="form-group"> <div class="container" ng-controller="EmployeeController" ng-app="app"> <div class="row"> <input type="button" id="btnExporttoExcel" class="btn btn-success" value="Export To Excel" /> </div> <br /> <div class="row"> <table id="tblEmployee" class="table table-hover"> <thead> <tr> <th scope="col">Employee Code</th> <th scope="col">Employee Name</th> <th scope="col">Joining Date</th> <th scope="col">Department</th> <th scope="col">Designation</th> <th scope="col">Salary</th> </tr> </thead> <tr ng-repeat="student in EmployeeData"> <th scope="row">{{student.EmpCode}} </th> <td>{{student.EmpName}} </td> <td>{{student.Joining}} </td> <td>{{student.Department}} </td> <td>{{student.Designation}} </td> <td>{{student.Salary}} </td> </tr> </table> </div> </div> </div>
Explanation
As you can see in the code above then we have created an HTML table and bound records of employees. Here we have used directive ng-repeat that is used for displaying an HTML table. we have also used a directive ng-app and it's described as the root element of the angular js application as well as we also used ng-controller directive that indicates a controller in angular js application there where ng-controller="EmployeeController" is an angular js directive. In angular js controller invoke with a $scope object where $scope is an application object.
Here, we have also created a simple button for export angular js HTML table in excel sheet and on click event of this button, we called a created javascript function for an export table in excel sheet.
Output
Summary
In this article we learned how to bind HTML table using angular js as well as how to export created angular js table with table2excel jQuery plugin.
