Gags Gags - 5 months ago 60
jQuery Question

Export Bootstrap Data table's all rows to Excel

I am facing an issue on exporting Bootstrap Data Table Rows to Excel.

For Exporting Data to Excel I am using an external plugin called

jquery.table2excel.js
.

Code for Exporting Data table to excel as below:

<script type="text/javascript" src="js/jquery.table2excel.js">
</script>
<script>
$(function() {
var startDate = $(".startDate").val();
var endDate = $(".endDate").val();
$("#exportExcel").click(function(){
$("#table_id").table2excel({
exclude: ".noExl",
//name: "Excel Document Name",
filename: "Data from " + startDate + " to " + endDate
});
});
$("#table_id").dataTable();
});
</script>


For Datatable I am using below library:

<script type="text/javascript" src="js/jquery.dataTables.min.js">
</script>
<script type="text/javascript" src="js/dataTables.bootstrap.js">
</script>


Table is as below:

<table id="table_id" class="table table-striped table-condensed table-
bordered">
<thead>`Table Headers here`</thead>
<tbody>`Rows from Database here`</tbody>
</table>


The problem is described as below:


  1. When I am trying to use Export function then only Visible Rows gets exported into the excel not the paginated rows.



e.g. Suppose if I have 10 rows per page then only first 10 rows will be exported and when I change per page rows to 25 then all 25 gets exported.

I want all rows to be exported at once with plugin I am using. Any ideas please?

Answer Source

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.