PHPExcel导出mysql数据库数据

2015/09/02 42316

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

数据库代码（数据库配置文件自行完成）
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php 
/*db.php*/ 
    require dirname(__FILE__)."/dbconfig.php"; 
   
    class db{ 
        public $conn = null; 
   
        public function __construct($config){ 
            $this->conn=mysql_connect($config['host'],$config['username'],$config['password']) or die(mysql_error()); 
            mysql_select_db($config['database'],$this->conn) or die(mysql_error()); 
            mysql_query("set names ".$config['charset']) or die(mysql_error()); 
        } 
   
        public function getResult($sql){ 
            $resource = mysql_query($sql,$this->conn) or die(mysql_error()); 
            $res = array(); 
            while(($row=mysql_fetch_assoc($resource))!=false){ 
                $res[] = $row; 
            } 
            return $res; 
        } 
   
        public function getUserinfo(){ 
            $sql = "。。。"; 
            $res = self::getResult($sql); 
            return $res; 
        } 
    }

数据库导出代码：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?php 
    $dir = dirname(__FILE__); 
    require $dir."/db.php"; 
    require $dir."/PHPExcel.php"; 
   
    $db = new db($phpexcel); 
    $objPHPExcel = new PHPExcel(); 
   
    for($i=0; $i<3; $i++){ 
        if($i>0){ 
            $objPHPExcel->createSheet(); 
        } 
        $objPHPExcel->setActiveSheetIndex($i); 
        $objSheet = $objPHPExcel->getActiveSheet(); 
        $data = $db->getUserinfo(); 
   
        $objSheet->setCellValue("A1","编号")->setCellValue("B1","登陆名") 
                ->setCellValue("C1","昵称")->setCellValue("D1","电子邮箱") 
                ->setCellValue("E1","学校")->setCellValue("F1","最后登陆时间"); 
   
        $j = 2; 
        foreach ($data as $key => $value) { 
            # code... 
            $objSheet->setCellValue("A".$j,$value['id'])->setCellValue("B".$j,$value['user_login']) 
                    ->setCellValue("C".$j,$value['user_nicename'])->setCellValue("D".$j,$value['user_email']) 
                    ->setCellValue("E".$j,$value['sch_name'])->setCellValue("F".$j,$value['last_login_time']); 
            $j++; 
        } 
    } 
   
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel5"); 
    // $objWriter->save($dir.'/export.xls'); //生成excel文件 
    browser_export("Excel5","browser_excel03.xls"); //浏览器输出 
    $objWriter->save("php://output"); 
   
    function browser_export($type, $filename){ 
        if($type == "Excel5"){ 
            header('Content-Type: application/vnd.ms-excel'); //excel2003 
        }else{ 
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007 
        } 
        header('Content-Disposition: attachment;filename="'.$filename.'"'); 
        header('Cache-Control: max-age=0'); 
    }