I think the best thing to do is to remove the paging, then do the export, then turn the paging back on once it's done.

I made a couple minor changes:

$(function () 
{
    var table = $('#example').DataTable();

    $("#btnExport").click(function(e) 
    {
        table.page.len( -1 ).draw();
        window.open('data:application/vnd.ms-excel,' + 
            encodeURIComponent($('#example').parent().html()));
      setTimeout(function(){
        table.page.len(10).draw();
      }, 1000)

    });
});

Updated fiddle: http://jsfiddle.net/jzdjdo3z/176/

Page Length docs: https://datatables.net/reference/api/page.len()

Paging option docs: https://datatables.net/reference/option/paging

I'm not sure why initializing with dataTables vs DataTables made a difference, but it did. So keep an eye out for that.

Answer from dmgig on Stack Overflow
🌐
DataTables
datatables.net › extensions › buttons › examples › initialisation › export.html
DataTables example - File export
new DataTable('#example', { layout: { topStart: { buttons: ['copy', 'csv', 'excel', 'pdf', 'print'] } } });
🌐
CodePen
codepen.io › GiSmo › pen › pbjzXw
Datatables buttons export excel
$(document).ready(function() { var table = $('#example').DataTable({ dom: 'Bfrtip', buttons: [ { extend: 'excel', text: 'Export excel', className: 'exportExcel', filename: 'Export excel', exportOptions: { modifier: { page: 'all' } } }, { extend: 'copy', text: '<u>C</u>opie presse papier', className: 'exportExcel', key: { key: 'c', altKey: true } }, { text: 'Alert Js', className: 'exportExcel', action: function(e, dt, node, config) { alert('Activated!'); // console.log(table); // new $.fn.dataTable.Buttons(table, { // buttons: [{ // text: 'gfdsgfsd', // action: function(e, dt, node, config) { // alert('ok!'); // } // }] // }); } }] }); });
Discussions
Catch Export to Excel onclick event — DataTables forums
Link to test case: Debugger code (debug.datatables.net): Error messages shown: Description of problem: More on datatables.net
🌐 datatables.net
December 15, 2022
jQuery datatables exporting data to Excel
No longer getting the len error, but now the excel sheet is only showing 'No data available in the table.' The datatable does clear out when I click the export button. Not sure if that has something to do with it. More on stackoverflow.com
🌐 stackoverflow.com
How to call Datatable csv button from custom button
This selects the element with the id "ExportReporttoExcel" and triggers a click with using jQuery. ... yeah this answer shows how to trigger the click of the id ExportReporttoExcel. I wasn't sure what your question was but based on your answer it seems you needed to alter the click event of ... More on stackoverflow.com
🌐 stackoverflow.com
jquery - automatic export to excel using datatables - Stack Overflow
But I want to download an excel file automatically when I initiate the datatable · Is there an option to do that? Here is a fiddle with export to excel working on click of excel button More on stackoverflow.com
🌐 stackoverflow.com
Videos
🌐 youtube.com
How to Export DataTable to Excel, PDF & CSV Using Custom Button ...
October 7, 2023
05:58
🌐 YouTube
Data Table Export Table Data in PDF, Excel, CSV | DataTable JS ...
January 22, 2022
08:30
🌐 YouTube
Jquery Datatable Tutorial | How to add Copy | Excel | PDF Button ...
February 26, 2017
10:03
🌐 YouTube
Export jQuery Datatables Data to Excel CSV PDF using PHP Ajax - ...
October 23, 2018
15:22
🌐 YouTube
jQuery datatables export to pdf - YouTube
August 10, 2015
79.7K
09:35
🌐 YouTube
Export jQuery Datatable Data into CSV,PDF and Excel Using PHP and ...
September 3, 2021
12.4K
View all
View all
🌐
DataTables
datatables.net › forums › discussion › 78202 › modifying-datatable-function-to-use-export-to-excel-functionality
Modifying datatable function to use export to excel functionality — DataTables forums
February 17, 2024 - I don't understand why you would want that when DataTables is already available as a jQuery plugin? ... I don't want to create any plugin. The function createDataTable is already there in the code as mentioned in my first post. Now I want to modify this main function createDataTable to take columns and adjust the logic accordingly on pages that uses it. Below is the code snippet for the export to excel button.
🌐
DataTables
datatables.net › forums › discussion › 74916 › catch-export-to-excel-onclick-event
Catch Export to Excel onclick event — DataTables forums
December 15, 2022 - $(".buttons-excel").on('click', function (event) {debugger event.preventDefault(); // Do my own export }); $(".buttons-excel").click( function (event) {debugger event.preventDefault(); // Do my own export } );
🌐
Stack Overflow
stackoverflow.com › questions › 50496386 › jquery-datatables-exporting-data-to-excel
jQuery datatables exporting data to Excel
Top answer
1 of 2
4

I think the best thing to do is to remove the paging, then do the export, then turn the paging back on once it's done.

I made a couple minor changes:

$(function () 
{
    var table = $('#example').DataTable();

    $("#btnExport").click(function(e) 
    {
        table.page.len( -1 ).draw();
        window.open('data:application/vnd.ms-excel,' + 
            encodeURIComponent($('#example').parent().html()));
      setTimeout(function(){
        table.page.len(10).draw();
      }, 1000)

    });
});

Updated fiddle: http://jsfiddle.net/jzdjdo3z/176/

Page Length docs: https://datatables.net/reference/api/page.len()

Paging option docs: https://datatables.net/reference/option/paging

I'm not sure why initializing with dataTables vs DataTables made a difference, but it did. So keep an eye out for that.

2 of 2
1

If you want download as .xlsx format, use following code. Here you can define the name of the file also. Scripts:

<script src="https://unpkg.com/xlsx/dist/shim.min.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<script src="https://unpkg.com/[email protected]/Blob.js"></script>
<script src="https://unpkg.com/[email protected]/FileSaver.js"></script>

Javacript:

var table = $('#example').DataTable();
 $("#btnExport").click(function(e) 
{
   var fileName = "test";
   var fileType = "xlsx";
   var table_obj = document.getElementById("example");
   var wb = XLSX.utils.table_to_book(table_obj, {sheet: "Sheet JS"});
   return XLSX.writeFile(wb, null || fileName + "." + (fileType || "xlsx"));
});

But issue is, you can download first page only. If any one found to full table, update here. Thanks in Advance.

🌐
JSFiddle
jsfiddle.net › opchggs3 › 3
jquery datatable export table to PDF/Excel demo - JSFiddle - Code Playground
Click Create new key, fill "JSFiddle" as the name for the API key, and save.
🌐
Stack Overflow
stackoverflow.com › questions › 45515559 › how-to-call-datatable-csv-button-from-custom-button
How to call Datatable csv button from custom button
Top answer
1 of 5
19

At last i found the solution.

In Datatable configuration , i added click event for the button to be triggered.

buttons: [
        { 
            extend: 'csv',
        }
    ]

$("#ExportReporttoExcel").on("click", function() {
    table.button( '.buttons-csv' ).trigger();
});

This works fine for me thanks for the comments and answers

2 of 5
6

dataTables export buttons is by default enriched with signature classes like .buttons-excel, .buttons-pdf, .buttons-csv and so on. Take advantage of that :

$('#ExportReporttoExcel').on('click', function() {
  $('.buttons-excel').click()
});
Find elsewhere
🌐
Stack Overflow
stackoverflow.com › questions › 38872668 › automatic-export-to-excel-using-datatables › 38872842
jquery - automatic export to excel using datatables - Stack Overflow
Top answer
1 of 2
2

You could click() the excel button in the initComplete callback :

initComplete: function() {
   $('.buttons-excel').click()
}

forked fiddle (not intentionally) -> https://jsfiddle.net/850z70do/

2 of 2
1

You can add a 'drawCallback' option to your datatables configuration.

    drawCallback: function () {
      $('.buttons-excel').trigger('click')
    }
🌐
C# Corner
c-sharpcorner.com › blogs › how-to-export-html-table-to-excel-pdf-csv-using-jquery-datatable2
How To Export HTML Table To Excel, PDF, CSV Using jQuery DataTable
March 28, 2023 - $(document).ready(function() { $('#myTable').DataTable( { dom: 'Bfrtip', buttons: [ 'excelHtml5', 'pdfHtml5', 'csvHtml5' ] } ); } ); Now you can export the table to Excel, PDF, or CSV by clicking the corresponding button.
🌐
Stack Overflow
stackoverflow.com › questions › 64217805 › jquery-datatable-export-to-excel-with-my-own-custom-button
jQuery datatable export to excel with my own custom button - Stack Overflow
$(document).ready(function () { var table = $('#example').DataTable({ "paging": false, "info": false, searching: false, dom: 'Bfrtip', buttons: [ { extend: 'excelHtml5' } ] }); }); I have added click handler using below thread button i don't ant to show datatble's export button.
🌐
DataTables
datatables.net › extensions › buttons › examples › html5 › simple.html
DataTables example - HTML5 export buttons
This example demonstrates these four button types with their default options. The other examples in this section demonstrate some of the options available. Please note that the copy, excel, csv and pdf button types may also use the HTML5 button types noted here.
🌐
Stack Overflow
stackoverflow.com › questions › 62393640 › tell-datatable-to-use-custom-button-for-file-export
tell datatable to use custom button for file export
Top answer
1 of 1
5

You can just trigger click events of the datatables buttons like this:

 $("#excelExport").on("click", function() {
    $(".buttons-excel").trigger("click");
 });
 $("#csvExport").on("click", function() {
    $(".buttons-csv").trigger("click");
 });
🌐
Stack Overflow
stackoverflow.com › questions › 60528517 › datatables-export-excel
Datatables export Excel - Stack Overflow
Top answer
1 of 1
6

There is a self-contained example at the end of this answer, but here are your two problems:

Large Numbers

The best way to fix this is to use 'excel' instead of 'csv' here:

dom: 'Bfrtip',
"buttons": [
  'excel'
]

This will ensure the Excel cell format is "number" instead of "general".

I don't know of a way to automatically control the Excel cell format when using the CSV export option - unless you are prepared to save the CSV as a text file, then import into Excel and format it during the import (a manual process).

Accented Characters

There are various reasons why you could be having this issue - many of which are outside the scope of DataTables - so the following may not help you, but...

Make sure your HTML page contains this inside the head tag:

<meta charset="UTF-8">

This is sufficient for me to get my demo working (see below). For example:

However, like I say, there could be many other reasons - for example, see here.

Full Example

Paste the following HTML into a text file (use Notepad++ not Notepad, if you are on Windows). Assuming Notepad++, make sure the file is saved as UTF-8 - menu > Encoding > UTF-8. Then open the file in any browser.

You don't need all of those JS imports provided below (for example the PDF one); feel free to remove extra ones. (I have them for a fuller demo and was too lazy to remove them.)

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Export to Excel</title>
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <!-- buttons -->
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.flash.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js"></script>

</head>

<body>

<div style="margin: 20px;">

<table id="example" class="display nowrap dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Adélaïde Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>6123456789012345</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

</div>

<script type="text/javascript">

  $(document).ready(function() {
    $('#example').DataTable({

      dom: 'Bfrtip',
      "buttons": [
        'excel'
      ]
    });
  });

</script>

</body>

Note on the CSV Option

If you do use "csv" instead of "excel" in your button definition, and if you open the resulting file in a text editor, instead of Excel, you will see this data:

"Name","Position","Office","Age","Start date","Salary"
"Adélaïde Nixon","System Architect","Edinburgh","6123456789012345","2011/04/25","$320,800"

The data is the way you need it to be - it's just that Excel will make various assumptions about how to format the data when opening the csv file.

🌐
DataTables
datatables.net › forums › discussion › 61669 › custom-button-for-excel-export
custom button for excel export — DataTables forums
I'm using the solution from @you2525 for excel export with multiple sheets Is there also away to define a Custom button which exports a specific table to…
🌐
Stack Overflow
stackoverflow.com › questions › 57887094 › i-want-to-add-excel-button-in-my-data-table-for-excel-export
I want to add excel button in my data table for excel export
Top answer
1 of 3
5

Add this script 

$(document).ready(function() {
$('#all_sub').DataTable( {
    dom: 'lBfrtip',
    buttons: [ 
        'excelHtml5', 
    ]
} );
} );

And load the below datatable libraries

https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css
https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css

https://code.jquery.com/jquery-3.3.1.js
https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js
https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js
https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js
https://cdn.datatables.net/buttons/1.5.6/js/buttons.html5.min.js
2 of 3
3

Note:- Please note - this property requires the Buttons extension for DataTables.

CSS

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css">

JS

<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.html5.min.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.print.min.js"></script>

HTML 

<table id="all_sub" class="table">
    <thead class="thead-light">
        <tr>
            <th>Subscriber Name</th>
            <th>Course Name</th>
      </tr>
    </thead>
    <tbody>
        <?php foreach($allsubscriber as $allsub):?>
        <tr>
            <td><?php echo $allsub->user_firstname;?></td>
            <td><?php echo $allsub->course_title;?></td>
        </tr>
        <?php endforeach; ?>              
    </tbody>
</table>

JQuery Script

$(document).ready(function() {
    $('#example').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ]
    } );
} );
🌐
Datatables
editor.datatables.net › examples › extensions › exportButtons.html
DataTables example - Buttons - Export buttons
var editor = new DataTable.Editor({ ajax: '../php/staff.php', fields: [ { label: 'First name:', name: 'first_name' }, { label: 'Last name:', name: 'last_name' }, { label: 'Position:', name: 'position' }, { label: 'Office:', name: 'office' }, { label: 'Extension:', name: 'extn' }, { label: 'Start date:', name: 'start_date', type: 'datetime' }, { label: 'Salary:', name: 'salary' } ], table: '#example' }); $('#example').DataTable({ ajax: '../php/staff.php', columns: [ { data: null, render: function (data, type, row) { // Combine the first and last names into a single table field return data.first
🌐
Stack Overflow
stackoverflow.com › questions › 35005053 › call-datatables-export-functions-from-javascript
jquery - Call DataTables export functions from JavaScript - Stack Overflow
Top answer
1 of 2
8

You can manually trigger the event of the button you wish to 'click'.

Example:

var myDataTable1 = $('#dt1').DataTable({
   buttons: [
      'excelHtml5'
   ]
});

// Use jQuery selector (in this case looking for known class name) for the hidden button, then trigger it's action event.
myDataTable1.buttons('.buttons-excel').trigger();

Extra: Based on the above example, here's a quick and dirty little function you can use.

//example: TriggerDataTableButton(myDataTable1, '.buttons-excel');
function TriggerDataTableButton(dataTable, buttonSelector) {
   dataTable.buttons(buttonSelector).trigger();
};
2 of 2
4

Couple what @DelightedD0D said in his comment:

You could always just do

$('#floatingBtn').click(function(){ $('#someExportBtn').click(); });

... with some CSS to force the original buttons out of view off-screen and you'd wind up with only one set of buttons being shown.

.buttons-html5, .buttons-print {
    position: absolute;
    left: -9999px;
}
🌐
DataTables
datatables.net › forums › discussion › 52732 › export-excel
Export excel — DataTables forums
yes, my links are https://datatables.net/extensions/buttons/examples/html5/simple.html https://datatables.net/extensions/buttons/examples/html5/filename.html https://codepen.io/GiSmo/pen/pbjzXw · i am usinng this version js https://code.jquery.com/jquery-3.3.1.js https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js https://cdn.datatables.net/buttons/1.5.2/js/buttons.html5.min.js
🌐
Medium
websolutionstuff.medium.com › datatable-custom-export-button-example-a97ef1de35eb
Datatable Custom Export Button Example - Websolutionstuff
July 23, 2021 - var buttons = new $.fn.dataTable.Buttons(table, { buttons: [ 'copyHtml5', 'excelHtml5', 'csvHtml5', 'pdfHtml5' ] }).container().appendTo($('#buttons')); ... Thanks for the reading…!! ... I am Laravel and PHP Developer. I have also Good Knowledge of JavaScript, jQuery, Bootstrap and REST API.
🌐
Microsoft Learn
learn.microsoft.com › en-gb › answers › questions › 1595415 › fix-repater-header-while-scrolling-and-export-to-e
FIX REPATER HEADER WHILE SCROLLING AND EXPORT TO EXCEL - Microsoft Q&A
On button click the data should export to excel with perfect alignment · Just add a button to your current code. <asp:Button ID="btnExport" Text="Export" runat="server" OnClientClick="return Export();" /> All Code · <html xmlns="http://ww...
Related queries
datatable export button dropdown
bootstrap 4 datatable export to excel
datatable excel button click event
datatable customize export button
datatables buttons
datatable export excel' customize
export excel button
datatables export buttons and pagination