本文介紹利用phpexcel對數據庫數據導入excel(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($linestyl