html中添加按钮
<a href="javascript:;" class="btn btn-success btn-export" title="{:__('导出数据')}" ><i class="fa fa-cloud-download"></i> {:__('导出数据')}</a>
对应的js添加代码处理点击事件,添加到初始化表格前
// 修改导出按钮点击事件处理逻辑$(document).on('click', '.btn-export', function() {Layer.confirm('确认根据筛选条件导出数据?不选择创建时间默认筛选最近30天', {offset: 'auto',btn: ['确定', '取消']}, function(index) {// 获取表格对象var table = $('#table');// 获取表格的当前查询参数var queryParams = table.bootstrapTable('getOptions').queryParams;// 构建查询参数对象var params = {};if (typeof queryParams === 'function') {params = queryParams({page: 1, pageSize: 10});}// 添加location.search中的参数var searchParams = Fast.api.query(location.search);$.extend(params, searchParams);// 构建完整的导出URLvar baseUrl = 'order/export';var exportUrl = baseUrl + '?' + $.param(params);// 创建隐藏的a标签用于触发下载const downloadLink = document.createElement('a');downloadLink.style.display = 'none';document.body.appendChild(downloadLink);// 使用fetch API获取文件流fetch(exportUrl).then(response => {// 检查响应状态if (!response.ok) {throw new Error('服务器响应错误: ' + response.status);}// 检查响应内容类型const contentType = response.headers.get('content-type');if (!contentType || !contentType.includes('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')) {// 如果不是Excel文件,尝试解析为JSON错误信息return response.json().then(errorData => {throw new Error(errorData.msg || '导出失败: 服务器返回无效内容');}).catch(() => {throw new Error('导出失败: 服务器返回无效内容');});}// 生成文件名const now = new Date();const year = now.getFullYear();const month = String(now.getMonth() + 1).padStart(2, '0');const day = String(now.getDate()).padStart(2, '0');const hours = String(now.getHours()).padStart(2, '0');const minutes = String(now.getMinutes()).padStart(2, '0');const filename = `订单导出_${year}-${month}-${day}_${hours}${minutes}.xlsx`;return response.blob().then(blob => ({ blob, filename }));}).then(({ blob, filename }) => {// 创建对象URL并触发下载const url = window.URL.createObjectURL(blob);downloadLink.href = url;downloadLink.download = filename;downloadLink.click();// 清理资源window.URL.revokeObjectURL(url);document.body.removeChild(downloadLink);Layer.close(index);}).catch(error => {console.error('导出失败:', error);Layer.alert('文件导出失败: ' + error.message, { title: '错误' });document.body.removeChild(downloadLink);Layer.close(index);});}, function(index) {Layer.close(index);});return false;});// 初始化表格table.bootstrapTable({url: $.fn.bootstrapTable.defaults.extend.index_url,templateView: true,pk: 'id',sortName: 'id',columns: [
后端处理导出的方法
/*** 数据导出*/public function export(){//设置过滤方法$this->request->filter(['strip_tags', 'trim']);$filter = $this->request->get("filter", '');$filter = (array)json_decode($filter, true);$filter = $filter ? $filter : [];list($where, $sort, $order, $offset, $limit) = $this->buildparams();//订单状态:1=待支付,2=待发货,3=待收货,4=待评论,5=售后订单(已弃用),6=已完成,7=已取消 -1 待核销if (!empty($filter['company_id'])) {$this->model->whereRaw("FIND_IN_SET({$filter['company_id']}, company_id)");}$pid = [];if (!empty($filter['pid'])) {$uid = User::where('parent_user_id', $filter['pid'])->column('id');$pid = $uid;}$team = [];if (!empty($filter['team_id'])) {$ids = self::forGetUser($filter['team_id']);$team = $ids;}if (!empty($filter['createtime'])) {$time = explode(' - ', $filter['createtime']);$create_time = [strtotime($time[0]), strtotime($time[1])];}else{$create_time = [time()-(86400*30), time()];}$pay_time = [];if (!empty($filter['paymenttime'])) {$time = explode(' - ', $filter['paymenttime']);$pay_time = [strtotime($time[0]), strtotime($time[1])];}$is_jd = -1;if (!empty($filter['is_jd'])) {$is_jd = $filter['is_jd'];}$shop_type = $filter['shop_type'] ?? -1;$state = [3, 4, 5, 6, 7, 8, 9];$list = $this->model->with(['user', 'shop', 'ordergoods'])->where(function ($query) use ($filter, $state, $pid, $team, $create_time, $pay_time,$shop_type,$is_jd) {if (!empty($filter['state']) && $filter['state'] == '-1') {$query->where('type', 1)->where('hx_status', 1);}if (!empty($filter['state']) && $filter['state'] == '2') {$query->where('type', 2)->where('fa_wanlshop_order.state', 2);}if (!empty($filter['state']) && in_array($filter['state'], $state)) {$query->where('fa_wanlshop_order.state', $filter['state']);}if (!empty($filter['order_no'])) {$query->where('fa_wanlshop_order.order_no', $filter['order_no']);}if (!empty($pid)) {$query->whereIn('fa_wanlshop_order.user_id', $pid);}if (!empty($team)) {$query->whereIn('fa_wanlshop_order.user_id', $team);}if (!empty($filter['express_no'])) {$query->where('fa_wanlshop_order.express_no', $filter['express_no']);}if (!empty($create_time)) {$query->whereBetween('fa_wanlshop_order.createtime', $create_time);}if (!empty($pay_time)) {$query->whereBetween('fa_wanlshop_order.paymenttime', $pay_time);}if ($shop_type >= 0) {$query->where('fa_wanlshop_order.shop_type', $shop_type);}if ($is_jd > 0) {$ids = \db('wanlshop_order_goods')->where('is_jd',$is_jd)->column('order_id');$query->whereIn('fa_wanlshop_order.id', $ids);}})->order('id', $order)->select();// 收集所有订单ID$orderIds = array_column($list, 'id');// 一次查询所有支付信息$pays = model('app\admin\model\wanlshop\Pay')->whereIn('order_id', $orderIds)->field('order_id, type, pay_no, price, order_price, freight_price, discount_price, actual_payment')->select();// 构建支付信息映射:order_id => type => pay_info$payMap = [];foreach ($pays as $pay) {if (!isset($payMap[$pay['order_id']])) {$payMap[$pay['order_id']] = [];}$payMap[$pay['order_id']][$pay['type']] = $pay;}// 赋值支付信息foreach ($list as $row) {$row->getRelation('user')->visible(['username', 'nickname']);$row->getRelation('shop')->visible(['shopname']);// 根据订单类型获取对应的支付信息if ($row['live_id'] > 0) {$row->pay = isset($payMap[$row['id']]['live']) ? $payMap[$row['id']]['live'] : null;} else {if ($row['activity_id'] == 0) {$row->pay = isset($payMap[$row['id']]['goods']) ? $payMap[$row['id']]['goods'] : null;} else {$row->pay = isset($payMap[$row['id']]['seckill']) ? $payMap[$row['id']]['seckill'] : null;}}}// 生成Excel文件$spreadsheet = new Spreadsheet();$sheet = $spreadsheet->getActiveSheet();// 设置表头$sheet->setCellValue('A1', '订单ID');$sheet->setCellValue('B1', '订单编号');$sheet->setCellValue('C1', '用户名称');$sheet->setCellValue('D1', '用户电话');$sheet->setCellValue('E1', '配送类型');$sheet->setCellValue('F1', '创建时间');$sheet->setCellValue('G1', '状态');$sheet->setCellValue('H1', '实际支付');$sheet->setCellValue('I1', '含运费');$sheet->setCellValue('J1', '店铺');// 填充数据$rowIndex = 2;foreach ($list as $order) {$sheet->setCellValue('A' . $rowIndex, $order['id']);// 将订单编号设置为文本格式$sheet->setCellValueExplicit('B' . $rowIndex, $order['order_no'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);$sheet->setCellValue('C' . $rowIndex, $order['user']['nickname'] ?? '');$sheet->setCellValueExplicit('D' . $rowIndex, $order['user']['mobile'] ?? '', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);$sheet->setCellValue('E' . $rowIndex, $order['type_text'] ?? '');$sheet->setCellValue('F' . $rowIndex, $order['createtime_text'] ?? '');$sheet->setCellValue('G' . $rowIndex, $order['state_text'] ?? '');$sheet->setCellValue('H' . $rowIndex, $order['pay']['order_price'] ?? '');$sheet->setCellValue('I' . $rowIndex, $order['pay']['freight_price'] ?? '');$sheet->setCellValue('J' . $rowIndex, $order['shop']['shopname'] ?? '');$rowIndex++;}// 设置响应头,返回Excel文件header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="订单数据导出_' . date('YmdHis') . '.xlsx"');header('Cache-Control: max-age=0');$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);$writer->save('php://output');exit;}