天天躁日日躁狠狠躁AV麻豆-天天躁人人躁人人躁狂躁-天天澡夜夜澡人人澡-天天影视香色欲综合网-国产成人女人在线视频观看-国产成人女人视频在线观看

利用phpexcel把excel導入數據庫和數據庫導出excel實現

2014-10-22 19:34:24 分類：php技術 閱讀()



復制代碼 代碼如下:
<?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');


?>

php技術：利用phpexcel把excel導入數據庫和數據庫導出excel實現，轉載需保留來源！

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。

標簽：
主站蜘蛛池模板： 国偷自产AV一区二区三区健身房 | 老师的快感电影完整版 | 人与禽物videos另类 | 国产精品A久久777777 | 在线视频 亚洲 | 暖暖日本大全免费观看 | 国产又粗又黄又爽的大片 | 国产偷国产偷亚州清高APP | 免费看亚洲 | 亚洲va在线va天堂XX xX | 最近中文字幕完整版免费高清 | 最近中文字幕无吗免费高清 | 精品人妻无码一区二区三区蜜桃臀 | 免费光看午夜请高视频 | 中文字幕精品视频在线 | 66美女人体 | 婷婷五月久久丁香国产综合 | 日本高清不卡码无码v亚洲 日本福利片午夜免费观着 日本粉嫩学生毛绒绒 | 二级毛片免费观看全程 | 青青app| 亚洲 天堂 欧美 日韩 国产 | 精品久久久亚洲精品中文字幕 | 午夜福利理论片在线播放 | 欧美性极品黑人hd | 亚洲国产中文字幕在线视频综合 | 久久久无码精品亚洲A片软件 | 人C交ZZZ0OOZZZ000 热综合一本伊人久久精品 热中文热国产热综合 | 亚洲精品视频免费看 | ass亚洲熟妇毛耸耸pics | 久久re这里视频精品15 | 日韩精品一卡二卡三卡四卡2021 | 新香蕉少妇视频网站 | 越南美女内射BBWXZ | 亚洲国产成人爱AV在线播放丿 | 国产Av男人的天堂精品良久 | 东京热一本无码av | 亚洲无遮挡 | 男女疯狂一边摸一边做羞羞视频 | 伊人国产视频 | 超碰在线公开视频 | 日韩精品无码久久一区二区三 |