tp5 PHPExcle 导出多商品订单

tp5 PHPExcle 导出多商品订单

例子
tp5 PHPExcle 导出多商品订单插图
代码:


public function export(){
        if ($this->request->isPost()) {
            set_time_limit(0);
            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $op = $this->request->post('op');
            $columns = $this->request->post('columns');
            $excel = new \PHPExcel();
            $excel->getProperties()
                ->setCreator("FastAdmin")
                ->setLastModifiedBy("FastAdmin")
                ->setTitle("标题")
                ->setSubject("Subject");
            $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
            $excel->getDefaultStyle()->getFont()->setSize(12);
            $this->sharedStyle = new \PHPExcel_Style();
            $this->sharedStyle->applyFromArray(
                array(
                    'fill'      => array(
                        'type'  => \PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => '000000')
                    ),
                    'font'      => array(
                        'color' => array('rgb' => "000000"),
                    ),
                    'alignment' => array(
                        'vertical'   => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                        'indent'     => 1
                    ),
                    'borders'   => array(
                        'allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN),
                    )
                ));

            $worksheet = $excel->setActiveSheetIndex(0);
            $worksheet->setTitle('标题');

            $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $list=Db::name('totalorder')
                ->alias('o')
                 ->join('users users','o.user_id=users.id','LEFT')
                ->join('orders d','o.id=d.order_id','LEFT')
                ->join('goods g','d.good_id=g.id','LEFT')
                ->field('o.*,d.goods_id,d.num as nums,d.price')

                ->where($where)
                ->select();

            if (empty($list)){
                $this->error('当前没有可以导出的订单','/admin/orders/orders?ref=addtabs');
            }
            $res=[];
            $pay_style=[1=>'LinePay'];
            $goods_info=[];
            foreach ($list as $k => $v) {
                $res[$v['order_number']]['order_number'] = ' '.$v['order_number'];
                $res[$v['order_number']]['realname'] =$v['realname'];
                $res[$v['order_number']]['odd_numbers'] = ' '.$v['odd_numbers'];
                $res[$v['order_number']]['logistics'] =$v['logistics'];
                $res[$v['order_number']]['status'] = $this->model->getStatusList()[$v['status']];
                $res[$v['order_number']]['names'] = $v['names'];
                $res[$v['order_number']]['pay_style'] =$pay_style[$v['pay_style']];
                $res[$v['order_number']]['phone'] = $v['phone'];
                $res[$v['order_number']]['total_price'] = $v['total_price'];
                $res[$v['order_number']]['address'] = $v['address'];
                $res[$v['order_number']]['goods_price'] = $v['price'];
                $res[$v['order_number']]['payment_time'] = date('Y-m-d H:i:s',$v['pay_time']);
                $res[$v['order_number']]['createtime'] = date('Y-m-d H:i:s',$v['createtime']);
                $res[$v['order_number']]['goods'][] = '商品名称:'.$v['goodsname'].' 商品单价:'.$v['price'].' 商品数量:'.$v['nums'];
                $goods_info[]='商品名称:'.$goods['goodsname'].' 商品单价:'.$v['price'].' 商品数量:'.$v['nums'];
            }
            $res=array_values($res);

            $data = [];
            #表头
            $data1 = ['订单编号','用户','订单金额', '购买商品', '订单状态', '支付方式', '下单时间','支付时间','收货人', '联系方式','收货地址','物流号','物流公司',
            ];
            #导出内容组合
            foreach ($res as $kk=>$vv){
            
                $data[$kk+1]['order_number']=$vv['order_number'];
                $data[$kk+1]['realname']=$vv['realname'];
                $data[$kk+1]['names']=$vv['names'];
                $data[$kk+1]['phone']=$vv['phone'];
                $data[$kk+1]['address']=$vv['address'];
                $data[$kk+1]['total_money']=$vv['total_price'];
                $data[$kk+1]['goods']=$vv['goods'];
                $data[$kk+1]['status']=$vv['status'];


                $data[$kk+1]['pay_style']=$vv['pay_style'];
                $data[$kk+1]['createtime']=$vv['createtime'];
                $data[$kk+1]['payment_time']=$vv['payment_time'];
                $data[$kk+1]['odd_numbers']=$vv['odd_numbers'];
                $data[$kk+1]['logistics']=$vv['logistics'];

            }

            $this->exportOrderExcel2("订单列表",$data1,$data);
            $this->success('导出成功');
        }
    }

function exportOrderExcel2($title, $cellName, $data) {
        //引入核心文件
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        //定义配置
        $topNumber = 2;//表头有几行占用
        $xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
        $fileName = $title.date('_YmdHis');//文件名称
        $cellKey = array(
            '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','AU','AV','AW','AX','AY','AZ'
        );

        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//所有单元格(列)默认宽度

        //垂直居中
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //处理表头标题
        $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($cellName)-1].'1');//合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$title);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //处理表头
        foreach ($cellName as $k=>$v)
        {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v);//设置表头数据
// $objPHPExcel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
// $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗
// $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
// if($v[3] > 0)//大于0表示需要设置宽度
// {
// $objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v[3]);//设置列宽度
// }
        }

        //处理数据
        $start = $topNumber+1;
        $j = $topNumber+1;
        foreach ($data as $k=>$v)
        {
// $j = $start;
            foreach ($v['goods'] as $k1=>$v1)
            {
                if($k1==0){
                    //计算初步当前单元格标识,以及 需要合并的单元格标识 A B C D E F G M
                    $end = $start+count($v['goods'])-1;
                    $objPHPExcel->getActiveSheet()->mergeCells("A".$start.':'."A".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("B".$start.':'."B".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("C".$start.':'."C".$end);
// $objPHPExcel->getActiveSheet()->mergeCells("D".$start.':'."D".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("E".$start.':'."E".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("F".$start.':'."F".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("G".$start.':'."G".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("H".$start.':'."H".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("I".$start.':'."I".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("J".$start.':'."J".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("K".$start.':'."K".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("L".$start.':'."L".$end);
                    $objPHPExcel->getActiveSheet()->mergeCells("M".$start.':'."M".$end);
// $objPHPExcel->getActiveSheet()->mergeCells("M".$start.':'."M".$end);

                    $objPHPExcel->getActiveSheet()->setCellValue("A".$start, $v['order_number']);
                    $objPHPExcel->getActiveSheet()->setCellValue("B".$start, $v['realname']);

                    $objPHPExcel->getActiveSheet()->setCellValue("C".$start, $v['total_money']);
                    $objPHPExcel->getActiveSheet()->setCellValue("E".$start, $v['status']);
                    $objPHPExcel->getActiveSheet()->setCellValue("F".$start, $v['pay_style']);
                    $objPHPExcel->getActiveSheet()->setCellValue("G".$start, $v['createtime']);
                    $objPHPExcel->getActiveSheet()->setCellValue("H".$start, $v['payment_time']);
                    $objPHPExcel->getActiveSheet()->setCellValue("I".$start, $v['names']);
                    $objPHPExcel->getActiveSheet()->setCellValue("J".$start, $v['phone']);
                    $objPHPExcel->getActiveSheet()->setCellValue("K".$start, $v['address']);
                    $objPHPExcel->getActiveSheet()->setCellValue("L".$start, $v['odd_numbers']);
                    $objPHPExcel->getActiveSheet()->setCellValue("M".$start, $v['logistics']);
                }

                $objPHPExcel->getActiveSheet()->setCellValue("D".$j, $v1);
              /* $objPHPExcel->getActiveSheet()->setCellValue("I".$j, $v1['sku_title']); $objPHPExcel->getActiveSheet()->setCellValue("J".$j, $v1['skuid']); $objPHPExcel->getActiveSheet()->setCellValue("K".$j, $v1['sku_num']); $objPHPExcel->getActiveSheet()->setCellValue("L".$j, sprintf("%.2f",($v1['sku_activity_price']*$v1['sku_num'])));*/
                $j++;
            }

            $start += count($v['goods']);
        }

        //导出execl
        ob_end_clean();//防止乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

引用

https://www.cnblogs.com/phpjinggege/p/9016162.html

没有账号? 忘记密码?

社交账号快速登录