php导出 excel

php 导出Excel

//导出excel的方法
    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = "订单".date('_Y-m-d')."_".uniqid();//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        vendor("PHPExcel.PHPExcel");
            
        $objPHPExcel = new \PHPExcel();
        $cellName = 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(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->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++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        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;
    }

    /**
     *
     * 导出Excel
     */
    function export_datahandle(){//导出Excel
        $xlsName  = "User";
        $xlsCell  = array(
            array('ofid','账号序列'),
            array('ordernumber','订单号'),
            array('goods','商品'),
            array('consignee','收货人'),
            array('phone','电话'),
            array('address','地址'),
            array('coupontype','优惠券'),
            array('integral','积分'),
            array('qiaodou','俏豆'),
            array('paytype','支付方式'),
            array('price','总价格'),
            array('logistics','物流'),
            array('oddnum','物流单号'),
            array('freight','运费'),
            array('created_at','下单时间'),
            array('ofstate','状态'),
            array('ofproblem','问题'),
        );
        //实例化数据库
        if(input('ofstate') != 0){
            $whereArr_orderform['of.ofstate'] = input('ofstate');
        }elseif(input('ofproblem') != 0){
            $whereArr_orderform['of.ofproblem'] = input('ofproblem');
        }else{
            $whereArr_orderform = null;
        }
        $res = db('orderform')->alias('of')
        ->join('qzb_user u','of.userid = u.uid')
        ->join('qzb_coupon c','c.cid = of.coupon','left') // 优惠券
        ->field('of.*,c.cid,c.cname,c.preferentialtype,u.uname') //c.preferentialtype 1是满减2是折扣
        ->order('of.ofid desc')
        ->where($whereArr_orderform)
        ->select();
        
        if($res == null){
            $this->error('暂无数据可导出');
            return;
        }

        foreach ($res as $key => $value) {
            // 查询订单的商品
            $whereArr_a['ofc.orderformid'] = $value['ofid'];

            $orderformcontent_res = db('orderformcontent')->alias('ofc')
            ->join('qzb_goods g','g.gid= ofc.goodsid','left') // 商品内容
            ->field('g.gname,g.gid,g.gimg,g.price,ofc.num')
            ->where($whereArr_a)
            ->select();
            $goods_res = "";
            foreach ($orderformcontent_res as $k => $val) {
                $k_res = $k + 1;
                $goods_res = $goods_res.$k_res."#"."商品：".$val['gname']."单价：".$val['price']."数量：".$val['num']."/  ";
            }
            $value['goods'] = $orderformcontent_res;

            // 查询订单的收货人及地址
            $whereArr_addr['aid'] = $value['addressid'];

            $address_res = db('address')->field('*')->where($whereArr_addr)->find();
            $address_res['address'] = getregion_chs($address_res["province"],$address_res["city"],$address_res["county"]);
            $value['address'] = $address_res;
            // 优惠券类别
            $value['coupontype'] = "";
            if($value['preferentialtype'] == 1){
                $value['coupontype'] = "满减";
            }elseif($value['preferentialtype'] == 2){
                $value['coupontype'] = "折扣";
            }
            // 支付方式
            if($value['paytype'] == 1){
                $value['paytype'] = "微信支付";
            }elseif($value['paytype'] == 2){
                $value['paytype'] = "支付宝支付";
            }
            // 状态
            if($value['ofstate'] == 1){
                $value['ofstate'] = "未支付";
            }elseif($value['ofstate'] == 2){
                $value['ofstate'] = "已支付/待发货";
            }elseif($value['ofstate'] == 3){
                $value['ofstate'] = "已发货";
            }elseif($value['ofstate'] == 4){
                $value['ofstate'] = "已签收";
            }
            // 问题
            if($value['ofproblem'] == 1){
                $value['ofproblem'] = "正常";
            }elseif($value['ofproblem'] == 2){
                $value['ofproblem'] = "问题";
            }elseif($value['ofproblem'] == 3){
                $value['ofproblem'] = "退款";
            }elseif($value['ofproblem'] == 4){
                $value['ofproblem'] = "退款完成";
            }
            $value['created_at'] = date("Y-m-d H:i:s",$value['created_at']);
            $arrayData['ofid'] = $value['ofid'];
            $arrayData['ordernumber'] = $value['ordernumber'];
            $arrayData['goods'] = $goods_res;
            $arrayData['consignee'] = $address_res['consignee'];
            $arrayData['phone'] = $address_res['phone'];
            $arrayData['address'] = $address_res['address']['province'].' '.$address_res['address']['city'].' '.$address_res['address']['county'].' '.$address_res['addrdetails'];
            $arrayData['coupontype'] = "优惠券：".$value['coupontype']." 详情：".$value['cname'];
            $arrayData['integral'] = $value['integral'];
            $arrayData['qiaodou'] = $value['qiaodou'];
            $arrayData['paytype'] = $value['paytype'];
            $arrayData['price'] = $value['price'];
            $arrayData['logistics'] = $value['logistics'];
            $arrayData['oddnum'] = $value['oddnum'];
            $arrayData['freight'] = $value['freight'];
            $arrayData['created_at'] = $value['created_at'];
            $arrayData['ofstate'] = $value['ofstate'];
            $arrayData['ofproblem'] = $value['ofproblem'];

            $xlsData[$key] = $arrayData;
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
            
    }