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的属性： 

<?php
创建人 
　　$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(&#39;Simple&#39;); 
　　设置单元格的值 
　　$objPHPExcel->getActiveSheet()->setCellValue("A1", &#39;String&#39;); 
　　$objPHPExcel->getActiveSheet()->setCellValue("A2", 12); 
　　$objPHPExcel->getActiveSheet()->setCellValue("A3", true); 
　　$objPHPExcel->getActiveSheet()->setCellValue("C5", &#39;=SUM(C2:C4)&#39;); 
　　$objPHPExcel->getActiveSheet()->setCellValue("B8", &#39;=MIN(B2:C5)&#39;); 
　　合并单元格 
　　$objPHPExcel->getActiveSheet()->mergeCells("A18:E22"); 
　　分离单元格 
　　$objPHPExcel->getActiveSheet()->unmergeCells("A28:B28");
?>

三、PHPEXCEL举例应用

整个代码如下,值得注意的是表头用了$orderCellData记录了每个商户编号的顺序,为了在表体把对应的数据取出,代码如下:

<?php
require_once &#39;../../../libs/PHPExcel/Classes/PHPExcel.php&#39;;
require_once &#39;../../../libs/PHPExcel/Classes/PHPExcel/Writer/Excel5.php&#39;;
include_once &#39;../../../libs/PHPExcel/Classes/PHPExcel/IOFactory.php&#39;;
include &#39;../common/config.php&#39;;
// 创建一个处理对象实例（此对象对于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[&#39;mssql&#39;][&#39;host&#39;], $config[&#39;mssql&#39;][&#39;user&#39;], $config[&#39;mssql&#39;][&#39;password&#39;]);
mssql_select_db($config[&#39;mssql&#39;][&#39;dbname&#39;], $conn);
$tm = $_REQUEST[&#39;tm&#39;];
$sql = "exec HNow05_getTTSpace &#39;&#39;,&#39;" . $tm . "&#39;,&#39;&#39;,1";
$sql = mb_convert_encoding($sql, &#39;GBK&#39;, &#39;UTF-8&#39;);
$res = mssql_query($sql);
$i = 0;
$k = array(
    &#39;站码&#39;,
    &#39;站名&#39;,
    &#39;河系&#39;,
    &#39;来报时间&#39;,
    &#39;水位&#39;,
    &#39;水势&#39;
);
$count = count($k);
$arrs = array(
    &#39;A&#39;,
    &#39;B&#39;,
    &#39;C&#39;,
    &#39;D&#39;,
    &#39;E&#39;,
    &#39;F&#39;
);
//添加表头
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 == &#39;6&#39;) {
        $tdptn = &#39;平&#39;;
    } else if ($tdptn == &#39;5&#39;) {
        $tdptn = &#39;涨&#39;;
    } else if ($tdptn == &#39;4&#39;) {
        $tdptn = &#39;落&#39;;
    }
    $u1 = $i + 2;
    $stnm = iconv("GBK", "utf-8", $stnm);
    $rvnm = iconv("GBK", "utf-8", $rvnm);
    $tm = iconv("GBK", "utf-8", $tm);
    /*----------写入内容-------------*/
    $objExcel->getActiveSheet()->setCellValue(&#39;a&#39; . $u1, "$stcd");
    $objExcel->getActiveSheet()->setCellValue(&#39;b&#39; . $u1, "$stnm");
    $objExcel->getActiveSheet()->setCellValue(&#39;c&#39; . $u1, "$rvnm");
    $objExcel->getActiveSheet()->setCellValue(&#39;d&#39; . $u1, "$tm");
    $objExcel->getActiveSheet()->setCellValue(&#39;e&#39; . $u1, "$tdz");
    $objExcel->getActiveSheet()->setCellValue(&#39;f&#39; . $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(&#39;FF00BBcc&#39;);
        //水平居中
        $objExcel->getActiveSheet()->getStyle($arrs[$j] . $a)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    }
}
// 高置列的宽度
$objExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(20);
$objExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension(&#39;F&#39;)->setWidth(10);
// 设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定.
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(&#39;&L&BPersonal cash register&RPrinted on &D&#39;);
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(&#39;&L&B&#39; . $objExcel->getProperties()->getTitle() . &#39;&RPage &P of &N&#39;);
// 设置页方向和规模
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// 重命名表
$objExcel->getActiveSheet()->setTitle(&#39;实时潮汐情况&#39;);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objExcel->setActiveSheetIndex(0);
// Redirect output to a client&#39;s web browser (Excel5)保存为excel2003格式
//设置Excel的名字
$excelName = &#39;实时潮汐情况(&#39; . $tm . &#39;)&#39;;
//$excelName = &#39;Excel_&#39;.date("YmdHis");
header(&#39;Content-Type: application/vnd.ms-excel&#39;);
header(&#39;Cache-Control: max-age=0&#39;);
header(&#39;Content-Disposition: attachment; filename=&#39; . iconv("utf-8", "GBK", $excelName) . &#39;.xls&#39;);
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, &#39;Excel5&#39;);
$objWriter->save(&#39;php://output&#39;);
exit;
?>

教程地址：

欢迎转载！但请带上文章地址^^