phpexcel是一个用来生成excel的php插件，他可以很方便的对excel数据进行操作，如：生成excel,修改excel数据等等。
一、phpexcel简介
PHPEXCEL提供了一系列的API，能够解析与生成excel，pdf之类的文档。
PHPEXCEL虽然强大，不过使用起来相对有些繁锁，如果需要输出较为复杂格式时，是一个不错的选择。可以到官方下载到源码。
二、PHPEXCEL部分函数
设置当前的工作簿，返回该工作簿对象：
$excelSheet = $excel->setActiveSheetIndex(0);
合并单元格，返回该单元格对象，以下示例即合并A列第一行与第二行所在单元格：
立即学习“PHP免费学习笔记（深入）”；
| 代码如下 | 复制代码 |
|
$excelSheet->mergeCells('A1: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记录了每个商户编号的顺序，为了在表体把对应的数据取出)：
| 代码如下 | 复制代码 |
|
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 $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 for($j=0;$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');
|
|
