Question 52864 in Javascript Question posted in
A very good W3school tutorial can be found here.

Export all rows from DataTables in excel but exported only visible rows, but i am try to export all rows that visible or hidden in the pagination – Javascript

$(document).ready(function() {
    $('#downloadBtn').click(function() {
        var table = $('.table2excel').DataTable();
    var allRows = table.rows().data().toArray();
        var allRowsTable = $('<table>').append($('.table2excel').find('thead').clone()).append($('<tbody>'));
        for (var i = 0; i < allRows.length; i++) {
            allRowsTable.append($('<tr>').append(allRows[i]));
    }
    allRowsTable.table2excel({
            filename: 'data.xls',
            sheetName: 'Sheet1',
            exclude: '.noExl',
            preserveColors: true
    });
    });
});

this code for Export all rows from DataTables in excel but exported only visible rows, but i trying to export all rows that visible or hidden in the pagination.

Note: Using table2excel.js

How to solve this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solution:

    var table = $('.exportexcel').DataTable();
$("#exportBtn").click(function(){
    $('<table>').append($(table.table().header()).clone()).append($(".exportexcel").DataTable().$('tr').clone()).table2excel({
        exclude: ".noExl",
        sheetName: 'Less Attendance Report',
        filename: "Less-Attendance-Report-" + new Date().toISOString().replace(/[-:.]/g, ""),
        fileext: ".xls",
        exclude_img: true,
        exclude_links: true,
        exclude_inputs: true,
        preserveColors: false
    });
});

  2. 0

    You can change this line of code:

    var allRows = table.rows().data().toArray();

    to this:

    var allRows = table.rows( { page: 'current' } ).data().toArray();

    This uses a selector modifier to select only the data in the currently displayed page.

    The selector modifier documentation shows various different selection settings you can choose from. It can be used in conjunction with row selectors for additional control over the subset of data you want to select.

    Alternative Approach

    In case this may be of interest:

    You are already using DataTables – so why not use its built-in support for exporting data to Excel? You can export just the current page using the same page: 'current' approach as shown above.

    $(document).ready(function() {

  var table = $('#example').DataTable( {
    dom: 'Brftip',
    buttons: [
      {
        extend: 'excel', 
        exportOptions: {
          modifier: {
            page: 'current'
          }
        }
      }
    ]
  } );

} );

    See this example for basic configuration of the buttons extension.

    For the approach using table2excel, you are probably going to want to get the <tr> nodes from the DataTable, not the data as an array:

    var allRows = table.rows().nodes();

    And then in the for loop, you don’t need to append an explicit <tr>:

    allRowsTable.append(allRows[i]);

    But (a) I have not tested this as I do not have table2excel – and (b) it would be much easier (in my opinion) to use DataTables for the export to Excel – as already shown above. (But maybe that does not meet your needs.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search