php 基於phpexcel製作的匯出excel類，phpexcelexcel_PHP教程

php 基於phpexcel製作的匯出excel類，phpexcelexcel

最近要寫個項目，由於項目中匯出excel太多，因此寫個類便於使用。

fileName = '檔案名稱';//設定檔案名稱，預設為時間戳記*$excel->format = '2007';//檔案類型，預設為2007，其他為excel5*$record = array('delKey'=>array('id','addTime','status'),//如果資料$data中有不需要顯示的列，可以在此說明。刪除不需要匯出的索引值'sort'=>array('keyName'=>array('subjectName','flag'),//按keyName列排序，如果不存在則不排序。//'reorder'=>'DESC',//排序方式，DESC為倒序，ASC為正序。如果keyName存在則排序keyName，如果不存在則按數組的鍵名排序，如果reorder不存在則不排序),//排序   如果sort存在則排序，否則不排序，如果keyName存在則按設定排序，如果不存在則按字元排序，如果reorder不存在或為空白或為DES則正序，等於DESC為倒序。'excelStyle'=>array('setName'=>'Arial',//字型樣式'setSize'=>'12',//字型大小),//表格全域樣式'title'=>array('tableName'=>'學科列表','center'=>true,'direction'=>'right','merge'=>'2','setSize'=>'30'),//標題,center垂直,direction為合并方向。right,left,up,down。 merge為合并幾個儲存格，setSize為字型大小'data'=>array(array('tableName'=>'科目名稱','width'=>'30','setName'=>'宋體','setSize'=>'20','background'=>'red','textColor'=>'white','bold'=>true,'underline'=>true,'borderColor'=>'cyan','center'=>true,),array('tableName'=>'學科','width'=>'50','center'=>true),//顏色表是：black,white,red,green,blue,yellow,magenta,cyan),//表名稱 tableName為名稱，width為表格寬度，setName為字型樣式，background為背景顏色,textColor為字型顏色，bold為加粗,underline為底線，borderColor為邊框顏色.'merge'=>array(//'flag'=>array('keyword'=>'初','direction'=>'right','merge'=>'2'),merge的索引值為需要處理資料數組的索引值，keyword為如果存在此關鍵字才執行其他樣式操作，如果keyword不存在則執行所有索引值為flag的儲存格。'all'=>array('width'=>'30','setName'=>'宋體','setSize'=>'20','background'=>'red','textColor'=>'white','bold'=>true,'underline'=>true,'borderColor'=>'cyan','center'=>true,),),//);//匯出配置*$excel->export($record,$data);//$record為匯出配置，$data為資料庫的資料,$data可以為數組，也可以為對象。****/$address = dirname(dirname(__FILE__)).'/PHPExcel';include $address.'/PHPExcel.class.php';include $address.'/PHPExcel/Writer/Excel2007.php';include $address.'/PHPExcel/Writer/Excel5.php';include $address.'/PHPExcel/IOFactory.php';/*****************************產生excel文檔。*/class excelC {public $format = '2007';//轉換格式，預設為2007版本，其他版本，請輸入不是2007的數字public $fileName;//檔案名稱預設為時間戳記。private $objExcel;private $letters;public function __construct()    {$this->fileName = time();$this->fileTitle = '匯出資料';$this->objExcel = new PHPExcel();$this->letters = $this->letter();}//匯出excel的屬性private function attribute(){$this->objExcel->getProperties()->setCreator("力達行有限公司");//建立人$this->objExcel->getProperties()->setLastModifiedBy("力達行有限公司");//最後修改人$this->objExcel->getProperties()->setTitle("匯出資料");//標題$this->objExcel->getProperties()->setSubject("匯出資料");//題目$this->objExcel->getProperties()->setDescription("資料匯出");//描述$this->objExcel->getProperties()->setKeywords("office 匯出");//關鍵字$this->objExcel->getProperties()->setCategory("excel");//種類}//設定表(如果只有一個sheet可以忽略該函數，將預設建立。)private function sheet(){$this->objExcel->setActiveSheetIndex(0);//設定當前的表$this->objExcel->getActiveSheet()->setTitle('excel');//設定表名稱。}/****************************匯出excel*@attr $record為表頭及樣式設定*@attr $data為需要匯出的資料*/public function export($record=array(),$data=array()){if(!$data)return false;if(!is_array($record))return false;//表樣式及其他設定//處理擷取到的資料$data = $this->maniData($record,$data);//擷取整體樣式。$this->excelData($record,$data);//$this->objExcel->getActiveSheet()->setCellValue('A1', '季度'); $this->down();//匯出下載}/**處理表格*/private function excelData(&$record,&$data){$this->attribute();//設定屬性$this->sheet();//設定表$this->whole($record);//設定整體樣式$this->tableHeader($record);//設定表格頭。$this->tableContent($record,$data);//設定表格$this->excelTitle($record,2);//設定標題}/**設定表格整體樣式*/private function whole(&$record){if(!array_key_exists('excelStyle',$record))return false;$excelStyle = $record['excelStyle'];$default = $this->objExcel->getDefaultStyle();if(array_key_exists('setName',$excelStyle))$default->getFont()->setName($excelStyle['setName']);//設定字型樣式if(array_key_exists('setSize',$excelStyle))$default->getFont()->setSize($excelStyle['setSize']);//設定字型大小}/**設定標題*/private function excelTitle($record,$num){$titleL = $this->letters[0];if(!array_key_exists('title',$record))return false;$this->appOintStyle($titleL ,1,$record['title']);}/**設定表格頭。*/private function tableHeader($record){if(!array_key_exists('data',$record))return false;$objExcel = $this->objExcel;$letters = $this->letters;if(!is_array($record['data']))return false;$i = 0;$hang = 2;foreach($record['data'] as $k=>$v){$this->appOintStyle($letters[$i],$hang,$v);$i++;}}private function setCellValue($letter,$data){if(@$data)$this->objExcel->getActiveSheet()->setCellValue($letter, $data);//填儲值return $this;}private function getColumnDimension($letter,$data){if(@$data)$this->objExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($data);//設定寬度 return $this;}private function setName($letter,$data){if(@$data)$this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setName($data);//設定字型return $this;}private function setSize($letter,$data){if(@$data) $this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setSize($data);//設定字型大小return $this;}private function background($letter,$data){if(@$data){$this->objExcel->getActiveSheet()->getStyle($letter)->getFill()->getStartColor()->setARGB($this->backColor($data));$this->objExcel->getActiveSheet()->getStyle($letter)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);//設定背景色樣式，無樣式將不顯示背景色。}return $this;}private function textColor($letter,$data){if(@$data){$this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->getColor()->setARGB($data);//字型顏色}return $this;}private function setBold($letter,$data){if(@$data){$this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setBold(true);//加粗}return $this;}private function setUnderline($letter,$data){if(@$data){$this->objExcel->getActiveSheet()->getStyle($letter)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);//底線}return $this;}private function border($letter,$data){if(@$data){$styleThinBlackBorderOutline = array(   'borders' => array ( 'outline' => array (   'style' => PHPExcel_Style_Border::BORDER_THIN, //設定border樣式   'color' => array ('argb' => $data),          //設定border顏色),  ),);$this->objExcel->getActiveSheet()->getStyle($letter)->applyFromArray($styleThinBlackBorderOutline);}return $this;}/**合并*/private function mergeCells($letters,$hang,$direction,$merge){$merge = $merge-1;if($merge > 0 && $direction){//print_r($this->letters);$l = array_flip($this->letters);$ln = $l[$letters];switch ($direction){case 'left':$signal = $this->letters[($ln-$merge)].$hang.':'.$letters.$hang;break;case 'right':$signal = $letters.$hang.':'.$this->letters[($ln+$merge)].$hang;break;case 'up':$signal = $letters.($hang-$merge).':'.$letters.$hang;break;case 'down':$signal = $letters.$hang.':'.$letters.($hang+$merge);break;default:$signal = '';}if($signal){$this->objExcel->getActiveSheet()->mergeCells($signal);}}return $this;}/**垂直置中*/private function setVertical($letter,$data){if($data){$this->objExcel->getActiveSheet()->getStyle($letter)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);$this->objExcel->getActiveSheet()->getStyle($letter)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);}return $this;}/**設定顏色*/private function backColor($color){$array = array('black'=> 'FF000000',//System Colour #1 - Black'white'=> 'FFFFFFFF',//System Colour #2 - White'red'=> 'FFFF0000',//System Colour #3 - Red'green'=> 'FF00FF00',//System Colour #4 - Green'blue'=> 'FF0000FF',//System Colour #5 - Blue'yellow'=> 'FFFFFF00',//System Colour #6 - Yellow'magenta'=> 'FFFF00FF',//System Colour #7- Magenta'cyan'=> 'FF00FFFF',//System Colour #8- Cyan);if(array_key_exists($color,$array)){return $array[$color];} else {return false;}}/**設定表*/private function tableContent(&$record,&$data){$objExcel = $this->objExcel;$letters = $this->letters;if(array_key_exists('merge',$record))$merge = $record['merge'];else$merge = '';$hang = 2;foreach($data as $k=>$v){$i=0;$hang++;foreach($v as $kk=>$vv){$this->setCellValue($letters[$i].$hang, $vv);//設定內容$this->Appoint($kk,$vv,$letters[$i],$hang,$merge);$i++;}}}/**設定表指定樣式*/private function Appoint($kk,$vv,$letters,$hang,$merge){if(!$merge)return false;if(array_key_exists($kk,$merge)){$v = $merge[$kk];if(array_key_exists('keyword',$v)){if(strpos($vv,$v['keyword']) > -1){$this->appOintStyle($letters,$hang,$v);}} else {$this->appOintStyle($letters,$hang,$v);}} else if(array_key_exists('all',$merge)){$v = $merge['all'];if(array_key_exists('keyword',$v)){if(strpos($vv,$v['keyword']) > -1){$this->appOintStyle($letters,$hang,$v);}} else {$this->appOintStyle($letters,$hang,$v);}}}/**終極樣式*/private function appOintStyle($letters,$hang,$v){$this->setCellValue($letters.$hang,@$v['tableName'])->getColumnDimension($letters,@$v['width'])->setName($letters.$hang,@$v['setName'])->setSize($letters.$hang,@$v['setSize'])->background($letters.$hang,@$v['background'])->textColor($letters.$hang,$this->backColor(@$v['textColor']))->setBold($letters.$hang,@$v['bold'])->setUnderline($letters.$hang,@$v['underline'])->border($letters.$hang,$this->backColor(@$v['borderColor']))->mergeCells($letters,$hang,@$v['direction'],@$v['merge'])->setVertical($letters.$hang,@$v['center']);}/**應為字母列表*/public function letter(){return array('A','B','C','D','F','G','H','I','G','K','L','M','N','O','P','Q','R','S','T','U','V','W','H','Y','Z');}/*****************************處理資料，排序及刪除欄位*/private function maniData($record,$data){if(!$data)return false;if(!is_array($record))return false;//表樣式及其他設定$data = $this->objectToArray($data);//對象轉數組$delKey = (array_key_exists('delKey',$record))?$record['delKey']:'';//是否刪除關鍵字$sort = (array_key_exists('sort',$record))?$record['sort']:'';//是否排序$data = $this->delSort($data,$delKey,$sort);return $data;}/*****************************對象轉數組*/private function objectToArray($data){if(!$data)return false;$data = (array)$data;foreach($data as $k=>$v){if(is_object($v) || is_array($v)){$data[$k] = (array)$this->objectToArray($v);}}return $data;}/*****************************刪除索引值，並排序*/private function delSort($data,$delKey='',$sort=''){if(!$data)return false;$array = array();foreach($data as $k=>$v){//刪除資料中的某個索引值$delData = $this->delData($v,$delKey);//按設定索引值排序$sortData = $this->sortData($delData,$sort);$array[$k] = $sortData;}return $array;}/*****************************刪除索引值*/public function delData($data,&$delKey){if($delKey){foreach($delKey as $delVal){if(array_key_exists($delVal,$data))//判斷索引值是否存在unset($data[$delVal]);//清除鍵名。}}return $data;}/*****************************索引值排序*/public function sortData($data,&$sort){$array = array();if($sort){if(array_key_exists('keyName',$sort)){$keyName = $sort['keyName'];if(array_key_exists('reorder',$sort)){if($sort['reorder'] == 'DESC'){krsort($keyName);} else if($sort['reorder'] == 'ASC'){ksort($keyName);}}foreach($keyName as $vn){$array[$vn] = (array_key_exists($vn,$data))?$data[$vn]:'';}} else {if(array_key_exists('reorder',$sort)){if($sort['reorder'] == 'DESC'){krsort($data);} else if($sort['reorder'] == 'ASC'){ksort($data);}$array = $data;}}}return $array;}//匯出下載private function down(){if($this->format == '2007'):header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');$excel = 'Excel2007';else:header('Content-Type: application/vnd.ms-excel'); $excel = 'Excel5';endif; header("Content-Disposition: attachment; filename=\"$this->fileName\""); header('Cache-Control: max-age=0');$objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, $excel);$objWriter->save('php://output');}}

想用PHP匯出excel表格，但是php環境是51*的，沒辦法用PHPExcel開源類，有啥辦法？

你好，其實非常簡單的問題，就是將要列印的頁面的頭設定為！

header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=php100.xls");

這樣，瀏覽器就直接下載該檔案。將檔案裡面的

PHPExcel產生Excel不可以讀取

總結 php匯出Excel php匯入Excel PhpExcel使用說明 PhpExcel使用手冊2009/03/06 上午 02:37方法一:特點,簡單,省心,

header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=test_data.xls");

$tx='表頭';
echo $tx."\n\n";
//輸出內容如下：
echo "姓名"."\t";
echo "年齡"."\t";
echo "學曆"."\t";
echo "\n";
echo "張三"."\t";
echo "25"."\t";
echo "本科"."\t";
?>

方法二: 引用google code中推薦的小類庫(大體同方法一,比較複雜點)

code.google.com/p/php-excel/downloads/list

方法三: PHPEXCEL 類庫,功能強大,支援win Excel2003 ,Win Excel2007.

www.codeplex.com/PHPExcel

//設定PHPExcel類庫的include path
set_include_path('.'. PATH_SEPARATOR .
'D:\Zeal\PHP_LIBS' . PATH_SEPARATOR .
get_include_path());

/**
* 以下是使用樣本，對於以 //// 開頭的行是不同的可選方式，請根據實際需要
* 開啟對應行的注釋。
* 如果使用 Excel5 ，輸出的內容應該是GBK編碼。
*/
require_once 'PHPExcel.php';

// uncomment
////require_once 'PHPExcel/Writer/Excel5.ph......餘下全文>>
 

http://www.bkjia.com/PHPjc/884175.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/884175.htmlTechArticlephp 基於phpexcel製作的匯出excel類，phpexcelexcel 最近要寫個項目，由於項目中匯出excel太多，因此寫個類便於使用。 ?php/**@使用方法。*引入類庫...



</table>就直接放在Excel的表格裡面了！詳情，請參考php100的教程。php100對這個問題有一個視頻教程。下載地址: www.php100.com/...0.html  

內容</td>