使用服务器端处理从DataTables导出所有内容？ [英] Export All from DataTables with Server Side processing?
问题描述
我有使用DataTables服务器端处理的表格在我的网站上显示。我希望能够全部导出并导出所有行，而不仅仅是要显示的行。有60000多行和65+列，所以必须通过服务器端处理来完成。
I have tables which use DataTables Server Side processing to show on my website. I want to be able to 'Export All' and have all rows be exported, not just those rows being displayed. There are 60000+ rows and 65+ columns, so it must be done with server side processing.
我已经尝试了一些东西，但到目前为止没有任何工作。
I have tried a few things, but so far nothing has worked.
我试过这个：
{ extend: 'excel',
text: 'Export Current Page',
exportOptions: {
modifier: {
page: 'current'
}
},
customize: function (xlsx)
{
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row:first c', sheet).attr('s', '7');
}
}
仅导出页面上显示的行。
Which only exported the rows that were showing on the page.
我试过这个：
{
text: 'Export All to Excel',
action: function (e, dt, button, config)
{
dt.one('preXhr', function (e, s, data)
{
data.length = -1;
}).one('draw', function (e, settings, json, xhr)
{
var excelButtonConfig = $.fn.DataTable.ext.buttons.excelHtml5;
var addOptions = { exportOptions: { 'columns': ':all'} };
$.extend(true, excelButtonConfig, addOptions);
excelButtonConfig.action(e, dt, button, excelButtonConfig);
}).draw();
}
}
这会将整个表的数据发送到屏幕而不是使用分页并将整个数据集发送到excel文件。
This sends the whole table's data to the screen instead of using the pagination and sending the whole data set to an excel file.
我在谷歌和SO搜索过，但是没有找到有效的解决方案。
I've searched around on Google and here in SO, but have not found a solution that works.
我还应该提一下，我想根据表格中设置的当前过滤器全部导出。这样最终用户只能获得他们正在搜索的行的导出。它们通常将其限制为30k-40k行，仍然使用65+列。我（还）不允许删除/隐藏列。
I should also mention that I want to Export All based on the current filters set on the table. So that the end user will get an Export of only those rows that they are searching for. They typically limit it to 30k - 40k rows, still with the 65+ columns. I don't (yet) allow to remove/hide columns.
编辑/更新
这是次要考虑因素：如果我无法从服务器的响应中导出全部，我可以在服务器上构建Excel文件吗？我的服务器没有安装Excel，我仍然希望我的最终用户获取该文件。我确信我必须找到一种方法将Excel放到我的服务器上，但是如何将任何创建的文件传输给最终用户，这甚至比仅发送一个包含整个数据集的响应更快用户计算机上的Excel文件？
Here's a secondary consideration: If I can't Export All from a response from the server, can I build the Excel file on the server? My servers don't have Excel installed and I will still want my end user to get the file. I'm sure that I'd have to find a way to get Excel onto my servers, but how would I transfer any created files to the end user and would that even be any faster than just sending a response with the whole dataset and creating the Excel file on the user's computer?
编辑
建议我尝试使用jquery的 $。ajax（）来实现此功能。如果有人可以告诉我如何做到这一点，我会尝试第三个按钮。
It was recommended that I try jquery's $.ajax() to get this to work. If someone could give me an idea of how to do that I'll try that for a third button.
我已经可以使用相同的过滤器和所有数据排序由用户添加，并使用按钮执行。上面的第二次尝试确实如此，但将其发送到屏幕。我有PHPExcel和一个可以创建Excel工作表的文件。我如何获取第二个按钮并将其发送到另一个文件以创建Excel工作表？我认为使用jquery的 $ .ajax（）可能会有效，我只是不知道怎么做到。我知道我必须使用 $ _ POST 因为数据可能太大而无法使用 $ _ GET 来将数据发送到PHPExcel文件。
I can already pull all the data, with the same filters and sorting that are added by the user, and do that with a button. The second attempt above does that but sends it to the screen. I have PHPExcel and a file that can create an Excel sheet. How would I take what I get in that second button and send it to the other file to create the Excel sheet? I thought that using jquery's $.ajax() might work, I just don't know how to get it to. I do know that I'll have to use $_POST since the data could be too big to use $_GET to send the data to the PHPExcel file.
我已经可以导出为CSV，但是我需要使用CSV没有的格式导出。这就是为什么我要麻烦使用PHPExcel。
I can already export to a CSV, but I need to export with some formatting, which CSV does not have. That's why I'm going to the trouble of use PHPExcel.
EDIT III
我正在尝试这个，虽然它还没有工作：
I am trying this, though it's not yet working:
{
text: 'Export all to Excel II',
action: function (e, dt, button, config)
{
dt.one('preXhr', function (e, s, data)
{
data.length = -1;
}).one('export', function (e, settings, json, xhr)
{
var excelButtonConfig = $.fn.DataTable.ext.buttons.excelHtml5;
var addOptions = { exportOptions: { 'columns': ':all'} };
$.extend(true, excelButtonConfig, addOptions);
excelButtonConfig.action(e, dt, button, excelButtonConfig);
})
}
}
编辑4
希望是最后一次修改。
我知道我必须做三件事这项工作：
I know that I have to do three things to make this work:
- 获取当前的排序和过滤
- 获取长度设置为-1的数据集
-
将此发送到PHPExcel文件以处理和创建Excel文件
我可以创建一个这样的按钮：
- Get current Sorting and Filtering
- Get dataset with length set to -1
Send this to PHPExcel file for processing and creation of Excel file I can create a button like this:
{
text：'将所有数据导出到Excel'，
操作：
}
{ text: 'Export all Data to Excel', action: }
我只是不知道需要采取什么行动。
I just don't know what the action needs to be.
我上面的第二次尝试拉出了我需要的整个数据集，但将其发送到屏幕而不是我的PHPExcel文件（ ExportAllToExcel.php ）。
My second attempt above pulls the whole dataset that I need, but sends it to the screen instead of to my PHPExcel file (ExportAllToExcel.php).
我一直试图解决这个问题并且没有走得太远。我被告知我需要使用 $。ajax（）来做到这一点，我被告知我不需要使用它。我曾尝试使用和不使用，但未能到达任何地方。
I have been trying to figure this out and haven't gotten very far. I've been told that I need to use $.ajax() to do this, I've been told that I don't need to use that. I have tried with and without and have not been able to get anywhere.
我也试过使用此功能无效：
I have also tried using this to no effect:
$.fn.dataTable.ext.buttons.export =
{
className: 'buttons-alert',
"text": "Export All Test",
action: function (e, dt, node, config)
{
var SearchData = dt.search();
var OrderData = dt.order();
alert("Test Data for Searching: " + SearchData);
alert("Test Data for Ordering: " + OrderData);
}
};
推荐答案
我主要是这样做的。它现在已超时，但这是一个单独的问题，因为数据大小不适用于此工作。对于小型数据集，它可以很好地工作。
I have this working, mostly. It is now timing out, but that's a separate issue due to data size not to this working. For small datasets, it works perfectly.
这是我创建按钮的方式（它是我在这里使用的 export 按钮）：
This is how I create the button (it's the export button that I'm using here):
"buttons": [{
extend: 'collection',
text: 'Selection',
buttons: ['selectAll', 'selectNone']
}, {
extend: 'collection',
text: 'Export',
buttons: ['export', 'excel', 'csv', 'pdf', { extend: 'excel',
text: 'Export Current Page',
exportOptions: {
modifier: {
page: 'current'
}
},
customize: function (xlsx)
{
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row:first c', sheet).attr('s', '7');
}
}]
}
]
这是上面创建的按钮的初始化：
This is the initialization of the button created above:
$.fn.dataTable.ext.buttons.export =
{
className: 'buttons-alert',
id: 'ExportButton',
text: "Export All Test III",
action: function (e, dt, node, config)
{
var SearchData = dt.rows({ filter: 'applied' }).data();
var SearchData1 = dt.search();
console.log(SearchData);
var OrderData = dt.order();
console.log(SearchData1);
var NumCol = SearchData[0].length;
var NumRow = SearchData.length;
var SearchData2 = [];
for (j = 0; j < NumRow; j++)
{
var NewSearchData = SearchData[j];
for (i = 0; i < NewSearchData.length; i++)
{
NewSearchData[i] = NewSearchData[i].replace("<div class='Scrollable'>", "");
NewSearchData[i] = NewSearchData[i].replace("</div>", "");
}
SearchData2.push([NewSearchData]);
}
for (i = 0; i < SearchData2.length; i++)
{
for (j = 0; j < SearchData2[i].length; j++)
{
SearchData2[i][j] = SearchData2[i][j].join('::');
}
}
SearchData2 = SearchData2.join("%%");
window.location.href = './ServerSide.php?ExportToExcel=Yes';
}
};
这是 ServerSide.php 文件中获取的部分数据并将其发送到服务器进行处理：
And here is the part of the ServerSide.php file that gets the data and sends it to the server for processing:
require('FilterSort.class.php');
if (isset($_GET['ExportToExcel']) && $_GET['ExportToExcel'] == 'Yes')
{
$request = @unserialize($_COOKIE['KeepPost']);
$DataReturn = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
require './ExportAllToExcel.php';
}
else
{
echo json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
}
这是我设置用于保持搜索和排序的cookie的方式条件：
This is how I set the cookie that I use to keep the search and sort criteria:
if(isset($_POST['draw']))
{
$KeepPost = $_POST;
$KeepPost['length'] = -1;
$PostKept = serialize($KeepPost);
setcookie("KeepPost",$PostKept,time() + (60*60*24*7));
}
所有这些组合将正确的标准发送到 FilterSort.class.php 应该处理标准并将数据集返回到 ExportAllToExcell.php ，然后创建Excel文件。现在我发送了大量报告，但它超时了。
All this combined sends the correct criteria to FilterSort.class.php which should process the criteria and return the dataset to ExportAllToExcell.php which then creates the Excel file. Right now I'm sending it huge reports and it times out, though.
更新
我稍微改变了我这样做的方式：
I have slightly changed the way that I do this:
这是一组新的按钮：
"buttons": [{
extend: 'collection',
text: 'Export',
buttons: ['export', { extend: 'csv',
text: 'Export All To CSV', //Export all to CSV file
action: function (e, dt, node, config)
{
window.location.href = './ServerSide.php?ExportToCSV=Yes';
}
}, 'csv', 'pdf', { extend: 'excel',
text: 'Export Current Page', //Export to Excel only the current page and highlight the first row as headers
exportOptions: {
modifier: {
page: 'current'
}
},
customize: function (xlsx)
{
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row:first c', sheet).attr('s', '7');
}
}]
}
]
以下是我创建全部导出到Excel 按钮的方法：
Here is how I create the Export All to Excel button:
$.fn.dataTable.ext.buttons.export =
{
className: 'buttons-alert', //Adds the "Export all to Excel" button
id: 'ExportButton',
text: "Export All To Excel",
action: function (e, dt, node, config)
{
window.location.href = './ServerSide.php?ExportToExcel=Yes';
}
};
这些现在将数据发送到同一个 ServerSide.php 文件中之前正在使用：
These now send the data to the same ServerSide.php file that I was using before:
require('FilterSort.class.php');
if (isset($_GET['ExportToExcel']) && $_GET['ExportToExcel'] == 'Yes')
{
include 'Helper/LogReport.php';
$GetSQL = "Select Value from PostKept where UserName = '" .$_COOKIE['UserName']. "'";
$KeepResult = $conn->query($GetSQL);
$KeepResults = $KeepResult->fetchALL(PDO::FETCH_ASSOC);
$request = unserialize($KeepResults[0]['Value']);
$DataReturn = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader,1));
require './ExportAllToExcel.php';
我也改变了保存查询的方式，我现在也保留了表名和用户名是这样的：
I have also changed the way that I keep the query, I have it now also keeping the Table Name and UserName like this:
include 'DBConn.php';
$KeepPost = $_POST; //POST holds all the data for the search
$KeepPost['length'] = -1; //-1 means pulling the whole table
$PostKept = serialize($KeepPost); //This takes the array of data and turns it into a string for storage in SQL
$SQLCheck = "select distinct UserName from PostKept"; //Gets all the distinct Usernames of users that have used the Report Dashboard.
$sth = $conn->query($SQLCheck);
$CheckedUser = $sth->fetchALL(PDO::FETCH_ASSOC);
foreach($CheckedUser as $User)
{
foreach($User as $Index => $Who)
{
$FoundUsers[] = $Who; //Taking all the found users and placing them into a simpler array for searching later
}
}
if(isset($_COOKIE['UserName']) && in_array($_COOKIE['UserName'],$FoundUsers)) //If the user already has an entry update it with new information
{
$TSQL = "UPDATE PostKept set Value = '" .$PostKept. "', TableName = '" .$TableName. "' where UserName = '" .$_COOKIE['UserName']. "'";
}
else
{
if(isset($_COOKIE['UserName'])) //If this is a new user
{
$TSQL = "INSERT into PostKept(Value, TableName, UserName) select '" .$PostKept. "','" .$TableName. "','" .$_COOKIE['UserName']. "'";
}
else //If this is on the Prod site and the User info is not yet kept
{
$TSQL = "INSERT into PostKept(Value, TableName) select '" .$PostKept. "','" .$TableName. "'";
}
}
$sth = $conn->prepare($TSQL);
$sth->execute();
现在，所有这些都将数据发送到 ExportAllToExcel.php 我拥有的文件，然后依次创建文件。
This is now what all combines to send the data to the ExportAllToExcel.php file that I have and then it in turn creates the file.
这篇关于使用服务器端处理从DataTables导出所有内容？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
