实例 | 使用PHPExcel导出网站数据

实现方法:

<?php 

    //excel导出会员
    public function user_export()
    {
        if($_GET['all']==1){
            $limit =888888;
        }else{
            $limit =200;
        }
        if(!empty($_GET['page'])){
            $listRows = 12; //每页显示的信息条数
            $cur_page = $_GET['page'];
            $limit_start = ($cur_page - 1) * $listRows;
            $limit = $limit_start . ',' . $listRows;
        }
         
         
        $xmlarr = $this->model->table('member')->order("uid desc")->limit($limit)->select();
    
    
        $objExcel = new PHPExcel();
        //设置属性 (这段代码无关紧要，其中的内容可以替换为你需要的)
        $objExcel->getProperties()->setCreator("Chow");
        $objExcel->getProperties()->setLastModifiedBy("Chow");
        $objExcel->getProperties()->setTitle("Office 2003 XLS Test Document");
        $objExcel->getProperties()->setSubject("Office 2003 XLS Test Document");
        $objExcel->getProperties()->setDescription("Test document for Office 2003 XLS, generated using PHP classes.");
        $objExcel->getProperties()->setKeywords("office 2003 openxml php");
        $objExcel->getProperties()->setCategory("Test result file");
        $objExcel->setActiveSheetIndex(0);
    
        $i=0;
        //表头
        $k1="UID";
        $k2="姓名";
        $k3="年龄";
        $k4="电话";
        $k5="注册时间";
       
        $objExcel->getActiveSheet()->setCellValue('a1', $k1);
        $objExcel->getActiveSheet()->setCellValue('b1', $k2);
        $objExcel->getActiveSheet()->setCellValue('c1', $k3);
        $objExcel->getActiveSheet()->setCellValue('d1', $k4);
        $objExcel->getActiveSheet()->setCellValue('e1', $k5);   
    
        foreach($xmlarr  as $item) {
            $u1=$i+2;
            /*----------写入内容-------------*/
            $objExcel->getActiveSheet()->setCellValue('a'.$u1, ' '.$item[uid]);
            $objExcel->getActiveSheet()->setCellValue('b'.$u1, $item['name']);
            $objExcel->getActiveSheet()->setCellValue('c'.$u1, $item['age']);
            $objExcel->getActiveSheet()->setCellValue('d'.$u1, $item['tel']);
            $objExcel->getActiveSheet()->setCellValue('e'.$u1, date("Y-m-d H:i:s",$item['createtime']));
 
            $i++;
        }
    
        // 高置列的宽度
        $objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

        $objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');
        $objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objExcel->getProperties()->getTitle() . '&RPage &P of &N');
    
        // 设置页方向和规模
        $objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objExcel->setActiveSheetIndex(0);
        $timestamp = time();
        $ex='2003';
        if($ex == '2007') { //导出excel2007文档
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="YG-User'.$timestamp.'.xlsx"');
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
            $objWriter->save('php://output');
            exit;
        } else {  //导出excel2003文档
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="YG-User'.$timestamp.'.xls"');
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
            $objWriter->save('php://output');
            exit;
        }
    
    }


 ?>

并引入相关PHPExcel文件,文件在下面,点击即可下载

源码下载:  Excel源码