我正在使用DataTables和CodeIgniter，并且我一直在按每列筛选表后导出表。
由于我们将使用大量的数据行，我开始开发所有处理（过滤、分页、搜索等）都基于服务器的版本。我当前在将筛选表导出为PDF时遇到问题。
出现的问题：
- 仅导出用户当前可见的行以供下载。其他行（尤其是其他页面上的行——通过分页分隔）都不包括在内
- 如果筛选后得到的行数大于每页允许的行数，则不会得到将表缩小到的所有行
关于如何克服这个问题，我希望得到一些帮助
我的代码如下：视图/List.php
<script>
$(document).ready( function () {
var oTable = $('#tablesupport').dataTable({
"sPaginationType":"bootstrap",
"oLanguage": {
"sSearch": "Search all columns:"
},
"sScrollY": "400px",
"bProcessing": true,
"bServerSide": true,
"sServerMethod": "GET",
"sAjaxSource": "dthelper",
"iDisplayLength": 10,
"aaSorting": [[0, 'asc']],
"aoColumns": [
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true }
]
});
//This function works completely fine if the table is processed client-side,
//I am not as successful on the server-side
function table2xls(oTable, exportmode, tableElm){
var xls = '';
var headers = [];
var rows = [];
xls +="<table><thead><tr>";
//Headers do not appear when server-side processing is used
$(tableElm+' thead').find('th').each(function() {
var $th = $(this);
var text = $th.text();
var header = '<th>' + text + '</th>';
// headers.push(header); // original code
if(text != "") headers.push(header);
});
xls += headers.join(" ");
xls += '<tbody><tr>';
// get table data
if (exportmode == "full") { // total data
var total = oTable.fnSettings().fnRecordsTotal();
for(i = 0; i < total; i++) {
var row = oTable.fnGetData(i);
row = strip_tags(row);
rows.push(row);
}
} else { // This branch works fine, however this does not work (in serv. side) for
// rows on other pages separated by pagination
var filteredrows = $("#tablesupport").dataTable()._('tr', {"filter": "applied"});
for ( var i = 0; i < filteredrows.length; i++ ) {
var numCols = 6;
var col = '<tr>';
for(var j = 0; j < numCols; j++){
if(j==2)
col+= '<td><div style="font-family: Mv Iyyu Formal;">' + filteredrows[i][j] + '</div></td>';
else
col += '<td>' + filteredrows[i][j] + '</td>';
}
//console.log(filteredrows[i][0]);
col += '</tr>';
rows.push(col);
};
/* $(tableElm+' tbody td:visible').each(function(index) {
var row = oTable.fnGetData(this);
row = strip_tags(row);
row = '<td>' + row + '</td>';
rows.push(row);
}); */
}
xls += rows.join();
xls += "</tr></tbody></table>";
//alert(xls);
$("#xlsinput").val(xls);
}
function strip_tags(html) {
var tmp = document.createElement("div");
tmp.innerHTML = html;
return tmp.textContent||tmp.innerText;
}
$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );
$.extend( $.fn.dataTableExt.oStdClasses, {
"sSortAsc": "header headerSortDown",
"sSortDesc": "header headerSortUp",
"sSortable": "header"
} );
$('#tablesupport tr').click(function() {
var href = $(this).find("a").attr("href");
if(href) {
window.location = href;
}
});
$('#export_visible_xls').click(function(event) {
event.preventDefault();
table2xls(oTable, 'visible', 'table#tablesupport');
});
} );
</script>
<div class="container">
<div class="row-fluid"><div class="span12">
<div class="span3">
<div class="btn-group" style="position:relative; right:170px;">
<a class="btn dropdown-toggle" data-toggle="dropdown" href="#">
Actions
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><a href="#" id="export_visible" value="CSV" />CSV</a></li>
<li><a href="#" id="export_visible_pdf" value="PDF">PDF</a></li>
<li><a href="#" id="export_visible_xls" value="XLS" >XLS</a></li>
</ul>
</div>
</div>
<div id="down-button">
</div>
<div class="span3">
<form action=<?php echo base_url() . 'exports/toxls/member_export'; ?> method="post">
<input type="hidden" id="xlsinput" name="xlspost" />
<button type="submit" style="display: none; position: relative; right: 300px;" id="xlsdownload" name="submit" value="Download" class="btn btn-danger" > <i class="icon-download" ></i> Download</button>
</form>
</div>
<div class="span3">
<form action=<?php echo base_url() . 'exports/topdf'; ?> method="post">
<input type="hidden" id="pdfinput" name="pdfpost"></input>
<button type="submit" style="display: none; position: relative; right: 500px;" id="pdfdownload" name="submit" value="Download" class="btn btn-info"> <i class="icon-download" ></i> Download</button>
</form>
</div>
<div class="span3">
<form action=<?php echo base_url() . 'exports/tocsv/member_export'; ?> method="post">
<input type="hidden" id="csvinput" name="csvpost"/>
<button type="submit" style="display: none; position: relative; right: 710px" id="csvdownload" name="submit" value="Download" class="btn btn-primary"> <i class="icon-download" ></i> Download</button>
</form>
</div></div></div></div>
<div class="row-fluid">
<div class="span12">
<div class="span2"></div>
<div class="span8">
<div class="table-responsive">
<div id="dvData">
<table class = "table table-hover" id="tablesupport">
<thead>
<tr>
<th id='nid'>NID</th>
<th>Name (ENG)</th>
<th>Name (DV)</th>
<th>Current address</th>
<th>Permanent address</th>
<th>Status</th>
</tr>
</thead>
<tbody id="fbody" class="rowlink">
</tbody>
<tfoot>
<tr>
<th><input type="text" name="search_nid" placeholder="Search NID" class="search_init" /></th>
<th><input type="text" name="search_name" placeholder="Search Name (ENG)" class="search_init" /></th>
<th><input type="text" name="search_namedv" id="thaanaInput7" class="thaana" placeholder="ނަން ހޯދާ" class="search_init" /></th>
<th><input type="text" name="search_caddress" placeholder="Search curr. address" class="search_init" /></th>
<th><input type="text" name="search_paddress" placeholder="Search perm. address" class="search_init" /></th>
<th><input type="text" name="search_status" placeholder="Search status" class="search_init" /></th>
</tr>
</tfoot>
</table>
</div></div><div class="span2"></div></div></div>
控制器/记录/dHelp（）功能
public function dthelper(){
$numCols = 6;
$this->load->database();
$aColumns = array('member_NID', 'member_fName', 'member_mName', 'member_lName', 'member_fNameDV', 'member_mNameDV', 'member_lNameDV',
'member_currAtoll', 'member_currIsland','member_currAddress', 'member_permAtoll', 'member_permIsland', 'member_permAddress', 'member_status');
$sTable = "membershiprecord";
$iDisplayStart = $this->input->get_post('iDisplayStart', true);
$iDisplayLength = $this->input->get_post('iDisplayLength', true);
$iSortCol_0 = $this->input->get_post('iSortCol_0', true);
$iSortingCols = $this->input->get_post('iSortingCols', true);
$sSearch = $this->input->get_post('sSearch', true);
$sSearch_0 = $this->input->get_post('sSearch_0', true);
$sSearch_1 = $this->input->get_post('sSearch_1', true);
$sSearch_2 = $this->input->get_post('sSearch_2', true);
$sSearch_3 = $this->input->get_post('sSearch_3', true);
$sSearch_4 = $this->input->get_post('sSearch_4', true);
$sEcho = $this->input->get_post('sEcho', true);
// Paging
if(isset($iDisplayStart) && $iDisplayLength != '-1')
{
$this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
}
// Ordering
if(isset($iSortCol_0))
{
for($i=0; $i<intval($iSortingCols); $i++)
{
$iSortCol = $this->input->get_post('iSortCol_'.$i, true);
$bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
$sSortDir = $this->input->get_post('sSortDir_'.$i, true);
if($bSortable == 'true')
{
$this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir));
}
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
if(isset($sSearch) && !empty($sSearch))
{
for($i=0; $i<count($numCols); $i++)
{
$bSearchable = $this->input->get_post('bSearchable_'.$i, true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->like('member_NID', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_fName', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_mName', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_lName', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_fNameDV', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_mNameDV', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_lNameDV', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_currAtoll', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_currIsland', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_currAddress', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_permAtoll', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_permIsland', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_permAddress', $this->db->escape_like_str($sSearch));
$this->db->or_like('member_status', $this->db->escape_like_str($sSearch));
}
}
}
if(isset($sSearch_0) && !empty($sSearch_0))
{
$bSearchable = $this->input->get_post('bSearchable_0', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->or_like('member_NID', $this->db->escape_like_str($sSearch_0));
}
}
if(isset($sSearch_1) && !empty($sSearch_1))
{
$bSearchable = $this->input->get_post('bSearchable_1', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->like('member_fName', $this->db->escape_like_str($sSearch_1));
$this->db->or_like('member_mName', $this->db->escape_like_str($sSearch_1));
$this->db->or_like('member_lName', $this->db->escape_like_str($sSearch_1));
}
}
if(isset($sSearch_2) && !empty($sSearch_2))
{
$bSearchable = $this->input->get_post('bSearchable_2', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->like('member_fNameDV', $this->db->escape_like_str($sSearch_2));
$this->db->or_like('member_mNameDV', $this->db->escape_like_str($sSearch_2));
$this->db->or_like('member_lNameDV', $this->db->escape_like_str($sSearch_2));
}
}
if(isset($sSearch_3) && !empty($sSearch_3))
{
$bSearchable = $this->input->get_post('bSearchable_3', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->like('member_currAtoll', $this->db->escape_like_str($sSearch_3));
$this->db->or_like('member_currIsland', $this->db->escape_like_str($sSearch_3));
$this->db->or_like('member_currAddress', $this->db->escape_like_str($sSearch_3));
}
}
if(isset($sSearch_4) && !empty($sSearch_4))
{
$bSearchable = $this->input->get_post('bSearchable_4', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->like('member_permAtoll', $this->db->escape_like_str($sSearch_4));
$this->db->or_like('member_permIsland', $this->db->escape_like_str($sSearch_4));
$this->db->or_like('member_permAddress', $this->db->escape_like_str($sSearch_4));
}
}
if(isset($sSearch_5) && !empty($sSearch_5))
{
$var = 0;
$bSearchable = $this->input->get_post('bSearchable_5', true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
switch($sSearch_5){
case "Registered member":
$var = 1;
break;
case "Non-registered member":
$var = 2;
break;
case "Ex-member":
$var = 3;
break;
}
$this->db->like('member_status', $this->db->escape_like_str($var));
}
}
// Select Data
$this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
$rResult = $this->db->get($sTable);
// Data set length after filtering
$this->db->select('FOUND_ROWS() AS found_rows');
$iFilteredTotal = $this->db->get()->row()->found_rows;
// Total data set length
$iTotal = $this->db->count_all($sTable);
// Output
$output = array(
'sEcho' => intval($sEcho),
'iTotalRecords' => $iTotal,
'iTotalDisplayRecords' => $iFilteredTotal,
'aaData' => array()
);
foreach($rResult->result_array() as $aRow)
{
$row = array();
/* foreach($aColumns as $col)
{
$row[] = $aRow[$col];
}*/
for($i=0; $i<sizeof($aColumns); $i++){
if($i == 1)
$row[] = $aRow[$aColumns[$i]] ." ". $aRow[$aColumns[$i+1]] ." ". $aRow[$aColumns[$i+2]];
else if($i == 4)
$row[] = $aRow[$aColumns[$i]] ." ". $aRow[$aColumns[$i+1]] ." ". $aRow[$aColumns[$i+2]];
else if($i == 7)
$row[] = $aRow[$aColumns[$i]] .", ". $aRow[$aColumns[$i+1]] .", ". $aRow[$aColumns[$i+2]];
else if($i == 10)
$row[] = $aRow[$aColumns[$i]] .", ". $aRow[$aColumns[$i+1]] .", ". $aRow[$aColumns[$i+2]];
else if($i==2 || $i == 3) continue;
else if($i ==5 || $i==6) continue;
else if($i ==8 || $i==9) continue;
else if($i ==11 || $i==12) continue;
else if($i==13){
$status = $aRow[$aColumns[$i]];
$var = '';
switch($status){
case "1":
$var = "Non-registered member";
break;
case "2":
$var = "Registered member";
break;
case "3":
$var = "Ex-member";
break;
}
$row[] = $var;
}
else $row[] = $aRow[$aColumns[$i]];
}
$output['aaData'][] = $row;
}
echo json_encode($output);
}
上述代码的丑陋与它是一次性编写的这一事实有很大关系。
它正在工作。但在搜索多值时存在漏洞。
你应该用我的功能来代替它。
//function searching by Jacker & Krucamper myreadyweb.com.
// Searching by Jacker & Kc.
if(!empty($sSearch))
{
$str_query = '';
$search_columns = array();
$total_searching = count($aColumns);
for($i=0; $i<$total_searching; $i++)
{
$bSearchable = $this->input->get_post('bSearchable_'.$i, true);
if(!empty($bSearchable))
{
$search_columns[$i] = array(
'columns' => $aColumns[$i],
'keyword' => addslashes($sSearch)
);
}
}
$total_search_column = count($search_columns);
if(!empty($search_columns))
{
foreach($search_columns as $key => $value)
{
if ($key != $total_search_column -1)
{
$str_query .= $value['columns']." REGEXP '".$value['keyword']."' OR ";
}
else
{
$str_query .= $value['columns']." REGEXP '".$value['keyword']."' ";
}
}
}
$str_query = "( $str_query )";
$this->db->where( $str_query, NULL, FALSE);
}