thinkphp将数据库表导出excel phpexcel

1、下载phpexcel放到control文件夹下

2、在control文件夹下建templete.xls模板  即excel行头

3、引入，写导出

<?phpnamespace Admin\Controller;use Think\Controller;use Think\Model;/* * 导出数据表数据至excel */class ExportDataController extends Controller{    //检查是否登录    public function _initialize() {        check_login();    }    public function index()    {        $sql = "select * from meeting_enrolls ORDER  by id ";        $lists = M()->query($sql);        $indexKey = array(                      'id',                      'openid',                      'name',                      'sex',                      'company_name',                      'job',                      'telep_phone',                      'mobile',                      'email',                      'enroll_time',                      'raoma',                      'enter_code',                      'examine',                      'invite_code',                      'sign_state',                      'sign_time',                      'pic',                      'source',                      'meeting_id',                      'trade_no',                      'remark',            );        //$this->exportExcel($lists,'报名记录',$indexKey);        $this->exportExcel2($lists,'报名记录',$indexKey);    }    function exportExcel2($list,$filename,$indexKey=array()){        require_once dirname(__FILE__) . '/PHPExcel/IOFactory.php';        require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';        require_once dirname(__FILE__) . '/PHPExcel/Writer/Excel2007.php';//        require_once "/ThinkPHP/Library/Vendor/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php";//        require_once "/ThinkPHP/Library/Vendor/PHPExcel-1.8/Classes/PHPExcel.php";//        require_once "/ThinkPHP/Library/Vendor/PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php";//        Vendor('PHPExcel-1.8.Classes.IOFactory');//        Vendor('PHPExcel-1.8.Classes.PHPExcel');//        Vendor('PHPExcel-1.8.Classes.PHPExcel.Excel2007');        $header_arr = 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');        $objPHPExcel = new \PHPExcel();                        //初始化PHPExcel(),不使用模板        $template = dirname(__FILE__).'/template.xls';          //使用模板        $objPHPExcel = \PHPExcel_IOFactory::load($template);     //加载excel文件,设置模板        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);  //设置保存版本格式        //接下来就是写数据到表格里面去        $objActSheet = $objPHPExcel->getActiveSheet();//        $objActSheet->setCellValue('A2',  "活动名称：江南极客");//        $objActSheet->setCellValue('C2',  "导出时间：".date('Y-m-d H:i:s'));        $i = 2;        foreach ($list as $row) {            foreach ($indexKey as $key => $value){                //这里是设置单元格的内容                $objActSheet->setCellValue($header_arr[$key].$i,$row[$value]);            }            $i++;        }        // 1.保存至本地Excel表格        //$objWriter->save($filename.'.xls');        // 2.接下来当然是下载这个表格了，在浏览器输出就好了        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="'.$filename.'.xls"');        header("Content-Transfer-Encoding:binary");        $objWriter->save('php://output');    }}

如果ajax请求下载，则改代码

 // 2.接下来当然是下载这个表格了，在浏览器输出就好了        /*        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="'.$filename.'.xls"');        header("Content-Transfer-Encoding:binary");        $objWriter->save('php://output');       */                
                header('pragma:public');header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$filename.'.xls"');header("Content-Disposition:attachment;filename=$filename.xls");//attachment新窗口打印inline本窗口打印header('Cache-Control: max-age=0');//$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);$excle_path = C('APP_PATH').(substr($this->saveExcelToLocalFile($objWriter, $filename),2));$response = array('success' => true,'url' => $excle_path,);if ($response) {$this->ajaxReturn($response, "json");}}
//ajax导出用到的方法function saveExcelToLocalFile($objWriter,$filename){$filePath = './Public/excel/'.$filename;$objWriter->save($filePath);return $filePath;}