fastadmin 使用PhpOffice 自定义导出
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; }
