Use Phpexcel to manipulate Excel usage instance analysis, phpexcel instance Analysis _php Tutorial

Use Phpexcel to manipulate Excel usage instance analysis, phpexcel instance analysis

This article provides an example of using Phpexcel to manipulate Excel usage. Share to everyone for your reference. The specific analysis is as follows:

Phpexcel Download Address:
Http://www.codeplex.com/PHPExcel
Http://www.phpexcel.net
The development Package tests directory has the detailed use instance support Chinese, note the file encoding file saves as Utf-8

1.header part:

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

2. Write Excel:

Include class require_once (' classes/phpexcel.php '); Require_once (' classes/phpexcel/writer/excel2007.php ');  $objPHPExcel = new Phpexcel (); Set properties settings file Attributes $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-XLSX, generated using PHP classes."); $objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP");  $objPHPExcel->getproperties ()->setcategory ("Test result file"); Add some data $objPHPExcel->setactivesheetindex (0); $objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' Hello ');//You can specify the 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 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 adds 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 merges 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 worksheet protection!  $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 the columns $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true);  $objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (12); Set Fonts font $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont ('->setname '); $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 Sets 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 sets the columns 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 setting 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 settings fill $objPHPExcelGetactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (phpexcel_style_fill::fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 ')  ; Add a hyperlink to the sheet Added link $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 added picture $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 $objConditional 1 = 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 automatic 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 Sets the worksheet safe $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 calculates the 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 '); $objValidation->setprompt (' only numbers between Allowed. '); $objValidation->setformula1, $objValidation->setformula2, $objPHPExcel->getactivesheet () Getcell (' B3 ')->setdatavalidation ($objValidation); Create a new worksheet, after the default sheet creates a work label $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 '); The Set page orientation and size direction size $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) ; Save Excel file Saved $objWriter = new phpexcel_writer_excel2007 ($objPHPExcel); $objWriter->save (Str_replace ('. php ', '. xlsx ', __file__)); Save Excel 5 file Saving 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__));

3. Read Excel

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

4. Read and 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 writes $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__));

5. 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__));

6. 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 ";

I hope this article is helpful to everyone's PHP programming.

http://www.bkjia.com/PHPjc/974676.html www.bkjia.com true http://www.bkjia.com/PHPjc/974676.html techarticle use Phpexcel to manipulate Excel usage instance analysis, phpexcel instance analysis This article analyzes the use of Phpexcel to manipulate Excel usage. Share to everyone for your reference. The specific analysis is as follows: PHP ...

