Share the method of using Phpexcel to achieve bulk export of data to Excel tables (must SEE)

Here is a small series for you to use Phpexcel to achieve the bulk export of data into Excel table method (must see). Small series feel very good, now share to everyone, also for everyone to make a reference. Let's take a look at it with a little knitting.

First you need to download the Phpexecel class file, the Help document can refer to phpexcel Chinese help manual | Phpexcel how to use it.

The following directly on the example, is I write a simple batch export data for Excel example

Front page is relatively simple, is a hyperlink, jump to the processing page, hyperlinks can also be with some parameters (see requirements)!

<a href= "./process1.php" rel= "external nofollow" > Export Excel Table </a>

Background process.php page

/*** Bulk Export Data * @param $arr query from the database, that is, the data to be exported * $name Excel table song name */function expexcel ($arr, $name) {require_once ' phpexcel.php '; /Instantiation $objPHPExcel = new Phpexcel (); /* information displayed by the right-click Property */$objPHPExcel->getproperties ()->setcreator ("ZXF")//author->setlastmodifiedby ("ZXF")//Last Save       ->settitle (' Data Excel Export ')//title->setsubject (' Data Excel Export ')//Subject->setdescription (' Export data ')//description  ->setkeywords ("Excel")//Mark->setcategory ("Result file"); Category//Set current table $objPHPExcel->setactivesheetindex (0); Set the first row of the table to display the contents $objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' owner's name ')->setcellvalue (' B1 ', ' Password '), Setcellvalue (' C1 ', ' mobile number ')->setcellvalue (' D1 ', ' address ')//Set first action red font->getstyle (' a1:d1 ')->getfont () GetColor ()->setargb (phpexcel_style_color::color_red); $key = 1;  /* The following is the processing of data in Excel, the data is taken horizontally */foreach ($arr as $v) {//Set loop starts from the second line $key + +; $objPHPExcel->getactivesheet ()//excel column A, name is the key value field that you isolated from the array, and so on->setcellvaluE (' A '. $key, $v [' name '])->setcellvalue (' B '. $key, $v [' pwd '])->setcellvalue (' C '. $key, $v [' phone ']) Setcellvalue (' D '. $key, $v [' address ']);　　 }//Set the current table $objPHPExcel->setactivesheetindex (0);  Ob_end_clean (); Clear buffer, avoid garbled header (' content-type:application/vnd.ms-excel '); File type header (' Content-disposition:attachment;filename= '. $name. '. XLS "');  FileName header (' cache-control:max-age=0 '); Header (' content-type:text/html; Charset=utf-8 ');  Encoding $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');   Excel 2003 $objWriter->save (' php://output '); Exit;} /*********** call **********************/header ("Content-type:text/html;charset=utf-8");//link Database $link = @mysql_ Connect (' localhost ', ' root ', ') or Die (' connection database failed '); mysql_select_db (' Test ', $link); mysql_query (' Set names UTF8 ');// Get the data First $sql = "select * from house"; $res = mysql_query ($sql); $arr = Array ();//$arr The result set to an array while ($row = Mys QL_FETCH_ASSOC ($res)) {$arr [] = $row;} Excel table Name $name ="User table";//Call Expexcel ($arr, $name) 

Use Phpexcel to export data to this end, for the use of Phpexcel import into the database can be see Using PHPEXCEL implementation data batch upload to the database