Laravel - Datatables export excel from filtered data

Question

Hello i would like to export data from my datatable based on user filtered data here for example :

I have done export excel for all row but now i'm trying to export data based on filtered, here is my filtered function() in index.blade php:

$(".filterButton").on('click', function(){
                        tableMediaOrder.column(8).search($('.input-advertiser-filter').val()).draw();
                        tableMediaOrder.column(7).search($('.input-agency-filter').val()).draw();
                        tableMediaOrder.column(9).search($('.input-brand-filter').val()).draw();
                    });

i have tried to use formated Datatables example from Datatable example : Format Output Data, but i don't know how to put the export button and make it as a custom <a href=""> for the Excel export in image above, maybe someone can provide an example how to make it? thank you!.

EDIT :

here what is my input in index.blade.php :

<div class="col">
            <button id="filterButton" class="btn btn-primary filterButton"><i class="fa fa-filter"></i> Filter</button>
            <div class="dropdown d-inline">

                <button class="btn btn-primary dropdown-toggle" type="button" id="dropdownMenuButton" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                    <i class="fa fa-file-excel-o"></i>  Export
                </button>
                <!-- dropdown-menu -->
                <div class="dropdown-menu" aria-labelledby="dropdownMenuButton" id="export-choose">
                    <a class="dropdown-item export-link" id="export-filtered">Excel</a>
                     <a class="dropdown-item export-link" href="{{ route('media-order.export') }}" id="exportRaw">Excel Raw</a>
                </div>
                <!-- end dropdown-menu -->
                    <button class="btn btn-primary float-right" data-toggle="modal" data-target="#addMediaOrderModal" data-backdrop="static" data-keyboard="false" class="btn btn-primary">
                            <i class="fa fa-plus-square mr-1"></i> Add Order
                        </button>
            </div><!-- dropdown -->
        </div>

So far i've been trying to put the <a href="" id="export-filtered"> to act as an Export button, add it as an onClick="exportFiltered" function and throw it into the javascript but it doesn't work, here is my javascript :

$(".exportFiltered").on('click', function(e) {
                $('.hiddenbuttons button').eq(0).click();
            });

but sadly it doesn't work, and it just make the Excel export become blank

UPDATE : Data Table

here is my datatable :

'use strict';


        var addMediaOrderSubmitButton = Ladda('#addMediaOrderSubmitButton');
        var editMediaOrderSubmitButton = Ladda('#editMediaOrderSubmitButton');

        var tableMediaOrder = dt('#dt-media-order','media_order',{

            // dom: '<"hiddenbuttons"B>rtip',
            processing: true,
            serverside: true,
            iDisplayLength: 100,
            bFilter: true,
            searchable: true,
            exportOptions: {
                rows: 'visible'
            }, 

            ajax: {
                url: "{{ route('media-order.index') }}?dt=1",
                data: function (d){
                    d.filter_order = $('#input-order-filter').val();
                    d.filter_agency = $('#input-agency-filter').val();
                    d.filter_advertiser = $('#input-advertiser-filter').val();
                    d.filter_brand = $('#input-brand-filter').val();
                    // d.filter_start = $('#input-start-date').val();
                    // d.filter_end = $('#input-end-date').val();
                    //d.filterButton = $('#filterButton').val();
                },
            },

            columns: [
                    {
                    data: 'action', 
                    name: 'action',
                    orderable: false, 
                    sortable: false, 
                    className: 'text-center'},
                    {data: 'nomor', name: 'nomor'},
                    {data: 'nomor_reference', name: 'nomor_reference'},
                    {data: 'periode_start', 
                    name: 'periode_start',
                        render: function(data){
                            var date = new Date(data);
                            var month = date.getMonth() + 1;
                            return (month.toString().length > 1 ? month : "0" + month) + "/" + date.getDate() + "/" + date.getFullYear();
                        }
                    },
                    {
                        searchable: true, 
                        data: 'periode_end', 
                        name: 'periode_end',
                        render: function(date){
                            var date = new Date(date);
                            var month = date.getMonth() + 1;
                            return (month.toString().length > 1 ? month : "0" + month) + "/" + date.getDate() + "/" + date.getFullYear();
                        }
                    },
                    {
                        searchable: true, 
                        data: 'category_id', 
                        name: 'category_id',
                        render: function(data, type, row) {
                            switch (data) {
                                case '1':
                                    return 'New Order';
                                    break;
                                case '2':
                                    return 'Additional Order';
                                    break;
                                case '3':
                                    return 'Cancel Order';
                                    break;
                                case '4':
                                    return 'Paid';
                                    break;
                                case '5':
                                    return 'Bonus';
                                    break;

                                default:
                                    return 'Null';
                                    break;
                            }
                        }
                    },
                    {
                        searchable: true, 
                        data: 'type_id', 
                        name: 'type_id',
                        render: function(data, type, row) {
                            switch (data) {
                                case '1':
                                    return 'Reguler';
                                    break;
                                case '2':
                                    return 'Reguler PIB';
                                    break;
                                case '3':
                                    return 'CPRP';
                                    break;
                                case '4':
                                    return 'Package';
                                    break;
                                case '5':
                                    return 'Sponsor';
                                    break;
                                case '6':
                                    return 'Blocking';
                                    break;

                                default:
                                    return 'Null';
                                    break;
                            }
                        }
                    },
                    {
                        searchable: true, 
                        data: 'agency_name', 
                        name: 'agency_name' 
                    },
                    {
                        searchable: true, 
                        data: 'advertiser_name', 
                        name: 'advertiser_name' 
                    },
                    {
                        searchable: true, 
                        data: 'brand_name', 
                        name: 'brand_name' 
                    },
                    {
                        searchable: true, 
                        data: 'version_code', 
                        name: 'version_code' 
                    },
                    {
                        data: 'gross_value', 
                        name: 'gross_value' ,
                        render: $.fn.dataTable.render.number( ',', '.', 2, 'Rp','' )
                    },
                    {
                        data: 'nett_budget', 
                        name: 'nett_budget',
                        render: $.fn.dataTable.render.number( ',', '.', 2, 'Rp','' ) 
                    },
                    {
                        data: 'nett_cashback', 
                        name: 'nett_cashback',
                        render: $.fn.dataTable.render.number( ',', '.', 2, 'Rp','' ) 
                    },
                    {
                        data: 'nett_bundling', 
                        name: 'nett_bundling',
                        render: $.fn.dataTable.render.number( ',', '.', 2, 'Rp','' )
                    },
                    {data: 'spot', name: 'spot' },
                    {
                        searchable: true, 
                        data: 'accountexecutive_name', 
                        name: 'accountexecutive_name' 
                    },
                    {
                        searchable: true, 
                        data: 'userto_name', 
                        name: 'userto_name' 
                    },
                    {
                        searchable: true, 
                        data: 'group_id', 
                        name: 'group_id' 
                    },
                    {data: 'notes', name: 'notes' },
                    {
                        searchable: true, 
                        data: 'attachment_name', 
                        name: 'attachment_name' 
                    }
                ],
                buttons: [
                    { // this exports only filtered data
                      extend: 'excelHtml5',
                      exportOptions: {
                        modifier: { search: 'applied' }
                      }
                    }, 
                    { // this exports all data regardless of filtering
                      extend: 'excelHtml5',
                      exportOptions: {
                        modifier: { search: 'none' }
                      }
                    }
                ],

                initComplete: function(setting, json){
                    $('.hiddenbuttons').css('display','none');
                },

                rowCallback: function( row, data, index) {
                    if (data.isdisabled == 1){
                        $(row).css('background-color', 'rgba(255, 0, 0, 0.2)');
                    }
                }
        });

UPDATE 2 : it turns out i forgot to add the :

<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/dataTables.buttons.min.js"></script> 
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/buttons.html5.min.js"></script>

And also is there a way to customize the column since the "Action" column are also being exported like this : But sadly the custom export <a href="" id="export-filtered"> is still not working, thanks again.

UPDATE 3 :

After searching and tinkering, i've finally found my solution which is using :

var buttons = new $.fn.dataTable.Buttons(tableMediaOrder, {
             buttons: [
               {
                      extend: 'excelHtml5',

                      // "dom": {
                      //   "button": {
                      //       "tag": "button",
                      //       "className" : "exportFiltered",
                      //       }
                      //   },
                      
                      exportOptions: {
                        // rows: '"visible'
                        columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20],
                        modifier: { search: 'applied' }
                      }
                    }
            ]
        }).container().appendTo($('#exportFiltered'));

And finally able to use the :

<a href="#" class="dropdown-item" id="exportFiltered"></a>

as a external link to export the excel.

I a not sure what you mean by "is there a way to customize the column" - but if you only want to export some specific columns (and ignore other columns), you can do that. See this example. — andrewJames
– andrewJames, Commented Nov 15, 2021 at 12:50
"But sadly the custom export <a href="" id="export-filtered"> is still not working" - in that case, there must be some other differences between your code and mine, not shown in your question. — andrewJames
– andrewJames, Commented Nov 15, 2021 at 12:54
is there any code you would like to see?, i've also been trying to create button as another export button but alas, doesn't working too. my datatables version is 1.10.19 — Vtych
– Vtych, Commented Nov 16, 2021 at 5:37

andrewJames · Accepted Answer · 2021-11-14 15:01:52Z

You can delegate a DataTables export button to another external (non-DataTables) element.

The following example uses two different Excel Export buttons - one for a full export of all data, regardless of any filtering which has been applied, and the other to export only the filtered-in data:

buttons: [ 
  // see https://datatables.net/reference/type/selector-modifier
  { // this exports only filtered data
    extend: 'excelHtml5',
    exportOptions: {
      modifier: { search: 'applied' }
    }
  }, 
  { // this exports all data regardless of filtering
    extend: 'excelHtml5',
    exportOptions: {
      modifier: { search: 'none' }
    }
  }
]

Then, we hide these buttons using the following:

dom: '<"hiddenbuttons"B>rtip'

and:

initComplete: function(settings, json) {
  $('.hiddenbuttons').css('display', 'none');
}

These two DataTables export buttons can now be invoked from elsewhere - for example, based on the change event of a select list.

Here is the select list:

<select name="export" id="export">
    <option value="noexport">-- select --</option>
    <option value="filtered">Excel Filtered Data</option>
    <option value="alldata">Excel All Data</option>
</select>

And here is the related event listener:

$("#export").on('change', function(e) {
  var mode = $("#export :selected").val();
  if (mode === 'filtered') {
    $('.hiddenbuttons button').eq(0).click();
  } else if (mode === 'alldata') {
    $('.hiddenbuttons button').eq(1).click();
  }
});

For reference, here is the full approach, as a self-contained web page:

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css"/> 
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>

</head>

<body>

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

    <input type="text" id="name" placeholder="Enter name">
    <input type="text" id="office" placeholder="Enter office">
    <button id="filterButton" type="button">Filter</button> 
    <select name="export" id="export">
        <option value="noexport">-- select --</option>
        <option value="filtered">Excel Filtered Data</option>
        <option value="alldata">Excel All Data</option>
    </select>

    <table id="example" class="display dataTable cell-border" style="width:100%">
    </table>

</div>

<script>

var dataSet = [
    {
      "id": "1",
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Zurich",
      "extn": "5421"
    },
    {
      "id": "2",
      "name": "Garrett Winters",
      "position": "Accountant",
      "salary": "$170,750",
      "start_date": "2011/07/25",
      "office": "Tokyo",
      "extn": "8422"
    },
    {
      "id": "3",
      "name": "Ashton Cox",
      "position": "Junior Technical Author",
      "salary": "$86,000",
      "start_date": "2009/01/12",
      "office": "San Francisco",
      "extn": "1562"
    },
    {
      "id": "4",
      "name": "Cedric Kelly",
      "position": "Senior Javascript Developer",
      "salary": "$433,060",
      "start_date": "2012/03/29",
      "office": "Edinburgh",
      "extn": "6224"
    },
    {
      "id": "5",
      "name": "Airi Satou",
      "position": "Accountant",
      "salary": "$162,700",
      "start_date": "2008/11/28",
      "office": "Tokyo",
      "extn": "5407"
    },
    {
      "id": "6",
      "name": "Donna Snider",
      "position": "Customer Support",
      "salary": "$112,000",
      "start_date": "2011/01/25",
      "office": "New York",
      "extn": "4226"
    }
  ];
 
$(document).ready(function() {

var table = $('#example').DataTable( {
  dom: '<"hiddenbuttons"B>rtip',
  lengthMenu: [ [5, -1], [5, "All"] ],
  data: dataSet,
  columns: [
    { title: "ID", data: "id" },
    { title: "Name", data: "name" },
    { title: "Office", data: "office" },
    { title: "Position", data: "position" },
    { title: "Start date", data: "start_date" },
    { title: "Extn.", data: "extn" },
    { title: "Salary", data: "salary" }
  ],
  buttons: [ 
    // see https://datatables.net/reference/type/selector-modifier
    { // this exports only filtered data
      extend: 'excelHtml5',
      exportOptions: {
        modifier: { search: 'applied' }
      }
    }, 
    { // this exports all data regardless of filtering
      extend: 'excelHtml5',
      exportOptions: {
        modifier: { search: 'none' }
      }
    }
  ],
  initComplete: function(settings, json) {
    $('.hiddenbuttons').css('display', 'none');
  }
} );

$("#filterButton").on('click', function() {
  table.column(1).search($('#name').val()).draw();
  table.column(2).search($('#office').val()).draw();
});

$("#export").on('change', function(e) {
  var mode = $("#export :selected").val();
  if (mode === 'filtered') {
    $('.hiddenbuttons button').eq(0).click();
  } else if (mode === 'alldata') {
    $('.hiddenbuttons button').eq(1).click();
  }
});



} );

</script>

</body>
</html>

Update

If you want to use a <a> link to generate an Excel export, then maybe this will help:

Let's assume we have a link like the one from your question:

<a href="" id="export-filtered">Excel</a>

To handle a click event for this, you can use the following:

$("#export-filtered").on('click', function(e) {
  e.preventDefault();
  $('.hiddenbuttons button').eq(0).click();
});

Note that the link's ID is export-filtered - therefore you need to refer to that in your JavaScript, using the # symbol (which is for an ID) - and not the . symbol (which is for a class name):

$("#export-filtered")

Then you need to prevent the default click action from being applied, because you do not want the click to cause you to navigate to another page. I recommend doing this even if you have href="".

That works for me, using my DataTables code.

In your question, you do not show how you changed your DataTables code - so this may still not work for you. If that is the case, then there must be other differences (which are not shown in the question) between my example and your overall solution.

Thank you so much for the example!, but is there a way to use it in <div class="dropdown"> menu and also using an <a class="dropdown-item" id="export-filtered">Excel</a> ?, because these was the dropdown i use. I have tried using on.click() in index.blade.php and it doesn't work. Thanks again!.
Can you edit your question, and show all the relevant code for your attempt? Also, what does "it doesn't work" mean? What happens? Any error messages in the browser console?
Thank you for the updates. I have tried to provide some more notes in my answer.
sadly it doesn't work for me, no error or anything, i put my datatable in the update.

Collectives™ on Stack Overflow

Laravel - Datatables export excel from filtered data

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related