程序部分require_once './phpexcel/phpexcel.php';
// 首先创建一个新的对象 phpexcel object$objphpexcel = new phpexcel();
// 设置文件的一些属性，在xls文件——>属性——>详细信息里可以看到这些值，xml表格里是没有这些值的$objphpexcel ->getproperties() //获得文件属性对象，给下文提供设置资源 ->setcreator( maarten balliauw) //设置文件的创建者 ->setlastmodifiedby( maarten balliauw) //设置最后修改者 ->settitle( office 2007 xlsx test document ) //设置标题 ->setsubject( office 2007 xlsx test document ) //设置主题 ->setdescription( test document for office 2007 xlsx, generated using php classes.) //设置备注 ->setkeywords( office 2007 openxml php) //设置标记 ->setcategory( test result file); //设置类别// 位置aaa *为下文代码位置提供锚// 给表格添加数据$objphpexcel->setactivesheetindex(0) //设置第一个内置表（一个xls文件里可以有多个表）为活动的 ->setcellvalue( 'a1', 'hello' ) //给表的单元格设置数据 ->setcellvalue( 'b2', 'world!' ) //数据格式可以为字符串 ->setcellvalue( 'c1', 12) //数字型 ->setcellvalue( 'd2', 12) // ->setcellvalue( 'd3', true ) //布尔型 ->setcellvalue( 'd4', '=sum(c1:d2)' );//公式
//得到当前活动的表,注意下文教程中会经常用到$objactsheet$objactsheet = $objphpexcel->getactivesheet();// 位置bbb *为下文代码位置提供锚// 给当前活动的表设置名称$objactsheet->settitle('simple2222');代码还没有结束，可以复制下面的代码来决定我们将要做什么
我们将要做的是1,直接生成一个文件$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'excel2007');$objwriter->save('myexchel.xlsx');
2、提示下载文件excel 2003 .xls// 生成2003excel格式的xls文件header('content-type: application/vnd.ms-excel');header('content-disposition: attachment;filename=01simple.xls');header('cache-control: max-age=0');
$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'excel5');$objwriter->save('php://output');exit;
excel 2007 .xlsx// 生成2007excel格式的xlsx文件header('content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('content-disposition: attachment;filename=01simple.xlsx');header('cache-control: max-age=0');
$objwriter =phpexcel_iofactory:: createwriter($objphpexcel, 'excel2007');$objwriter->save( 'php://output');exit;
pdf 文件// 下载一个pdf文件header('content-type: application/pdf');header('content-disposition: attachment;filename=01simple.pdf');header('cache-control: max-age=0');
$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'pdf');$objwriter->save('php://output');exit;// 生成一个pdf文件$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'pdf');$objwriter->save('a.pdf');
csv 文件$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'csv')->setdelimiter(',' ) //设置分隔符 ->setenclosure('' ) //设置包围符 ->setlineending(\r\n )//设置行分隔符 ->setsheetindex(0) //设置活动表 ->save(str_replace('.php' , '.csv' , __file__));
html 文件$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'html'); //将$objphpecel对象转换成html格式的$objwriter->setsheetindex(0); //设置活动表//$objwriter->setimagesroot('http://www.example.com');$objwriter->save(str_replace('.php', '.htm', __file__)); //保存文件
设置表格样式和数据格式设置默认的字体和文字大小 锚：aaa$objphpexcel->getdefaultstyle()->getfont()->setname( 'arial');$objphpexcel->getdefaultstyle()->getfont()->setsize(20);
日期格式 锚：bbb//获得秒值变量$datetimenow = time();//三个表格分别设置为当前实际的 日期格式、时间格式、日期和时间格式//首先将单元格的值设置为由phpexcel_shared_date::phptoexcel方法转换后的excel格式的值，然后用过得到该单元格的样式里面数字样式再设置显示格式$objactsheet->setcellvalue( 'c9',phpexcel_shared_date::phptoexcel( $datetimenow )); $objactsheet->getstyle( 'c9')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_yyyymmdd2);$objactsheet->setcellvalue( 'c10',phpexcel_shared_date::phptoexcel( $datetimenow ));$objactsheet->getstyle( 'c10')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_time4);$objactsheet->setcellvalue( 'c10',phpexcel_shared_date::phptoexcel( $datetimenow ));$objactsheet->getstyle( 'c10')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_date_time4);//将e4到e13的数字格式设置为eur$objphpexcel->getactivesheet()->getstyle( 'e4:e13')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_currency_eur_simple);
设置列的宽度 锚：bbb$objactsheet->getcolumndimension( 'b')->setautosize(true); //内容自适应$objactsheet->getcolumndimension( 'a')->setwidth(30); //30宽
设置文件打印的页眉和页脚 锚：bbb//设置打印时候的页眉页脚（设置完了以后可以通过打印预览来看效果）字符串中的&*好像是一些变量$objactsheet->getheaderfooter()->setoddheader( '&l&g&c&hplease treat this document as confidential!');$objactsheet->getheaderfooter()->setoddfooter( '&l&b' . $objphpexcel->getproperties()->gettitle() . '&rpage &p of &n' );
设置页面文字的方向和页面大小 锚：bbb$objphpexcel->getactivesheet()->getpagesetup()->setorientation(phpexcel_worksheet_pagesetup:: orientation_landscape);$objphpexcel->getactivesheet()->getpagesetup()->setpapersize(phpexcel_worksheet_pagesetup:: papersize_a4); //a4纸大小
为页眉添加图片 office中有效 wps中无效 锚：bbb$objdrawing = new phpexcel_worksheet_headerfooterdrawing();$objdrawing->setname('phpexcel logo');$objdrawing->setpath('./images/phpexcel_logo.gif');$objdrawing->setheight(36);$objphpexcel->getactivesheet()->getheaderfooter()->addimage($objdrawing,phpexcel_worksheet_headerfooter::image_header_left );
设置单元格的批注 锚：bbb//给单元格添加批注$objphpexcel->getactivesheet()->getcomment( 'e13')->setauthor('phpexcel' ); //设置作者$objcommentrichtext = $objphpexcel->getactivesheet()->getcomment('e13' )->gettext()->createtextrun('phpexcel:'); //添加批注$objcommentrichtext->getfont()->setbold( true); //将现有批注加粗$objphpexcel->getactivesheet()->getcomment( 'e13')->gettext()->createtextrun(\r\n ); //添加更多批注$objphpexcel->getactivesheet()->getcomment( 'e13')->gettext()->createtextrun('total amount on the current invoice, including vat.' );$objphpexcel->getactivesheet()->getcomment( 'e13')->setwidth('100pt' ); //设置批注显示的宽高 ，在office中有效在wps中无效$objphpexcel->getactivesheet()->getcomment( 'e13')->setheight('100pt' );$objphpexcel->getactivesheet()->getcomment( 'e13')->setmarginleft('150pt' );$objphpexcel->getactivesheet()->getcomment( 'e13')->getfillcolor()->setrgb('eeeeee' ); //设置背景色 ，在office中有效在wps中无效
添加文字块 看效果图 office中有效 wps中无效 锚：bbb//大概翻译 创建一个富文本框 office有效 wps无效$objrichtext = new phpexcel_richtext();$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.');//将文字写到a18单元格中$objphpexcel->getactivesheet()->getcell( 'a18')->setvalue($objrichtext);学习笔记>
合并拆分单元格 锚：bbb$objphpexcel->getactivesheet()->mergecells( 'a28:b28'); // a28:b28合并$objphpexcel->getactivesheet()->unmergecells( 'a28:b28'); // a28:b28再拆分
单元格密码保护 锚：bbb// 单元格密码保护不让修改$objphpexcel->getactivesheet()->getprotection()->setsheet( true); // 为了使任何表保护，需设置为真$objphpexcel->getactivesheet()->protectcells( 'a3:e13', 'phpexcel' ); // 将a3到e13保护 加密密码是 phpexcel$objphpexcel->getactivesheet()->getstyle( 'b1')->getprotection()->setlocked(phpexcel_style_protection::protection_unprotected); //去掉保护
设置单元格字体 锚：bbb//将b1的文字字体设置为candara，20号的粗体下划线有背景色$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);
文字对齐方式 锚：bbb$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); //垂直方向上中间居中
设置单元格边框 锚：bbb$stylethinblackborderoutline = array( 'borders' => array ( 'outline' => array ( 'style' =>phpexcel_style_border::border_thin, //设置border样式 //'style' =>phpexcel_style_border::border_thick, 另一种样式 'color' => array ('argb' => 'ff000000'), //设置border颜色 ), ),);$objphpexcel->getactivesheet()->getstyle( 'a4:e10')->applyfromarray($stylethinblackborderoutline);
背景填充颜色 锚：bbb//设置填充的样式和背景色$objphpexcel->getactivesheet()->getstyle( 'a1:e1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid);$objphpexcel->getactivesheet()->getstyle( 'a1:e1')->getfill()->getstartcolor()->setargb('ff808080');
综合设置样例$objphpexcel->getactivesheet()->getstyle( 'a3:e3')->applyfromarray( array( 'font' => array ( 'bold' => true ), 'alignment' => array ( 'horizontal' =>phpexcel_style_alignment::horizontal_right , ), 'borders' => array ( 'top' => array ( 'style' =>phpexcel_style_border::border_thin ) ), 'fill' => array ( 'type' =>phpexcel_style_fill::fill_gradient_linear , 'rotation' => 90, 'startcolor' => array ( 'argb' => 'ffa0a0a0' ), 'endcolor' => array ( 'argb' => 'ffffffff' ) ) ));学习笔记>
给单元格内容设置url超链接 锚：bbb$objactsheet->getcell('e26')->gethyperlink()->seturl( 'http://www.phpexcel.net'); //超链接url地址$objactsheet->getcell('e26')->gethyperlink()->settooltip( 'navigate to website'); //鼠标移上去连接提示信息
给表中添加图片 锚：bbb$objdrawing = new phpexcel_worksheet_drawing();$objdrawing->setname('paid');$objdrawing->setdescription('paid');$objdrawing->setpath('./images/paid.png'); //图片引入位置$objdrawing->setcoordinates('b15'); //图片添加位置$objdrawing->setoffsetx(210);$objdrawing->setrotation(25); $objdrawing->setheight(36);$objdrawing->getshadow()->setvisible (true );$objdrawing->getshadow()->setdirection(45);$objdrawing->setworksheet($objphpexcel->getactivesheet());//还可以添加有gd库生产的图片，详细见自带实例25
创建一个新工作表和设置工作表标签颜色 锚：bbb$objexcel->createsheet(); $objphpexcel->setactivesheetindex(1); //设置第2个表为活动表，提供操作句柄$objexcel->getsheet(1)->settitle( '测试2'); //直接得到第二个表进行设置,将工作表重新命名为测试2$objphpexcel->getactivesheet()->gettabcolor()->setargb( 'ff0094ff'); //设置标签颜色
添加或删除行和列 锚：bbb$objphpexcel->getactivesheet()->insertnewrowbefore(6, 10); //在行6前添加10行$objphpexcel->getactivesheet()->removerow(6, 10); //从第6行往后删去10行$objphpexcel->getactivesheet()->insertnewcolumnbefore( 'e', 5); //从第e列前添加5类$objphpexcel->getactivesheet()->removecolumn( 'e', 5); //从e列开始往后删去5列
隐藏和显示某列 锚：bbb$objphpexcel->getactivesheet()->getcolumndimension( 'c')->setvisible(false); //隐藏$objphpexcel->getactivesheet()->getcolumndimension( 'd')->setvisible(true); //显示
重新命名活动的表的标签名称 锚：bbb$objphpexcel->getactivesheet()->settitle( 'invoice');
设置工作表的安全$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);
设置文档安全 锚：bbb$objphpexcel->getsecurity()->setlockwindows( true);$objphpexcel->getsecurity()->setlockstructure( true);$objphpexcel->getsecurity()->setworkbookpassword( phpexcel); //设置密码
样式复制 锚：bbb//将b2的样式复制到b3至b7$objphpexcel->getactivesheet()->duplicateconditionalstyle( $objphpexcel->getactivesheet()->getstyle( 'b2')->getconditionalstyles(), 'b3:b7' );
add conditional formatting 锚：bbbecho date('h:i:s' ) , add conditional formatting , php_eol;$objconditional1 = new phpexcel_style_conditional ();$objconditional1->setconditiontype(phpexcel_style_conditional ::condition_cellis );$objconditional1->setoperatortype(phpexcel_style_conditional ::operator_between );$objconditional1->addcondition('200');$objconditional1->addcondition('400');
设置分页（主要用于打印） 锚：bbb//设置某单元格为页尾$objphpexcel->getactivesheet()->setbreak( 'a' . $i, phpexcel_worksheet::break_row );
用数组填充表 锚：bbb//吧数组的内容从a2开始填充$dataarray = array( array(2010 , q1, united states, 790), array(2010 , q2, united states, 730), );$objphpexcel->getactivesheet()->fromarray($dataarray, null, 'a2');
设置自动筛选 锚：bbb$objphpexcel->getactivesheet()->setautofilter($objphpexcel->getactivesheet()->calculateworksheetdimension());//$objphpexcel->getactivesheet()->calculateworksheetdimension()....得到a1行的所有内容个
打印出的到所有的公式$objcalc =phpexcel_calculation::getinstance();print_r($objcalc->listfunctionnames())
设置单元格值的范围 锚：bbb$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->setshowdropdown(true);$objvalidation->seterror('only numbers between 10 and 20 are allowed!'); //错误内容$objvalidation->setprompttitle('allowed input'); //设置提示标题$objvalidation->setprompt('only numbers between 10 and 20 are allowed.'); //提示内容$objvalidation->setformula1(10); //设置最大值$objvalidation->setformula2(120); //设置最小值//或者这样设置 $objvalidation->setformula2(1,5,6,7); 设置值是1，5，6，7中的一个数
其他$objphpexcel->getactivesheet()->getstyle( 'b5')->getalignment()->setshrinktofit(true); //长度不够显示的时候 是否自动换行$objphpexcel->getactivesheet()->getstyle( 'b5')->getalignment()->setshrinktofit(true); //自动转换显示字体大小,使内容能够显示$objphpexcel->getactivesheet()->getcell(b14)->getvalue(); //获得值，有可能得到的是公式$objphpexcel->getactivesheet()->getcell(b14)->getcalculatedvalue();//获得算出的值
导入或读取文件//通过phpexcel_iofactory::load方法来载入一个文件，load会自动判断文件的后缀名来导入相应的处理类，读取格式保含xlsx/xls/xlsm/ods/slk/csv/xml/gnumericrequire_once '../classes/phpexcel/iofactory.php';$objphpexcel =phpexcel_iofactory::load(//吧载入的文件默认表（一般都是第一个）通过toarray方法来返回一个多维数组$dataarray = $objphpexcel->getactivesheet()->toarray();//读完直接写到一个xlsx文件里$objwriter =phpexcel_iofactory::createwriter($objphpexcel, 'excel2007'); //$objphpexcel是上文中读的资源$objwriter->save(str_replace('.php', '.xlsx', __file__));
读取xml文件$objreader =phpexcel_iofactory:: createreader('excel2003xml' );$objphpexcel = $objreader->load( excel2003xmltest.xml );读取ods文件$objreader =phpexcel_iofactory:: createreader('oocalc' );$objphpexcel = ->load(oocalctest.ods );读取numeric文件$objreader =phpexcel_iofactory:: createreader('gnumeric' );$objphpexcel = $objreader->load( gnumerictest.gnumeric );读取slk文件$objphpexcel =phpexcel_iofactory:: load(sylktest.slk );
循环遍历数据$objreader =phpexcel_iofactory::createreader('excel2007' ); //创建一个2007的读取对象$objphpexcel = $objreader->load (05featuredemo.xlsx ); //读取一个xlsx文件foreach ($objphpexcel->getworksheetiterator() as $worksheet) { //遍历工作表 echo 'worksheet - ' , $worksheet->gettitle() , php_eol; foreach ($worksheet->getrowiterator() as $row) { //遍历行 echo ' row number - ' , $row->getrowindex() , php_eol; $celliterator = $row->getcelliterator(); //得到所有列 $celliterator->setiterateonlyexistingcells( false); // loop all cells, even if it is not set foreach ($celliterator as $cell) { //遍历列 if (!is_null($cell)) { //如果列不给空就得到它的坐标和计算的值 echo ' cell - ' , $cell->getcoordinate() , ' - ' , $cell->getcalculatedvalue() , php_eol; } } }}
吧数组插入的表中//插入的数据 3行数据