PHPExcel连接MySQL下载其中数据生成excel文件

PHPExcel连接MySQL下载其中数据生成excel文件
请注意 PHPExcel 要在php.ini文件中开启 php_zip.dll这个extension，去掉前面的 ‘;’ 就可以了
以下分享代码：

session_start();
include ('../Classes/PHPExcel.php');
include ('../Classes/PHPExcel/Writer/Excel2007.php');
global $data;
$conn = mysql_connect("127.0.0.1", "root", "yourpassword");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}

if (!mysql_select_db("dbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$creatorid=$_SESSION['MM_Userid'];

$sql = "SELECT * FROM tablename WHERE creatorid = '$creatorid' ORDER BY id DESC";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

    
    $fileheader= array('courseid','coursetitle','chapterid','chaptertitle','bookid','booktitle','author','previous','next','sourcelink','publishingtime','clicks','content'); 
	$savefile=date('YmdHis',time()).'courseinfo';
	$savefile=iconv('utf-8','gb2312',$savefile);
  
	$sheetname='sheet1';

	$arr= array();
while($list=mysql_fetch_assoc($result)){
$arr[]=$list;
}
$data=$arr;
//var_dump($data);
//print $data[0]['id'];
exportExcel($data,$savefile,$fileheader,$sheetname);


 function exportExcel($data,$savefile,$fileheader,$sheetname){  
  //或者excel5，用户输出.xls，不过貌似有bug，生成的excel有点问题，底部是空白，不过不影响查看。
        //import("Org.Util.PHPExcel.Reader.Excel5");
        //new一个PHPExcel类，或者说创建一个excel，tp中“\”不能掉
 $excel = new PHPExcel();

        //设置excel属性
   $objActSheet = $excel->getActiveSheet();
        //根据有生成的excel多少列，$letter长度要大于等于这个值
       $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
        //设置当前的sheet
        $excel->setActiveSheetIndex(0);
        //设置sheet的name
		
        $objActSheet->setTitle($sheetname);
       
        //设置表头
		
        for($i = 0;$i < count($fileheader);$i++) {
            //单元宽度自适应,1.8.1版本phpexcel中文支持勉强可以，自适应后单独设置宽度无效
            //$objActSheet->getColumnDimension("$letter[$i]")->setAutoSize(true); 
            //设置表头值，这里的setCellValue第二个参数不能使用iconv，否则excel中显示false
            $objActSheet->setCellValue("$letter[$i]1",$fileheader[$i]); 
            //设置表头字体样式
            $objActSheet->getStyle("$letter[$i]1")->getFont()->setName('微软雅黑');
            //设置表头字体大小
            $objActSheet->getStyle("$letter[$i]1")->getFont()->setSize(12);
            //设置表头字体是否加粗
            $objActSheet->getStyle("$letter[$i]1")->getFont()->setBold(true);
            //设置表头文字垂直居中
            $objActSheet->getStyle("$letter[$i]1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置文字上下居中
            $objActSheet->getStyle($letter[$i])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设置表头外的文字垂直居中
            $excel->setActiveSheetIndex(0)->getStyle($letter[$i])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        //单独设置D列宽度为15
        $objActSheet->getColumnDimension('M')->setWidth(150);

        //这里$i初始值设置为2，$j初始值设置为0，自己体会原因
for ($i = 2;$i <= count($data) + 1;$i++) {		
$j=0;
foreach ($data[$i-2] as $key=>$value){
 
     $objActSheet->setCellValue("$letter[$j]$i",$value);
	 $j++;
	}
}

//$objActSheet->setCellValue("$letter[0]2",$data[0]['id']);
	
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  //      header('Content-Type: application/vnd.ms-excel');
        //下载的excel文件名称，为Excel5，后缀为xls，不过影响似乎不大
        header('Content-Disposition: attachment;filename="' . $savefile . '.xlsx"'); 
        header('Cache-Control: max-age=0');
        // 用户下载excel
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output');
        // 保存excel在服务器上
        //$objWriter = new PHPExcel_Writer_Excel2007($excel);
        //或者$objWriter = new PHPExcel_Writer_Excel5($excel);
        //$objWriter->save("保存的文件地址/".$savefile);
        
}