欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-01-12 08:22:49
本文实例分析了使用phpexcel操作excel用法。分享给大家供大家参考。具体分析如下: phpexcel下载地址: http://www.codeplex.com/...

本文实例分析了使用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程序设计有所帮助。