SOLUTION
You can use $() method to get access to all rows even not present in DOM and construct a new table using these rows. Then you can execute table2excel() on a newly constructed table to get Excel file that contains all rows.
For example:
$(function() {
var startDate = $(".startDate").val();
var endDate = $(".endDate").val();
$("#exportExcel").click(function(){
$('<table>')
.append(
$("#table_id").DataTable().$('tr').clone()
)
.table2excel({
exclude: ".excludeThisClass",
name: "Worksheet Name",
filename: "SomeFile" //do not include extension
});
});
$("#table_id").dataTable();
});
DEMO
See this page for code and demonstration.
NOTES
Excel 2013 displays the following error when opening the file produced by table2excel.js.
Excel cannot open the file
[filename]because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Because of this error, I would rather use DataTables TableTools plug-in instead even though it can only produce CSV files and also uses Flash.
Answer from Gyrocode.com on Stack OverflowDataTables
datatables.net › extensions › buttons › examples › initialisation › export.html
DataTables example - File export
new DataTable('#example', { layout: { topStart: { buttons: ['copy', 'csv', 'excel', 'pdf', 'print'] } } });
Top answer 1 of 2
7
SOLUTION
You can use $() method to get access to all rows even not present in DOM and construct a new table using these rows. Then you can execute table2excel() on a newly constructed table to get Excel file that contains all rows.
For example:
$(function() {
var startDate = $(".startDate").val();
var endDate = $(".endDate").val();
$("#exportExcel").click(function(){
$('<table>')
.append(
$("#table_id").DataTable().$('tr').clone()
)
.table2excel({
exclude: ".excludeThisClass",
name: "Worksheet Name",
filename: "SomeFile" //do not include extension
});
});
$("#table_id").dataTable();
});
DEMO
See this page for code and demonstration.
NOTES
Excel 2013 displays the following error when opening the file produced by table2excel.js.
Excel cannot open the file
[filename]because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Because of this error, I would rather use DataTables TableTools plug-in instead even though it can only produce CSV files and also uses Flash.
2 of 2
0
I find the solution here to export data from the table to excel sheet
Videos
Bootsnipp
bootsnipp.com › snippets › yyoZ1
Bootstrap Snippet Bootstrap 4 - DataTables with PDF, Excel Export Feature using HTML CSS Bootstrap jQuery
body { background-color: #F1F4F5; } .card-header { padding: 0.2rem 1.25rem; /* margin-bottom: 0; */ background-color: #ffffff; border-bottom: 0px; } .card-body { padding: 0rem 1.25rem; } p { margin-top: 0; margin-bottom: 10px; } .card { border-radius: 0px; padding-top: 15px; padding-bottom: 15px; } .flex-wrap { margin-bottom: -35px; } div.dataTables_wrapper div.dataTables_paginate { margin-top: -25px; } .page-item.active .page-link { z-index: 1; color: #fff; background-color: #5D78FF; border-color: #5D78FF; } $(document).ready(function() { $('#table_id').DataTable({ dom: 'Bfrtip', responsive: true, pageLength: 25, // lengthMenu: [0, 5, 10, 20, 50, 100, 200, 500], buttons: [ 'copy', 'csv', 'excel', 'pdf', 'print' ] }); });
YouTube
youtube.com › watch
Bootstrap v5 Data Table. Export Table Data in Excel, PDF, CSV, Print and Copy. - YouTube
In this tutorial, I'm going to show you how to use Bootstrap5 Data Table in a very easy way and how to Export Table Data in Copy, CSV, Excel, PDF and Print. ...
Published April 1, 2024
MDBootstrap
mdbootstrap.com › standard › material design for bootstrap 5 & vanilla javascript
How to export data table to excel - Material Design for Bootstrap
https://datatables.net/extensions/buttons/examples/initialisation/export.html · https://datatables.net/reference/button/excel · Add comment · Write Create snippet · FREE CONSULTATION · Hire our experts to build a dedicated project. We'll analyze your business requirements, for free. Status · Answered · Specification of the issue · ForumUser: Priority · Premium support: Yes · Technology: General Bootstrap questions ·
DataTables
datatables.net › forums › discussion › 78202 › modifying-datatable-function-to-use-export-to-excel-functionality
Modifying datatable function to use export to excel functionality — DataTables forums
February 17, 2024 - There are two web pages that will be using the export to excel code and this is kept in a single .js file. The problem here is, the number of columns is not the same in both of these web pages. In one of the page, there are 7 columns. In the other page, there are 3 columns.
Bootstrap Table
bootstrap-table.com › docs › extensions › export
Table Export · Bootstrap Table
Default: ['json', 'xml', 'csv', 'txt', 'sql', 'excel'] ... Set true to force export a column e.g.
DataTables
datatables.net › forums › discussion › 68680 › export-to-excel-with-bootstrap-switch-in-one-column
Export to Excel with bootstrap switch in one column — DataTables forums
I tried this nice workaround but I still have an empty value in Excel What I do which works perfectly well by the way is to change the input for a span and then change the span back to an input after export. For the purpose, I changed the bootstrapSwitch for a normal input type checkbox
DataTables
datatables.net › extensions › buttons › examples › styling › bootstrap.html
DataTables example - Bootstrap 3
This example shows DataTables and ... integration provides seamless integration for DataTables to be used in a Bootstrap page. ... new DataTable('#example', { layout: { topStart: { buttons: ['copy', 'excel', 'pdf', 'colvis'] } } });...
Prepbootstrap
prepbootstrap.com › bootstrap-template › table-export-to-excel
Export a Table to Excel Template | PrepBootstrap
This template shows a standard Bootstrap table containing some data, which is parsed by the ShieldUI's DataSource widget and exported to Excel using the Excel Export functionality provided by the same library.
CodePen
codepen.io › yashwant › pen › MbjvzJ
DataTable - Export To Excel
$(document).ready( function() { $('#example').DataTable( { dom: 'Bfrtip', buttons: [ { extend: 'excelHtml5', customize: function( xlsx ) { var sheet = xlsx.xl.worksheets['sheet1.xml']; $('row c[r^="C"]', sheet).attr( 's', '2' ); } } ] } ); } );
MDBootstrap
mdbootstrap.com › standard › material design for bootstrap 5 & vanilla javascript
Export Datatable to xlsx file - Material Design for Bootstrap
Datatable doesn't have any internal method to export data to an excel file.
CodePen
codepen.io › oliuz › pen › mYObzg
bootstrap 4 datatable export
:after, :before { box-sizing: ... } .excel { background-color: #3ca23c !important; } .csv { background-color: #e86c3a !important; } .imprimir { background-color: #8766b1 !important; } /* Esto es opcional pero sirve para que todos los botones de exportacion se distribuyan de manera equitativa usando flexbox .flexcontent { display: flex; justify-content: space-around; } */ .selectTable{ height:40px; float:right; } div.dataTables_wrapper ...
YouTube
youtube.com › watch
DataTabels JS | How to export table in Excel, CSV, PDF with "DataTable JS" | "Export Table to Excel" - YouTube
Hello Friends,In this video, I will going to show you How to export table in Excel, CSV, PDF using DataTable JS, "Export Table to Excel"Example to refer: ht...
Published August 15, 2020
JSFiddle
jsfiddle.net › opchggs3 › 3
jquery datatable export table to PDF/Excel demo - JSFiddle - Code Playground
JSFiddle - Test your JavaScript, CSS, HTML or CoffeeScript online with JSFiddle.
DataTables
datatables.net › extensions › buttons › examples › styling › bootstrap4.html
DataTables example - Bootstrap 4
Format output data - export options · Excel - auto filter · Excel - Cell background · Excel - Bold text · Excel - Customise borders · PDF - message · PDF - page size and orientation · PDF - image · PDF - open in new window · Custom file (JSON) Basic column visibility · Multi-column layout · Customisation of column button text · Internationalisation · Restore column visibility · Select columns · Visibility toggle buttons ·
Top answer 1 of 2
1
You are initializing the dataTable before the table gets populated, that's why it can't see any of the dynamic data you are fetching via ajax. Put the initialization code after the data table has been rendered (see -- INIT -- below)
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
// push the init code
$("#showGroupSummaryForm").validate({
//debug: true,
rules: {
startDate: {
required: true
},
endDate: {
required: true
}
},
messages: {
startDate: {
required: "Please select a start date."
},
endDate: {
required: "Please select an end date."
}
},
submitHandler : function(showGroupSummaryForm) {
$('#joinedTable tbody > tr').remove();
$('#ajaxGetUserServletResponse1').text('');
var dataToBeSent = {
ssAccountID : sessionStorage.getItem('ssAccountID'),
startDate : $("#startDate").val(),
endDate: $("#endDate").val()
};
//Joined
$.ajax({
url : 'GroupSummaryJoinedView', // Your Servlet mapping or JSP(not suggested)
data : dataToBeSent,
type : 'POST',
dataType: 'JSON',
})
.fail (function(jqXHR, textStatus, errorThrown) {
if(jqXHR.responseText.includes('No members joined in this date range')){
$('#ajaxGetUserServletResponse1').text('No members joined in this date range.');
}else{
$('#ajaxGetUserServletResponse1').text('Error getting joined data.');
}
$("#datepicker1").focus();
})
.done(function(responseJson1a){
// JSON response to populate the joined table
populateTable(responseJson1a)
// -- INIT --
// initialize the table
// after the data has loaded
$('#joinedTable').DataTable( {
dom: 'Bfrtip',
buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
serverSide: true,
initComplete : function () {
table.buttons().container()
.appendTo( $('#joinedSpace .col-sm-6:eq(0)'));
},
});
})
}
});
}); // end document.ready
function populateTable(object) {
var obj = object;
var table = $("<table id='joinedTable'/>");
table[0].border = "1";
var columns = Object.keys(obj[0]);
var columnCount = columns.length;
var row = $(table[0].insertRow(-1));
for (var i = 0; i < columnCount; i++) {
var headerCell = $("<th />");
headerCell.html([columns[i]]);
row.append(headerCell);
}
for (var i = 0; i < obj.length; i++) {
row = $(table[0].insertRow(-1));
for (var j = 0; j < columnCount; j++) {
var cell = $("<td />");
cell.html(obj[i][columns[j]]);
row.append(cell);
}
}
var dvTable = $("#joinedSpace");
// dvTable.html("");
dvTable.append(table);
}
$(function(){
$("#includedContent").load("MenuGL.html");
$('#datepicker1').datepicker({
format: 'dd/mm/yyyy',
});
$('#datepicker2').datepicker({
useCurrent: false, //Important! See issue #1075
format: 'dd/mm/yyyy',
});
$("#datepicker1").on("dp.change", function (e) {
$('#datepicker2').data("DatePicker").minDate(e.date);
});
$("#datepicker2").on("dp.change", function (e) {
$('#datepicker1').data("DatePicker").maxDate(e.date);
});
});
Another alternative that saves you from populating the table yourself, is to pass the url to the JSON feed directly to the dataTable plugin and let the table handle the ajax and the data population. The Controller has to return the data in the format specified here
$('#example').DataTable( {
"ajax": 'pathToYourController',
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
]
} );
2 of 2
1
The resolution main points are:
var table = $('#joinedTable').DataTable( {
table.clear();
table.rows.add(responseJson1a).draw();
HTML:
<!DOCTYPE html>
<html>
<head>
<title>Group Summary</title>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="A Scout award tracking application">
<meta name="author" content="Glyndwr (Wirrin) Bartlett">
<!-- JQuery -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!-- Validate -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.17.0/jquery.validate.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.17.0/additional-methods.min.js"></script>
<!-- Bootstrap -->
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
<!-- Bootstrap Date Picker-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/js/bootstrap-datepicker.min.js"></script>
<!-- DataTables -->
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<!-- Le styles -->
<!-- Bootstrap -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<!-- DataTables -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css">
</head>
<body>
<div id="groupSummary" class="container-fluid" style="background-repeat: repeat; background-image: url('images/body-bg.jpg');">
<div id="includedContent"></div>
<form data-toggle="validator" role="form" id="showGroupSummaryForm">
<div class="row">
<div class="container-fluid">
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
<div class="jumbotron">
<h3>Group Summary</h3>
<div class="container">
<div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
<div class="form-group">
<div class="input-group date" id="datepicker1">
<input type="text" id="startDate" class="form-control" placeholder="Start Date">
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
</div>
</div>
</div>
<div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
<div class="form-group">
<div class='input-group date' id='datepicker2'>
<input type='text' id="endDate" class="form-control" placeholder="End Date">
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
</div>
</div>
</div>
<button id='submit' class='btn btn-primary btn-lg'>Display Details</button>
</div>
<div class="container" id=joined>
<div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
<table class="table table-hover table-bordered" id="joinedTable">
<thead>
<tr>
<th>Section</th>
<th>Joined</th>
</tr>
</thead>
<tbody id="mytablebody">
</tbody>
</table>
<div style="text-align: center;">
<span id="ajaxGetUserServletResponse1" style="color: red;"></span>
</div>
</div>
</div><!-- /container -->
</div>
</div>
</div>
</div><!-- /row -->
</form>
</div> <!-- /container -->
<!-- Le javascript
================================================== -->
<!-- Placed at the end of the document so the pages load faster -->
<script src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>
<script src="js/DataTable.js"></script>
</body>
</html>
AJAX:
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
var table = $('#joinedTable').DataTable( {
"paging": false,
"ordering": false,
"info": false,
"searching": false,
dom: 'Bfrtip',
buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
columns: [
{data: 'section'},
{data: 'metricTotal'}
]
} );
$("#showGroupSummaryForm").validate({
//debug: true,
rules: {
startDate: {
required: true
},
endDate: {
required: true
}
},
messages: {
startDate: {
required: "Please select a start date."
},
endDate: {
required: "Please select an end date."
}
},
submitHandler : function(showGroupSummaryForm) {
$('#ajaxGetUserServletResponse1').text('');
var dataToBeSent = {
ssAccountID : sessionStorage.getItem('ssAccountID'),
startDate : $("#startDate").val(),
endDate: $("#endDate").val()
};
//Joined
$.ajax({
url : 'GroupSummaryJoinedView', // Your Servlet mapping or JSP(not suggested)
data : dataToBeSent,
type : 'POST',
})
.fail (function(jqXHR, textStatus, errorThrown) {
//alert(jqXHR.responseText);
if(jqXHR.responseText.includes('No members joined in this date range')){
$('#ajaxGetUserServletResponse1').text('No members joined in this date range.');
}else{
$('#ajaxGetUserServletResponse1').text('Error getting joined data.');
}
$("#startDate").focus();
})
.done(function(responseJson1a){
dataType: "json";
// alert(JSON.stringify(responseJson1a));
// Result of alert is:
// [{"section":"Cub","subSection":"Explorer","metric":"Joined","metricTotal":5},{"section":"Cub","subSection":"Pioneer","metric":"Joined","metricTotal":8},{"section":"Joey","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Leader","subSection":"blank","metric":"Joined","metricTotal":5},{"section":"Rover","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Scout","subSection":"blank","metric":"Joined","metricTotal":2}]
table.clear();
table.rows.add(responseJson1a).draw();
})
}
})
}); // end document.ready
$(function(){
$("#includedContent").load("Menu.html");
$('#datepicker1').datepicker({
format: 'dd/mm/yyyy',
});
$('#datepicker2').datepicker({
useCurrent: false, //Important! See issue #1075
format: 'dd/mm/yyyy',
});
$("#datepicker1").on("dp.change", function (e) {
$('#datepicker2').data("DatePicker").minDate(e.date);
});
$("#datepicker2").on("dp.change", function (e) {
$('#datepicker1').data("DatePicker").maxDate(e.date);
});
});
GitHub
gist.github.com › sumonst21 › acd43119361c2e13f4e32bd9891c4efc
bootstrap 4 datatable export · GitHub
Save sumonst21/acd43119361c2e13f4e32bd9891c4efc to your computer and use it in GitHub Desktop. Download ZIP · bootstrap 4 datatable export · Raw · bootstrap-4-datatable-export.markdown · A Pen by Sumon Islam on CodePen. License. Raw · index.html · This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below.
GitHub
gist.github.com › northcoder-repo › 05bde971c7879ea5ebc4907f323376fc
Example using DataTables to export multiple tables to Excel sheets. · GitHub
Example using DataTables to export multiple tables to Excel sheets. - excel_add_sheets.htm