jQuery Datatables Export to Excel

jQuery Datatables provides a convenient way to export data to Excel format. This jQuery Datatables tutorial helps to export Datatables data to Excel using jQuery. The Excel file format is using .xls and .xlsx extension.

What’s jQuery Datatable

jQuery Datatables is a powerful JavaScript library that enhances the functionality and appearance of HTML tables. One of the essential features of Datatables is the ability to export table data to various formats, including Excel.

What’s Excel

Excel is one of the most popular file formats used to store tabular data in spreadsheets. We can use Excel format data to import and export data and store in an excel file.

This functionality is available only in modern browsers, including IE8 and above. The Buttons extension for DataTables offers three plugins that offer similar features for data export

HTML5 Export Buttons: This plugin utilizes HTML5 APIs to generate files directly on the client-side.
Flash Export Buttons: For legacy browsers, this plugin relies on Adobe Flash to handle the export functionality.
Print Button: This plugin allows users to print the data displayed in the DataTable directly.

You can also check other tutorials of export table data,

I am using excelHtml5 so that you can use export to Excel only on the latest browser, not on a legacy browser. I am explaining step by step jQuery datatable export to Excel tutorial.

I am extending the previous tutorial that’s why I will change the extension format and label from the previous Export jQuery datatable to pdf tutorial.

We have learned Export Datatable to CSV and Export Datatable to PDF in earlier datatables tutorials, Now I am extending this datatables tutorial and adding export datatable to Excel file.

How To Initialize Datatables

To enable Datatables functionality on the table, initialize it using the jQuery selector and the DataTable() function.

$(document).ready(function() {
  $('#myTableId').DataTable();
});

Where myTableId is the HTML table id.

What’s the TableTools plugin

The table tools plugin enables data export functionality from HTML tables, we need to include the TableTools plugin, which provides export options for Datatables.

Download the plugin from the official Datatables website or include it via a CDN as like below:

<script src="https://cdn.datatables.net/tabletools/2.2.4/js/dataTables.tableTools.min.js"></script>

Exporting jQuery Datatables data to Excel File

Step 1: We need to include the necessary CSS and JavaScript files. These files can be downloaded from the official Datatables website or linked via a content delivery network (CDN).

Let’s include all datatables library files in the head section of index.html file.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.js"></script>

Step 2: Created an HTML layout for jQuery datatables listing in index.html file, and apply the appropriate class for Datatables initialization.

<table id="listing" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>User ID</th>

            <th>Title</th>

            <th>Body</th>
        </tr>
    </thead>
</table>

Step 3: Fetched data from restful web service and processed data as required for jQuery datatables.

var arrayReturn = [];
            $.ajax({
                url: "https://jsonplaceholder.typicode.com/posts",
                async: true,
                dataType: 'json',
                success: function (data) {
					for (var i = 0, len = data.length; i &lt; len; i++) {
						var desc = data[i].body;
						var title = data[i].title;
						var id = (data[i].id).toString();
						arrayReturn.push([id, '<a href="https://google.com" target="_blank">'+title.substring(0, 20)+'</a>', desc.substring(0, 120)]);
					}
				inittable(arrayReturn);
                }
            });

Step 4: Now I will pass formatted data to the jQuery datatables "aaData" property.

function inittable(data) {	
		$('#listing').DataTable({
			"aaData": data,
			"dom": 'lBfrtip',
			buttons: [
            {
                extend: 'excelHtml5',
                text: 'Export to Excel',
				title: 'js-tutorials.com : Export to datatable data to Excel',
				 download: 'open',
				 orientation:'landscape',
				  exportOptions: {
				  columns: ':visible'
				}
            }]
		} );
	}

in the above code, I am using excelHtml5 plugin to overlap excel export functionality using html5 api. I have added the title and button text as well.

Conclusion:

In this article, we explored how to export jQuery Datatables data to Excel using the TableTools plugin. By including the required files, initializing Datatables and TableTools, and implementing the necessary JavaScript code, we can provide users with the ability to export table data to Excel using jQuery.

You can download the source code and Demo from the below link.

Live Demo

Download Source Code