Laravel - Datatables export excel from filtered data

0 votes

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

enter image description here

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 : enter image description here 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.

Mar 28 in Database by Edureka
• 9,700 points
268 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

+2 votes
1 answer

How to export data from MySql to a CSV file?

If you are using MySql workbench then ...READ MORE

answered Jan 4, 2019 in Database by Priyaj
• 58,060 points
368 views
0 votes
1 answer

How to Export Tally Data programmatically to CSV or Excel format

Open data (Ledger/P&L or Balance Sheet) that ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
56 views
0 votes
1 answer

Easy way to export multiple data.frame to multiple Excel worksheets

To export numerous data frames from R ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
32 views
0 votes
1 answer

How to Export Tally Data programmatically to CSV or Excel format

Tally Data in Excel or PDF: How ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
23 views
0 votes
1 answer
0 votes
1 answer

Importing images along with other fields (username,subjectname) from excel in laravel

$objphpexcel = PHPExcel_IOFactory::load("MyExcelFile.xls"); foreach ($objphpexcel ->getSheetByName("My Sheet1")->getDrawingCollection() as ...READ MORE

answered Apr 1 in Database by Edureka
• 8,820 points
119 views
0 votes
0 answers

Make sentence from data in cells in Excel

I have a form with three cells ...READ MORE

Apr 6 in Database by Edureka
• 9,700 points
19 views
0 votes
0 answers

Datatables export Excel

I'm exporting the datatables in csv. And ...READ MORE

Apr 7 in Database by Edureka
• 9,700 points
16 views
0 votes
1 answer

What are the ways of Data Import and Export in MySQL Workbench?

There are majorly three ways to export ...READ MORE

answered Dec 14, 2018 in Database by DataKing99
• 8,240 points
1,496 views
0 votes
1 answer

What is the best way to fetch data from table?

Hey Shraddha, I understand your doubts about fetching ...READ MORE

answered May 25, 2019 in Database by sampriti
• 1,120 points
940 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP