1.header
header(content-type:application/vnd.ms-excel);header(content-disposition:attachment;filename=sample.xls);header(pragma:no-cache);header(expires:0);
2.phpexcel
http://www.codeplex.com/phpexcel
http://www.phpexcel.net
http://phpexcel.codeplex.com/
开发包tests目录有详细使用实例  支持中文,注意文件编码   文件保存为utf-8
写excel
//include classrequire_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;$igetactivesheet()->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__));
读excel //include classrequire_once('classes/phpexcel/reader/excel2007.php');$objreader = new phpexcel_reader_excel2007;$objphpexcel = $objreader->load(05featuredemo.xlsx);
读写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__));
写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__));
写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 usageecho date('h:i:s') . peak memory usage: . (memory_get_peak_usage(true) / 1024 / 1024) . mb\r\n;