1.header
[php]
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
开发包tests目录有详细使用实例  支持中文,注意文件编码   文件保存为utf-8
写excel
[php]
//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$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__));
读excel
[php]
//include class
require_once('classes/phpexcel/reader/excel2007.php');
$objreader = new phpexcel_reader_excel2007;
$objphpexcel = $objreader->load(05featuredemo.xlsx);
读写csv
[php]
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
[php]
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
[php]
require_once(05featuredemo.inc.php);
require_once('classes/phpexcel/iofactory.php');
//write to pdf format www.2cto.com
$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)
http://www.bkjia.com/phpjc/477393.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/477393.htmltecharticle1.header [php] header(content-type:application/vnd.ms-excel); header(content-disposition:attachment;filename=sample.xls); header(pragma:no-cache); header(expires:0); 2.phpexcel htt...