2012年12月7日 星期五

phpexcel 寫入到excel文檔


文章來源:http://blog.163.com/wangkangming2008@126/blog/static/7827792820105121581553/


error_reporting(E_ALL);
set_include_path(get_include_path() . PATH_SEPARATOR . 'classes/');//設置路徑

include 'classes/PHPExcel.php';
include 'classes/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(16);//設置單元格寬度
$objPHPExcel->getActiveSheet()->setTitle('test');//設置當前工作表的名稱

//註:單元格第一豎是以0開始的,第一行是以1開始的。
for($j=0;$j<10 div="div" j="j">
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, 1)->getFont()->setBold(true);//設置第一行內容加粗
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, 1, 'Test'.($j+1));//設置第一行的標題
    for($i=2;$i<12 div="div" i="i">
        //因為第一行顯示了標題,所以$i是以2開始
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, $i, '第'.$i.'行,第'.($j+1).'列');
     }
}


$objPHPExcel->createSheet();//創建一個新的工作表
$objPHPExcel->setActiveSheetIndex(1);//設置為當前工作表

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(16);//設置單元格寬度
$objPHPExcel->getActiveSheet()->setTitle('Details');//設置當前工作表的名稱



/*
$rowVal = array(0=>'Date', 1=>'IP', 2=>'Email');
foreach($rowVal as $k=>$r){
     $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($k, 1)->getFont()->setBold(true);
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 1, $r);
}
*/


//設置第一行標題
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 'Date');
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 1)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 'IP');
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, 1)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, 'Email');

/***********************
一般這些數據都是從數據庫查詢出來,然後循環輸出。
如:$rs是一個從數據庫查詢出來的數組
$count = count($rs);
for($i=2;$i<$count+2;$i++){
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $rs['date']);
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $rs['ip']);
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $rs['email']);
}
***********************/


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, '2009-12-1 星期二');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, '127.0.0.1');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, 'andy@palmary.com.hk');


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, '2009-12-1 星期二');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, '127.0.0.1');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, '403126795@qq.com');


$objPHPExcel->setActiveSheetIndex(0);//設置打開excel時顯示哪個工作表
$excelName = 'Excel_'.date("YmdHis").'.xls';//設置導出excel的文件名

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=".urlencode($excelName));
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d MYH:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
?>

沒有留言: