之前写过一篇有关php excel类的教程《phpexcel使用（导出和导入excel文件）教程》，可是最近发现这篇文章似乎介绍的有点太简单了，所以在网上找到了《phpexcel中文帮助手册》,在本文末尾再据此给出一个新的示例。
1.header
[php] header(content-type:application/vnd.ms-excel);
header(content-disposition:attachment;filename=product.xls);
header(pragma:no-cache);
header(expires:0);[/php]
2.phpexcel 相关资料
http://www.codeplex.com/phpexcel
http://www.phpexcel.net
初始化phpexcel
//include class
[php]require_once('classes/phpexcel.php');
require_once('classes/phpexcel/writer/excel2007.php');
/*使用phpexcel 导出 excel 2007文件时要注意将php配置文件中如下功能开启：
*
* php version 5.2.0 or higher
* php extension php_zip enabled **
* php extension php_xml enabled
* php extension php_gd2 enabled (if not compiled in)
* php_zip is only needed by phpexcel_reader_excel2007,phpexcel_writer_excel2007,
* phpexcel_reader_oocalc. in other words, if you need phpexcel to handle .xlsx or .ods files
* you will need the zip extension, but otherwise not.
*/
$objphpexcel = new phpexcel();[/php]
//set properties 设置文件属性
[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);[/php]
//设置全表的默认样式
[php]$objphpexcel = new phpexcel;
// 设置默认的字体样式
$objphpexcel->getdefaultstyle()->getfont()->setname('calibri');
// 设置表格字体的大小
$objphpexcel->getdefaultstyle()->getfont()->setsize(12);[/php]
//批量修改单元格样式
[php]$style_array = array(
'borders' => array(
'top' => array('style' => phpexcel_style_border::border_thin),
'left' => array('style' => phpexcel_style_border::border_thin),
'bottom' => array('style' => phpexcel_style_border::border_thin),
'right' => array('style' => phpexcel_style_border::border_thin)
),
'alignment' => array(
'horizontal' => phpexcel_style_alignment::horizontal_center,
'vertical'   => phpexcel_style_alignment::vertical_center,
'wrap'       => true
)
);
/*获取当前所有包含内容的单元格的最大范围*/
$highestcolumn = $objsheet->gethighestcolumn();
$highestrow = $objsheet->gethighestrow();
//applyfromarray()的第二个参数设置为false，给一定范围内的所有单元格都加上数组里的样式
$objsheet->getstyle('a1:' . $highestcolumn . $highestrow)->applyfromarray($style_array,false);[/php]
//add some data 添加数据
[php]$objphpexcel->setactivesheetindex(0);
$objphpexcel->getactivesheet()->setcellvalue('a1', 'hello');//可以指定位置
$objphpexcel->getactivesheet()->setcellvalue('a2', true);
$objphpexcel->getactivesheet()->setcellvalue('a3', false);
$objphpexcel->getactivesheet()->setcellvalue('b2', 'world!');
$objphpexcel->getactivesheet()->setcellvalue('b3', 2);
$objphpexcel->getactivesheet()->setcellvalue('c1', 'hello');
$objphpexcel->getactivesheet()->setcellvalue('d2', 'world!');
//数据的值可以使用excel的函数，比如：
$objsheet->setcellvalue('f3','=sum(f4:f7)');
$objsheet->setcellvalue('f13','=sum(f3,g8,f9,g13)');[/php]
//循环
[php]for($i = 1;$i $objphpexcel->getactivesheet()->setcellvalue('a' . $i, $i);
$objphpexcel->getactivesheet()->setcellvalue('b' . $i, 'test value');
}[/php]
//日期格式化
[php]$objphpexcel->getactivesheet()->setcellvalue('d1', time());
$objphpexcel->getactivesheet()->getstyle('d1')->getnumberformat()->
setformatcode(phpexcel_style_numberformat::format_date_yyyymmddslash);[/php]
//add comment 添加注释
[php]$objphpexcel->getactivesheet()->getcomment('e11')->setauthor('phpexcel');
$objcommentrichtext = $objphpexcel->getactivesheet()->getcomment('e11')
->gettext()->createtextrun('phpexcel:');
$objcommentrichtext->getfont()->setbold(true);
$objphpexcel->getactivesheet()->getcomment('e11')->gettext()->createtextrun(\r\n);
$objphpexcel->getactivesheet()->getcomment('e11')->gettext()->
createtextrun('total amount on the current invoice, excluding vat.');[/php]
//add rich-text string 添加文字，可设置特定一段内容的样式，比如下面的例子中'payable within thirty days after the end of the month'这句话将会变成深绿色。
[php]$objrichtext = new phpexcel_richtext( $objphpexcel->getactivesheet()->getcell('a18') );
$objrichtext->createtext('this invoice is ');
//只有使用createtextrun()方法才能添加样式。
$objpayable = $objrichtext->
createtextrun('payable within thirty days after the end of the month');
$objpayable->getfont()->setbold(true);
$objpayable->getfont()->setitalic(true);
$objpayable->getfont()->
setcolor( new phpexcel_style_color( phpexcel_style_color::color_darkgreen ) );
$objrichtext->createtext(', unless specified otherwise on the invoice.');[/php]
//merge cells 合并分离单元格
[php]$objphpexcel->getactivesheet()->mergecells('a18:e22');
$objphpexcel->getactivesheet()->unmergecells('a18:e22');[/php]
//protect cells 保护单元格
[php]$objphpexcel->getactivesheet()->getprotection()->setsheet(true);
//needs to be set to true in order to enable any worksheet protection!
$objphpexcel->getactivesheet()->protectcells('a3:e13', 'phpexcel');[/php]
//set cell number formats 数字格式化
[php]$objphpexcel->getactivesheet()->getstyle('e4')->getnumberformat()
->setformatcode(phpexcel_style_numberformat::format_currency_eur_simple);
$objphpexcel->getactivesheet()
->duplicatestyle( $objphpexcel->getactivesheet()->getstyle('e4'), 'e5:e13' );
//保留两位小数
$phpexcel -> getstyle($colstring.6:.$colstring.$row)
-> getnumberformat() -> setformatcode(phpexcel_style_numberformat::format_number_00);
$objphpexcel->getactivesheet()->getstyle($str)->getnumberformat()
->setformatcode(phpexcel_style_numberformat::format_percentage_00);
//导出的数据位日期格式，但numberformat.php给出的的日期格式没有（前后加空格）
$objphpexcel->getactivesheet()->getstyle($str)->getnumberformat()
->setformatcode(phpexcel_style_numberformat::format_text);
$objphpexcel->getactivesheet()->setcellvalue($str, .$arr[$j]. );
//千分位科学计数法
$objphpexcel->getactivesheet()->getstyle('a1')->getnumberformat()
->setformatcode(phpexcel_style_numberformat::format_number_comma_separated1);
$objphpexcel->getactivesheet()->getstyle('a1')->getnumberformat()
->setformatcode('#,##0.00');
$objphpexcel->getactivesheet()->getstyle('a1')->getnumberformat()
->setformatcode('[blue][>=3000]$#,##0;[red][
//set column widths 设置列宽度
[php]$objphpexcel->getactivesheet()->getcolumndimension('b')->setautosize(true);
$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12);[/php]
//set fonts 设置字体
[php]$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setname('candara');
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setsize(20);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setbold(true);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()
->setunderline(phpexcel_style_font::underline_single);
$objphpexcel->getactivesheet()->getstyle('b1')->getfont()
->getcolor()->setargb(phpexcel_style_color::color_white);[/php]
//set alignments 设置对齐
[php]$objphpexcel->getactivesheet()->getstyle('d11')->getalignment()
->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('a18')->getalignment()
->sethorizontal(phpexcel_style_alignment::horizontal_justify);
$objphpexcel->getactivesheet()->getstyle('a18')->getalignment()
->setvertical(phpexcel_style_alignment::vertical_center);
$objphpexcel->getactivesheet()->getstyle('a3')->getalignment()->setwraptext(true);[/php]
//set column borders 设置列边框
[php]$objphpexcel->getactivesheet()->getstyle('a4')->getborders()->gettop()
->setborderstyle(phpexcel_style_border::border_thin);
$objphpexcel->getactivesheet()->getstyle('a10')->getborders()->getleft()
->setborderstyle(phpexcel_style_border::border_medium);
$objphpexcel->getactivesheet()->getstyle('e10')->getborders()->getright()
->setborderstyle(phpexcel_style_border::border_double);
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getallborders()
->setborderstyle(phpexcel_style_border::border_thick);
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->getbottom()
->setborderstyle(phpexcel_style_border::border_thick);[/php]
//set border colors 设置边框颜色
[php]$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getleft()
->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->gettop()
->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getbottom()
->getcolor()->setargb('ff993300');
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->getright()
->getcolor()->setargb('ff993300');[/php]
//set fills 设置填充
[php]/*fill type 和 startcolor需要同时写*/
$objphpexcel->getactivesheet()->getstyle('a1')
->getfill()->setfilltype(phpexcel_style_fill::fill_solid);
$objphpexcel->getactivesheet()->getstyle('a1')
->getfill()->getstartcolor()->setargb('ff808080');
/*
*或者
*$objphpexcel->getactivesheet()->getstyle('a1')
* ->getfill()->getstartcolor()->setrgb('ff8080');
*/
//也可以使用数组的形式填充
$objphpexcel->getactivesheet()->getstyle('a3:g3')->applyfromarray(
array(
'fill' => array(
'type' => phpexcel_style_fill::fill_solid,
'color' => array('rgb' => 'ff8080')
)
)
);[/php]
//add a hyperlink to the sheet 添加链接
[php]$objphpexcel->getactivesheet()->setcellvalue('e26', 'www.phpexcel.net');
$objphpexcel->getactivesheet()->getcell('e26')->gethyperlink()
->seturl('http://www.phpexcel.net');
$objphpexcel->getactivesheet()->getcell('e26')->gethyperlink()
->settooltip('navigate to website');
$objphpexcel->getactivesheet()->getstyle('e26')->getalignment()
->sethorizontal(phpexcel_style_alignment::horizontal_right);[/php]
//add a drawing to the worksheet 添加图片
[php]$objdrawing = new phpexcel_worksheet_drawing();
$objdrawing->setname('logo');
$objdrawing->setdescription('logo');
$objdrawing->setpath('./images/officelogo.jpg');
$objdrawing->setheight(36);
$objdrawing->setcoordinates('b15');
$objdrawing->setoffsetx(110);
$objdrawing->setrotation(25);
$objdrawing->getshadow()->setvisible(true);
$objdrawing->getshadow()->setdirection(45);
$objdrawing->setworksheet($objphpexcel->getactivesheet());[/php]
//play around with inserting and removing rows and columns 插入、删除行或者列
[php]$objphpexcel->getactivesheet()->insertnewrowbefore(6, 10);
$objphpexcel->getactivesheet()->removerow(6, 10);
$objphpexcel->getactivesheet()->insertnewcolumnbefore('e', 5);
$objphpexcel->getactivesheet()->removecolumn('e', 5);[/php]
//add conditional formatting 添加格式
[php]$objconditional1 = new phpexcel_style_conditional();
$objconditional1->setconditiontype(phpexcel_style_conditional::condition_cellis);
$objconditional1->setoperatortype(phpexcel_style_conditional::operator_lessthan);
$objconditional1->setcondition('0');
$objconditional1->getstyle()->getfont()->getcolor()->setargb(phpexcel_style_color::color_red);
$objconditional1->getstyle()->getfont()->setbold(true);[/php]
//set autofilter 自动过滤
[php]$objphpexcel->getactivesheet()->setautofilter('a1:c9');[/php]
//hide phone and fax column 隐藏列
[php]$objphpexcel->getactivesheet()->getcolumndimension('c')->setvisible(false);
$objphpexcel->getactivesheet()->getcolumndimension('d')->setvisible(false);[/php]
//set document security 设置文档安全
[php]$objphpexcel->getsecurity()->setlockwindows(true);
$objphpexcel->getsecurity()->setlockstructure(true);
$objphpexcel->getsecurity()->setworkbookpassword(phpexcel);[/php]
//set sheet security 设置工作表安全
[php]$objphpexcel->getactivesheet()->getprotection()->setpassword('phpexcel');
$objphpexcel->getactivesheet()->getprotection()->setsheet(true);
// this should be enabled in order to enable any of the following!
$objphpexcel->getactivesheet()->getprotection()->setsort(true);
$objphpexcel->getactivesheet()->getprotection()->setinsertrows(true);
$objphpexcel->getactivesheet()->getprotection()->setformatcells(true);[/php]
//calculated data 计算
[php]echo 'value of b14 [=count(b2:b12)]: ' . $objphpexcel->getactivesheet()
->getcell('b14')->getcalculatedvalue() . \r\n;[/php]
//set outline levels
[php]$objphpexcel->getactivesheet()->getcolumndimension('e')->setoutlinelevel(1);
$objphpexcel->getactivesheet()->getcolumndimension('e')->setvisible(false);
$objphpexcel->getactivesheet()->getcolumndimension('e')->setcollapsed(true);[/php]
//freeze panes，冻结单元格
[php]$objphpexcel->getactivesheet()->freezepane('c6');
//表示冻结第6行（不包括第6行）以上，c列往左（不包括c列）的所有单元格。[/php]
//rows to repeat at top,下拉重复
[php]$objphpexcel->getactivesheet()->getpagesetup()->setrowstorepeatattopbystartandend(1, 1);[/php]
//set data validation 验证输入值
[php]$objvalidation = $objphpexcel->getactivesheet()->getcell('b3')->getdatavalidation();
$objvalidation->settype( phpexcel_cell_datavalidation::type_whole );
$objvalidation->seterrorstyle( phpexcel_cell_datavalidation::style_stop );
$objvalidation->setallowblank(true);
$objvalidation->setshowinputmessage(true);
$objvalidation->setshowerrormessage(true);
$objvalidation->seterrortitle('input error');
$objvalidation->seterror('number is not allowed!');
$objvalidation->setprompttitle('allowed input');
$objvalidation->setprompt('only numbers between 10 and 20 are allowed.');
$objvalidation->setformula1(10);
$objvalidation->setformula2(20);
$objphpexcel->getactivesheet()->getcell('b3')->setdatavalidation($objvalidation);[/php]
//create a new worksheet, after the default sheet 创建新的工作标签
[php]$objphpexcel->createsheet();
$objphpexcel->setactivesheetindex(1);[/php]
//set header and footer. when no different headers for odd/even are used, odd header is assumed.页眉页脚
[php]$objphpexcel->getactivesheet()->getheaderfooter()
->setoddheader('&c&hplease treat this document as confidential!');
$objphpexcel->getactivesheet()->getheaderfooter()
->setoddfooter('&l&b'.$objphpexcel->getproperties()->gettitle().'&rpage &p of &n');[/php]
//set page orientation and size 方向大小
[php]$objphpexcel->getactivesheet()->getpagesetup()
->setorientation(phpexcel_worksheet_pagesetup::orientation_landscape);
$objphpexcel->getactivesheet()->getpagesetup()
->setpapersize(phpexcel_worksheet_pagesetup::papersize_a4);[/php]
//rename sheet 重命名工作标签
[php]$objphpexcel->getactivesheet()->settitle('simple');
//set active sheet index to the first sheet, so excel opens this as the first sheet
$objphpexcel->setactivesheetindex(0);[/php]
//save excel 2007 file 保存
[php]$objwriter = new phpexcel_writer_excel2007($objphpexcel);
$objwriter->save(str_replace('.php', '.xlsx', __file__));[/php]
//save excel 5 file 保存
[php]require_once('classes/phpexcel/writer/excel5.php');
$objwriter = new phpexcel_writer_excel5($objphpexcel);
$objwriter->save(str_replace('.php', '.xls', __file__));[/php]
//1.6.2新版保存
[php]require_once('classes/phpexcel/iofactory.php');
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel2007');
$objwriter->save(str_replace('.php', '.xls', __file__));[/php]
读excel
[php]//include class
require_once('classes/phpexcel/reader/excel2007.php');
$objreader = new phpexcel_reader_excel2007;
$objphpexcel = $objreader->load(05featuredemo.xlsx);[/php]
读写csv
[php]require_once(05featuredemo.inc.php);
require_once('classes/phpexcel/writer/csv.php');
require_once('classes/phpexcel/reader/csv.php');
require_once('classes/phpexcel/writer/excel2007.php');[/php]
//write to csv format 写
[php]$objwriter = new phpexcel_writer_csv($objphpexcel);
$objwriter->setdelimiter(';');
$objwriter->setenclosure('');
$objwriter->setlineending(\r\n);
$objwriter->setsheetindex(0);
$objwriter->save(str_replace('.php', '.csv', __file__));[/php]
//read from csv format 读
[php]$objreader = new phpexcel_reader_csv();
$objreader->setdelimiter(';');
$objreader->setenclosure('');
$objreader->setlineending(\r\n);
$objreader->setsheetindex(0);
$objphpexcelfromcsv = $objreader->load(str_replace('.php', '.csv', __file__));[/php]
//write to excel2007 format
[php]$objwriter2007 = new phpexcel_writer_excel2007($objphpexcelfromcsv);
$objwriter2007->save(str_replace('.php', '.xlsx', __file__));[/php]
写html
[php]require_once(05featuredemo.inc.php);
require_once('classes/phpexcel/writer/html.php');[/php]
//write to html format
[php]$objwriter = new phpexcel_writer_html($objphpexcel);
$objwriter->setsheetindex(0);
$objwriter->save(str_replace('.php', '.htm', __file__));[/php]
写pdf
[php]require_once(05featuredemo.inc.php);
require_once('classes/phpexcel/iofactory.php');
//write to pdf format
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'pdf');
$objwriter->setsheetindex(0);
$objwriter->save(str_replace('.php', '.pdf', __file__));
//echo memory peak usage
echo date('h:i:s'). peak memory usage: .(memory_get_peak_usage(true) / 1024 / 1024). mb\r\n;
//=============================中文手册部分结束====================================//[/php]
示例：
我们将使用phpexcel创建一个如下图所示的excel文件（销售报告），使用calibri字体，默认大小 8pt。单位则使用euro 欧元。
[caption id=attachment_916 align=aligncenter width=434] php excel[/caption]
全部代码如下：
[php]
// include phpexcel
require('phpexcel.php');
//或者include 'phpexcel/writer/excel5.php'; 用于输出.xls的
require('phpexcel/iofactory.php'); //phpexcel工厂类
require('phpexcel/writer/excel2007.php');
// create new phpexcel object
$objphpexcel = new phpexcel;
// set default font
$objphpexcel->getdefaultstyle()->getfont()->setname('calibri');
// set default font size
$objphpexcel->getdefaultstyle()->getfont()->setsize(8);
// create the writer
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, excel2007);
//也可以使用 $objwriter = new phpexcel_writer_excel2007($objphpexcel);
/*输出pdf格式*/
//$objwriter = phpexcel_iofactory::createwriter($objphpexcel, pdf);
/**
* define currency and number format.
*/
// currency format, € with getactivesheet();
// rename the sheet
$objsheet->settitle('my sales report');
// let's bold and size the header font and write the header
// as you can see, we can specify a range of cells, like here: cells from a1 to a4
$objsheet->getstyle('a1:d1')->getfont()->setbold(true)->setsize(12);
// write header
$objsheet->getcell('a1')->setvalue('product');
$objsheet->getcell('b1')->setvalue('quanity');
$objsheet->getcell('c1')->setvalue('price');
$objsheet->getcell('d1')->setvalue('total price');
// we could get this data from database, but for simplicty, let's just write it
$objsheet->getcell('a2')->setvalue('motherboard');
$objsheet->getcell('b2')->setvalue(10);
$objsheet->getcell('c2')->setvalue(5);
$objsheet->getcell('d2')->setvalue('=b2*c2');
$objsheet->getcell('a3')->setvalue('processor');
$objsheet->getcell('b3')->setvalue(6);
$objsheet->getcell('c3')->setvalue(3);
$objsheet->getcell('d3')->setvalue('=b3*c3');
$objsheet->getcell('a4')->setvalue('memory');
$objsheet->getcell('b4')->setvalue(10);
$objsheet->getcell('c4')->setvalue(2.5);
$objsheet->getcell('d4')->setvalue('=b4*c4');
$objsheet->getcell('a5')->setvalue('total');
$objsheet->getcell('b5')->setvalue('=sum(b2:b4)');
$objsheet->getcell('c5')->setvalue('-');
$objsheet->getcell('d5')->setvalue('=sum(d2:d4)');
// bold and resize the font of the last row
$objsheet->getstyle('a5:d5')->getfont()->setbold(true)->setsize(12);
// set number and currency format to columns
$objsheet->getstyle('b2:b5')->getnumberformat()->setformatcode($numberformat);
$objsheet->getstyle('c2:d5')->getnumberformat()->setformatcode($currencyformat);
// create some borders
// first, create the whole grid around the table
$objsheet->getstyle('a1:d5')->getborders()->
getallborders()->setborderstyle(phpexcel_style_border::border_thin);
// create medium border around the table
$objsheet->getstyle('a1:d5')->getborders()->
getoutline()->setborderstyle(phpexcel_style_border::border_medium);
// create a double border above total line
$objsheet->getstyle('a5:d5')->getborders()->
gettop()->setborderstyle(phpexcel_style_border::border_double);
// create a medium border on the header line
$objsheet->getstyle('a1:d1')->getborders()->
getbottom()->setborderstyle(phpexcel_style_border::border_medium);
// autosize the columns
$objsheet->getcolumndimension('a')->setautosize(true);
$objsheet->getcolumndimension('b')->setautosize(true);
$objsheet->getcolumndimension('c')->setautosize(true);
$objsheet->getcolumndimension('d')->setautosize(true);
// 写入文件中
$objwriter->save('test.xlsx');
//输出pdf格式文件
//$objwriter->save('test.pdf');
[/php]