Using Phpexcel to manipulate Excel usage examples to analyze _php techniques

An example of this article is an analysis 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
Development Package tests directory has a detailed use of examples to support Chinese, note that the file encoding file saved as Utf-8

1.header section:

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 setting file Attributes $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 ');//You can specify a 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 style $objRichText = new Phpexcel_richtext ($objPHPExcel->getactivesheet ()->getcell (' 
A18 ')); 
$objRichText->createtext (' This invoice is '); 
$objPayable = $objRichText->createtextrun (' payable within thirty 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 merged separate cells $objPHPExcel->getactivesheet ()->mergecells (' A18:e22 '); 
 
$objPHPExcel->getactivesheet ()->unmergecells (' A18:e22 '); Protect cells Protection Cell $objPHPExcel->Getactivesheet ()->getprotection ()->setsheet (true);//needs to is set to true in order to enable any worksheet protect 
ion! 
 
$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 width widths $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true); 
 
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (12); 
Set Fonts font $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 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 the 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 The 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 sets the fill $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (phpexcel_ 
Style_fill::fill_solid); $objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ("->setargb") 
 
; 
Add a hyperlink to the sheet 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 adding a 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, 1 
0); 
$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 Filtration $objPHPExcel->getactivesheet ()->setautofilter (' a1:c9 '); Hide "Phone" and "Fax" column hidden 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 Setting 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 calculates 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 in top $objPHPExcel->getactivesheet ()->getpagesetup ()->setrowstorepeatattopbystartandend ( 
 
1, 1); The Set data validation validates the 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 are allowed. ');
$objValidation->setformula1 (10);
$objValidation-&GT;SETFORMULA2 (20);
 
$objPHPExcel->getactivesheet ()->getcell (' B3 ')->setdatavalidation ($objValidation);
Create a new worksheet, after the default sheet creates a job tag $objPHPExcel->createsheet ();
 
$objPHPExcel->setactivesheetindex (1); The Set header and footer. When no different headers for odd/even are used, odd the header is assumed. Header and 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 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 "sheet", so Excel opens this as the $objPHPExcel->setactivesheetindex (0
 
);
Save Excel 2007 File $objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);
 
$objWriter->save (Str_replace ('. php ', '. xlsx ', __file__)); 
Save Excel 5 file Saves require_once (' classes/phpexcel/writer/excel5.php '); 
$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);
 
$objWriter->save (Str_replace ('. php ', '. xls ', __file__)); 1.6.2 New Edition 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 $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 reads $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 $objWriter 2007 = 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 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 usage
echo date (' H:i:s '). Peak memory Usage: ". (Memory_get_peak_usage (TRUE)/1024/1024). " Mb\r\n ";

I hope this article will help you with your PHP program design.