1、现在需要导出的index.html里添加导出按钮
<a href="javascript:;" class="btn btn-info btn-export {:$auth->check('transportation/transport/export')?'':'hide'}" title="{:__('导出')}"><i class="fa fa-download"></i> {:__('导出')}</a>
2、然后在对应的JS文件里添加点击方法
2.1、先定义导出的访问路径，在 [Table.api.init] 的 [extend] 对象添加新路径：
export_url: 'test/export',//导出路径
2.2、后在：var table = $("#table"); 的后面添加 JS 的事件
//导出
$(document).on("click", ".btn-export", function () {
var ids = Table.api.selectedids(table);
var page = table.bootstrapTable('getData');
var all = table.bootstrapTable('getOptions').totalRows;
console.log(ids, page, all);
Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl($.fn.bootstrapTable.defaults.extend.export_url) + "' method='post' 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) {
$(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
},
yes: function (index, layero) {
submitForm(ids.join(","), layero);
return false;
},
btn2: function (index, layero) {
var ids = [];
$.each(page, function (i, j) {
ids.push(j.id);
});
submitForm(ids.join(","), layero);
return false;
},
btn3: function (index, layero) {
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();
};
3、接下来，编写控制器的export的方法
3.1、声明插件类：
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\IOFactory;
3.2、获取及处理导出表的信息
/**
* 获取导出表信息
* @param $field array 需要显示的字段
* @return int[]|string[]
*/
public function getField($field = [])
{
$prefix = config('database')['prefix'];
$table = $prefix . $this->model->name;//查询的表名（带前缀）
$data = Db::query('SHOW FULL COLUMNS FROM ' . $table);
$returnField = array_flip($field); //键值对换
//判断跳过注释为空的和需要忽略的字段
foreach ($data as $val) {
$add = [];
//只获取导出的字段
if (!in_array($val['Field'], $field)) continue;
if ($val['Comment'] != '') {
$add['field'] = $val['Field']; //字段
$add['comment'] = $val['Comment']; //注释
$add['type'] = $val['Type']; //数据类型
$add['chose'] = []; //选择项，当注释包含格式：类型:1=大,2=小，才有选择项
//处理选择项
if (strpos($val['Comment'], ':') !== false) {
$commentArr = explode(':', $val['Comment']);
$add['comment'] = $commentArr[0];
$commentArr = explode(',', $commentArr[1]);
foreach ($commentArr as $k => $v) {
$commentArr = explode('=', $v);
$add['chose'][$commentArr[0]] = $commentArr[1];
}
}
$returnField[$val['Field']] = $add;
}
}
return $returnField;
}
3.3、编写export方法
//导出数据
public function export()
{
set_time_limit(0);//设置时间限制
$ids = $this->request->post('ids'); //获取数据的所有id
//导出的字段
$field = [
'user>nickname', 'user>mobile', 'createtime', 'confirm_time', 'begintime', 'order_num', 'status', 'type','star_name', 'end_name', 'insure_money', 'car_model', 'license_plate', 'quotation>name'
];
//连表数据需要显示的字段，user>mobile表示为：user表下的mobile字段
$fieldComment = [
'user>mobile' => '用户手机号',
'user>nickname' => '用户昵称',
'quotation>name' => '承运商名称',
];
$fields = $this->getField($field);
$objPHPExcel = new Spreadsheet();
$firstChar = 65;
$tempChar = '';
$tempCharNum = '';
foreach ($fields as $key => &$field) {
//当列超出 A-Z时，为：AA-AZ,BA-BZ，以此类推
if ($firstChar > 90) {
$firstChar = 65; //当超出 Z 时，重新跳到 A 开始
if ($tempChar == '') {
$tempCharNum = 65;//当超出 Z 时，从 A 开始时需要加上 A-Z
} else {
$tempCharNum += 1;
}
$tempChar = chr($tempCharNum);
}
$column = chr($firstChar);
$firstChar++;
$char = $tempChar . $column; //哪一列
//处理连表的导出字段
if (is_numeric($field)) {
$field = [
'field' => $key,
'comment' => $fieldComment[$key],
'type' => 'varchar',
'chose' => [],
];
}
$field['char'] = $char;//记录哪一列，数据循环时可直接取用
//设置表头
$objPHPExcel->getActiveSheet()->setCellValue($char . '1', $field['comment']);
//改变长度
$objPHPExcel->getActiveSheet()->getColumnDimension($char)->setWidth(30);
}
// halt($fields);
//查询数据
$wheres = [
"transport.status" => ['in', '1,2']
];
if ($ids != 'all' && !empty($ids)) {
$wheres['transport.id'] = ['in', $ids];
}
$list = $this->model
->with(['user', 'quotation'])
->where($wheres)
->select();
//输出表格
foreach ($list as $key => &$val) {
$val = $val->toArray();
$i = $key + 2;//表格是从2开始的,第1行为表头
foreach ($fields as &$field) {
$data = $val[$field['field']] ?? '';
//连表显示的字段
if (strpos($field['field'], '>') !== false) {
$fieldArr = explode('>', $field['field']);
$data = $val[$fieldArr[0]][$fieldArr[1]];
}
//转换为时间格式
if (is_numeric($data) && mb_strlen($data) == 10) {
$data = date('Y-m-d H:i:s', $data);
}
//将选择项转换成对应的值
if (!empty($field['chose'])) {
$data = $field['chose'][$data];
}
//将char和varchar数据类型的单元格设置为文本类型，以防出现科学计数法
if (strpos($field['type'], 'char') !== false) {
$objPHPExcel->getActiveSheet()->setCellValueExplicit($field['char'] . $i, $data, 's');
} else {
$objPHPExcel->getActiveSheet()->setCellValue($field['char'] . $i, $data);
}
}
}
$outputFileName = date("YmdHis") . '导出.xls';
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $outputFileName . '"');
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter = IOFactory::createWriter($objPHPExcel, 'Xlsx');
$objWriter->save('php://output');
exit;
}
标签：自定义,val,field,data,导出,ids,PhpOffice,fastadmin,layero From： https://www.cnblogs.com/j-jian/p/18146550