一、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
$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');
exit;
?>
http://www.bkjia.com/phpjc/630735.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/630735.htmltecharticlephpexcel是一个用来生成excel的php插件，他可以很方便的对excel数据进行操作，如：生成excel,修改excel数据等等。 一、phpexcel简介 phpexcel提供了一...