Using Phpexcel to import Excel into database and database to export Excel implementation

This article introduces the use of Phpexcel to the Database data import Excel (Excel filter), export Excel, we refer to the use of the bar

        Code as follows: <?php/* *author zhy *date *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); Define (' EOL ', (Php_sapi = = ' cli ')? Php_eol: ' <br/> ');   Require_once ('.. /classes/phpexcel.php '); Require_once ("config.php"); Require_once ("mysql.class.php");  //Generate purchase report 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 '));  //instantiation of 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 ', ' Item number ')               &N Bsp ->setcellvalue (' C5 ', ' Product name ')              ,  ->setcellvalue (' D5 ', ' Purchase quantity ');  //Set selected sheet table name $objPHPExcel->getactivesheet ()->settitle (' ancestral name '); Set font style $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfont ()->setname (' Arial ')->setsize (25 );//////->setunderline (true);/////->getcolor ()->setargb (' FFFF0000 ');///->setbold (true); Merges cells to assign values (values, strings, formulas) to cells $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 ', "date of purchase:". $date _now. "　". $apm." "); Set single row width $objPHPExcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (15); $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setwidth;//$objPHPExcel-> Getactivesheet ()->getcolumndimension (' G ')->setrowheight (m)/$objPHPExcel->getactivesheet ()-> Getcolumndimension (' C ')->setwidth (44); $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 '),  ),  ); Header style $head = array (    ' font '    => array (    ' bold '      => true & nbsp ),   ' alignment ' =&GT Array (    ' horizontal ' => phpexcel_style_alignment::horizontal_center,     ' vertical ' => Phpexcel_style_alignment::vertical_center    ),  ); Heading style $title = Array (    ' font '    => array (    ' bold '      => true    ),); Center aligned $CENTER = array (    ' alignment ' => array (      ' horizontal ' => Phpexcel_style_alig Nment::horizontal_center       ' vertical ' => phpexcel_style_alignment::vertical_center      ),); Right-aligned $RIGHT = array (    ' alignment ' => array (      ' horizontal ' => phpexcel_style_align Ment::horizontal_right       ' vertical ' => phpexcel_style_alignment::vertical_center     & nbsp;),); Thin 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); auto wrap $objPHPExcel->getactivesheet ()->getstyle (' A4:d4 ')->applyfromarray ($RIGHT );  $objPHPExcel->getactivesheet ()->getstyle (' A5:d5 ')->applyfromarray ($title);   //Fill Color// $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor () (" FFFF0000 ');/       //Insert Data $dsql->execute (' omebrand_list ', "Select i.goods_id, sum (' nums ') as Nu M, i.name,i.addon,i.price,g.bn as b,i.bn as H, g.goods_id,i.goods_id,i.order_id from ' Sdb_b2c_order_items ' as i,sdb_b2c_ Goods as G WHERE i.order_id in (select order_id from sdb_b2c_orders WHERE status = ' active ' and createtime between $stime a nd $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 + +; nbsp } $row _count = 5; $objPHPExcel->setactivesheetindex (0)    ->setcellvalue (' A6 ', 12325416541)            ->setcellvalue (' B6 ', 4962132165262)            ->setcellvalue (' C6 ', 12 1515212515241521)            ->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)  {  &N Bsp   $spec _value = $arr _special_info[' value '];    }   &nbsp}      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;}   $objPHPExcel->setactivesheetindex (0)    ->setcellvalue (' A '. $row, $dataRow [' B '])                ->setcellvalue (' B ' $row, $bn)             &NBSP ->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 the paper type $objPHPExcel->getactivesheet ()->getpagesetup ()->setpapersize () Phpexcel_worksheet_pagesetup:: PAPERSIZE_A4); Set AutoFilter $objPHPExcel->getactivesheet ()->setautofilter (' A5:d '. $row _count); Set up wrap $objPHPExcel->getactivesheet ()->getstyle (' b6:b '. $row _count)->getalignment ()->setwraptext ( true); Sets the formatted 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 pictures  /* $obj = $objPHPExcel->getactivesheet (); $objDrawing = new phpexcel_worksheet_drawing ();    $objDrawing->setname (' wsyimg ');    $objDrawing->setdescription (' Image inserted by Zhy ');    $objDrawing->setpath ('./wsy.jpg ');    $objDrawing->setheight (50);    $objDrawing->setcoordinates (' H23 ');    $objDrawing->setoffsetx (60);    $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); &n Bsp   Chown ($dir, ' daemon ');   CHGRP ($dir, ' daemon ');    &nbsp}} $name = ' Forexmple_excel '; Rename (' Str_replace php ', '. xls ', __file__), $name. " XLS ');    ?>