PHPEXCEL的用法与简介
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记录了每个商户编号的顺序,为了在表体把对应的数据取出,代码如下:
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); //开源代码phpfensi测试数据 // 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 ;查看更多关于PHPEXCEL的用法与简介 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did30441