使用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程序设计有所帮助。
上一篇: 以前穷
下一篇: php字符串函数学习之strstr()