将Bootstrap Data表的所有行导出到Excel

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

我正面临将Bootstrap数据表行导出到Excel的问题。

For Exporting Data to Excel I am using an external plugin called jquery.table2excel.js.

对于将数据导出到Excel，我使用的是一个名为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:

对于Datatable，我使用下面的库：

<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.
2. 当我尝试使用导出功能时，只有Visible Rows会导出到excel而不是分页行。

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.

例如假设如果每页有10行，则只导出前10行，当我将每页行数更改为25时，则导出所有25行。

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

我想用我正在使用的插件一次导出所有行。有什么想法吗？

1 个解决方案

#1

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.

您可以使用$（）方法来访问甚至不存在于DOM中的所有行，并使用这些行构造新表。然后，您可以在新构造的表上执行table2excel（）以获取包含所有行的Excel文件。

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.

打开table2excel.js生成的文件时，Excel 2013显示以下错误。

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.

Excel无法打开文件[filename]，因为文件格式或文件扩展名无效。验证文件是否已损坏，以及文件扩展名是否与文件格式匹配。

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.

由于这个错误，我宁愿使用DataTables TableTools插件，即使它只能生成CSV文件并使用Flash。