PHPEXCEL是一個用來生成excel的php插件，他可以很方便的對excel數據進行操作，如：生成excel,修改excel數據等等。
一、PHPEXCEL簡介
PHPEXCEL提供了一系列的API，能夠解析與生成excel，pdf之類的文檔。
PHPEXCEL雖然強大，不過使用起來相對有些繁鎖，如果需要輸出較為復雜格式時，是一個不錯的選擇。可以到官方下載到源碼。
二、PHPEXCEL部分函數
設置當前的工作簿，返回該工作簿對象：
$excelSheet = $excel->setActiveSheetIndex(0);
合並單元格，返回該單元格對象，以下示例即合並A列第一行與第二行所在單元格：
代碼如下 復制代碼$excelSheet->mergeCells('A1:A2');
設置單元格的值，參數：單元格名稱，值：
$excelSheet->setCellValue('A1', '字符串內容');
$excelSheet->setCellValue('A2', 26); //數值
$excelSheet->setCellValue('A3', true); //布爾值
$excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //公式
phpexcel用法介紹
代碼如下 復制代碼
include ‘PHPExcel.php’;
include ‘PHPExcel/Writer/Excel2007.php’;
//或者include ‘PHPExcel/Writer/Excel5.php’; 用於輸出.xls的
創建一個excel
$objPHPExcel = new PHPExcel();
保存excel—2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$objWriter->save(”xxx.xlsx”);
直接輸出到浏覽器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
header(”Pragma: public”);
header(”Expires: 0″);
header(”Cache-Control:must-revalidate, post-check=0, pre-check=0″);
header(”Content-Type:application/force-download”);
header(”Content-Type:application/vnd.ms-execl”);
header(”Content-Type:application/octet-stream”);
header(”Content-Type:application/download”);;
header(’Content-Disposition:attachment;filename=”resume.xls”‘);
header(”Content-Transfer-Encoding:binary”);
$objWriter->save(’php://output’);
——————————————————————————————————————–
設置excel的屬性：
代碼如下 復制代碼
創建人
$objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);
最後修改人
$objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”);
標題
$objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”);
題目
$objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”);
描述
$objPHPExcel->getProperties()->setDescription(”Test document for Office 2007 XLSX, generated using PHP classes.”);
關鍵字
$objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”);
種類
$objPHPExcel->getProperties()->setCategory(”Test result file”);
——————————————————————————————————————–
設置當前的sheet
$objPHPExcel->setActiveSheetIndex(0);
設置sheet的name
$objPHPExcel->getActiveSheet()->setTitle(’Simple’);
設置單元格的值
$objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’);
$objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);
$objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);
$objPHPExcel->getActiveSheet()->setCellValue(’C5′, ‘=SUM(C2:C4)’);
$objPHPExcel->getActiveSheet()->setCellValue(’B8′, ‘=MIN(B2:C5)’);
合並單元格
$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);
分離單元格
$objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);
三、PHPEXCEL舉例應用
整個代碼如下(值得注意的是表頭用了$orderCellData記錄了每個商戶編號的順序，為了在表體把對應的數據取出)：
<<?php
require_once '../../../libs/PHPExcel/Classes/PHPExcel.php';
require_once '../../../libs/PHPExcel/Classes/PHPExcel/Writer/Excel5.php';
include_once '../../../libs/PHPExcel/Classes/PHPExcel/IOFactory.php';
include '../common/config.php';
// 創建一個處理對象實例（此對象對於2003 2007是相同的）
$objExcel = new PHPExcel();
//設置屬性(這段代碼無關緊要，其中的內容可以替換為你需要的)
$objExcel->getProperties()->setCreator("office 2003 excel");
$objExcel->getProperties()->setLastModifiedBy("office 2003 excel");
$objExcel->getProperties()->setTitle("Office 2003 XLS Test Document");
$objExcel->getProperties()->setSubject("Office 2003 XLS Test Document");
$objExcel->getProperties()->setDescription("Test document for Office 2003 XLS, generated using PHP classes.");
$objExcel->getProperties()->setKeywords("office 2003 openxml php");
$objExcel->getProperties()->setCategory("Test result file");
//開始處理數據(索引從0開始)
$objExcel->setActiveSheetIndex(0);
$conn = mssql_connect($config['mssql']['host'],$config['mssql']['user'],$config['mssql']['password']);
mssql_select_db($config['mssql']['dbname'],$conn);
$tm=$_REQUEST['tm'];
$sql = "exec HNow05_getTTSpace '','".$tm."','',1";
$sql=mb_convert_encoding($sql,'GBK','UTF-8');
$res=mssql_query($sql);
$i=0;
$k = array('站碼','站名','河系','來報時間','水位','水勢');
$count = count($k);
$arrs = array('A','B','C','D','E','F');
//添加表頭
for($i=0;$i<$count;$i++){
$objExcel->getActiveSheet()->setCellValue($arrs[$i]."1", "$k[$i]");
}
/*--------從數據庫讀取數據-------*/
$i=0;
while($arr=mssql_fetch_array($res))
{
$stcd = $arr["STCD"];
$stnm = $arr["STNM"];
$rvnm = $arr["RVNM"];
$tm= $arr["TM"];
$tdz= $arr["TDZ"];
$tdptn= $arr["TDPTN"];
if($tdptn=='6'){
$tdptn='平';
}else if($tdptn=='5'){
$tdptn='漲';
}else if($tdptn=='4'){
$tdptn='落';
}
$u1=$i+2;
$stnm=iconv("GBK","utf-8",$stnm);
$rvnm=iconv("GBK","utf-8",$rvnm);
$tm=iconv("GBK","utf-8",$tm);
/*----------寫入內容-------------*/
$objExcel->getActiveSheet()->setCellValue('a'.$u1, "$stcd");
$objExcel->getActiveSheet()->setCellValue('b'.$u1, "$stnm");
$objExcel->getActiveSheet()->setCellValue('c'.$u1, "$rvnm");
$objExcel->getActiveSheet()->setCellValue('d'.$u1, "$tm");
$objExcel->getActiveSheet()->setCellValue('e'.$u1, "$tdz");
$objExcel->getActiveSheet()->setCellValue('f'.$u1, "$tdptn");
$i++;
}
/*----------設置單元格邊框和顏色-------------*/
$rows = mssql_num_rows($res);
for($i=0;$i<($rows+1);$i++){
for($j=0;$j<$count;$j++){
$a = $i+1;
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->getColor()->setARGB('FF00BBcc');
//水平居中
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
}
// 高置列的寬度
$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
// 設置頁眉和頁腳。如果沒有不同的標題奇數/即使是使用單頭假定.
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objExcel->getProperties()->getTitle() . '&RPage &P of &N');
// 設置頁方向和規模
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// 重命名表
$objExcel->getActiveSheet()->setTitle('實時潮汐情況');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)保存為excel2003格式
//設置Excel的名字
$excelName = '實時潮汐情況('.$tm.')';
//$excelName = 'Excel_'.date("YmdHis");
header('Content-Type: application/vnd.ms-excel');
header('Cache-Control: max-age=0');
header( 'Content-Disposition: attachment; filename='.iconv("utf-8", "GBK", $excelName).'.xls');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
