PHP Operation excel–phpexcel Basic usage-source diandian_520

The following article source: diandian_520

http://blog.csdn.net/diandian_520/article/details/7827038

1.header

Header ("Content-type:application/vnd.ms-excel");  Header ("Content-disposition:attachment;filename=sample.xls");  Header ("Pragma:no-cache");  

2.PHPExcel

The development Package tests directory has the detailed use instance support Chinese, note the file encoding file saves as Utf-8

Write Excel

Include classrequire_once (' classes/phpexcel.php '); require_once (' classes/phpexcel/writer/excel2007.php '); $ Objphpexcel = new Phpexcel ();//set Properties Settings File Property $objphpexcel->getproperties ()->setcreator ("Maarten Balliauw "), $objPHPExcel->getproperties ()->setlastmodifiedby (" Maarten Balliauw "); $objPHPExcel GetProperties ()->settitle ("Office" XLSX Test Document "), $objPHPExcel->getproperties ()->setsubject (" Office "XLSX Test Document"); $objPHPExcel->getproperties ()->setdescription ("Test Document for Office 2007 XLSX, generated using PHP classes. "); $objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP"), $objPHPExcel->getproperties () Setcategory ("Test result file");//add some data adds $objphpexcel->setactivesheetindex (0); $objPHPExcel Getactivesheet ()->setcellvalue (' A1 ', ' Hello ');//You can specify Location $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! '); /loop for ($i = 1; $i <200; $i + +) {$objPHPExcel->getactivesheet ()->setcellvalue (' A '. $i, $i); $objPHPExcel Getactivesheet ()->setcellvalue (' B '. $i, ' Test value ');} Date format $objphpexcel->getactivesheet ()->setcellvalue (' D1 ', Time ()), $objPHPExcel->getactivesheet () GetStyle (' D1 ')->getnumberformat ()->setformatcode (Phpexcel_style_numberformat::format_date_yyyymmddslash) ;//add Comment 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 Add text to set the style $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 Merge separate cells $objphpexcel->getactivesheet ()->mergecells (' A18:e22 '); $objPHPExcel->getactivesheet ( )->unmergecells (' A18:e22 ');//protect cells Protection Cell $objphpexcel->getactivesheet ()->getprotection () Setsheet (true);//needs to BES set to true in order to enable any Workshprotection! $objPHPExcel->getactivesheet ()->protectcells (' A3:e13 ', ' phpexcel ');//set cell number formats Digital format $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 sets the width of columns $objphpexcel-> Getactivesheet ()->getcolumndimension (' B ')->setautosize (true); $objPHPExcel->getactivesheet () Getcolumndimension (' D ')->setwidth ()//set fonts set Font $objphpexcel->getactivesheet ()->getstyle (' B1 ')- >getfont ()->setname (' Candara '); $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont () SetSize, $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 Set the alignment $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 Set column Border $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 Set Border color $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 set fill $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 Add Link $objphpexcel->getactivesheet ()->setcellvalue (' E26 ', ' Www.phpexcel.net '), $objPHPExcel->getactivesheet ()->getcell (' E26 ')->gethyperlink (' 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 add picture $objdrawing = new phpexcel_worksheet_ Drawing (), $objDrawing->setname (' logo '), $objDrawing->setdescription (' logo '), $objDrawing->setpath ('./ Images/officelogo.jpg '), $objDrawing->setheight, $objDrawing->setcoordinates (' B15 '); $objDrawing Setoffsetx, $objDrawing->setrotation, $objDrawing->getshadow ()->setvisible (true); $objDrawing- >getshadow ()->setdirection, $objDrawing->setworksheet ($objPHPExcel->getactivesheet ());//play around with inserting and removing rows and Columns$objphpexcel->getactivesheet ()->insertnewrowbefore (6, ten); $objPHPExcel Getactivesheet ()->removerow (6), $objPHPExcel->getactivesheet ()->insertnewcolumnbefore (' E ', 5); $ Objphpexcel->getactivesheet ()->removecolumn (' E ', 5);//add Conditional formatting$objconditional1 = new Phpexcel_style_conditional (); $objConditional 1->setconditiontype (Phpexcel_style_conditional::condition_ Cellis); $objConditional 1->setoperatortype (Phpexcel_style_conditional::operator_lessthan); $objConditional 1- >setcondition (' 0 '); $objConditional 1->getstyle ()->getfont ()->getcolor ()->setargb (phpexcel_style_ color::color_red); $objConditional 1->getstyle ()->getfont ()->setbold (true);//set AutoFilter Auto Filter $ Objphpexcel->getactivesheet ()->setautofilter (' a1:c9 '),//hide "Phone" and "Fax" column hidden columns $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 Set sheet safety $objphpexcel->getactivesheet ()->getprotection () ->setpassword (' Phpexcel '); $objPHPExcel->getactivesheet ()->getprotection ()->setsheet (TRUE);//This Should is 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 computes 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 verify input value $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 '); $objValidationSetprompt (' Only numbers between and allowed. '); $objValidation->setformula1, $objValidation->setformula2, $objPHPExcel->getactivesheet () Getcell (' B3 ')->setdatavalidation ($objValidation);//create a new worksheet, after the default sheet create a work label for $ Objphpexcel->createsheet (); $objPHPExcel->setactivesheetindex (1);//set header and footer. When no different headers to Odd/even is used, odd header is assumed. Header Footer $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 direction sizes $objphpexcel->getactivesheet ()->getpagesetup ( )->setorientation (Phpexcel_worksheet_pagesetup::orientation_landscape), $objPHPExcel->getactivesheet ()- >getpagesetup ()->setpapersize (phpexcel_worksheet_PageSetup::P apersize_a4);//rename Sheet Rename sheet tab $objphpexcel->getactivesheet ()->settitle (' simple ');//set Active sheet index to the first sheet, so Excel opens this as the first sheet$objphpexcel->setactivesheetindex (0);//sav E Excel File Save $objwriter = new phpexcel_writer_excel2007 ($objPHPExcel); $objWriter->save (Str_replace ('. php ', ' . xlsx ', __file__));  //save Excel 5 FILE Save require_once (' classes/phpexcel/writer/excel5.php '); $objWriter = new Phpexcel_writer_excel5 ($objPHPExcel); $objWriter->save (Str_replace ('. php ', '. xls ', __file__));// 1.6.2 New version Save require_once (' classes/phpexcel/iofactory.php '); $objWriter = Phpexcel_iofactory::createwriter ($ Objphpexcel, ' Excel2007 '); $objWriter->save (Str_replace ('. php ', '. xls ', __file__));

　　

Read Excel

Include classrequire_once (' classes/phpexcel/reader/excel2007.php '); $objReader = new phpexcel_reader_excel2007;$ Objphpexcel = $objReader->load ("05featuredemo.xlsx");

　　

Read/write 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 Write $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 read $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); $objWriter 2007->save (Str_replace ('. php ', '. Xlsx ', __file__));

　　

Write 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__));

　　

Write a 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";

　　

PHP Operation excel–phpexcel Basic usage-source diandian_520