jQuery datatables exporting data to Excel

Question

I am trying to export directly from the datatable to Excel.

Starting with the ajax call:

displayRecords();
function displayRecords()
{
  $.ajax({
    url: 'process/getRecords.php',
    type: 'POST',
    data: '',
    dataType: 'html',
    success: function(data, textStatus, jqXHR)
    {
      var jsonObject = $.parseJSON(data); 
      var table = $('#resultsTable').DataTable({    
      {
        "data": jsonObject,
        "columns": [
          {"data": "JOB_REFERENCE"},
          {"data": "VOYAGE_REFERENCE"},
          // few more columns
        ],
        "iDisplayLength": 25,
        "scrollY": 500,
        "scrollX": true,
        "bDestroy": true,
        "paging": true,
        "stateSave": true
      }
    },
    error: function(jqHHR, textStatus, errorThrown)
    {
      console.log('fail: '+ errorThrown);
    }
  });

   // button click to export results
   var tableresults = $('#resultsTable').dataTable();

   $("#btnExport").on('click', function(e) 
   {
     e.preventDefault();
     window.open('data:application/vnd.ms-excel,' +
       encodeURIComponent(table[0].outerHTML));
   });
}

Using all of the above, I can successfully export the results from the datatable. I can even use the filter search to drill down to a smaller data set, and export the results.

I was using the following fiddle: http://jsfiddle.net/donpayne/jzdjdo3z/

The problem I am having lies with the Show Entries dropdown of the datatable. Typically, the dropdown is set to 10. Whether you filter the search down or not, if the total record count is greater than the Show Entries dropdown, the Excel sheet will only return the total amount set in the dropdown.

You can test what I am talking about in the fiddle. Set the Show Entries dropdown to 10, then export to Excel. If you'll notice, there are 58 total records in that table. The Excel sheet will only return 10 records.

I need the to return all the records. If I have 2000 records, and the Show Entries dropdown is set to 10, I need the exported Excel sheet to include all 2000 records.

The same if I filter the search down to about 56 records; when I export to excel, I should have a total of 56 records on that spreadsheet, regardless of what the Show Entries dropdown is set to.

As stated, I referenced the code from the fiddle and altered it to fit my datatable.

I worked on this for a bit - my solution was to use table.page.len(-1) to disable paging then restore it. but it occurred to me - you're possibly trying to pass 2000 rows of data through through a url string, aren't you? Aren't you limited in the amount of data you can pass through there? — dmgig
– dmgig, Commented May 23, 2018 at 20:34
@dgig - I am not limited to the amount of data. I just picked the number 2000. Although, currently, the table I am working with does have a little over 2100 records. This table could hold more or less. I hope that answers your question. — John Beasley
– John Beasley, Commented May 23, 2018 at 21:09
Might help: datatables.net/extensions/buttons/examples/initialisation/… — Karlo Kokkak
– Karlo Kokkak, Commented May 24, 2018 at 4:41
@KarloKokkak - I was able to achieve the results I needed thanks to your suggestion. — John Beasley
– John Beasley, Commented May 25, 2018 at 12:46

dmgig · Accepted Answer · 2018-05-23 21:32:47Z

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.

edited May 23, 2018 at 21:32

answered May 23, 2018 at 20:21

dmgig

4,5785 gold badges40 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John Beasley Over a year ago

I'm getting this error: Cannot read property 'len' of undefined. I didn't see the additional links you provided. checking again.

John Beasley Over a year ago

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.

dmgig Over a year ago

Did you make all of the changes I did in the fiddle? There's quite a few that are important even if they don't seem to be. The dataTables --> DataTables for one. When you use my fiddle, what do you see when you open the downloaded file? I see basically an html table with all 58 of your records.

dmgig Over a year ago

Hm. It shouldn't clear out, that's a problem for sure - table.page.len( -1 ).draw(); means basically remove any pagination. Did you see the comment from Karlo btw? datatables.net/extensions/buttons/examples/initialisation/… It seems to have a built in function for excel exports. This might be what you need instead of rolling your own?

dmgig Over a year ago

Oh, I wonder if the fact that we're reinitializing the table again with DataTables is a problem because we're not giving it data. Maybe skip that second var tableresults = $('#resultsTable').DataTable(); and just call table.page.len(-1) on the variable you created from your ajax call. That's the only thing I can think of.

|

KarSho · Accepted Answer · 2024-07-23 16:27:02Z

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.

Collectives™ on Stack Overflow

jQuery datatables exporting data to Excel

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related