PHP Operation Excel–phpexcel Basic usage detailed _php tutorial

1.header
[PHP]
Header ("Content-type:application/vnd.ms-excel");
Header ("Content-disposition:attachment;filename=sample.xls");
Header ("Pragma:no-cache");
Header ("expires:0");
2.PHPExcel
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

Write Excel
[PHP]
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 ');//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-&GT;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
[PHP]
Include class
Require_once (' classes/phpexcel/reader/excel2007.php ');
$objReader = new phpexcel_reader_excel2007;
$objPHPExcel = $objReader->load ("05featuredemo.xlsx");

Read and write CSV
[PHP]
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 write
$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
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcelFromCSV);
$objWriter 2007->save (Str_replace ('. php ', '. xlsx ', __file__));

Write HTML
[PHP]
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
[PHP]
Require_once ("05featuredemo.inc.php");
Require_once (' classes/phpexcel/iofactory.php ');
Write to PDF format www.2cto.com
$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)

http://www.bkjia.com/PHPjc/477393.html www.bkjia.com true http://www.bkjia.com/PHPjc/477393.html techarticle 1.header [PHP] Header (content-type:application/vnd.ms-excel); Header (content-disposition:attachment;filename= Sample.xls); Header (Pragma:no-cache); Header (expires:0); 2.PHPExcel htt ...

