PHP 生成excel文件

<?php
	// 模板下载
    public function mbDownload(){
		
		ini_set('max_execution_time', '0');
        import('PHPExcel.PHPExcel');
        import('PHPExcel.PHPExcel.IOFactory');
        import('PHPExcel.PHPExcel.Reader.Excel5');

        $riqi = date("Ymd");
        $path = "/Uploads/excel/apply/".$riqi;
        $filePath = ROOT_PATH.'/public/'.$path;
        if (!file_exists($filePath)){
            mkdir ($filePath,0777,true);
        }

        $info = db('user')->order('id','desc')->select();

        $objectPHPExcel = new \PHPExcel();
        $objectPHPExcel->setActiveSheetIndex(0);

            $objectPHPExcel->getActiveSheet()->mergeCells('A1:L1');
            $objectPHPExcel->getActiveSheet()->setCellValue('A1','申请教师列表');
            $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A2:L2')->getFont()->setSize(11);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A2','申请列表');
            $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(18);
            $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //表格头的输出
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A2','申请ID');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','姓名');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C2','性别');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(6);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('D2','身份证号');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('E2','手机号');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('F2','邮寄地址');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G2','所在学校');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('H2','所教科目');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('I2','获奖学生姓名');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('J2','获奖级别');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('K2','获奖证书');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('L2','申请文件');
            $objectPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            //设置居中
            $objectPHPExcel->getActiveSheet()->getStyle('A2:L2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格为text属性
            $objectPHPExcel->getActiveSheet()->getStyle('C3:C9999')->getNumberFormat()->setFormatCode('@');
            // $objectPHPExcel->getActiveSheet()->getStyle('D3:D9999')->getNumberFormat()->setFormatCode('@');
            // $objectPHPExcel->getActiveSheet()->getStyle('I3:I9999')->getNumberFormat()->setFormatCode('@');
            // $objectPHPExcel->getActiveSheet()->getStyle('K3:K9999')->getNumberFormat()->setFormatCode('@');
            $objectPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $n = 3;
            foreach ($info as $key => $val)
            {
                $objectPHPExcel->getActiveSheet()->setCellValue('A'.($n) ,$val['id']);
                $objectPHPExcel->getActiveSheet()->setCellValue('B'.($n) ,$val['name']);
                switch($val['sex'])
                {
                    case '0': $val['temp_sex']='保密'; break;
                    case '1': $val['temp_sex']='男'; break;
                    case '2': $val['temp_sex']='女'; break;
                }
                $objectPHPExcel->getActiveSheet()->setCellValue('C'.($n) ,$val['temp_sex']);

                $objectPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$n,$val['account'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $objectPHPExcel->getActiveSheet()->getStyle('D'.$n)->getNumberFormat()->setFormatCode("@");
                $objectPHPExcel->getActiveSheet()->setCellValue('E'.($n) ,$val['mobile']);
                
                $objectPHPExcel->getActiveSheet()->setCellValue('F'.($n) ,$val['address']);
                $objectPHPExcel->getActiveSheet()->setCellValue('G'.($n) ,$val['school_name']);
                $objectPHPExcel->getActiveSheet()->setCellValue('H'.($n) ,$val['subject']); //支付类型

                $objectPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$n,$val['student_name'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $objectPHPExcel->getActiveSheet()->getStyle('I'.$n)->getNumberFormat()->setFormatCode("@");

                $objectPHPExcel->getActiveSheet()->setCellValue('J'.($n) ,$val['student_award']);

                $objectPHPExcel->getActiveSheet()->setCellValue('K'.($n) ,'点击查看');
                $objectPHPExcel->getActiveSheet()->getCell('K'.($n))->getHyperlink()->setUrl($_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['SERVER_NAME'].'/'.$val['award_img']);
                $objectPHPExcel->getActiveSheet()->setCellValue('L'.($n) ,'点击下载');
                $objectPHPExcel->getActiveSheet()->getCell('L'.($n))->getHyperlink()->setUrl($_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['SERVER_NAME'].'/'.$val['apply_word']); 
                
                $n++;
            }

            //冻结窗口
            $objectPHPExcel->getActiveSheet()->freezePane('A3');

            //设置背景填充颜色
            $objectPHPExcel->getActiveSheet()->getStyle('A1:L1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objectPHPExcel->getActiveSheet()->getStyle('A1:L1')->getFill()->getStartColor()->setARGB('FF66CCCC');

        $objWriter = new \PHPExcel_Writer_Excel2007($objectPHPExcel);

        // Rename sheet
        $objectPHPExcel->getActiveSheet()->setTitle('申请汇总表');

        ob_end_clean();//清除缓冲区,避免乱码(在windows下添加这条代码没问题，但是在linux系统中，就会报错 错误代码：ERR_CONTENT_DECODING_FAILED)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="申请列表' . date('Y-m-d H:i:s') . '.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel5');  //避免乱码
        $objWriter->save('php://output');

        // 保存到服务器 也可不写则直接下载到本地
        $name2='申请列表-'.date("Y-m-d H_i_s");
        $filename = $filePath.'/'.$name2.'.xls';
        $objWriter->save($filename);



        exit();

    }



?>