Use Phpexcel to import database data into Excel (Excel filter), export Excel

<?php/**author zhy*date 2012 06 12*for excel*/date_default_timezone_set ("PRC");  error_reporting (E_all); error_reporting (0); Ini_set (' display_errors ',  true); Ini_set (' Display_startup_ Errors ',  true);d efine (' EOL ', (php_sapi ==  ' CLI ')  ? PHP_EOL :  ' <br / > ');require_once  ('. /classes/phpexcel.php '); require_once ("config.php"); Require_once ("mysql.class.php");//Generate purchase reports based on time $time =  date ("a"); $minute  = date ("i"); $APM   =  ""; if ($time = = ' pm ') {      $APM      =  $time;     $stime    =  Mktime (12,00,00,date (' m '), date (' d ') -1,date (' Y '));     $etime    = mktime (11,59,59,date (' m '), date (' d '), date (' Y '));} else{  $apm      =  $time;     $stime    =  mktime (12,00,00,date (' m '), date (' d ') -1,date (' Y '));      $etime    = mktime (11,59,59,date (' m '), date (' d '), date (' Y '));} Instantiate Excel Class $objphpexcel = new phpexcel ();////////Get Document Information////////$objProps  = $ Objphpexcel->getproperties ()///////print_r ($objProps);///////echo  "<br/>",///////$objProps SetDescription ("test_123456");///////print_r ($objProps); $objPHPExcel->setactivesheetindex (0) Setcellvalue (' A5 ', ' Product Code ')                  ->setcellvalue (' B5 ', ' Article No. ')                  ->setcellvalue (' C5 ', ' Product name ')                  ->setcellvalue (' D5 ', ' purchase volume ');//Set the selected sheet table name $objphpexcel-> Getactivesheet ()->settitle (' ancestor name ');//Set Font style $objphpexcel->getactivesheet ()->getstyle (' A1 ')->getfont () ->setname (' Arial ')->setsize (+);//////->setundErline (True);/////->getcolor ()->setargb (' FFFF0000 ');///->setbold (true);//Merge cells   Assign values to cells (numeric, string, formula) $objPHPExcel->getactivesheet ()->mergecells (' A1:d3 ')->setcellvalue (' A1 ',  ' Zhongyi list ');///////$ Objphpexcel->getactivesheet ()->mergecells (' A4:d4 ')->setcellvalue (' A4 ',  "=sum (E4:F4)"); $date _now   = date ("y-m-d"), $objPHPExcel->getactivesheet ()->mergecells (' A4:d4 ')->setcellvalue (' A4 ',  ' purchase date: ". $date _now."　  ". $apm." ");//Set single column width $objphpexcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (), $objPHPExcel Getactivesheet ()->getcolumndimension (' B ')->setwidth,//$objPHPExcel->getactivesheet () Getcolumndimension (' G ')->setrowheight ()/$objPHPExcel->getactivesheet ()->getcolumndimension (' C ')- >setwidth (), $objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (15);//large Border style   Border Bold $lineborder = array (' Borders '  => array (' outline '  => arrAy (' style '  => phpexcel_style_border::border_thick, ' Color '  => array (' ARGB '  =>   ' 000000 '),),),///table header style $head = array (     ' font '     = > array (    ' bold '       => true), ' alignment '   => array (' Horizontal '  => phpexcel_style_alignment::horizontal_center, ' vertical '  = > phpexcel_style_alignment::vertical_center), or//heading style $title = array (     ' Font '     => array (   ' bold '       =>  true   );//center Align $center = array (     ' alignment '  =>  array (     ' horizontal '  => phpexcel_style_alignment::horizontal_center,      ' Vertical '  => PHPExcel_Style_Alignment::VERTICAL_CENTER     );//Align Right $righT = array (     ' alignment '  => array (     ' Horizontal '  => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,     ' vertical '  => PHPExcel_Style_Alignment::VERTICAL_CENTER    );//fine border style $linestyle =  array (' Borders '  => array (' Outline '  => array (' style '  => phpexcel_ Style_border::border_thin, ' Color '  => array (' argb '  =>  ' FF000000 '),),); $objPHPExcel- >getactivesheet ()->getstyle (' A1:d3 ')->applyfromarray ($head);///->getalignment ()->getHorizontal ( ");///->getborders ()->gettop ()->setborderstyle (");//->setwraptext (true); Wrap $objphpexcel-> Getactivesheet ()->getstyle (' A4:d4 ')->applyfromarray ($RIGHT);  $objPHPExcel->getactivesheet () GetStyle (' A5:d5 ')->applyfromarray ($title);  //fill Color/////$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getsTartcolor ()->setargb (' FFFF0000 ');/   //inserting Data $dsql->execute (' Omebrand_list ', "select  I.goods_id , sum (  ' nums '  )  as num, i.name,i.addon,i.price,g.bn as  b,i.bn as h,g.goods_id,i.goods_id,i.order_idFROM  ' Sdb_b2c_order_items '  as i,sdb _b2c_goods as gwhere i.order_id in  (select order_id from sdb_b2c_ Orders where status = ' active '  and createtime between  $stime  and $ etime)  and i.goods_id=g.goods_id and g.cat_id=173 group by h "); $m  =  0;unset ($re), while ($row = $dsql->getobject (' omebrand_list ')) {$re [$m] = get_object_vars ($row); $m + +;} $row _count = 5; $objPHPExcel->setactivesheetindex (0)->setcellvalue (' A6 ',  12325416541)             ->setcellvalue (' B6 ',  4962132165262)    &Nbsp;        ->setcellvalue (' C6 ',  121515212515241521)              ->setcellvalue (' D6 ',  96215465415); foreach ($re  as  $r  =>  $dataRow)  {$baseRow  = 6; $row  =  $baseRow  +  $r $bn = $dataRow [h]; $goods _id =  $dataRow [goods_id]; $spec _value =  ""; $aa  =  unserialize ($dataRow [addon]);if  ($aa [' product_attr ']) {foreach  ($aa [' Product_attr '] as   $arr _special_info)   {$spec _value =  $arr _special_info[' value '];}}             preg_match_all ('/\-?\d+\.? \d*/i ', $spec _value, $row 1), $num  =  $row 1[0][0]; $all  =  $num * $dataRow [num];    if ($spec _value== ") {     $all = $dataRow [' num '];    //$prce = $dataRow [ PRICE];&NBSP;&NBSP;&NBSP,} $objPHPExcel->setactivesheetiNdex (0)->setcellvalue (' A '. $row,  $dataRow [' B '])                  ->setcellvalue (' B '. $row,  $bn)              ->setcellvalue (' C '. $row,  $dataRow [' name '])              ->setcellvalue (' D '. $row,  $all);      $objPHPExcel->getactivesheet ()->getstyle (' A '. $row _count)->applyfromarray ($linestyle);                  $ Objphpexcel->getactivesheet ()->getstyle (' B '. $row _count)->applyfromarray ($linestyle);      $objPHPExcel->getactivesheet ()->getstyle (' C '. $row _count)->applyfromarray ($linestyle);      $objPHPExcel->getactivesheet ()->getstyle (' D '. $row _count)->applyfromarray ($ LineStyle);                                $baseRow ++;     $row _count++;} $objPHPExcel->getactivesheet ()->getstyle (' A '. $row _count)->applyfromarray ($linestyle);               $objPHPExcel->getactivesheet () GetStyle (' B '. $row _count)->applyfromarray ($linestyle); $objPHPExcel->getactivesheet ()->getstyle (' C '. $ Row_count)->applyfromarray ($linestyle), $objPHPExcel->getactivesheet ()->getstyle (' D '. $row _count) Applyfromarray ($linestyle);   $objPHPExcel->getactivesheet ()->getstyle (' A5:d '. $row _count) Applyfromarray ($CENTER);   $objPHPExcel->getactivesheet ()->getstyle (' A1:d '. $row _count) Applyfromarray ($lineBORDER);//Set Print margins $objphpexcel->getactivesheet ()->getpagemargins ()->settop (0); $ Objphpexcel->getactivesheet ()->getpagemargins ()->setright (0); $objPHPExcel->getactivesheet ()->getpagemargins ()->setleft (0); $objPHPExcel Getactivesheet ()->getpagemargins ()->setbottom (0);//Set Paper Type $objphpexcel->getactivesheet (), Getpagesetup ()->setpapersize (phpexcel_worksheet_pagesetup::P apersize_a4);//Set AutoFilter $objphpexcel-> Getactivesheet ()->setautofilter (' A5:d '. $row _count);//Set Line wrap $objphpexcel->getactivesheet ()->getstyle (' B6:b '. $row _count)->getalignment ()->setwraptext (TRUE);//Set format number $objphpexcel->getactivesheet () GetStyle (' a6:a '. $row _count)->getnumberformat ()->setformatcode (' 0000000000 ');//Set Security level $MD=MD5 (Time ()); $MD = substr ($MD, 0,8), $objPHPExcel->getactivesheet ()->getprotection ()->setpassword ("$md"); $objPHPExcel Getactivesheet ()->getprotection ()->setsheet (TRUE);//$objPHPExcel->getactivesheet ()->getprotection ( )->setsort (True), $objPHPExcel->getactivesheet ()->getprotection ()->setinsertrows (true); Objphpexcel->getactivesheet ()->getprotectiOn ()->setformatcells (TRUE);//Add picture  /* $obj = $objPHPExcel->getactivesheet (); $objDrawing  = new  phpexcel_worksheet_drawing ();    $objDrawing->setname (' wsyimg ');    $ Objdrawing->setdescription (' Image inserted by zhy ');    $objDrawing SetPath ('./wsy.jpg ');    $objDrawing->setheight ();    $objDrawing Setcoordinates (' H23 ');    $objDrawing->setoffsetx;    $objDrawing Setrotation ( -10);    /$objDrawing->getshadow ()->setvisible (true);    $ Objdrawing->getshadow ()->setdirection ( -20); /  $objDrawing->setworksheet ($obj); *///Header Footer//$ Objphpexcel->getactivesheet ()->getheaderfooter ()->setoddheader (' Zhy '),  //$objPHPExcel Getactivesheet ()->getheaderfooter ()->setoddfooter (' End ');  $objPHPExcel->setactivesheetindex (0); $ Tname=date (' Y-m-dh ', Time ()); $tnam =iconv (' UTF-8', ' GBK ', ' Ancestral name order '); $tname = $tnam. $tname;// excel 2007 save//$objWriter  = new phpexcel_writer _excel2007 ($objPHPExcel);  //$objWriter->save (Str_replace ('. php ',  '. xlsx ',  __file__));    // excel 5 Save  //$objWriter  = new phpexcel_writer_excel5 ($objPHPExcel) ;  //$objWriter->save (Str_replace ('. php ',  '. xls ',  __file__));  $objWriter  =  Phpexcel_iofactory::createwriter ($objPHPExcel,  ' Excel5 '); $objWriter->save (Str_replace ('. php ',  '). XLS ',  __file__));//$url  =  "/data/home/htdocs/ec/public/files/". Date ("Y"). " /". Date (" Ym ")." /"; Createdir ($url); Function createdir ($dir)  {if   (!is_dir  ($dir  ))  {mkdir ($ Dir, 0777, true) chmod ($dir,  0777) chown (  $dir,  ' daemon '  ); Chgrp (  $dir,   ' daemon '  );}} $name = ' forexmple_excel '; rename (Str_replace ('. php ',  '. xls ',  __file__),  $name. XLS ');? >http://Www.cnblogs.com/yuwensong/p/3771787.html 

Use Phpexcel to import database data into Excel (Excel filter), export Excel