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