Phpexcel exporting an Excel plus hyperlink instance code

Write Excel Include class Require_once (' classes/phpexcel.php '); Require_once (' classes/phpexcel/writer/excel2007.php '); $objPHPExcel = new Phpexcel (); /** * Phpexcel Export Excel * by bbs.it-home.org */ 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 ');//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! '); Cycle for ($i = 1; $i <200; $i + +) { $objPHPExcel->getactivesheet ()->setcellvalue (' A '. $i, $i); $objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, ' Test value '); } Date formatting $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 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 detached cells $objPHPExcel->getactivesheet ()->mergecells (' a18:e22′); $objPHPExcel->getactivesheet ()->unmergecells (' a18:e22′); Protect Cells Cell protection $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 numeric formatting $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 setting columns width $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 setting 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 setting 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 setting padding $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 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, 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 Filtering $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 Setup worksheet 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 calculation 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 Validate input values $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 and allowed. '); $objValidation->setformula1 (10); $objValidation->SETFORMULA2 (20); $objPHPExcel->getactivesheet ()->getcell (' b3′)->setdatavalidation ($objValidation); Create a new worksheet, after the default sheet, creates a work label $objPHPExcel->createsheet (); bbs.it-home.org $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::o Rientation_landscape); $objPHPExcel->getactivesheet ()->getpagesetup ()->setpapersize (phpexcel_worksheet_pagesetup::P apersize_ A4); Rename Sheet Renaming sheet tabs $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 Require_once (' classes/phpexcel/writer/excel5.php '); $objWriter = new Phpexcel_writer_excel5 ($objPHPExcel); $objWriter->save (Str_replace ('. php ', '. xls ', __file__)); 1.6.2 New Save Require_once (' classes/phpexcel/iofactory.php '); $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' excel2007′ '); $objWriter->save (Str_replace ('. php ', '. xls ', __file__)); Read Excel Include class Require_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 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 reading $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 = 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 usage echo Date (' H:i:s '). "Peak Memory Usage:". (Memory_get_peak_usage (TRUE)/1024/1024). "Mb\r\n"; Copy Code

