Phpexcel exporting an Excel plus hyperlink instance code [go]

Phpexcel implements the code that exports the Excel file, and you can add hyperlinks to the Excel file.

Description: Phpexcel's development Package tests directory has a detailed usage instance.
The following code supports Chinese, note the file encoding, the file is saved as Utf-8.

Full code:

code example:

<?php
Write Excel

Include class
Require_once (' classes/phpexcel.php ');
Require_once (' classes/phpexcel/writer/excel2007.php ');
$objPHPExcel = new Phpexcel ();

/**
* Phpexcel Export Excel
* by www.jbxue.com
*/

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 sets the 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 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 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 add 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 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 and 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 (); Www.jbxue.com
$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";

Phpexcel exporting an Excel plus hyperlink instance code [go]