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

Source: Internet
Author: User
Tags apm table name

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 ');    ?>  
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.