導(dǎo)出，導(dǎo)入Excel文件
連接數(shù)據(jù)庫?
<?php
class db{
public $conn=null;
public function __construct($config){//構(gòu)造方法 實例化類時自動調(diào)用
$this->conn=mysql_connect($config['host'],$config['username'],$config['password']) or die(mysql_error());//連接數(shù)據(jù)庫
mysql_select_db($config['database'],$this->conn) or die(mysql_error());//選擇數(shù)據(jù)庫
mysql_query("set names ".$config['charset']) or die(mysql_error());//設(shè)定mysql編碼
}
/**
**根據(jù)傳入sql語句 查詢mysql結(jié)果集
**/
public function getResult($sql){
$resource=mysql_query($sql,$this->conn) or die(mysql_error());//查詢sql語句
$res=array();
while(($row=mysql_fetch_assoc($resource))!=false){
$res[]=$row;
}
return $res;
}
/**
** 根據(jù)傳入年級數(shù) 查詢每個年級的學(xué)生數(shù)據(jù)
**/
public function getDataByGrade($grade){
$sql="select username,score,class from user where grade=".$grade." order by score desc";
$res=self::getResult($sql);
return $res;
}
/**
** 查詢所有的年級
**/
public function getAllGrade(){
$sql="select distinct(grade) from user? order by grade asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根據(jù)年級數(shù)查詢所有的班級
**/
public function getClassByGrade($grade){
$sql="select distinct(class) from user where grade=".$grade." order by class asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根據(jù)年級數(shù)班級數(shù)查詢學(xué)生信息
**/
public function getDataByClassGrade($class,$grade){
$sql="select username,score from user where class=".$class." and grade=".$grade." order by score desc";
$res=$this->getResult($sql);
return $res;
}
}
?>
導(dǎo)出Excel文件
<?php
$dir=dirname(__FILE__);//查找當(dāng)前腳本所在路徑
require $dir."/db.php";//引入mysql操作類文件
require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
$db=new db($phpexcel);//實例化db類 連接數(shù)據(jù)庫
$objPHPExcel=new PHPExcel();//實例化PHPExcel類惰爬， 等同于在桌面上新建一個excel
for($i=1;$i<=3;$i++){
if($i>1){
$objPHPExcel->createSheet();//創(chuàng)建新的內(nèi)置表
}
$objPHPExcel->setActiveSheetIndex($i-1);//把新創(chuàng)建的sheet設(shè)定為當(dāng)前活動sheet
$objSheet=$objPHPExcel->getActiveSheet();//獲取當(dāng)前活動sheet
$objSheet->setTitle($i."年級");//給當(dāng)前活動sheet起個名稱
$data=$db->getDataByGrade($i);//查詢每個年級的學(xué)生數(shù)據(jù)
$objSheet->setCellValue("A1","姓名")->setCellValue("B1","分?jǐn)?shù)")->setCellValue("C1","班級");//填充數(shù)據(jù)
$j=2;
foreach($data as $key=>$val){
$objSheet->setCellValue("A".$j,$val['username'])->setCellValue("B".$j,$val['score'])->setCellValue("C".$j,$val['class']."班");
$j++;
}
}
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件
//$objWriter->save($dir."/export_1.xls");//保存文件
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');//告訴瀏覽器將要輸出excel03文件
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告訴瀏覽器數(shù)據(jù)excel07文件
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//告訴瀏覽器將輸出文件的名稱
header('Cache-Control: max-age=0');//禁止緩存
}
?>
導(dǎo)入Excel
<?php
?header("Content-Type:text/html;charset=utf-8");
$dir=dirname(__FILE__);//找到當(dāng)前腳本所在路徑require $dir."/PHPExcel/PHPExcel/IOFactory.php";//引入讀取excel的類文件$filename=$dir."/export_1.xls";$fileType=PHPExcel_IOFactory::identify($filename);//自動獲取文件的類型提供給phpexcel用
$objReader=PHPExcel_IOFactory::createReader($fileType);//獲取文件讀取操作對象$sheetName=array("2年級","3年級");$objReader-setLoadSheetsOnly($sheetName);//只加載指定的sheet$objPHPExcel=$objReader->load($filename);//加載文件
/**$sheetCount=$objPHPExcel->getSheetCount();//獲取excel文件里有多少個sheetfor($i=0;$i<$sheetCount;$i++){$data=$objPHPExcel->getSheet($i)->toArray();//讀取每個sheet里的數(shù)據(jù) 全部放入到數(shù)組中print_r($data);}**/
foreach($objPHPExcel->getWorksheetIterator() as $sheet){
//循環(huán)取sheetforeach($sheet->getRowIterator() as $row){//逐行處理
if($row->getRowIndex()<2){continue;
}
foreach($row->getCellIterator() as $cell){
//逐列讀取$data=$cell->getValue();
//獲取單元格數(shù)據(jù)
echo $data." ";}
echo '';
}
echo '';
}exit;?>
