excellaraveldatatable
Laravel - Datatables export excel from filtered data
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.
Solution
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.
- How to make picture %60 transparency in vba?
- Draw vertical line in scatter plot?
- Special character check mark
- How to change Font Color of Dates without changing color of other text or background using VBA for Excel
- Update cell value based on another cell VBA
- Use Console as debug window in VBA
- Calculate median using vba
- How can I join cells with a vertical line?
- How to change font color for specific line (row) of Textbox
- How do i use Excel Random list generation without duplicate
- excel countif entire row in a table
- Simplifying If Statements Into Loop
- What is the equivalent of the "Days" function or number of days between 2 dates in excel 2007?
- How to find and replace with regex in excel
- How to format graph to show dates from one or more sheets on the horizontal axis?
- How to embed a SVG into a Cell using Aspose.Cells
- Run-Time Error 91 (object variable or with block variable not set) on VBA Email code "mailItem.Display" after IF statements
- loop through excel raw data file and input them to other excel formula sheet and perform iteration calculation
- Deleting duplicate header rows
- mixed date converted from default mm/dd/yy in excel to dd/mm/yy
- Overwrite Excel 2016 worksheet data with data from CSV file
- Laravel validator and excel files error
- Need help aligning a table to a column based on similar values in one table field
- Index rows and columns unexpected results
- Ascii Art using VBA
- How to obtain the full-path names of files read with WorkbookConnections
- How can export all part of columns to pdf file with VBA code?
- Difficult to run
- Allow User Input in Empty Cells, Prevent Editing or Deletion in Occupied Cells
- Export Charts from Excel as images using Python