Introduction
This example shows how to export an HTML table to an Excel sheet using jquery table2excel plugin.
What is table2excel?
table2excel is a jQuery Plugin. it is used to export HTML tables to Excel sheets.
How to Include table2excel jQuery Plugin Within HTML File
- <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
- <script src="table2excel.js" type="text/javascript"></script>
table2excel.js Markup
Create one text document within the root directory of your project and save that document with name table2excel.js
After this, write the following code in table2excel.js file and save it.
-
- ;
- (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
- };
-
- function Plugin(element, options) {
- this.element = element;
-
-
-
-
-
- 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 = [];
-
- $(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) {
- var rc = {
- rows: $(this).attr("rowspan"),
- cols: $(this).attr("colspan"),
- flag: $(q).find(e.settings.exclude)
- };
- if (rc.flag.length > 0) {
- tempRows += "<td> </td>";
- } 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);
- });
-
- if (e.settings.exclude_img) {
- tempRows = exclude_img(tempRows);
- }
-
- if (e.settings.exclude_links) {
- tempRows = exclude_links(tempRows);
- }
-
- 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 + 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 = false || !!document.documentMode;
-
- if (isIE) {
- if (typeof Blob !== "undefined") {
-
- fullTemplate = e.format(fullTemplate, e.ctx);
- fullTemplate = [fullTemplate];
-
- var blob1 = new Blob(fullTemplate, {
- type: "text/html"
- });
- window.navigator.msSaveBlob(blob1, getFileName(e.settings));
- } else {
-
-
- 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");
- }
-
- 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 "";
- }
- });
- }
-
- function exclude_links(string) {
- return string.replace(/<a[^>]*>|<\/a>/gi, "");
- }
-
- 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));
- }
- });
-
- return e;
- };
- })(jQuery, window, document);
HTML Markup
Now, create another text document within the root directory of your project and save it with the name of "Example.html".
After this, write the following code within your created Example.html file and save it.
- <html
- xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <title></title>
- <style type="text/css">
- body
- {
- font-family: Arial;
- font-size: 10pt;
- }
- table
- {
- border: 1px solid #ccc;
- border-collapse: collapse;
- }
- table th
- {
- background-color: #ff7f00;
- color: #fff;
- font-weight: bold;
- }
- table th, table td
- {
- padding: 5px;
- border: 1px solid #ccc;
- }
- </style>
- </head>
- <body>
- <table id="tblEmployee" cellspacing="0" cellpadding="0">
- <tr>
- <th>Employee Id</th>
- <th>Employee Name</th>
- <th>Department</th>
- </tr>
- <tr>
- <td>1</td>
- <td>Nikunj Satasiya</td>
- <td>Asp.Net</td>
- </tr>
- <tr>
- <td>2</td>
- <td>Hiren Dobariya</td>
- <td>PHP</td>
- </tr>
- <tr>
- <td>3</td>
- <td>Vivek Ghadiya</td>
- <td>Android</td>
- </tr>
- <tr>
- <td>4</td>
- <td>Pratik Pansuriya</td>
- <td>SEO</td>
- </tr>
- </table>
- <br />
- <input type="button" id="btnExporttoExcel" value="Export To Excel" />
- <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
- <script src="table2excel.js" type="text/javascript"></script>
- <script type="text/javascript">
- $(function () {
- $("#btnExporttoExcel").click(function () {
- $("#tblEmployee").table2excel({
- filename: "Your_File_Name.xls"
- });
- });
- });
- </script>
- </body>
- </html>
NOTE
At the end of table tag </table>, you need to add the following script.
- $("#yourHtmTable").table2excel({
- exclude: ".excludeThisClass",
- name: "Worksheet Name",
- filename: "SomeFile"
- });
The screen after exporting HTML table to Excel sheet.
Summary
When the Export to Excel button is clicked, the jQuery table2excel plugin is applied to the created HTML table. The jQuery table2excel plugin accepts the file name as a parameter which sets the name of the Excel file.