Export HTML Table to xlsx using jQuery

Category: Text & Input | September 13, 2020
Export HTML Table to xlsx using jQuery
Author: Michael Richardson
Official Page: Go to Website
Last Update:September 13, 2020
License:MIT

The “table2excel” is a well developed and lightweight jQuery plugin to export HTML table to xlsx file. The plugin comes with multiple configuration options to export the table with custom settings. It can be integrated with any data-rich HTML table and that table can be exported in one click.

The plugin (programmatically) allows you to set a custom filename for exported files. Similarly, you can also set a custom file extension (xls or xlsx). Besides this, you can exclude a specific table row to export. Likewise, there is an option to include/exclude images and links placed inside the HTML table.

In short, this plugin fulfills all the basic requirements to export an HTML table to an Excel sheet. If you are working on tables, also check out this pagination project for an HTML table.

Create an HTML Table

First of all, create an HTML table or add a class name "table2xcel" to your existing table. Similarly, create HTML5 data-attribute “data-tableName” and specify table name. If you want to exclude a row, you can add a class name “noExl” to that row. Create a button element with the class name "exportToExcel" at the end of your table. This button will be used to export the HTML table to an Excel file.

<table class="table2excel" data-tableName="Test Table 2">
   <thead>
      <tr class="noExl">
         <td>This shouldn't get exported</td>
         <td>This shouldn't get exported either</td>
      </tr>
      <tr>
         <td>This Should get exported as a header</td>
         <td>This should too</td>
      </tr>
   </thead>
   <tbody>
      <tr>
         <td>data1a</td>
         <td>data1b</td>
      </tr>
      <tr>
         <td>data2a</td>
         <td>data2b</td>
      </tr>
   </tbody>
   <tfoot>
      <tr>
         <td colspan="2">This footer spans 2 cells</td>
      </tr>
   </tfoot>
</table>
<button class="exportToExcel">Export to XLS</button>

After that, add the following basic CSS styles for the table and export button. These styles are optional, you can skip this step if you want to integrate this plugin with your existing HTML table. You can check this CSS buttons pack if you wanted to create a custom style for the export button.

.table-container {
	max-width: 660px;
	margin: 10px auto;
}
.table2excel {
	background: #fff;
	border-collapse: collapse;
	width: 100%;
	margin: 10px 0;
}
.table2excel input {
	padding: 5px;
	border: 1px solid #ddd;
	color: #777;
}
.table2excel td {
	border: 1px solid #ddd;
	padding: 6px;
}
.table2excel tr:nth-child(odd) {
	background: #f5f5f5;
}
.exportToExcel {
	border: 0;
	padding: 10px 15px;
	background: #16ba4f;
	color: #fff;
	transition: .25s;
}
.exportToExcel:hover {
	background: #109d41;
	transition: .25s;
}

Export HTML Table to Excel File using jQuery

After creating and styling the HTML table, now its time to integrate with “table2excel.js” jQuery plugin. So, load the jQuery JavaScript library and plugin JS file just before the closing of </body> tag.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="dist/jquery.table2excel.js"></script>

Finally, add the plugin configuration script after the above scripts. The following is the initialization with some basic plugin configurations. You can define values for each option according to your needs.

$(function () {
	$(".exportToExcel").click(function (e) {
		var table = $(this).prev('.table2excel');
		if (table && table.length) {
			var preserveColors = (table.hasClass('table2excel_with_colors') ? true : false);
			$(table).table2excel({
				exclude: ".noExl",
				name: "Excel Document Name",
				filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xlsx",
				fileext: ".xlsx",
				exclude_img: true,
				exclude_links: true,
				exclude_inputs: true,
				preserveColors: preserveColors
			});
		}
	});
});

Advanced Configuration Options

You can use the following available options to customize the working of the plugin.

OptionDefaultTypeDescription
exclude“.noExl”StringIt defines a class name for the table row that you want to exclude.

Example:

$(table).table2excel({
	exclude : ".noExl",
});
filename“table2excel”StringDefines the exported file name.

Example:

$(table).table2excel({
	filename : "table2excel",
});
fileext“.xls”StringThe file extension for exported file. Possible options are: “.xls” and “.xlsx”.

Example:

$(table).table2excel({
	fileext : ".xls",
});
exclude_imgtrueBooleanDecide whether to include or exclude table images.

Example:

$(table).table2excel({
	exclude_img : true,
});
exclude_linkstrueBooleanExclude/include links in exported file.

Example:

$(table).table2excel({
	exclude_links : true,
});

That’s all! Hopefully, now you are able to export any HTML table to xlsx Excel file using this jQuery plugin. If you are facing any issue or need further guidance, let us know by comment below.

You May Also Like

Leave a Comment

one × five =

This site uses Akismet to reduce spam. Learn how your comment data is processed.