Export AngularJs Table in Excel using JQuery in ASP.NET Web Forms

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.

Export AngularJs Table in Excel using JQuery in ASP.NET Web Forms

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.

Add Comments

More Settings

SAIDULU R

could u pls send me this code without angularjs (with jquery and c#)

December 17, 2019 at 7:27 PM

Codingvila

Hi We can give source code without angular js, please give your requirement by using our contact form. or you can mail us on info.codingvila@gmail.com

December 17, 2019 at 9:59 PM

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

Tutup Konverter! Emoticon