原js代码
// 初始化表格参数配置
Table.api.init({
extend: {
index_url: 'xx/xx/index',
del_url: 'xx/xx/del',
table: 'xx',
},
pagination:true,
pageSize:10,
pageList:[10,20,50,100,'All'],
exportTypes: ['excel'], //'json', 'xml', 'txt', 'doc', 'csv',
exportOptions: {
type: 'excel',
onMsoNumberFormat: function(cell, row, col) {
return (row > 0 && col == 0) ? '\\@' : '';
}
}
});
在列表页增加导出按钮
<div class="panel-body">
<div id="myTabContent" class="tab-content">
<div class="tab-pane fade active in" id="one">
<div class="widget-body no-padding">
<div id="toolbar" class="toolbar">
{:build_toolbar('refresh,delete')}
<a href="javascript:;" class="btn btn-success btn-export" title="{:__('Export')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('Export')}</a>
</div>
<table id="table" class="table table-striped table-bordered table-hover table-nowrap"
data-operate-verify="{:$auth->check('xx/xx/xx')}"
width="100%">
</table>
</div>
</div>
</div>
</div>js文件中增加如下代码
function Removedu(data){
var ids = [];
$.each(data, function (i, j) {
if(ids.indexOf(j.id)===-1){
ids.push(j.id);
}
});
return ids;
}
$(document).on("click", ".btn-export", function () {
var ids = Table.api.selectedids(table);
var page = Removedu(table.bootstrapTable('getData'));
var all = table.bootstrapTable('getOptions').totalRows;
Layer.confirm("请选择导出的选项!<form action='" + Fast.api.fixurl("exam/record/exportout") + "' method='get' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='search'><input type='hidden' name='columns'></form>", {
title: '导出数据',
btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
success: function (layero, index) {
console.log('btn0:');
$(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
}, yes: function (index, layero) {
if(ids.length == 0){
layer.msg('未勾选数据！');
return false;
}
console.log('btn1:',ids);
submitForm(ids.join(","), layero);
return false;
},
btn2: function (index, layero) {
console.log('btn2:',page);
if(page.length == 0){
layer.msg('空数据！');
return false;
}
submitForm(page.join(","), layero);
layer.close(index);
return false;
},
btn3: function (index, layero) {
console.log('btn3:');
submitForm("all", layero);
return false;
}
})
});
var submitForm = function (ids, layero) {
var options = table.bootstrapTable('getOptions');
console.log(options);
var columns = [];
$.each(options.columns[0], function (i, j) {
if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
columns.push(j.field);
}
});
var search = options.queryParams({});
$("input[name=search]", layero).val(options.searchText);
$("input[name=ids]", layero).val(ids);
$("input[name=filter]", layero).val(search.filter);
$("input[name=op]", layero).val(search.op);
$("input[name=columns]", layero).val(columns.join(','));
$("form", layero).submit();
};后台待增加如下代码
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
public function exportout()
{
if ($this->request->isGet()) {
$ids = $this->request->get('ids');
$columns = $this->request->get('columns');
//设置过滤方法
$this->request->filter(['strip_tags']);
//如果发送的来源是Selectpage，则转发到Selectpage
if ($this->request->request('keyField')) {
return $this->selectpage();
}
if(empty($ids)){
$this->error('数据不能为空');
}
// 跨模型搜索可以重新定义搜索字段名称
$this->changeName(["exam_member.openid"=> "examMember.openid"]);
if($ids === 'all'){
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
// halt($where);
$list = $this->model
->with(['ExamMember','ExamLab'])
->field('exam_record.*')
->where($where)
->select();
}else{
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
$list = $this->model
->with(['ExamMember','ExamLab'])
->field('exam_record.*')
->where("exam_record.id in (".$ids.")")
->where($where)
->select();
}
$initForm = [
array('title' => 'Id', 'field' => 'id', 'width' => 10,'type' => 'number'),
array('title' => '姓名', 'field' => 'name', 'width' => 20,'type' => 'string'),
array('title' => '年龄', 'field' => 'age', 'width' => 20,'type' => 'string'),
];
$fields = array_column($initForm,'field');
$titles = array_column($initForm,'title','field');
$widths = array_column($initForm,'width','field');
$types = array_column($initForm,'type','field');
$columnsarr = $this->alphabet($fields);
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置标题名称
$worksheet->setTitle('用户列表');
$starkey = key($columnsarr);
end($columnsarr);
$endkey = key($columnsarr);
$worksheet->getStyle($starkey.':'.$endkey)->getAlignment()->setHorizontal('center');//halt($columnsarr);
foreach ($columnsarr as $key => $value) {
$worksheet->setCellValue($key.'1', $titles[$value]);
$worksheet->getColumnDimension($key)->setWidth($widths[$value]);//设置列宽
}
$j = 2;
foreach ($list as $k => &$v){
// 逻辑代码
foreach($columnsarr as $key=>$val){
if($types[$val] == 'string'){
$worksheet->setCellValueExplicit($key.$j,$v[$val],DataType::TYPE_STRING);
}else{
$worksheet->setCellValue($key.$j,$v[$val]);
}
}
$j++;
}
$total = count($list);
$file_name = '用户列表' .date('YmdHis').$total.'.xlsx';
// 客户端文件下载
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename='.$file_name);
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
ob_end_clean();
$writer->save('php://output');
exit;
}
}
protected function alphabet($arr)
{
$newarr = [];
foreach($arr as $k=>$v){
$y = ($k / 26);
if ($y >= 1) {
$y = intval($y);
$key = chr($y+64).chr($k-$y*26 + 65);
$newarr[$key] = $v;
} else {
$key = chr($k+65);
$newarr[$key] = $v;
}
}
return $newarr;
}changeName方法代码如下
/*
* 修改筛选请求字段名称
* @param String $old 请求的字段名称
* @param String $new 需要修改的字段名称
*/
public function changeName($fields = array())
{
$f = json_decode($_GET['filter'], true);
$res = [];
foreach($fields as $old => $new){
// @符屏蔽错误，偷懒没写验证
@$s = $f[$old];
if ($s != '') {
unset($f[$old]);
$f[$new] = $s;
$_GET['filter'] = json_encode($f);
}
}
$res["filter"] = json_encode($f);
$f = json_decode($_GET['op'], true);
foreach($fields as $old => $new){
@$s = $f[$old];
if ($s != '') {
unset($f[$old]);
$f[$new] = $s;
$_GET['op'] = json_encode($f);
}
}
$res["op"] = json_encode($f);
$this->request->get($res);
}