excel文件的导出

if ($export == 1)//导入条件
        {
            $data = array();
            $data[0][] = 'ID';
            $data[0][] = '用户id';
            $data[0][] = '申请金额';
            $data[0][] = '实际金额';
            $data[0][] = '开户行';
            $data[0][] = '卡号';
            $data[0][] = '开户人姓名';
            $data[0][] = '申请方式';
            $data[0][] = '状态';
            $data[0][] = '时间';
            $result = $model->where($where)->order('id desc')->select();

            foreach ($result as $k=>$v)
            {
                $data[$k+1][] = $v['id'];
                $data[$k+1][] = $v['uid'];
                $data[$k+1][] = $v['price'];
                $data[$k+1][] = $v['tx_price'];
                $data[$k+1][] = $v['bank_name'];
                $data[$k+1][] = $v['bank_num'];
                $data[$k+1][] = $v['name'];
                if ($v['tixian_type'] == 1) { $data[$k+1][] = '申请提现'; }
                if ($v['tixian_type'] == 2) { $data[$k+1][] = '红包发放'; }
                if ($v['tixian_type'] == 3) { $data[$k+1][] = '企业付款'; }
                if ($v['status'] == 1) { $data[$k+1][] = '已完成'; }
    //导出excel文件方法
    public function excel($data=null,$merge_arr=null,$sheetTitle=null){
        if(empty($sheetTitle)){
            $excelFileName=$sheetTitle='data';    
        }else{
            $excelFileName=$sheetTitle;
        }
        
        /*导入phpExcel核心类 */
        require_once APP_PATH.'PHPExcel/PHPExcel.php'; 
        require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel5.php';     // 用于其他低版本xls 
        require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel2007.php'; // 用于 excel-2007 格式 
        /* 实例化类 */
        $objPHPExcel = new PHPExcel(); 
        
        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        
        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);
        
        $objActSheet = $objPHPExcel->getActiveSheet();
        
        /*设置宽度*/
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        
        //设置自动换行
        $arr=array('J','K','L','R');
        foreach ($arr as $v) {
            $objActSheet ->getStyle($v)->getAlignment()->setWrapText(true);//自动换行
        }    
        /* sheet标题 */
        $objActSheet->setTitle($sheetTitle);
        
        $i = 1;
        foreach($data as $value)
        {
            /* excel文件内容 */
            $j = 'A';
            foreach($value as $value2)
            {
                $objActSheet->setCellValueExplicit($j.$i,$value2,PHPExcel_Cell_DataType::TYPE_STRING);
                $j++;
            }
            $i++;
        }
        
        //guth 同一个订单再把相同的内容给合并
        foreach($merge_arr as $info)
        {
            $start_num = $info[0];
            $end_num = $info[count($info)-1];
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$start_num.':A'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('B'.$start_num.':B'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('C'.$start_num.':C'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('D'.$start_num.':D'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('E'.$start_num.':E'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('F'.$start_num.':F'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('G'.$start_num.':G'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('H'.$start_num.':H'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('I'.$start_num.':I'.$end_num.'');        
            $objPHPExcel->getActiveSheet()->mergeCells('J'.$start_num.':J'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('K'.$start_num.':K'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('Q'.$start_num.':Q'.$end_num.'');            
            $objPHPExcel->getActiveSheet()->mergeCells('R'.$start_num.':R'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('S'.$start_num.':S'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('T'.$start_num.':T'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('U'.$start_num.':U'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('V'.$start_num.':V'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('W'.$start_num.':W'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('X'.$start_num.':X'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('Y'.$start_num.':Y'.$end_num.'');
            $objPHPExcel->getActiveSheet()->mergeCells('Z'.$start_num.':Z'.$end_num.'');
        }
        
        //$objPHPExcel->getActiveSheet()->mergeCells('K5:L6');
        /* 生成到浏览器,提供下载 */ 
        ob_end_clean();  //清空缓存             
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$excelFileName.'.xlsx"');
        header("Content-Transfer-Encoding:binary"); 
        $objWriter->save('php://output');
    }

 

elseif ($v['status'] == 2) { $data[$k+1][] = '已退回'; }
                elseif ($v['status'] == 3) { $data[$k+1][] = '红包发放中'; }
                else{$data[$k+1][] = '待审核';}
                $data[$k+1][] = $v['time'];
            }
            R('excel',array($data));
            exit;
        }

 

posted @ 2019-04-11 13:13  罗松  阅读(196)  评论(0编辑  收藏  举报