Phpexcel Discovery Tour---Stage one

Similar to java,php also has the operation of Excel methods, which need to download the Phpexcel plug-in, the Classes folder is renamed Phpexcel and extracted to the root folder outside the two folders together in the desired project

Using the Phpexcel plugin requires instantiating a Phpexcel object the entire process requires the following function:

New Phpexcel ()----Create an Phpexcel object

Createsheet ()-----Create a new sheet table

Setactivesheetindex ()----Set the active sheet table by subscript

Getactivesheet ()----Gets the sheet table that is currently active and returns the object

Settitle ()----Set a name for the currently active sheet table

Setcellvalue ("A1", "Fill content")----fill the cell with data

$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, "Excel2007")-----generate an Excel file

Save $objWriter, $dir. /export_1.xlsx ")-----saved to the current directory

Here is the code that extracts the data from the user table in the database and populates it into Excel

1<?PHP2     /***3 * MySQL configuration file4      * */5     $phpexcel=Array(6"Host" = "localhost",7"Username" = "root",8"Password" and "" ",9"Database" = "Test",Ten"CharSet" = "UTF8" One);

dbconfig.php

1<?PHP2     /**3 * Create a class db to connect to the database and define operations on the database4      * */5     require dirname(__file__).‘ /dbconfig.php ';//introducing a configuration file6 //Create a DB Class7     classdb{8          Public $conn=NULL;9         Ten          Public function__construct ($config){//automatically called when the constructor method instantiates a class One             $this-Conn =mysql_connect($config["Host"],$config["username"],$config["Password"]) Or die(Mysql_error()); A             mysql_select_db($config["Database"]) Or die(Mysql_error());//Select Database -             mysql_query("Set names".)$config["CharSet"]) Or die(Mysql_error());//Set Database Encoding -         } the          -         //query MySQL result set = "MySQL singleton mode based on incoming SQL statement -          Public functionGetResult ($sql){ -             $resource=mysql_query($sql) or die(Mysql_error());//Querying SQL statements +             $res=Array(); -              while(($row=Mysql_fetch_assoc($resource)) !=false){//extracting information from a resource handle in a row +                 $res[] =$row; A             } at             return $res; -             //var_dump ($res). " <br> "; -         } -          -         //incoming age to export student data for each age -          Public functionGetdatabygrade ($grade){ in             $sql= "Select Username,score,class from User where grade=".$grade. "ORDER BY score Desc"; -             //$sql = "Select Username,score,class from User where grade=". $grade. "ORDER BY score Desc"; to             $res= Self::getresult ($sql); +             Var_dump($res); -             return $res; the         } *          $}

db.php

1<?PHP2     /**3 * Used to export data from a datasheet to the main file in Excel4      * */5     $dir=dirname(__file__);6     7     require $dir." /db.php ";8     require $dir." /phpexcel/phpexcel.php ";9     Ten     $db=Newdb$phpexcel);//instantiate DB class to connect to MySQL database One     $objPHPExcel=NewPhpexcel ();//Create a new Phpexcel object A      -      for($i= 1;$i<= 3;$i++){ -         if($i> 1){ the             $objPHPExcel-Createsheet (); -         } -         $objPHPExcel-Setactivesheetindex ($i-1);//the subscript starts from scratch -         $objSheet=$objPHPExcelGetactivesheet ();//sets the new sheet to the current activity sheet +         $objSheet-Settitle ($i.‘ Grade); -         $data=$db-Getdatabygrade ($i);//find information for each grade student + //var_dump ($data). " <br> "; A          at          - //Export Data -         $objSheet-Setcellvalue ("A1", "name"), Setcellvalue ("B1", "score"), Setcellvalue ("C1", "class")); -         $j= 2; -  -              foreach($data  as $key=$val){ in                  Var_dump($val); -                 $objSheet->setcellvalue ("A".$j,$val[' username ']) ->setcellvalue ("B").$j,$val[' Score ']) ->setcellvalue ("C".$j,$val[' class ']. Class); to                 $j++; +              } -            the              *              $     }Panax Notoginseng          -         $objWriter= Phpexcel_iofactory::createwriter ($objPHPExcel, "Excel2007");//To generate an Excel file the         $objWriter-Save ($dir." /export_1.xlsx ");//save to current directory +          A          the          +          -     

export.php

The main problems arise:

　　

$res = Array ();
while (($row = Mysql_fetch_assoc ($resource))! = False) {//Line out the information in the resource handle
$res [] = $row;
}

The above code means that each piece of data in the data table is taken out as an associative array and as an atom of the array $res, and the array $res contains all the information in the entire data table.

So the output is:

foreach ($data as $key = = $val) {
$objSheet->setcellvalue ("A". $j, $val [' username '])->setcellvalue ("B". $j, $val [' score '])->setcellvalue ("C ". $j, $val [' class ']." Class ");
$j + +;
}

Phpexcel Discovery Tour---Stage one