简单的数据导出功能
<?php
require_once './PHPExcel/Classes/PHPExcel.php';
require_once './PHPExcel/Classes/PHPExcel/Reader/Excel2007.php';
require_once './PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
include_once './PHPExcel/Classes/PHPExcel/IOFactory.php';
/*--------------下面是对mysql数据库的连接----------*/
include_once( "inc.php" );
$sql=mysql_query("select ban,room,username,department,state,live,quit from hostel where username!=''");
mysql_query('SET NAMES UTF8'); //设置字符集为UTF8
while($row=mysql_fetch_assoc($sql)){$data[]=$row;}
$fileName = "home";
$headArr = array("楼栋","房号","姓名","部门","状态","入住时间","离职时间");
getExcel($fileName,$headArr,$data);
function getExcel($fileName,$headArr,$data){
//判断数据是否是数组
if(empty($data) || !is_array($data)){
die("data must be a array");
}
//判断文件是否存在
if(empty($fileName)){
exit;
}
$date = date("Y_m_d",time()); //时间
$fileName .= "_{$date}.xlsx"; //文件名
$fileName = iconv("gb2312","utf-8", $fileName);
//创建新的PHPExcel对象
$objPHPExcel = new PHPExcel();
$objProps = $objPHPExcel->getProperties();
//设置表头
$key = ord("A"); //返回ASCII值
foreach($headArr as $v){
$colum = chr($key); //ASCII值返回字符
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1',iconv("gb2312","utf-8", $v));
$key += 1;
}
/*--------------开始从数据库提取信息插入Excel表中------------------*/
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($data as $key => $rows){ //行写入
$span = ord("A");
foreach($rows as $keyName=>$value){// 列写入
$j = chr($span);
$objActSheet->setCellValue($j.$column, iconv("gb2312","utf-8", $value));
$span++;
}
$column++;
}
/*--------------下面是设置其他信息------------------*/
$objPHPExcel->getActiveSheet()->setTitle('Simple'); //重命名表
$objPHPExcel->getActiveSheet()->getStyle()->getFont()->setName('微软雅黑'); //设置字体
$objPHPExcel->getActiveSheet()->getStyle()->getFont()->setSize(14); //字体大小
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
//将输出重定向到一个客户端web浏览器(Excel2007)
//ob_clean();
//ob_end_clean(); //清除缓冲区,避免乱码
header("Content-Type: application/vnd.ms-excel; charset==UTF-8");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$fileName\"");
header('Cache-Control: max-age=0');
/*--------------下面是设置保存路径------------------*/
//通过PHPExcel_IOFactory的写函数将上面数据写出来
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//脚本方式运行，保存在当前目录
$objWriter->save($fileName);
//下载到浏览器
echo "<!>".$fileName."<!>";
exit;
}
?>测试过的，可以用，其他的excel格式自己设置哦！
