PHPEXCEL是一个用来生成excel的php插件,他可以很方便的对excel数据进行操作,如:生成excel,修改excel数据等等。
一、PHPEXCEL简介
PHPEXCEL提供了一系列的API,能够解析与生成excel,pdf之类的文档。
PHPEXCEL虽然强大,不过使用起来相对有些繁锁,如果需要输出较为复杂格式时,是一个不错的选择。可以到官方下载到源码。
二、PHPEXCEL部分函数
设置当前的工作簿,返回该工作簿对象:
$excelSheet = $excel->setActiveSheetIndex0);
合并单元格,返回该单元格对象,以下示例即合并A列第一行与第二行所在单元格:
代码如下 | 复制代码 |
$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->setActiveSheetIndex0); 设置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′, ‘=SUMC2:C4)’); $objPHPExcel->getActiveSheet)->setCellValue’B8′, ‘=MINB2: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->setActiveSheetIndex0); $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)->setBorderStylePHPExcel_Style_Border::BORDER_THIN); $objExcel->getActiveSheet)->getStyle$arrs[$j].$a)->getBorders)->getAllBorders)->getColor)->setARGB’FF00BBcc’); //水平居中 $objExcel->getActiveSheet)->getStyle$arrs[$j].$a)->getAlignment)->setHorizontalPHPExcel_Style_Alignment::HORIZONTAL_CENTER); } } // 高置列的宽度 $objExcel->getActiveSheet)->getColumnDimension’A’)->setWidth10); $objExcel->getActiveSheet)->getColumnDimension’B’)->setWidth15); $objExcel->getActiveSheet)->getColumnDimension’C’)->setWidth15); $objExcel->getActiveSheet)->getColumnDimension’D’)->setWidth20); $objExcel->getActiveSheet)->getColumnDimension’E’)->setWidth10); $objExcel->getActiveSheet)->getColumnDimension’F’)->setWidth10); // 设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定. $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)->setOrientationPHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT); $objExcel->getActiveSheet)->getPageSetup)->setPaperSizePHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); // 重命名表 $objExcel->getActiveSheet)->setTitle’实时潮汐情况’); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objExcel->setActiveSheetIndex0); // 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; ?> |
http://www.bkjia.com/PHPjc/630735.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/630735.htmlTechArticlePHPEXCEL是一个用来生成excel的php插件,他可以很方便的对excel数据进行操作,如:生成excel,修改excel数据等等。 一、PHPEXCEL简介 PHPEXCEL提供了一…