How To Export HTML Table To Excel Using jQuery Plugin

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 
  1. <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>  
  2. <script src="table2excel.js" type="text/javascript"></script>  
table2excel.js Markup
 
Create one text document within 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.
  1. //table2excel.js  
  2. ; (function ($, window, document, undefined) {  
  3.     var pluginName = "table2excel",  
  4.   
  5.     defaults = {  
  6.         exclude: ".noExl",  
  7.         name: "Table2Excel",  
  8.         filename: "table2excel",  
  9.         fileext: ".xls",  
  10.         exclude_img: true,  
  11.         exclude_links: true,  
  12.         exclude_inputs: true  
  13.     };  
  14.   
  15.     // The actual plugin constructor  
  16.     function Plugin(element, options) {  
  17.         this.element = element;  
  18.         // jQuery has an extend method which merges the contents of two or  
  19.         // more objects, storing the result in the first object. The first object  
  20.         // is generally empty as we don't want to alter the default options for  
  21.         // future instances of the plugin  
  22.         //  
  23.         this.settings = $.extend({}, defaults, options);  
  24.         this._defaults = defaults;  
  25.         this._name = pluginName;  
  26.         this.init();  
  27.     }  
  28.   
  29.     Plugin.prototype = {  
  30.         init: function () {  
  31.             var e = this;  
  32.   
  33.             var utf8Heading = "<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">";  
  34.             e.template = {  
  35.                 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>",  
  36.                 sheet: {  
  37.                     head: "<x:ExcelWorksheet><x:Name>",  
  38.                     tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>"  
  39.                 },  
  40.                 mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>",  
  41.                 table: {  
  42.                     head: "<table>",  
  43.                     tail: "</table>"  
  44.                 },  
  45.                 foot: "</body></html>"  
  46.             };  
  47.   
  48.             e.tableRows = [];  
  49.   
  50.             // get contents of table except for exclude  
  51.             $(e.element).each(function (i, o) {  
  52.                 var tempRows = "";  
  53.                 $(o).find("tr").not(e.settings.exclude).each(function (i, p) {  
  54.   
  55.                     tempRows += "<tr>";  
  56.                     $(p).find("td,th").not(e.settings.exclude).each(function (i, q) { // p did not exist, I corrected  
  57.   
  58.                         var rc = {  
  59.                             rows: $(this).attr("rowspan"),  
  60.                             cols: $(this).attr("colspan"),  
  61.                             flag: $(q).find(e.settings.exclude)  
  62.                         };  
  63.   
  64.                         if (rc.flag.length > 0) {  
  65.                             tempRows += "<td> </td>"// exclude it!!  
  66.                         } else {  
  67.                             if (rc.rows & rc.cols) {  
  68.                                 tempRows += "<td>" + $(q).html() + "</td>";  
  69.                             } else {  
  70.                                 tempRows += "<td";  
  71.                                 if (rc.rows > 0) {  
  72.                                     tempRows += " rowspan=\'" + rc.rows + "\' ";  
  73.                                 }  
  74.                                 if (rc.cols > 0) {  
  75.                                     tempRows += " colspan=\'" + rc.cols + "\' ";  
  76.                                 }  
  77.                                 tempRows += "/>" + $(q).html() + "</td>";  
  78.                             }  
  79.                         }  
  80.                     });  
  81.   
  82.                     tempRows += "</tr>";  
  83.                     console.log(tempRows);  
  84.   
  85.                 });  
  86.                 // exclude img tags  
  87.                 if (e.settings.exclude_img) {  
  88.                     tempRows = exclude_img(tempRows);  
  89.                 }  
  90.   
  91.                 // exclude link tags  
  92.                 if (e.settings.exclude_links) {  
  93.                     tempRows = exclude_links(tempRows);  
  94.                 }  
  95.   
  96.                 // exclude input tags  
  97.                 if (e.settings.exclude_inputs) {  
  98.                     tempRows = exclude_inputs(tempRows);  
  99.                 }  
  100.                 e.tableRows.push(tempRows);  
  101.             });  
  102.   
  103.             e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName);  
  104.         },  
  105.   
  106.         tableToExcel: function (table, name, sheetName) {  
  107.             var e = this, fullTemplate = "", i, link, a;  
  108.   
  109.             e.format = function (s, c) {  
  110.                 return s.replace(/{(\w+)}/g, function (m, p) {  
  111.                     return c[p];  
  112.                 });  
  113.             };  
  114.   
  115.             sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName;  
  116.   
  117.             e.ctx = {  
  118.                 worksheet: name || "Worksheet",  
  119.                 table: table,  
  120.                 sheetName: sheetName  
  121.             };  
  122.   
  123.             fullTemplate = e.template.head;  
  124.   
  125.             if ($.isArray(table)) {  
  126.                 for (i in table) {  
  127.                     //fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail;  
  128.                     fullTemplate += e.template.sheet.head + sheetName + i + e.template.sheet.tail;  
  129.                 }  
  130.             }  
  131.   
  132.             fullTemplate += e.template.mid;  
  133.   
  134.             if ($.isArray(table)) {  
  135.                 for (i in table) {  
  136.                     fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail;  
  137.                 }  
  138.             }  
  139.   
  140.             fullTemplate += e.template.foot;  
  141.   
  142.             for (i in table) {  
  143.                 e.ctx["table" + i] = table[i];  
  144.             }  
  145.             delete e.ctx.table;  
  146.   
  147.             var isIE = /*@cc_on!@*/false || !!document.documentMode; // this works with IE10 and IE11 both :)              
  148.             //if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // this works ONLY with IE 11!!!  
  149.             if (isIE) {  
  150.                 if (typeof Blob !== "undefined") {  
  151.                     //use blobs if we can  
  152.                     fullTemplate = e.format(fullTemplate, e.ctx); // with this, works with IE  
  153.                     fullTemplate = [fullTemplate];  
  154.                     //convert to array  
  155.                     var blob1 = new Blob(fullTemplate, { type: "text/html" });  
  156.                     window.navigator.msSaveBlob(blob1, getFileName(e.settings));  
  157.                 } else {  
  158.                     //otherwise use the iframe and save  
  159.                     //requires a blank iframe on page called txtArea1  
  160.                     txtArea1.document.open("text/html""replace");  
  161.                     txtArea1.document.write(e.format(fullTemplate, e.ctx));  
  162.                     txtArea1.document.close();  
  163.                     txtArea1.focus();  
  164.                     sa = txtArea1.document.execCommand("SaveAs"true, getFileName(e.settings));  
  165.                 }  
  166.   
  167.             } else {  
  168.                 var blob = new Blob([e.format(fullTemplate, e.ctx)], { type: "application/vnd.ms-excel" });  
  169.                 window.URL = window.URL || window.webkitURL;  
  170.                 link = window.URL.createObjectURL(blob);  
  171.                 a = document.createElement("a");  
  172.                 a.download = getFileName(e.settings);  
  173.                 a.href = link;  
  174.   
  175.                 document.body.appendChild(a);  
  176.   
  177.                 a.click();  
  178.   
  179.                 document.body.removeChild(a);  
  180.             }  
  181.   
  182.             return true;  
  183.         }  
  184.     };  
  185.   
  186.     function getFileName(settings) {  
  187.         return (settings.filename ? settings.filename : "table2excel");  
  188.     }  
  189.   
  190.     // Removes all img tags  
  191.     function exclude_img(string) {  
  192.         var _patt = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i;  
  193.         return string.replace(/<img[^>]*>/gi, function myFunction(x) {  
  194.             var res = _patt.exec(x);  
  195.             if (res !== null && res.length >= 2) {  
  196.                 return res[2];  
  197.             } else {  
  198.                 return "";  
  199.             }  
  200.         });  
  201.     }  
  202.   
  203.     // Removes all link tags  
  204.     function exclude_links(string) {  
  205.         return string.replace(/<a[^>]*>|<\/a>/gi, "");  
  206.     }  
  207.   
  208.     // Removes input params  
  209.     function exclude_inputs(string) {  
  210.         var _patt = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i;  
  211.         return string.replace(/<input[^>]*>|<\/input>/gi, function myFunction(x) {  
  212.             var res = _patt.exec(x);  
  213.             if (res !== null && res.length >= 2) {  
  214.                 return res[2];  
  215.             } else {  
  216.                 return "";  
  217.             }  
  218.         });  
  219.     }  
  220.   
  221.     $.fn[pluginName] = function (options) {  
  222.         var e = this;  
  223.         e.each(function () {  
  224.             if (!$.data(e, "plugin_" + pluginName)) {  
  225.                 $.data(e, "plugin_" + pluginName, new Plugin(this, options));  
  226.             }  
  227.         });  
  228.   
  229.         // chain jQuery functions  
  230.         return e;  
  231.     };  
  232.   
  233. })(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. 
  1. <html xmlns="http://www.w3.org/1999/xhtml">  
  2. <head>  
  3.     <title></title>  
  4.      <style type="text/css">  
  5.         body  
  6.         {  
  7.             font-family: Arial;  
  8.             font-size: 10pt;  
  9.         }  
  10.         table  
  11.         {  
  12.             border: 1px solid #ccc;  
  13.             border-collapse: collapse;  
  14.         }  
  15.         table th  
  16.         {  
  17.             background-color: #ff7f00;  
  18.             color: #fff;  
  19.             font-weight: bold;  
  20.         }  
  21.         table th, table td  
  22.         {  
  23.             padding: 5px;  
  24.             border: 1px solid #ccc;  
  25.         }  
  26.     </style>  
  27. </head>  
  28. <body>  
  29.     <table id="tblEmployee" cellspacing="0" cellpadding="0">  
  30.         <tr>  
  31.             <th>Employee Id</th>  
  32.             <th>Employee Name</th>  
  33.             <th>Department</th>  
  34.         </tr>  
  35.         <tr>  
  36.             <td>1</td>  
  37.             <td>Nikunj Satasiya</td>  
  38.             <td>Asp.Net</td>  
  39.         </tr>  
  40.         <tr>  
  41.             <td>2</td>  
  42.             <td>Hiren Dobariya</td>  
  43.             <td>PHP</td>  
  44.         </tr>  
  45.         <tr>  
  46.             <td>3</td>  
  47.             <td>Vivek Ghadiya</td>  
  48.             <td>Android</td>  
  49.         </tr>  
  50.         <tr>  
  51.             <td>4</td>  
  52.             <td>Pratik Pansuriya</td>  
  53.             <td>SEO</td>  
  54.         </tr>  
  55.     </table>  
  56.     <br />  
  57.     <input type="button" id="btnExporttoExcel" value="Export To Excel" />  
  58.     <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>  
  59.     <script src="table2excel.js" type="text/javascript"></script>  
  60.     <script type="text/javascript">  
  61.         $(function () {  
  62.             $("#btnExporttoExcel").click(function () {  
  63.                 $("#tblEmployee").table2excel({  
  64.                     filename: "Your_File_Name.xls"  
  65.                 });  
  66.             });  
  67.         });  
  68.     </script>  
  69. </body>  
  70. </html>  
NOTE

At the end of table tag </table>, you need to add the following script.
  1. $("#yourHtmTable").table2excel({    
  2.     exclude: ".excludeThisClass",    
  3.     name: "Worksheet Name",    
  4.     filename: "SomeFile" //do not include extension    
  5. });   
Screenshots/Output

Output 
 
The screen after exporting HTML table to Excel sheet.
 
Output 
 
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.
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now