Now the database has a set of data, that is, according to grade classification of student scores, how to follow the grade classification to export to Excel table
1. Database configuration file config.php
<?php$config = Array (' host ' = ' 127.0.0.1 ', ' username ' = ' root ', ' password ' = ', ' ' database ' = ' phpexcel ') , ' charset ' = ' utf8 ');
2, the database operation file db.php, single-case mode operation
<?PHPclassDb {Private Static $_instance; Private $conn=NULL; Private function__construct () {require"./dbconfig.php"; $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 ']); } //Query Result set Public functionGetResult ($sql) { $resource=mysql_query($sql,$this->conn) or die(Mysql_error()); $res=Array(); while($row=Mysql_fetch_assoc($resource)) { $res[] =$row; } return $res; } //single-case mode Public Static functiongetinstance () {if(!self::$_instanceinstanceof Self) { Self::$_instance=NewSelf (); } returnSelf::$_instance; } Private function__clone () {Trigger_error(' Clone is not allow! ',E_user_error); }}
3. Generating an Excel Class
<?PHPHeader("Content-type:text/html;charset=utf-8");require"./db.php";require"./phpexcel/phpexcel.php";//connecting to a database$db= Db::getinstance ();//instantiating an Excel class$objPHPExcel=NewPhpexcel ();//Create sheet for($i= 1;$i<=3;$i++) { if($i> 1) {//The default already has a sheet, created from the second start $objPHPExcel-Createsheet (); } $objPHPExcel->setactivesheetindex ($i-1); //Gets the action object for the currently active sheet $objSheet=$objPHPExcel-Getactivesheet (); //Set sheet title $objSheet->settitle ($i.‘ Grade); //querying data from a database $Db= Db::getinstance (); //query activity data for each grade $data=$Db->getresult ("SELECT * from user where grade =".$i); $objSheet->setcellvalue ("A1", "name")->setcellvalue ("B1", "Score")->setcellvalue ("C1", "class")); //Add Data $j= 2; foreach($data as $value) { $objSheet->setcellvalue ("A".$j,$value[' username ']) ->setcellvalue ("B").$j,$value[' Score ']) ->setcellvalue ("C".$j,$value[' Grade ']); $j++; }}//generate an Excel file in the specified format$objWriter= Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel2007 ');//Generate Excel$objWriter->save (' class.xlsx ');//If you need to output to a browser, open the following comment and comment out the Save Line//output to the browser/*browser_export (' Excel7 ', ' export.xlsx '); $objWriter->save (' php://output '); function Browser_export ($type, $ Exportname) {//Output to browser 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= '. $exportName. ' '); /File name Header (' cache-control:max-age=0 ');//Disallow browser cache}*/
Above is the operation example, if you need to reprint please indicate the source, careless grateful.
PHP build Excel (2)