2022年10月

先贴代码

【JS部分】

index:function(){
        //*************************** 自定义export开始
        $(document).on("click", "#btn-export-all", function () {
            //当前窗口
            var index;
            var options = table.bootstrapTable('getOptions');
            var search = options.queryParams({});
            //拼接参数
            var export_url = "card/orders/export";
            var post_url = export_url + '?' + Object.keys(search).map(i => `${i}=${search[i]||''}`).join('&')
            //请求导出
            layer.confirm('确定导出?',{btn: ['确定', '取消'],title:"提示"}, function(){
                    //设置等待5s
                    layer.load(0,{time:5000});
                    //关闭窗口
                    layer.close(layer.index);
                    //执行下载
                    $.ajax({
                    type: "post",
                    url: post_url,
                    data: null,
                    dataType: "json",
                    async:true,
                    success:function(data) {
                            if(data.code == 1){
                                // 创建a标签,设置属性,并触发点击下载
                                var $a = $("<a>");
                                $a.attr("href", data.file);
                                $a.attr("download",data.filename);
                                $("body").append($a);
                                $a[0].click();
                                $a.remove();
                                layer.msg('导出成功', {icon: 1});
                            }else{
                                layer.msg('导出失败', {icon: 2});
                            }
                        }
                    });
                });
        });
        //*************************** 自定义export结束
}

【控制器部分】


/**
 * 导出功能
 */

 public function export()
{
       $this->relationSearch = true;
        if ($this->request->isPost()) {
            set_time_limit(0);
            ini_set("memory_limit", -1); //-1不限制内存

            $xlsName =date('YmdHis').'订单数据';
            //设置过滤方法
            $this->request->filter(['strip_tags', 'trim']);

            //如果发送的来源是Selectpage,则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $total = $this->model
                ->with(['merchandise','lists','user'])
                ->where($where)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->with(['merchandise','lists','user'])
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $order_status_lists=['-1'=>'已退货','1'=>'待发货','2'=>'发货中','3'=>'已发货'];
            $pay_status_lists=['-1'=>'支付失败','1'=>'待支付','2'=>'已支付'];
            $process_status_lists=['-1'=>'处理失败','1'=>'未处理','2'=>'待处理','3'=>'已处理'];

            #数据重组
            foreach ($list as $row) {
                  $row['store_name']=$row['lists']['name'];
                  $row['order_status_txt']=isset($order_status_lists[$row['order_status']])?$order_status_lists[$row['order_status']]:'';
                  $row['pay_status_txt']=isset($pay_status_lists[$row['pay_status']])?$pay_status_lists[$row['pay_status']]:'';
                  $row['process_status_txt']=isset($process_status_lists[$row['process_status']])?$process_status_lists[$row['process_status']]:'';
                  $row['create_time_txt']=!empty($row['create_time'])?date('Y-m-d H:i:s',$row['create_time']):'';
                  $row['pay_time_txt']=!empty($row['pay_time'])?date('Y-m-d H:i:s',$row['pay_time']):'';
                  $row['process_time_txt']=!empty($row['process_time'])?date('Y-m-d H:i:s',$row['process_time']):'';
                  $row['refund_time_txt']=!empty($row['refund_time'])?date('Y-m-d H:i:s',$row['refund_time']):'';
            }
             $xlsCell = [
                ['card_order_no', '订单编号'],
                ['title', '订单标题'],
                ['store_id', '网点Id'],
                ['user_id', '用户ID'],
                ['card_merchandise_id', '卡券商品ID'],
                ['store_name', '店铺名称'],
                ['mobile', '手机号'],
                ['card_type', '卡券类型'],
                ['coupons_no', '卡券编号'],
                ['original_price', '原价'],
                ['shop_price', '售价'],
                ['subsidy_price', '补贴价'],
                ['coupon_price', '优惠价格'],
                ['number', '数量'],
                ['pay_price', '支付金额'],
                ['pay_type', '支付类型'],
                ['pay_status_txt', '支付状态'],
                ['order_status_txt', '订单状态'],
                ['process_status_txt', '处理状态'],
                ['pay_time_txt', '支付日期'],
                ['process_time_txt', '处理日期'],
                ['refund_time_txt', '退款日期'],
                ['refund_reason', '退款原因'],
                ['refund_remark', '退款信息标注'],
                ['create_time_txt', '创建日期']
              ];

          return $this->downloadExcel($xlsName,$xlsCell,$list);    
        }
        
        
}

■注意:不要生搬硬套!我的控制使用了联查

【导出的类】可放在controller里面

    protected function downloadExcel($expTitle, $expCellName, $expTableData)
{
    $xlsTitle    = iconv('utf-8', 'gb2312', $expTitle);//文件名称
    $fileName    = $expTitle;
    $cellNum     = count($expCellName);// 单元格长度
    $dataNum     = count($expTableData);
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    $worksheet->setTitle($expTitle);

    $cellName = [
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 
        'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT'
    ];
    $spreadsheet->getActiveSheet(0)
        ->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格为表头
    $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);// 设置表头单元格
    for ($i = 0; $i < $cellNum; $i++) {
        $spreadsheet->setActiveSheetIndex(0)
        ->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
    // 设置列
    }
    // Miscellaneous glyphs, UTF-8  循环写入数据
    for ($i = 0; $i < $dataNum; $i++) {
        for ($j = 0; $j < $cellNum; $j++) {
            $item_index =$expTableData[$i][$expCellName[$j][0]];
            if(is_numeric($item_index)&strlen($item_index)>9){
              $spreadsheet->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3),' '.$item_index);
            }else{
              $spreadsheet->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3),$item_index);
            }
        }
    }
    $end_filename =$fileName.'.xls';
    #ob_end_clean();
    //这一步非常关键,用来清除缓冲区防止导出的excel乱码
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel');
    //header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls");
    header('Cache-Control: max-age=0');
    //"xls"参考下一条备注
    //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $writer = new Xlsx($spreadsheet);
    //"Excel2007"生成2007版本的xlsx,"Excel5"生成2003版本的xls 调用工厂类
    $writer->save('php://output');
    $xlsData = ob_get_contents();
    ob_end_clean();
    return json(['code'=>1,'filename'=>$end_filename,'file'=>'data:application/vnd.ms-excel;base64,'.base64_encode($xlsData)]);
}

【特别声明】

use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

【视图部分】

index.html

增加导出按钮

   <a href="javascript:;" class="btn btn-warning" title="导出全部" id="btn-export-all"><i class="fa fa-send"></i> 全部导出</a>