Phpexcel common methods "with PHP export Excel plus hyperlinks" _php tutorial

The development Package tests directory has a detailed usage instance
Support Chinese, note file encoding, save file as Utf-8

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 added
$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 formatting
$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 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 Set 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 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 set 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 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 (10);
$objValidation->SETFORMULA2 (20);
$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 ');

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

Author: Qee

http://www.bkjia.com/PHPjc/478084.html www.bkjia.com true http://www.bkjia.com/PHPjc/478084.html techarticle Development Package Tests directory has detailed use instance support Chinese, note file encoding, file saved as Utf-8 write Excel//include class require_once (classes/phpexcel.php); require_once (classes/php ...

