使用PHPExcel操作Excel用法实例分析

本文实例分析了使用phpexcel操作excel用法。分享给大家供大家参考。具体分析如下：

phpexcel下载地址：
http://www.codeplex.com/phpexcel
http://www.phpexcel.net
开发包tests目录有详细使用实例  支持中文,注意文件编码   文件保存为utf-8

1.header部分：

header("content-type:application/vnd.ms-excel");
header("content-disposition:attachment;filename=sample.xls"); 
header("pragma:no-cache"); 
header("expires:0");

2.写excel：

//include class 
require_once('classes/phpexcel.php'); 
require_once('classes/phpexcel/writer/excel2007.php'); 
$objphpexcel = new phpexcel(); 
 
//set properties 设置文件属性 
$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"); 
 
//add some data 添加数据 
$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!'); 
 
//循环 
for($i = 1;$i<200;$i++) { 
$objphpexcel->getactivesheet()->setcellvalue('a' . $i, $i); 
$objphpexcel->getactivesheet()->setcellvalue('b' . $i, 'test value'); 
} 
 
//日期格式化 
$objphpexcel->getactivesheet()->setcellvalue('d1', time()); 
$objphpexcel->getactivesheet()->getstyle('d1')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_yyyymmddslash); 
 
//add comment 添加注释 
$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.'); 
 
//add rich-text string 添加文字 可设置样式 
$objrichtext = new phpexcel_richtext( $objphpexcel->getactivesheet()->getcell('a18') ); 
$objrichtext->createtext('this invoice is '); 
$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.'); 
 
//merge cells 合并分离单元格 
$objphpexcel->getactivesheet()->mergecells('a18:e22'); 
$objphpexcel->getactivesheet()->unmergecells('a18:e22'); 
 
//protect cells 保护单元格 
$objphpexcel->getactivesheet()->getprotection()->setsheet(true);//needs to be set to true in order to enable any worksheet protection! 
$objphpexcel->getactivesheet()->protectcells('a3:e13', 'phpexcel'); 
 
//set cell number formats 数字格式化 
$objphpexcel->getactivesheet()->getstyle('e4')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_currency_eur_simple); 
$objphpexcel->getactivesheet()->duplicatestyle( $objphpexcel->getactivesheet()->getstyle('e4'), 'e5:e13' ); 
 
//set column widths 设置列宽度 
$objphpexcel->getactivesheet()->getcolumndimension('b')->setautosize(true); 
$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12); 
 
//set fonts 设置字体 
$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); 
 
//set alignments 设置对齐 
$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); 
 
//set column borders 设置列边框 
$objphpexcel->getactivesheet()->getstyle('a4')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin); 
$objphpexcel->getactivesheet()->getstyle('a10')->getborders()->getleft()->setborderstyle(phpexcel_style_border::border_thin); 
$objphpexcel->getactivesheet()->getstyle('e10')->getborders()->getright()->setborderstyle(phpexcel_style_border::border_thin); 
$objphpexcel->getactivesheet()->getstyle('d13')->getborders()->getleft()->setborderstyle(phpexcel_style_border::border_thick); 
$objphpexcel->getactivesheet()->getstyle('e13')->getborders()->getbottom()->setborderstyle(phpexcel_style_border::border_thick); 
 
//set border colors 设置边框颜色 
$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'); 
 
//set fills 设置填充 
$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid); 
$objphpexcel->getactivesheet()->getstyle('a1')->getfill()->getstartcolor()->setargb('ff808080'); 
 
//add a hyperlink to the sheet 添加链接 
$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); 
 
//add a drawing to the worksheet 添加图片 
$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()); 
 
//play around with inserting and removing rows and columns 
$objphpexcel->getactivesheet()->insertnewrowbefore(6, 10); 
$objphpexcel->getactivesheet()->removerow(6, 10); 
$objphpexcel->getactivesheet()->insertnewcolumnbefore('e', 5); 
$objphpexcel->getactivesheet()->removecolumn('e', 5); 
 
//add conditional formatting 
$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); 
 
//set autofilter 自动过滤 
$objphpexcel->getactivesheet()->setautofilter('a1:c9'); 
 
//hide "phone" and "fax" column 隐藏列 
$objphpexcel->getactivesheet()->getcolumndimension('c')->setvisible(false); 
$objphpexcel->getactivesheet()->getcolumndimension('d')->setvisible(false); 
 
//set document security 设置文档安全 
$objphpexcel->getsecurity()->setlockwindows(true); 
$objphpexcel->getsecurity()->setlockstructure(true); 
$objphpexcel->getsecurity()->setworkbookpassword("phpexcel"); 
 
//set sheet security 设置工作表安全 
$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); 
 
//calculated data 计算 
echo 'value of b14 [=count(b2:b12)]: ' . $objphpexcel->getactivesheet()->getcell('b14')->getcalculatedvalue() . "\r\n"; 
 
//set outline levels 
$objphpexcel->getactivesheet()->getcolumndimension('e')->setoutlinelevel(1); 
$objphpexcel->getactivesheet()->getcolumndimension('e')->setvisible(false); 
$objphpexcel->getactivesheet()->getcolumndimension('e')->setcollapsed(true); 
 
//freeze panes 
$objphpexcel->getactivesheet()->freezepane('a2'); 
 
//rows to repeat at top 
$objphpexcel->getactivesheet()->getpagesetup()->setrowstorepeatattopbystartandend(1, 1); 
 
//set data validation 验证输入值 
$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);
 
//create a new worksheet, after the default sheet 创建新的工作标签
$objphpexcel->createsheet();
$objphpexcel->setactivesheetindex(1);
 
//set header and footer. when no different headers for odd/even are used, odd header is assumed. 页眉页脚
$objphpexcel->getactivesheet()->getheaderfooter()->setoddheader('&c&hplease treat this document as confidential!');
$objphpexcel->getactivesheet()->getheaderfooter()->setoddfooter('&l&b' . $objphpexcel->getproperties()->gettitle() . '&rpage &p of &n');
 
//set page orientation and size 方向大小
$objphpexcel->getactivesheet()->getpagesetup()->setorientation(phpexcel_worksheet_pagesetup::orientation_landscape);
$objphpexcel->getactivesheet()->getpagesetup()->setpapersize(phpexcel_worksheet_pagesetup::papersize_a4);
 
//rename sheet 重命名工作表标签 
$objphpexcel->getactivesheet()->settitle('simple');
 
//set active sheet index to the first sheet, so excel opens this as the first sheet
$objphpexcel->setactivesheetindex(0);
 
//save excel 2007 file 保存 
$objwriter = new phpexcel_writer_excel2007($objphpexcel);
$objwriter->save(str_replace('.php', '.xlsx', __file__));
 
//save excel 5 file 保存 
require_once('classes/phpexcel/writer/excel5.php'); 
$objwriter = new phpexcel_writer_excel5($objphpexcel); 
$objwriter->save(str_replace('.php', '.xls', __file__));
 
//1.6.2新版保存 
require_once('classes/phpexcel/iofactory.php');
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel2007'); 
$objwriter->save(str_replace('.php', '.xls', __file__));

3.读excel

//include class 
require_once('classes/phpexcel/reader/excel2007.php'); 
$objreader = new phpexcel_reader_excel2007; 
$objphpexcel = $objreader->load("05featuredemo.xlsx");

4.读写csv

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'); 
//write to csv format 写 
$objwriter = new phpexcel_writer_csv($objphpexcel);
$objwriter->setdelimiter(';'); 
$objwriter->setenclosure(''); 
$objwriter->setlineending("\r\n"); 
$objwriter->setsheetindex(0); 
$objwriter->save(str_replace('.php', '.csv', __file__));
//read from csv format 读 
$objreader = new phpexcel_reader_csv();
$objreader->setdelimiter(';'); 
$objreader->setenclosure(''); 
$objreader->setlineending("\r\n");
$objreader->setsheetindex(0); 
$objphpexcelfromcsv = $objreader->load(str_replace('.php', '.csv', __file__));
//write to excel2007 format 
$objwriter2007 = new phpexcel_writer_excel2007($objphpexcelfromcsv);
$objwriter2007->save(str_replace('.php', '.xlsx', __file__));

5.写html

require_once("05featuredemo.inc.php"); 
require_once('classes/phpexcel/writer/html.php');
//write to html format 
$objwriter = new phpexcel_writer_html($objphpexcel);
$objwriter->setsheetindex(0); 
$objwriter->save(str_replace('.php', '.htm', __file__));

6.写pdf

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程序设计有所帮助。