利用phpexcel把excel导入数据库和数据库导出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);
define('eol',(php_sapi == 'cli') ? php_eol : '<br />');

require_once ('../classes/phpexcel.php');
require_once("config.php");
require_once("mysql.class.php");

//根据时间生成采购报表
$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'));
}

//实例化excel类
$objphpexcel = new phpexcel();

////////获取文档信息
////////$objprops = $objphpexcel->getproperties();
///////print_r($objprops);
///////echo "<br/>";
///////$objprops->setdescription("test_123456");
///////print_r($objprops);

$objphpexcel->setactivesheetindex(0)
    ->setcellvalue('a5','商品编码')
                ->setcellvalue('b5','货号')
                ->setcellvalue('c5','商品名称')
                ->setcellvalue('d5','采购量');

//设置选定sheet表名
$objphpexcel->getactivesheet()->settitle('祖名');
//设置字体样式
$objphpexcel->getactivesheet()->getstyle('a1')->getfont()->setname('arial')->setsize(25);//////->setunderline(true);/////->getcolor()->setargb('ffff0000');///->setbold(true);
//合并单元格 给单元格赋值(数值，字符串，公式)
$objphpexcel->getactivesheet()->mergecells('a1:d3')->setcellvalue('a1', 'zhongyi清单');
///////$objphpexcel->getactivesheet()->mergecells('a4:d4')->setcellvalue('a4', "=sum(e4:f4)");

$date_now  = date("y-m-d");
$objphpexcel->getactivesheet()->mergecells('a4:d4')->setcellvalue('a4', "采购日期：".$date_now." ".$apm."　");
//设置单列宽度
$objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(15);
$objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(20);//$objphpexcel->getactivesheet()->getcolumndimension('g')->setrowheight(50);/
$objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(44);
$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(15);

//大边框样式 边框加粗
$lineborder = array(
 'borders' => array(
  'outline' => array(
   'style' => phpexcel_style_border::border_thick,
   'color' => array('argb' => '000000'),
  ),
 ),
);
//表头样式
$head = array(
    'font'    => array(
    'bold'      => true
  ),
 'alignment' => array(
    'horizontal' => phpexcel_style_alignment::horizontal_center,
    'vertical' => phpexcel_style_alignment::vertical_center
   ),

);
//标题样式
$title = array(
    'font'    => array(
    'bold'      => true
    ),
);
//居中对齐
$center = array(
    'alignment' => array(
      'horizontal' => phpexcel_style_alignment::horizontal_center,
      'vertical' => phpexcel_style_alignment::vertical_center
     ),
);
//靠右对齐
$right = array(
    'alignment' => array(
      'horizontal' => phpexcel_style_alignment::horizontal_right,
      'vertical' => phpexcel_style_alignment::vertical_center
     ),
);
//细边框样式
$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);自动换行
$objphpexcel->getactivesheet()->getstyle('a4:d4')->applyfromarray($right);
$objphpexcel->getactivesheet()->getstyle('a5:d5')->applyfromarray($title);

//填充色
/////$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->getstartcolor()->setargb('ffff0000');/
    

//插入数据
$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_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 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)
            ->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,$row1);
   $num = $row1[0][0];
   $all = $num*$datarow[num];
   if($spec_value==''){
    $all=$datarow['num'];
    //$prce=$datarow[price];
   }
 $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);

//设置打印页边距
$objphpexcel->getactivesheet()->getpagemargins()->settop(0);
$objphpexcel->getactivesheet()->getpagemargins()->setright(0);
$objphpexcel->getactivesheet()->getpagemargins()->setleft(0);
$objphpexcel->getactivesheet()->getpagemargins()->setbottom(0);
//设置纸张类型
$objphpexcel->getactivesheet()->getpagesetup()->setpapersize(phpexcel_worksheet_pagesetup::papersize_a4);
//设置自动筛选
$objphpexcel->getactivesheet()->setautofilter('a5:d'.$row_count);
//设置自动换行
$objphpexcel->getactivesheet()->getstyle('b6:b'.$row_count)->getalignment()->setwraptext(true);
//设置格式化数字
$objphpexcel->getactivesheet()->getstyle('a6:a'.$row_count)->getnumberformat()->setformatcode('0000000000');

//设置安全级别
$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);

//添加图片
/*
$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);
*/

//页眉页脚
//$objphpexcel->getactivesheet()->getheaderfooter()->setoddheader('zhy');
//$objphpexcel->getactivesheet()->getheaderfooter()->setoddfooter('end');

$objphpexcel->setactivesheetindex(0);
$tname=date('y-m-dh',time());
$tnam=iconv('utf-8','gbk','祖名订单');
$tname=$tnam.$tname;

// excel 2007保存
//$objwriter = new phpexcel_writer_excel2007($objphpexcel);
//$objwriter->save(str_replace('.php', '.xlsx', __file__));

// excel 5保存
//$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');

?>