db.php
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());//设定mysql编码 } /** **根据传入sql语句 查询mysql结果集 **/ 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; } /** ** 根据传入年级数 查询每个年级的学生数据 **/ 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; } /** **根据年级数查询所有的班级 **/ public function getClassByGrade($grade){ $sql="select distinct(class) from user where grade=".$grade." order by class asc"; $res=$this->getResult($sql); return $res; } /** **根据年级数班级数查询学生信息 **/ 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; } }?> conf.php
"127.0.0.1", "username"=>"root", "password"=>"", "database"=>"phpexcel", "charset"=>"utf8" );?>
export.php
1){ $objPHPExcel->createSheet();//创建新的内置表 } $objPHPExcel->setActiveSheetIndex($i-1);//把新创建的sheet设定为当前活动sheet $objSheet=$objPHPExcel->getActiveSheet();//获取当前活动sheet $objSheet->setTitle($i."年级");//给当前活动sheet起个名称 $data=$db->getDataByGrade($i);//查询每个年级的学生数据 $objSheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");//填充数据 $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');//告诉浏览器数据excel07文件 } header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称 header('Cache-Control: max-age=0');//禁止缓存 }?>